Backup and Restore in PostgreSQL

Suppose we have postgresql in streaming replication mode. master-server and hot-standby ready to replace the deceased comrade. In case of a bad development of events, we just have to create a trigger file and switch our applications to work with the new master. However, situations are possible when completely legitimate changes were made by crookedly written migration and got both on the master and on the slave server. For example, the data in parts of the tables were deleted / changed, or the tables were completely deleted. From the point of view of the database, everything is fine, but from the point of view of business - a disaster. In this case, the proclamation of a hot hot-standby to the master, the procedure is clearly useless ...
To prevent such a situation, there are at least two options ...

• use periodic backup with pg_dump;
• use backups based on base copies and WAL archives.

The first method is quite simple to implement and requires a minimum of installation and maintenance. Put "pg_dump | lbzip2 "in CZK and forget. However, this option does not offer to restore the database directory at the time of the previous crash, at the time of the backup. The second option is a bit more complicated and costly in terms of storage, but this option is a more flexible solution in the case of recovery. About him just will be discussed.
Of the benefits:

• the ability to restore the cluster base at any time relative to the time of creating the base copy and the time of failure;
• as a condition for recovery can serve as a timestamp or a specific transaction.

Minuses:

• The base copy occupies an approximate database cluster size;
• the need to store WAL archives during the storage period of the base copy.

As mentioned above, this backup method offers flexible recovery options (you can restore database states at a specific point in time or at the moment before or after performing a certain transaction), but at the same time add significant backup storage requirements. The implementation looks like this:

• setting archiving mode for WAL-logs;
• backup setup;
• storing one or more backups;
• deleting the oldest backup in case of successful execution of claim 1;
• deleting the corresponding WAL archives from the backup copy from item 3;
• Optionally, you can carry out the procedure for checking backups for their “professional suitability”.

The archiving mode of WAL logs is configured by including the archive_mode and archive_command parameters in postgresql.conf and creating the directory where the archives will be stored. First you need to turn on the archiving mode and estimate the amount of archives created for one day of the database operation. This will allow an assessment of the required storage space for archives and base copies. For archiving are the options:
archive_mode = on
archive_command = 'cp% p / opt / pgsql / pgbackup / archive /% f'
')
Direct backup is set up using pg_basebackup. This is a program from the suite of utilities that comes with PostgreSQL that can be used to set up streaming replication as well as to make backups. The principle of operation allows you to take a backup without stopping the database cluster. Proceeding from the task, we just need to run pg_basebackup according to the schedule in cron. Given the requirements of the place, you need to take care of sufficient disk space, in order to avoid overflow.

Storage of backup copies is optional, as it is enough to have at least one backup. It is understood that at the time of launching the backup, we agree with the statement that the base is in the "correct" state (we will not copy the broken base).

After the backup is completed, the old copy can be deleted along with the archives. Archives are deleted using the pg_archivecleanup utility. The utility allows you to carefully remove unnecessary archives based on special tags files that are created during the backup.

It is also important to configure the backup verification procedure after it is created. The algorithm is quite simple: you need to copy the base copy to a sandbox directory (be careful, place!), Create the minimal necessary configuration files in it necessary to run in recovery mode and run postgres relative to this sandbox directory, after launching you need to analyze the log and make a conclusion whether the backup is recoverable.
Thus, the process fits into three steps: creating a base copy, checking it, and deleting the old, previous base copy.

Now suppose that the worst has happened and you need to perform a recovery. You need to stop the main postgres cluster and rename the database directory to an arbitrary name. The backup directory must be renamed to the database cluster directory. If necessary, copy the configuration files. After determining the configuration files, run postgres relative to our directory. Upon startup, Postgres will detect recovery.conf and start in recovery mode. It remains to wait until postgres recovers its state with the help of archives, after which it will be possible to connect to the database and continue working. That's it, the recovery procedure is complete.

This is how it is. Keep your data safe! Scripts for backup and validation of copies here .