File system level backup

An alternative backup strategy is to directly copy the files that PostgreSQL uses to store data in the database. By default, ADPG places the database files at the following path: /pg_data1/adpg14. ADPG creates the adpg14 directory at the path specified in the Data directory parameter (see Configuration parameters). The default value of this parameter is /pg_data1.

You can use any method that you prefer to back up the file system. Possible options are described below.

Use pg_basebackup

You can use the pg_basebackup tool to take a base backup of a running PostgreSQL database cluster. The pg_basebackup tool creates backups without affecting other database clients.

The pg_basebackup utility creates an exact copy of the cluster files, automatically enabling and disabling backup mode. Backups are created for the entire cluster. You cannot make a copy of individual databases or database objects. For selective backups, use pg_dump as described in the SQL dump article.

The pg_basebackup tool makes backups over a regular PostgreSQL connection that uses the replication protocol. The connection must be made on behalf of a superuser or a user that has a permission to initiate a replication, and pg_hba.conf must allow the replication connection.

The following example creates backup of the server at the 10.92.6.36 host and stores it in the local directory /usr/local/pgsql/data:

pg_basebackup -h 10.92.6.36 -D /usr/local/pgsql/data

The code below backs up the local server with one compressed tar file for each tablespace and stores it in the directory backup2022-12-22:

pg_basebackup -D backup2022-12-22 -Ft

For more information on the pg_basebackup options, see pg_basebackup.

Create an archive file

It is also possible to create an archive file:

tar -cf backup20221215.tar /pg_data1/adpg14

This method has two restrictions:

  • The database server must be shut down. Use the Stop action for this. You also need to shut down the server before restoring the data.

  • It is impossible to back up or restore only certain individual tables or databases from their respective files or directories. This does not work because the information contained in these files is not usable without the commit log files, pg_xact/*, which contain the commit status of all transactions. A table file is only usable with this information. File system backups must be used for complete backup and restoration of an entire database cluster.

Make a snapshot

An alternative file-system backup approach is to make a consistent snapshot of the data directory, if the file system supports that functionality. The typical procedure is to make a "frozen snapshot" of the volume containing the database, then copy the whole data directory from the snapshot to a backup device, then release the "frozen snapshot". This works even while the database server is running. However, a backup created in this way saves the database files in a state as if the database server is not properly shut down. When you start the database server on the backed-up data, it operates as if the previous server instance crashes and replays the WAL log. Be sure to include the WAL files in your backup. You can execute the CHECKPOINT command before you take the snapshot to reduce recovery time.

If your database is spread across multiple file systems, there might not be any way to obtain exactly-simultaneous "frozen snapshots" of all the volumes. For example, if your data files and WAL log are on different disks, or if tablespaces are on different file systems, it might not be possible to use snapshot backup because the snapshots must be simultaneous. You can shut down the database server and make all the "frozen snapshots".

Use rsync

Another option is to use the rsync utility to perform a file system backup. Run rsync while the database server is running, then shut down the database server to execute rsync --checksum (--checksum is necessary because rsync only has the file modification-time granularity of one second). The second rsync is faster than the first because it needs to transfer less data. The result is consistent because the server is down. This method allows you to back up your file system with minimal downtime.

NOTE
Note that a file system backup is usually larger than an SQL dump. For example, pg_dump does not need to dump the contents of indexes, only the commands to recreate them. However, backing up the file system might be faster.
Found a mistake? Seleсt text and press Ctrl+Enter to report it