Here is a list of commands used by me most frequently to manage importing/exporting databases in PostgreSql using console.

Feel free to enhance them by creating automatic scripts. Sample codes in comments are welcome :)

Full Backup selected database using pg_dump in PostreSQL

example usage:

pg_dump -i -h localhost -p 5432 -U postgres -F t -b -v -f "~/backups/backup-file.backup" database_name_to_backup

used arguments

-i, –ignore-version proceed even when server version mismatches. Useful while migrating data between servers.
-h localhost, host to connect, can be IP address
-p 5432, default port used by PostgreSQL
-U postgres, this is default username, make sure user has rights to backup database
-F t, -format used for backup, I suggest always using "t" (tar), other options are listed below
-b, –blobs include large objects in dump
-v, –verbose
-f "~/backups/test-database.backup", file where backup will be stored
database_name_to_backup, database name which we want to backup

in windows environment use pg_dump.exe instead

If you get the following error:

pg_dump: [tar archiver] could not open TOC file "~/backups/test-database.backup" for output: No such file or directory

There are 2 possible causes to this problem

• Change “~/backups/test-database.backup” to “/home/ec2-user/backups/test-database.backup”
• Create directory “/home/ec2-user/backups”, backup file is created automatically but directories are not!

This is just an example of a correct path to represent your home directory. ec2-user is default user for Amazon EC2 instances.

Complete help for pg_dump

To get a list of commands for pg_dump just type

pg_dump -?

-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode
-F, –format=c|t|p output file format (custom, tar, plain text)
-c, –clean clean (drop) schema prior to create
-b, –blobs include large objects in dump
-v, –verbose verbose mode
-f, –file=FILENAME output file name

Restore, load data using pg_restore

pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v "~/backups/backup-file.backup"

To get a list of commands for pg_restore just type

pg_restore -?



-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode