Backup methods of Postgresql

There are multiple classification methods for database backup. According to the type of file after backup, it can be divided into physical backup (file system level backup) and logical backup (backup file is sql file or export file in a specific format); according to whether the database service is stopped during the backup process, it can be divided into Cold backup (the database service is stopped during the backup process) and hot backup (the database service is enabled during the backup process and can be accessed by users); according to whether the backup is a complete database, it can be divided into full backup (backup is a complete database) and incremental Backup (backup is what the database changed after the last full backup).

There are three common backup methods of Postgresql:

1. Cold backup at the file system level.

This backup method requires closing the database and then copying the complete directory of the data files. When restoring the database, simply copy the data directory to the original location. This method is rarely used in actual work.

2. SQL dump.

The tools we use here are pg_dump and pg_dumpall.

This method allows complete and consistent backups while the database is in use, without blocking other users’ access to the database. It will generate a script file that contains the SQL statements of various database objects and data in each table that were created when the backup started. You can use the tools pg_dumpall and pg_dump provided by the database to back up. pg_dump only backs up data of a database in the database cluster. It does not export information related to roles and table spaces, because this information is shared by the entire database cluster and does not belong to a separate database. pg_dumpall, call pg_dump on each database in the cluster to complete the work, and also dump global objects common to all databases ( pg_dump does not save these objects). This currently includes attributes such as database users and groups, table spaces, and access permissions for all databases.

For example, on my computer, you can use the following command to back up the database named dbname:

pg_dump — h 127.0.0.1 -p 5432 -U postgres -c -C — f dbname.sql dbname
Use the following command to back up all pg databases.
pg_dumpall –h 127.0.0.1 –p 5432 -U postgres –c -C –f db_bak.sql
The recovery method is simple. Just execute the restore command:
psql –h 127.0.0.1 -p 5432 -U postgres –f db_bak.sql

3. Continuous archiving

The strategy in this way is to combine a full backup at the file system level and an incremental backup at the WAL (Write-ahead Log) level. When recovery is required, we first restore the file system level backup, and then replay the backup WAL file to restore the system to a previous state. This kind of backup has significant advantages:

You don’t need a perfect and consistent file system backup as a starting point. Any internal inconsistencies in the backup will be corrected by log replay.
It can be combined with an infinitely long sequence of WAL files for playback, and continuous backup can be achieved by simply archiving WAL files.
There is no need to replay the WAL item until the end. You can stop replay at any point and restore the database to a consistent state at that time.
A series of WAL files can be continuously sent to another machine that has been loaded with the same basic backup file to obtain a real-time hot backup system.

How to do continuous archiving?

In the following example, the operating system is Windows 10 and the Postgresql version is 9.6.

First, you need to modify several parameters of the postgresql.conf file as follows:

wal_level =’replica’
archive_mode =’on’
archive_command =’copy /y “%p” “D:\\archive\\%f”’

When archive_command is executed, %p will be replaced by the file path to be archived, and %f will only be replaced by the file name. If you need to embed a real% character in the command, you can use %%. “D:\\archive\\” is replaced with the storage path of the archive log, to ensure that the archive directory exists.

After that, you need to restart the database for the configuration to take effect.

Next, you need to make a non-exclusive basic backup. Postgresql provides two backup methods, exclusive backup and non-exclusive backup. Non-exclusive backup methods are recommended.

1. Connect to the server as a user with the right to run pg_start_backup (don’t care which database) and issue the command:

Select pg_start_backup(‘backup_label’, false, false);

2. Make a file system level backup of the database. Copy the data directory and its contents under the postgresql installation directory to another location.

3. In the same connection, issue the command:

select * from pg_stop_backup(false);

This command represents the end of a non-exclusive backup.

Now let’s look at recovery based on time.

If your database fails, you need to restore to a consistent state at a certain time before, you need to restore based on time.

The process is:

1. If the server is still running, stop it.

2. If you have enough space, copy the entire cluster data directory and table space to a temporary location. Note that it is better to copy rather than move. If you do not have enough space, you should at least save the contents of the cluster’s pg_xlog subdirectory, because it may contain logs that have not been archived before the system crashed.

3. Remove the data directory and all its subfiles and subdirectories.

4. Restore the database file from the file system backup. Note that they must be restored with the correct user and use the correct permissions. If you are using tablespaces, you should verify that the symbolic links in pg_tblspc/ are restored correctly.

5. Now that the entire data directory has been restored from backup, next, you need to partially or completely delete the following files in the data directory if they exist:

(1) postmaster.pid; (required)
(2) Files in pg_xlog; (required)
(3) Temporary files at the beginning of pgsql_tmp; (optional)
(4) postgresql.auto.conf.tmp; (optional)
(5) Files in the pg_replslot directory; (optional)
(6) Files in the pg_stat_tmp directory. (Optional)

6. If you have unarchived WAL segment files saved in step 2, copy them to pg_xlog/ or the archive directory of the WAL log.

Create a recovery command file recovery.conf in the cluster data directory. If you want to restore to the most recent consistent state, write the following two lines in recovery.conf:

restore_command =’copy /y D:\\archive \\%f\\%p’
recovery_target_timeline =’latest’

Among them, the content of restore_command means to copy the contents of the archive log folder to pg_xlog, and the meaning of its parameters is exactly the same as the meaning of the parameters of archive_command above; recovery_target_timeline =’latest’ means to restore to the latest time point.

7. If you want to prevent ordinary users from connecting before successfully restoring, modify pg_hba.conf.

8. Start the server. The server will enter recovery mode and then read the archived WAL file as needed. Recovery may be terminated due to an external error. You can simply restart the server so that it will continue to recover. After the recovery process ends, the server will rename recovery.conf to recovery.done (in order to prevent accidentally re-entering recovery mode in the future), and start normal database operations.

9. Check the contents of the database to ensure that you have recovered to the desired state. If not, return to step 1. If everything is normal, allow the user to connect by restoring pg_hba.conf to normal.

This completes a full backup at the file system level and achieves an incremental backup at the WAL file level.

Attachment: What do pg_start_backup() and pg_stop_backup() do?

1. pg_start_backup()

The function prototype of pg_start_backup() is as follows:

pg_start_backup(label text [, fast boolean [, exclusive boolean ]])

Where label is an arbitrary string used to uniquely identify the backup operation, fast indicates whether to perform a mandatory checkpoint immediately, and exclusive indicates whether the backup is an exclusive backup.

When using this function, it is recommended to set exclusive to false, which means non-exclusive backup.

Execute the following command:

Select pg_start_backup(‘backup_label’, false, false);

This command will produce three actions:

1. Create two variables in the buffer: label_fileAnd tblspc_map_file. The former contains the starting position of WAL, the starting position of the checkpoint, backup method, backup mode, backup start time and the name of the backup label (in this case, the name is “backup_label”); the latter contains “pg_tblspc/” Information about symbolic links in table spaces, if they exist.

2. Force a checkpoint to occur. Refresh the changes to the database committed by the transaction submitted before the checkpoint to disk.

3. Set the write log flag as: XLogCtl->Insert.forcePageWrites = true. After this flag is set to true, if other transactions modify the database during the backup period, the system will record the entire page of the modified data page before the modification to the WAL, not just the changes in the page. section.

Why record the complete page in WAL?

The reason is that during the backup process, other transactions modify the database, which may cause the backup data to be mixed with old and new data, and this may also happen on the same data page. If only the changed part is recorded in the WAL log, the database cannot be restored to the state at the end of the last backup. Therefore, the modified complete page should be written to the WAL to ensure the consistency after the database is restored.

2. pg_stop_backup()

The function prototype of pg_stop_backup() is as follows:

pg_stop_backup([, exclusive boolean ]);

exclusive indicates whether the backup is an exclusive backup.

If non-exclusive backup is used, execute:

Select pg_stop_backup(false);

This command will produce the following actions:

1. The contents of label_file and tblspc_map_file will be included in the result returned by this function, and should be written to some files of the backup (these contents are not in the data directory).

2. Create a backup history file (.hostory) in the transaction log archive area. This history file contains the label of pg_start_backup, the location of the backup start and end transaction logs, and the start and end time of the backup. The return value is the location of the backup transaction log (which can also be ignored). After recording the end position, the current transaction log insertion point is automatically advanced to the next transaction log file, so that the end transaction log file can be archived immediately to end the backup.

References

[1] PostgreSQL Global Development Team. PostgreSQL 9.6.0 documentation . Continuous archiving and point-in-time recovery (PITR) .

[2] 2ndquadrant. What does pg_start_backup() do?

Thanks for reading this post. you can follow me up to get the latest update on this.