# PostgreSQL backup with pg\_dump and pg\_restore

The pg\_dump and pg\_restore command line tools are used to **export and import [PostgreSQL](https://www.ionos.com/digitalguide/server/know-how/postgresql/) databases**. They create PostgreSQL backups and migrate PostgreSQL databases between servers.

## What is a PostgreSQL dump?

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 optimized data structures. Therefore, extracting structured data from a PostgreSQL database requires a special procedure.

---

### Note

[What is a backup?](https://www.ionos.com/digitalguide/server/security/what-is-a-backup/). We answer this basic question in our dedicated guide.

---

The pg\_dump tool creates a **text file with SQL commands**, similar to [MySQL backup with MySQL dump](https://www.ionos.com/digitalguide/server/security/mysql-backup/ "MySQL backup"). Running the commands will restore the database to the time of the dump. According to the official PostgreSQL documentation:

---

### Quote

> “The idea behind this dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump.”

---

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:

<table>
  <thead>
    <tr>
      <th>PostgreSQL Object</th>
      <th>Contains</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Server</td>
      <td>Databases</td>
    </tr>
    <tr>
      <td>Database</td>
      <td>Tables</td>
    </tr>
    <tr>
      <td>Table</td>
      <td>Records</td>
    </tr>
    <tr>
      <td>Record</td>
      <td>Fields</td>
    </tr>
  </tbody>
</table>

## How do pg\_dump and pg\_restore work?

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:

```none
pg_dump dbname > db.dump
```

We use a redirection of the output (“&gt;”) 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:

```none
psql dbname < db.dump
```

As you can see, the **command is similar to the command for pg\_dump**. However, the input (“&lt;”) 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**:

<table>
  <thead>
    <tr>
      <th>Tool</th>
      <th>Explanation</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>pg\_dump</td>
      <td>Command line tool to create a PostgreSQL dump</td>
    </tr>
    <tr>
      <td>pg\_restore</td>
      <td>Command line tool to restore a PostgreSQL database from a PostgreSQL dump; allows special operations like partial imports, re-sorting of import data, parallel import of multiple tables, etc.</td>
    </tr>
    <tr>
      <td>psql</td>
      <td>PostgreSQL command line interface; accepts SQL commands from the command line or from a PostgreSQL dump file and runs them</td>
    </tr>
  </tbody>
</table>

Let’s take a look at the **pattern when using pg\_restore** for a PostgreSQL dump:

```none
pg_restore --dbname=dbname db.dump
```

**The PostgreSQL dump must be created in a special format** for pg\_restore. Below are the possible output formats for pg\_dump:

<table>
  <thead>
    <tr>
      <th>pg\_dump output format</th>
      <th>Explanation</th>
      <th>Import via</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Plain</td>
      <td>Plain text file with SQL commands; compressing requires an additional tool, such as Gzip</td>
      <td>psql</td>
    </tr>
    <tr>
      <td>Custom</td>
      <td>Compressed dump format; import can be controlled in detail</td>
      <td>pg\_restore</td>
    </tr>
    <tr>
      <td>Directory</td>
      <td>Creates directory with one file per table/blob; table of contents; can be edited with standard Unix tools; allows parallel export of multiple tables</td>
      <td></td>
    </tr>
    <tr>
      <td>Tar</td>
      <td>Archiving format “Tape Archive”; can be converted to directory format; compressing requires additional tool, such as Gzip; not possible to control sequence of imports</td>
      <td>pg\_restore</td>
    </tr>
  </tbody>
</table>

Finally, the pattern for pg\_dump used when **creating a PostgreSQL dump in a “custom” dump format is**:

```none
pg_dump --format=custom dbname > db.dump
```

---

### Tip

If your PostgreSQL installation is running in a Docker container, you can use pg\_dump inside the container to create a PostgreSQL backup. You can also save the entire container as [Docker backup](https://www.ionos.com/digitalguide/server/security/docker-backup/ "Docker backup"). We explain how to do this in detail in our article

---

## Step by step guide: Create and restore a PostgreSQL backup

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:

```none
hostname:port:database:username:password
```

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.

### Check if the tools are available and install them if necessary

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:

```none
pg_dump --version
```

- Verify that pg\_restore is installed:

```none
pg_restore --version
```

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

```none
psql --version
```

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

- Install the PostgreSQL client applications using Homebrew on **Mac**:

```none
brew install libpq
brew link --force libpq
```

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

```none
sudo apt-get install postgresql-client
```

- Follow our instructions to [install PostgreSQL on Windows Server 2016](https://www.ionos.com/digitalguide/server/configuration/install-postgresql-on-windows-server-2016/) on **Windows**.

### Create and restore PostgreSQL backup

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:

```none
pg_dump dbname > db.dump
```

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:

```none
psql dbname < db.dump
```

The pg\_dump tool allows users to **create specialized 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:

<table>
  <thead>
    <tr>
      <th>PostgreSQL dump format</th>
      <th>Detailed options syntax</th>
      <th>Short options syntax</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Custom</td>
      <td>pg\_dump --format=custom</td>
      <td>pg\_dump -Fc</td>
    </tr>
    <tr>
      <td>Directory</td>
      <td>pg\_dump --format=directory</td>
      <td>pg\_dump -Fd</td>
    </tr>
  </tbody>
</table>

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

```none
pg_dump --format=custom dbname > db.dump
```

Use the pg\_restore tool to **restore to another server**:

```none
pg_restore --dbname=dbname db.dump
```

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:

```none
pg_restore --clean --create --dbname=dbname db.dump
```

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.

### Migrate PostgreSQL database between remote servers

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:

```none
pg_dump --host=export_host dbname | psql --host=import_host dbname
```

### Create PostgreSQL backup of large databases

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*:

```none
pg_dump dbname | gzip > db.dump.gz
```

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:

```none
gunzip -c db.dump.gz | psql dbname
```

The [3 2 1 backup rule](https://www.ionos.com/digitalguide/server/security/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:

```none
pg_dump dbname | split -b 1G - db.dump
```

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:

```none
cat db.dump* | psql dbname
```

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:

```none
pg_dump --jobs=3 --format=directory --file=dump.dir dbname
```


This is a markdown version of: [https://www.ionos.com/digitalguide/server/security/postgresql-backup/](https://www.ionos.com/digitalguide/server/security/postgresql-backup/) for AI/LLM consumption.