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
You can use any method that you prefer to back up the file system. Possible options are described below.
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
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
pg_basebackup -D backup2022-12-22 -Ft
For more information on the pg_basebackup options, see pg_basebackup.
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.
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".
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.
NOTENote 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.