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:
[code lang=”sql”]
# createdb testdb
[/code]
Remove a PostgreSQL database
# dropdb <dbname>
e.g:
[code lang=”sql”]
# dropdb testdb
[/code]

Backing up a PostgreSQL database
[code lang=”sql”]
# su – postgres
[/code]

[code lang=”sql”]
# pg_dump –blob -Fc testdb -f /var/lib/pgsql/backups/testdb_backup
[/code]

Restoring PostgreSQL database from back up dump
[code lang=”sql”]
# pg_restore –dbname=testdb /var/lib/pgsql/backups/testdb_backup
[/code]

Writing query output to a CSV file:
[code lang=”sql”]
# \o ‘tmp/logs/query_out_dump.csv’
[/code]

After this operation all the query results will be stored in a CSV file.
Using console again for query output:
[code lang=”sql”]
# \o
[/code]
For more on pg_dump and pg_restore pl. check the documentation

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.