PostgreSQL QuickStart/Reference Commands...

Since I don't use PostgreSQL as often as MySQL, I tend to forget 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
    

Related Reading:

• PostgreSQL Server Administration