There are two things might happen when we do the backup and restore database. We need to know whether the database is located in local server or in any separate server like RDS.

Here, I’d like to raise two ways of backup Postgres database. One is the local database, and other one is the separate database.

Local Database

Backup: pg_dump -n schema_name -d db_name > backup_filename.dump

Restore: psql db_name < backup_filename.dump

Host Database (Amazon RDS) (You will be prompted to type your database password.)

Backup: pg_dump -h db_host -p db_port -U db_user -n schema_name -d db_name > backup_filename.dump

Restore: psql db_name -h db_host -p db_port -U db_user < backup_filename.dump

You should notice that we need to specify database host, port and user if we have the database setup in a host server, Amazon RDS in my case. That is the important point that some developers might forget.

For further detail of the other backup and restore options, please refer to the PostgreSQL docs and look for database backup and that applies to the Postgres version you use.

Enjoy your DBA time!