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
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
-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.