Postgresql 10 Backup & Restore

Let’s take a pg_dump!

I’m working on CentOS 7 and Postgresql 10, both fully patched at time of writing. That said, the process of backing up and restoring postgres databases is the same between the version 9.3 systems that I interact with.

First we have to connect to the server that is hosting the database instance that we want to backup. Here’s an example of the process one might go through.

1. Connect to VPN

I’m using vpnc to connect to a dedicated firewall (Adaptive Security Appliance), and I’ve designed a config file located in /etc/vpnc/ I can invoke with this command:

$ sudo vpnc rsvpn

If you’re using this approach, you should see a terminal response of:
VPNC started in background (pid: 6497)…

2. Connect to Server

I’ve created a config file locally ~/.ssh/config. Each entry in this file is structured like this:
Host <alphanumeric-name-of-db-server> (example: dbserver1)
HostName <numeric-IP-address> (example: 192.168.0.1)
User <your-Linux-username> (exmple: timbeach)
IdentityFile <local-path-to-private-ssh-key> (example: ~/.ssh/id_rsa)
For more info on how to get your ssh config on, check out this article:
Using SSH Keys to Log Into Remote Servers.

With a properly setup ssh config, you can connect with a simple command like:

$ ssh dbserver1

Which is awesome.. and this works because of the properly defined config for our example dbserver1. We could also connect without config using something like:
$ ssh linux-user@<ip-address-of-remote>

3. Get Root and Execute pg_dump

While connected to remote server, I will switch to root, then execute pg_dump as the postgres user the directory you want the dump file to be created in. I have /root/dump directories set up on all the servers I administer.

-- switch to root
$ sudo su -
-- execute the pg_dump
# pg_dump -U postgres -Fc database-instance-name > date-database-instance-name.dump

The dump file is amazing. This allows us to use pg_restore to restore this backup into another database instance.
The -Fc option in the above commands allows for this. Check out the Posgresql documentation for more details about this.

More Info About how to Form the pg_dump Command:

• pg_dump invokes the utility.
• -U postgres switches the command to be run as the postgres user.
• -Fc puts the resulting file into a format that can be restored from.
• database-instance-name is the exact name of the database instance you are backing up.
• > outputs the command to the singular dump file.
• date-database-instance-name.dump is my suggested format for taking the dump. An example would be 20180706-db1.dump
• The synopsis from the official documentation is:
  pg_dump [connection-option...] [option...] [dbname]

4. Create a New Blank Database to Restore the dump File into

Now we must create a new empty db to restore the dump into. First switch to the postgres user and invoke the psql interactive terminal.

-- switch to postgres user
# su - postgres
-- invoke interactive terminal
$ psql
-- create the new database
postgres-# CREATE DATABASE new_db;
-- We can verify creation by listing current DBs with:
postgres-# \l

At this point, we should have a new database called new_db that we can restore our dump file into. To do this, I recommend exiting the interactive psql terminal and executing as root.

-- exit psql
postgres=# \q
-bash-4.1$ exit
logout
-- execute pg_restore as root
# pg_restore -U postgres -d new_db /root/dumps/20180706-db1.dump

More Info About how to Form the pg_restore Command:

• pg_restore invokes the utility.
• -U postgres switches the command to be run as the postgres user.
• -d is used to define the target database instance. In this case, new_db.
• /root/dumps/20180706-db1.dump is used to define the path to the file we’re using to restore from for this example.
• The synopsis from the official documentation is:
  pg_restore [connection-option...] [option...] [filename]