PostgreSQL backup with pg_dump and pg_restore

The pg_dump and pg_restore command line tools are used to export and import PostgreSQL databases. They create PostgreSQL backups and migrate PostgreSQL databases between servers.

A PostgreSQL dump is the output file which is created when exporting a PostgreSQL database. PostgreSQL is a sophisticated database management system which stores data in optimised data structures. Therefore, extracting structured data from a PostgreSQL database requires a special procedure.

The pg_dump tool creates a text file with SQL commands, similar to MySQL backup with MySQL dump. Running the commands will restore the database to the time of the dump. According to the official PostgreSQL documentation:

It is important to understand what `PostgreSQL database´ means. The term is often confused with PostgreSQL server. In fact, it is not uncommon for a single PostgreSQL server to contain multiple databases. Below is an overview of the hierarchy of objects in a PostgreSQL installation:

The command line tools pg_dump and pg_restore are usually installed with the PostgreSQL client applications, as well as the PostgreSQL command line interface psql. The tools follow the Unix philosophy and use text streams for input and output. This allows them to be linked to other programs through pipes. Input and output can also be read from files or output to files using redirections.

We show the general pattern when using pg_dump to create a PostgreSQL dump:

We use a redirection of the output (“>”) to a file. The PostgreSQL dump generated from this contains SQL commands. These can be run with the psql tool. We’ll show the general pattern when using psql to read a PostgreSQL dump:

As you can see, the command is similar to the command for pg_dump. However, the input (“<”) from the PostgreSQL dump file must be redirected.

The pg_dump tool can do a lot more. It is also possible to output databases in special dump formats. However, the pg_restore or psql tool is needed to restore them depending on the format of the generated PostgreSQL dump.

Below you will find an overview of the command line tools used for creating and restoring PostgreSQL backups:

Let’s take a look at the pattern when using pg_restore for a PostgreSQL dump:

The PostgreSQL dump must be created in a special format for pg_restore. Below are the possible output formats for pg_dump:

Finally, the pattern for pg_dump used when creating a PostgreSQL dump in a `custom´ dump format is:

There are several different ways to create and restore PostgreSQL backups. The methods offer different advantages and disadvantages, so it depends on the deployment scenario and requirements. The procedure used when creating the PostgreSQL dump will determine which method should be used during import.

A quick note before we get into the specific methods of creating and restoring PostgreSQL dumps; The examples below simply reference the name of the database used. However, they do not include database usernames or passwords. These are stored in the .pgpass password file following the PostgreSQL convention. This file can be found in the user’s home directory and contains PostgreSQL connection data. The following format is used:

The information contained in the password file is automatically used when the command line tools are enabled. This eliminates the risk of entering any sensitive data on the command line.

Firstly, you must check if pg_dump and pg_restore are installed. Try to enable the tools to display their version. If this fails, then the tool is not on your system and must be installed.

• Verify that pg_dump is installed:

• Verify that pg_restore is installed:

• Furthermore, check if the PostgreSQL command line interface psql is installed:

The tools can be easily installed if they are not found.

• Install the PostgreSQL client applications using Homebrew on Mac:

• Use the built-in package management under Ubuntu-Linux:

• Follow our instructions to install PostgreSQL on Windows Server 2016 on Windows.

Firstly, let’s look at the simplest way to create a PostgreSQL backup. We extract a single database from a PostgreSQL server. The structure and contents of the database are written on a file in SQL commands. Enabling the pg_dump tool will look like this:

But what if you want to restore the PostgreSQL dump to another server? The PostgreSQL command line interface psql is used. The command is very simple:

The pg_dump tool allows users to create specialised PostgreSQL dump formats, other than the output of a PostgreSQL dump as a text file with SQL commands. These are controlled through options when they are enabled. Below is an overview of the two most useful dump formats:

Now, PostgreSQL dump in a custom format must be created:

Use the pg_restore tool to restore to another server:

An additional step is required to restore the PostgreSQL dump to the same server, as the database and tables already exist on the server and must be removed before importing. This is similar to '--add-drop-tables' for MySQL dump. PostgreSQL conveniently allows you to add the functionality during import:

The '—clean' option removes the existing database before the import. The '--create' option creates the database under the specified name. This allows the import to run without problems.

Export and import of a PostgreSQL dump can be connected with a pipe (‘|’). This exports the data directly into the import. It is possible to import the export from one server directly to another server, since pg_dump, pg_restore, and psql operate on a remote host when needed. Let’s take a look at the command used for this. Use the '—host' option to specify the host names:

PostgreSQL is a professional database management system. A special procedure is needed to create backups of large databases, as PostgreSQL dumps can be very large. Firstly, compression is advised. PostgreSQL dumps exported as text files usually contain large amounts of redundant SQL commands which can be easily compressed.

Pipe the output of pg_dump to the Gzip compression tool and write it as a compressed .gz file:

Reverse the process to recover from a compressed PostgreSQL dump. The gunzip tool unpacks the compressed data and outputs it to standard output. Pipe the output to the psql tool and use the '-c' option to ensure the input file is unaffected when unpacking:

The 3 2 1 backup rule requires at least one backup in the cloud. Uploading very large files can be problematic under certain circumstances. It might make sense to split the PostgreSQL dump into multiple files using the split tool. A pipe should be used again to forward the output of pg_dump to split. We split the PostgreSQL dump into individual files with a maximum size of 1 GB in the example below:

How can the partial files created by split during import be merged again? No special software is needed for this, as the standard cat tool can be used. We forward a list of partial PostgreSQL dumps, which have been combined by the cat tool into a coherent data stream. Pipe this to psql as usual:

It is possible to dump multiple tables in parallel when using the directory dump format. This is faster, but it also leads to a higher load on the database server. We control the number of tables exported in parallel using the '—jobs' option. In our example, we export three tables in parallel. It is not possible to redirect the output to a file as we are writing a directory. We use the '—file' option instead with specification for the directory name: