PostgreSQL Backup/Restore or Export/Import or pg_dump-dbname outfile/psql-dbname-infile

1. Introduction

This post shows how to backup/restore (or Export/Import or pg_dump-dbname>outfile/psql-dbname<infile).

The cookbook is:
- Export dump file of source database
- Drop Target database
- Import dump file on target database

2. Step-by-Step

a) Export PostgreSQL dumpfile

$ pg_dumpall > pg_dumpall.psql

or

$ pg_dump -U postgres -d mydb > pg_dump_mydb.psql

b) Transfer file from source server to target server

$ scp ...


c) Drop target database

$ echo "*** Warning: Before execute following command, make sure you are executing this command on the target server ***"
$ dropdb mydb


d) Import PostgreSQL dumpfile 

$ psql -U USERNAME mydb < pg_dumpall.psql

3. References

• https://www.a2hosting.com/kb/developer-corner/postgresql/import-and-export-a-postgresql-database
• http://help.ninefold.com/getstarted/exporting_and_importing_postgresql_data/
• http://www.postgresql.org/docs/9.3/static/backup-dump.html
• http://www.postgresql.org/docs/9.3/static/app-pgrestore.html