Backup and restore of data in PostgreSQL

Backing up the data using pg_dump

Consider a remote postgres instance like AWS RDS. To backup the data that is currently present in a database in that RDS server, we can use the following command

pg_dump -U master_user -d blog_db -h dbhost.rds.amazonaws.com -vx -Fc > blog_export.pgsql

The options

v - Verbose output of the operation

x - Do not dump privileges. This makes sure that the statements such as grant/revoke are not added. The reason for using this is to avoid getting errors when certain roles being available in the source database and not available in the destination database.

-F - specifies format. The option -Fc means custom archive format.

We can take a back up of the data in the local by simply omitting the ‘h’ option

pg_dump -U postgres -d blogs -vx -Fc > dbexport.dump

Restoring the data using pg_restore

pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump. pg_restore works for one of the non-plain-text formats(like tar or dump or any custom format). pg_restore provides more options compared to simple psql based restore.

To restore from an archive file named blogs_export.dump, the below command can be used.

pg_restore -U master_user blogs.dump -vOCcex --if-exists --dbname=postgres -h prod.rds.amazonaws.com

Same command in the local postgres,

  pg_restore -U postgres sickfits.dump -vOCcex --if-exists --dbname=postgres

Options

-v - prints verbose output of the restore process

-O - To avoid generating commands that sets the ownership of objects to match the original database. If the pg_restore is started without this option and with a non-superuser, this will fail. Simply put, with this option any user can be used for the initial connection and that user will own the database objects.

-C - The option -C asks the pg_restore to create the database that is present in the dump.

-c - Drop the objects(such as the database) before recreating them

-e - exit if an error occurs

--if-exists - before any drop statements objects are checked for their existence before the drop command

--dbname - This option provides a default database to initially to connect to. Once the tool connects to a particular database, it will create a new database, connect to it and run the restore commands. If the option -C(–create) is not given then the database in the dump file is restored into the default database to which the pg_restore initially connects.