PostgreSQL Commands

Reference : http://www.linuxweblog.com/blogs/sandip/20101203/listing-directories-tree-format


Create db user and grant permission to create databases.

# adduser <dbuser>
# su - postgres
$ createuser -d -S -R <dbuser>

simple commands for administering the database. Hopefully these notes will help as reference when working with PostgreSQL:

1. Login as "postgres" (SuperUser) to start using database:
   

   # su - postgres

2. Create a new database:

   $ createdb mydb

3. Drop database:

   $ dropdb mydb

4. Access database:

   $ psql mydb

5. Get help:

   mydb=# \h

6. Quit:

   mydb=# \q

7. Read command from file:

   mydb=# \i input.sql

8. To dump a database:

   $ pg_dump mydb > db.out

9. To reload the database:

   $ psql -d database -f db.out

10. Dump all database:

    # su - postgres
    # pg_dumpall > /var/lib/pgsql/backups/dumpall.sql

11. Restore database:

    # su - postgres
    # psql -f /var/lib/pgsql/backups/dumpall.sql mydb

12. Show databases:

    #psql -l
    or
    mydb=# \l;

13. Show users:

    mydb=# SELECT * FROM "pg_user";

14. Show tables:

    mydb=# SELECT * FROM "pg_tables";

15. Set password:

    mydb=# UPDATE pg_shadow SET passwd = 'new_password' where usename = 'username';

16. Clean all databases (Should be done via a daily cron):

    $ vacuumdb --quiet --all

    DROP TABLE name [, ...] [ CASCADE ]
    DROP TABLE removes tables from the database. Only its owner may destroy a table. To empty a table of rows, without destroying the table, use DELETE.
    DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.
    
    
    
    
    
    Add the below line to /var/lib/pgsql/data/postgresql.conf to make postgresql database listen to external connections:
    

    listen_addresses = '*'

    Edit /var/lib/pgsql/data/pg_hba.conf and add the appropriate permissions:
    

    host all all 0.0.0.0/0 md5

     

     

     

     

    change postgresql database owner

    Submitted by sandip on Sat, 08/16/2008 - 00:58.

    template1=# ALTER DATABASE <dbname> OWNER TO <dbuser>;

     

     

     

     

     

     

    setup postgresql database to use passwords to connect

    Submitted by sandip on Thu, 08/07/2008 - 09:07.

    Edit "/var/lib/pgsql/data/pg_hba.conf" with:
    

    local   all       all                          md5
host    all       all       127.0.0.1/32       md5

    local - socket connection
    host - tcp connection
    To connect via socket:
    
    

    $ psql -U <username> -d <dbname>

    To connect via tcp:
    
    

    $ psql -U <username> -h <hostname> -d <dbname>

     

     

     

     

     

    password reset

    Submitted by sandip on Thu, 08/07/2008 - 08:57.

    template1=# ALTER USER <dbuser> WITH PASSWORD '<password>';

    »

    • reply

    Viewing owner and permissions

    Submitted by sandip on Wed, 08/06/2008 - 17:01.

    \d -- view the owner
\dp -- view permissions