PG Dump & Restore
This scenario will go over using pg_dump to create a logical database backup and using pg_restore to recreate that backup.
This scenario also gives an example of setting up a database instance in a user's home directory to show how a postgres database can be created and run from anywhere on a system, without requiring root privileges. This can be extremely useful when restoring a backup for testing or in any emergency data recovery situation.
Let's get started!
PG Dump & Restore
There are two commands used to perform data dumps in PostgreSQL. The pg_hba.conf file has been configured in this environment to allow all the commands that are run to connect without requiring a password. For your own environment, you'll have to use whichever credentials allow you to connect and read the necessary information.
pg_dumpall, is very simplistic and only does plaintext dumps of the entire instance.
This is often not the most useful form of a dump since both the dump and the restore of the database are all or nothing and is entirely single-threaded. What it is useful for is dumping out data that is only cluster-wide, such as roles and tablespaces. In fact, this is the only way to dump out such data. The location of our dump output will be the home directory of our
pg_dumpall -g -f /home/training/globals.sql
-g option tells pg_dumpall to dump out only the cluster-wide data. In the case of this scenario, this will just be the roles. Run the following command the see the contents of this dump
Often a superuser role is required for this tool, especially when extracting global data.
We'll be restoring this in a secondary database in a later step. Next let's look at the command that is recommended to be used when dumping out actual databases.