Postgres – How to Dump, Restore, Create and Drop Databases

1. Postgres – Dump

pg_dump -p 5432 -U postgres --verbose --inserts \
        --format=plain --encoding=utf8 --verbose \
        --file DB_NAME.$(date +"%Y%m%d_%H%M").sql DB_NAME

Example

pg_dump -p 5432 -U postgres --verbose --inserts \
        --format=plain --encoding=utf8 --verbose \
        --file postgres.$(date +"%Y%m%d_%H%M").sql postgres

Dumped File: postgres.20130114_1045.sql

Note:
–inserts and –format=plain is not the fastest option for dumping databases and often leads to slower database imports. It is nonetheless a good option if the database is relatively small (< 1 million records) or if some records are corrupted. Alternative: --format=c. Dump a certain table

pg_dump -p 5432 -U postgres --verbose --inserts --format=plain \
        --encoding=utf8 --verbose -t TABLE_NAME \
        --file DB_NAME.$(date +"%Y%m%d_%H%M").sql DB_NAME

2. Postgres – Restore

pg_restore -p 5432 -v -d DB_NAME DB_NAME.sql

Redirect Logging to File

pg_restore -p 5432 -v -d DB_NAME \
           DB_NAME.$(date +"%Y%m%d_%H%M").sql \
           1> restore.debug.log 2> restore.error.log

Restore a single table

pg_restore -p 5432 --format=c -v -d DB_NAME \
           --table=TABLE_NAME DB_NAME.20130114_1030.sql

3. Create Databases

createdb -p 5432 -U postgres --encoding=utf8 -e  DB_NAME_$(date +"%u")

4. Drop Databases

dropdb   -p 5432 -U postgres -e DB_NAME_$(date +"%u")

5. Execute Query

psql -U postgres -d DB_NAME -c 'select 1;'

Example:

psql -U postgres -d postgres -c 'select 1;'

6. Drop Extensions

psql -U postgres -d DB_NAME -c 'DROP EXTENSION btree_gin;DROP EXTENSION btree_gist;DROP EXTENSION citext;'