PostgreSQL useful tips

I am posting some of the PostgreSQL commands which I use frequently.

Create a new database name testdb

# createdb <dbname>
e.g:


# createdb testdb

Remove a PostgreSQL database
# dropdb <dbname>
e.g:

# dropdb testdb

Backing up a PostgreSQL database


# su - postgres


# pg_dump --blob -Fc testdb -f /var/lib/pgsql/backups/testdb_backup

Restoring PostgreSQL database from back up dump


# pg_restore --dbname=testdb /var/lib/pgsql/backups/testdb_backup

Writing query output to a CSV file:


# \o 'tmp/logs/query_out_dump.csv'

After this operation all the query results will be stored in a CSV file.
Using console again for query output:


# \o

For more on pg_dump and pg_restore pl. check the documentation

Author: Pradeep Pant

Software Engineer

Leave a Reply

Your email address will not be published. Required fields are marked *