Post­gr­eSQL backup with pg_dump and pg_restore

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

A Post­gr­eSQL dump is the output file which is created when exporting a Post­gr­eSQL database. Post­gr­eSQL is a soph­ist­ic­ated database man­age­ment system which stores data in optimised data struc­tures. Therefore, ex­tract­ing struc­tured data from a Post­gr­eSQL 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 Post­gr­eSQL doc­u­ment­a­tion:

It is important to un­der­stand what `Post­gr­eSQL database´ means. The term is often confused with Post­gr­eSQL server. In fact, it is not uncommon for a single Post­gr­eSQL server to contain multiple databases. Below is an overview of the hierarchy of objects in a Post­gr­eSQL in­stall­a­tion:

The command line tools pg_dump and pg_restore are usually installed with the Post­gr­eSQL client ap­plic­a­tions, as well as the Post­gr­eSQL command line interface psql. The tools follow the Unix philo­sophy 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 re­dir­ec­tions.

We show the general pattern when using pg_dump to create a Post­gr­eSQL dump:

We use a re­dir­ec­tion of the output (“>”) to a file. The Post­gr­eSQL 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 Post­gr­eSQL dump:

As you can see, the command is similar to the command for pg_dump. However, the input (“<”) from the Post­gr­eSQL dump file must be re­dir­ec­ted.

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 Post­gr­eSQL dump.

Below you will find an overview of the command line tools used for creating and restoring Post­gr­eSQL backups:

Let’s take a look at the pattern when using pg_restore for a Post­gr­eSQL dump:

The Post­gr­eSQL 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 Post­gr­eSQL dump in a `custom´ dump format is:

There are several different ways to create and restore Post­gr­eSQL backups. The methods offer different ad­vant­ages and dis­ad­vant­ages, so it depends on the de­ploy­ment scenario and re­quire­ments. The procedure used when creating the Post­gr­eSQL dump will determine which method should be used during import.

A quick note before we get into the specific methods of creating and restoring Post­gr­eSQL 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 Post­gr­eSQL con­ven­tion. This file can be found in the user’s home directory and contains Post­gr­eSQL con­nec­tion data. The following format is used:

The in­form­a­tion contained in the password file is auto­mat­ic­ally used when the command line tools are enabled. This elim­in­ates 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:

• Fur­ther­more, check if the Post­gr­eSQL command line interface psql is installed:

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

• Install the Post­gr­eSQL client ap­plic­a­tions using Homebrew on Mac:

• Use the built-in package man­age­ment under Ubuntu-Linux:

• Follow our in­struc­tions to install Post­gr­eSQL on Windows Server 2016 on Windows.

Firstly, let’s look at the simplest way to create a Post­gr­eSQL backup. We extract a single database from a Post­gr­eSQL 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 Post­gr­eSQL dump to another server? The Post­gr­eSQL command line interface psql is used. The command is very simple:

The pg_dump tool allows users to create spe­cial­ised Post­gr­eSQL dump formats, other than the output of a Post­gr­eSQL dump as a text file with SQL commands. These are con­trolled through options when they are enabled. Below is an overview of the two most useful dump formats:

Now, Post­gr­eSQL dump in a custom format must be created:

Use the pg_restore tool to restore to another server:

An ad­di­tion­al step is required to restore the Post­gr­eSQL 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. Post­gr­eSQL con­veni­ently allows you to add the func­tion­al­ity 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 Post­gr­eSQL 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:

Post­gr­eSQL is a pro­fes­sion­al database man­age­ment system. A special procedure is needed to create backups of large databases, as Post­gr­eSQL dumps can be very large. Firstly, com­pres­sion is advised. Post­gr­eSQL dumps exported as text files usually contain large amounts of redundant SQL commands which can be easily com­pressed.

Pipe the output of pg_dump to the Gzip com­pres­sion tool and write it as a com­pressed .gz file:

Reverse the process to recover from a com­pressed Post­gr­eSQL dump. The gunzip tool unpacks the com­pressed 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 un­af­fected when unpacking:

The 3 2 1 backup rule requires at least one backup in the cloud. Uploading very large files can be prob­lem­at­ic under certain cir­cum­stances. It might make sense to split the Post­gr­eSQL 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 Post­gr­eSQL dump into in­di­vidu­al 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 Post­gr­eSQL 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 spe­cific­a­tion for the directory name: