pgBackRest User Guide - RHEL

A backup is a consistent copy of a database cluster that can be restored to recover from a hardware failure, to perform Point-In-Time Recovery, or to bring up a new standby.

Full Backup: pgBackRest copies the entire contents of the database cluster to the backup. The first backup of the database cluster is always a Full Backup. pgBackRest is always able to restore a full backup directly. The full backup does not depend on any files outside of the full backup for consistency.

Differential Backup: pgBackRest copies only those database cluster files that have changed since the last full backup. pgBackRest restores a differential backup by copying all of the files in the chosen differential backup and the appropriate unchanged files from the previous full backup. The advantage of a differential backup is that it requires less disk space than a full backup, however, the differential backup and the full backup must both be valid to restore the differential backup.

Incremental Backup: pgBackRest copies only those database cluster files that have changed since the last backup (which can be another incremental backup, a differential backup, or a full backup). As an incremental backup only includes those files changed since the prior backup, they are generally much smaller than full or differential backups. As with the differential backup, the incremental backup depends on other backups to be valid to restore the incremental backup. Since the incremental backup includes only those files since the last backup, all prior incremental backups back to the prior differential, the prior differential backup, and the prior full backup must all be valid to perform a restore of the incremental backup. If no differential backup exists then all prior incremental backups back to the prior full backup, which must exist, and the full backup itself must be valid to restore the incremental backup.

A restore is the act of copying a backup to a system where it will be started as a live database cluster. A restore requires the backup files and one or more WAL segments in order to work correctly.

WAL is the mechanism that PostgreSQL uses to ensure that no committed changes are lost. Transactions are written sequentially to the WAL and a transaction is considered to be committed when those writes are flushed to disk. Afterwards, a background process writes the changes into the main database cluster files (also known as the heap). In the event of a crash, the WAL is replayed to make the database consistent.

WAL is conceptually infinite but in practice is broken up into individual 16MB files called segments. WAL segments follow the naming convention 0000000100000A1E000000FE where the first 8 hexadecimal digits represent the timeline and the next 16 digits are the logical sequence number (LSN).

Encryption is the process of converting data into a format that is unrecognizable unless the appropriate password (also referred to as passphrase) is provided.

pgBackRest will encrypt the repository based on a user-provided password, thereby preventing unauthorized access to data stored within the repository.

Upgrading from v1 to v2 is fairly straight-forward. The repository format has not changed and all non-deprecated options from v1 are accepted, so for most installations it is simply a matter of installing the new version.

However, there are a few caveats:

Many option names have changed to improve consistency although the old names from v1 are still accepted. In general, db- options have been renamed to *pg- and *backup-/*retention- options have been renamed to *repo-* when appropriate.

PostgreSQL and repository options must be indexed when using the new names introduced in v2, e.g. pg1-host, pg1-path, repo1-path, repo1-type, etc.

Upgrading from v2.x to v2.y is straight-forward. The repository format has not changed, so for most installations it is simply a matter of installing binaries for the new version. It is also possible to downgrade if you have not used new features that are unsupported by the older version.

Creating the demo cluster is optional but is strongly recommended, especially for new users, since the example commands in the user guide reference the demo cluster; the examples assume the demo cluster is running on the default port (i.e. 5432). The cluster will not be started until a later section because there is still some configuration to do.

pg-primary ⇒ Create the demo cluster

By default RHEL 7-8 includes the day of the week in the log filename. This makes the user guide a bit more complicated so the log_filename is set to a constant.

pg-primary:*/var/lib/pgsql/11/data/postgresql.conf* ⇒ Set log_filename

log_filename = 'postgresql.log'

A stanza is the configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc. Most db servers will only have one PostgreSQL database cluster and therefore one stanza, whereas backup servers will have a stanza for every database cluster that needs to be backed up.

It is tempting to name the stanza after the primary cluster but a better name describes the databases contained in the cluster. Because the stanza name will be used for the primary and all replicas it is more appropriate to choose a name that describes the actual function of the cluster, such as app or dw, rather than the local cluster name, such as main or prod.

The name 'demo' describes the purpose of this cluster accurately so that will also make a good stanza name.

pgBackRest needs to know where the base data directory for the PostgreSQL cluster is located. The path can be requested from PostgreSQL directly but in a recovery scenario the PostgreSQL process will not be available. During backups the value supplied to pgBackRest will be compared against the path that PostgreSQL is running on and they must be equal or the backup will return an error. Make sure that pg-path is exactly equal to data_directory in postgresql.conf.

By default RHEL 7-8 stores clusters in /var/lib/pgsql/[version]/data so it is easy to determine the correct path for the data directory.

When creating the /etc/pgbackrest/pgbackrest.conf file, the database owner (usually postgres) must be granted read privileges.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure the PostgreSQL cluster data directory

[demo]
pg1-path=/var/lib/pgsql/11/data

pgBackRest configuration files follow the Windows INI convention. Sections are denoted by text in brackets and key/value pairs are contained in each section. Lines beginning with # are ignored and can be used as comments.

There are multiple ways the pgBackRest configuration files can be loaded:

The files are concatenated as if they were one big file; order doesn’t matter, but there is precedence based on sections. The precedence (highest to lowest) is:

pgBackRest can also be configured using environment variables as described in the command reference.

pg-primary ⇒ Configure log-path using the environment

The repository is where pgBackRest stores backups and archives WAL segments.

It may be difficult to estimate in advance how much space you’ll need. The best thing to do is take some backups then record the size of different types of backups (full/incr/diff) and measure the amount of WAL generated per day. This will give you a general idea of how much space you’ll need, though of course requirements will likely change over time as your database evolves.

For this demonstration the repository will be stored on the same host as the PostgreSQL server. This is the simplest configuration and is useful in cases where traditional backup software is employed to backup the database host.

pg-primary ⇒ Create the pgBackRest repository

The repository path must be configured so pgBackRest knows where to find it.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure the pgBackRest repository path

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
repo1-path=/var/lib/pgbackrest

Multiple repositories may also be configured. See Multiple Repositories for details.

Backing up a running PostgreSQL cluster requires WAL archiving to be enabled. Note that at least one WAL segment will be created during the backup process even if no explicit writes are made to the cluster.

pg-primary:*/var/lib/pgsql/11/data/postgresql.conf* ⇒ Configure archive settings

archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
log_filename = 'postgresql.log'
max_wal_senders = 3
wal_level = replica

%p is how PostgreSQL specifies the location of the WAL segment to be archived. Setting wal_level to at least replica and increasing max_wal_senders is a good idea even if there are currently no replicas as this will allow them to be added later without restarting the primary cluster.

The PostgreSQL cluster must be restarted after making these changes and before performing a backup.

pg-primary ⇒ Restart the demo cluster

When archiving a WAL segment is expected to take more than 60 seconds (the default) to reach the pgBackRest repository, then the pgBackRest archive-timeout option should be increased. Note that this option is not the same as the PostgreSQL archive_timeout option which is used to force a WAL segment switch; useful for databases where there are long periods of inactivity. For more information on the PostgreSQL archive_timeout option, see PostgreSQL Write Ahead Log.

The archive-push command can be configured with its own options. For example, a lower compression level may be set to speed archiving without affecting the compression used for backups.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Config archive-push to use a lower compression level

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
repo1-path=/var/lib/pgbackrest

[global:archive-push]
compress-level=3

This configuration technique can be used for any command and can even target a specific stanza, e.g. demo:archive-push.

pgBackRest expires backups based on retention options.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure retention to 2 full backups

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2

[global:archive-push]
compress-level=3

More information about retention can be found in the Retention section.

The repository will be configured with a cipher type and key to demonstrate encryption. Encryption is always performed client-side even if the repository type (e.g. S3 or other object store) supports encryption.

It is important to use a long, random passphrase for the cipher key. A good way to generate one is to run: openssl rand -base64 48.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure pgBackRest repository encryption

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2

[global:archive-push]
compress-level=3

Once the repository has been configured and the stanza created and checked, the repository encryption settings cannot be changed.

The stanza-create command must be run to initialize the stanza. It is recommended that the check command be run after stanza-create to ensure archiving and backups are properly configured.

pg-primary ⇒ Create the stanza and check the configuration

The check command validates that pgBackRest and the archive_command setting are configured correctly for archiving and backups for the specified stanza. It will attempt to check all repositories and databases that are configured for the host on which the command is run. It detects misconfigurations, particularly in archiving, that result in incomplete backups because required WAL segments did not reach the archive. The command can be run on the PostgreSQL or repository host. The command may also be run on the standby host, however, since pg_switch_xlog()/pg_switch_wal() cannot be performed on the standby, the command will only test the repository configuration.

Note that pg_create_restore_point('pgBackRest Archive Check') and pg_switch_xlog()/pg_switch_wal() are called to force PostgreSQL to archive a WAL segment.

pg-primary ⇒ Check the configuration

By default pgBackRest will wait for the next regularly scheduled checkpoint before starting a backup. Depending on the checkpoint_timeout and checkpoint_segments settings in PostgreSQL it may be quite some time before a checkpoint completes and the backup can begin. Generally, it is best to set start-fast=y so that the backup starts immediately. This forces a checkpoint, but since backups are usually run once a day an additional checkpoint should not have a noticeable impact on performance. However, on very busy clusters it may be best to pass --start-fast on the command-line as needed.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure backup fast start

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y

[global:archive-push]
compress-level=3

To perform a backup of the PostgreSQL cluster run pgBackRest with the backup command.

pg-primary ⇒ Backup the demo cluster

By default pgBackRest will attempt to perform an incremental backup. However, an incremental backup must be based on a full backup and since no full backup existed pgBackRest ran a full backup instead.

The type option can be used to specify a full or differential backup.

pg-primary ⇒ Differential backup of the demo cluster

This time there was no warning because a full backup already existed. While incremental backups can be based on a full or differential backup, differential backups must be based on a full backup. A full backup can be performed by running the backup command with --type=full.

During an online backup pgBackRest waits for WAL segments that are required for backup consistency to be archived. This wait time is governed by the pgBackRest archive-timeout option which defaults to 60 seconds. If archiving an individual segment is known to take longer then this option should be increased.

Backups can be scheduled with utilities such as cron.

In the following example, two cron jobs are configured to run; full backups are scheduled for 6:30 AM every Sunday with differential backups scheduled for 6:30 AM Monday through Saturday. If this crontab is installed for the first time mid-week, then pgBackRest will run a full backup the first time the differential job is executed, followed the next day by a differential backup.

Once backups are scheduled it’s important to configure retention so backups are expired on a regular schedule, see Retention.

Use the info command to get information about backups.

pg-primary ⇒ Get info for the demo cluster

The info command operates on a single stanza or all stanzas. Text output is the default and gives a human-readable summary of backups for the stanza(s) requested. This format is subject to change with any release.

For machine-readable output use --output=json. The JSON output contains far more information than the text output and is kept stable unless a bug is found.

Each stanza has a separate section and it is possible to limit output to a single stanza with the --stanza option. The stanza 'status' gives a brief indication of the stanza’s health. If this is 'ok' then pgBackRest is functioning normally. If there are multiple repositories, then a status of 'mixed' indicates that the stanza is not in a healthy state on one or more of the repositories; in this case the state of the stanza will be detailed per repository. For cases in which an error on a repository occurred that is not one of the known error codes, then an error code of 'other' will be used and the full error details will be provided. The 'wal archive min/max' shows the minimum and maximum WAL currently stored in the archive and, in the case of multiple repositories, will be reported across all repositories unless the --repo option is set. Note that there may be gaps due to archive retention policies or other reasons.

The 'backup/expire running' message will appear beside the 'status' information if one of those commands is currently running on the host.

The backups are displayed oldest to newest. The oldest backup will always be a full backup (indicated by an F at the end of the label) but the newest backup can be full, differential (ends with D), or incremental (ends with I).

The 'timestamp start/stop' defines the time period when the backup ran. The 'timestamp stop' can be used to determine the backup to use when performing Point-In-Time Recovery. More information about Point-In-Time Recovery can be found in the Point-In-Time Recovery section.

The 'wal start/stop' defines the WAL range that is required to make the database consistent when restoring. The backup command will ensure that this WAL range is in the archive before completing.

The 'database size' is the full uncompressed size of the database while 'database backup size' is the amount of data in the database to actually back up (these will be the same for full backups).

The 'repo' indicates in which repository this backup resides. The 'backup set size' includes all the files from this backup and any referenced backups in the repository that are required to restore the database from this backup while 'backup size' includes only the files in this backup (these will also be the same for full backups). Repository sizes reflect compressed file sizes if compression is enabled in pgBackRest.

The 'backup reference list' contains the additional backups that are required to restore this backup.

Backups can protect you from a number of disaster scenarios, the most common of which are hardware failure and data corruption. The easiest way to simulate data corruption is to remove an important PostgreSQL cluster file.

pg-primary ⇒ Stop the demo cluster and delete the pg_control file

Starting the cluster without this important file will result in an error.

pg-primary ⇒ Attempt to start the corrupted demo cluster

To restore a backup of the PostgreSQL cluster run pgBackRest with the restore command. The cluster needs to be stopped (in this case it is already stopped) and all files must be removed from the PostgreSQL data directory.

pg-primary ⇒ Remove old files from demo cluster

pg-primary ⇒ Restore the demo cluster and start PostgreSQL

This time the cluster started successfully since the restore replaced the missing pg_control file.

More information about the restore command can be found in the Restore section.

The PostgreSQL COPY command allows pgBackRest info to be loaded into a table. The following example wraps that logic in a function that can be used to perform real-time queries.

pg-primary ⇒ Load pgBackRest info function for PostgreSQL

Now the monitor.pgbackrest_info() function can be used to determine the last successful backup time and archived WAL for a stanza.

pg-primary ⇒ Query last successful backup time and archived WAL

Bundling files together in the repository saves time during the backup and some space in the repository. This is especially pronounced when the repository is stored on an object store such as S3. Per-file creation time on object stores is higher and very small files might cost as much to store as larger files.

The file bundling feature is enabled with the repo-bundle option.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure repo1-bundle

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
repo1-bundle=y
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y

[global:archive-push]
compress-level=3

A full backup without file bundling will have 1000+ files in the backup path, but with bundling the total number of files is greatly reduced. An additional benefit is that zero-length files are not stored (except in the manifest), whereas in a normal backup each zero-length file is stored individually.

pg-primary ⇒ Perform a full backup

pg-primary ⇒ Check file total

The repo-bundle-size and repo-bundle-limit options can be used for tuning, though the defaults should be optimal in most cases.

While file bundling is generally more efficient, the downside is that it is more difficult to manually retrieve files from the repository. It may not be ideal for deduplicated storage since each full backup will arrange files in the bundles differently. Lastly, file bundles cannot be resumed, so be careful not to set repo-bundle-size too high.

Block incremental backups save space by only storing the parts of a file that have changed since the prior backup rather than storing the entire file.

The block incremental feature is enabled with the repo-block option and it works best when enabled for all backup types. File bundling must also be enabled.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure repo1-block

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
beta=y
repo1-block=y
repo1-bundle=y
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y

[global:archive-push]
compress-level=3

The repo-block-size-map, repo-block-size-super, repo-block-size-super-full, repo-block-age-map, and repo-block-checksum-size-map options can be used for tuning, though the defaults should be optimal in most cases. Note that these options may not be be available after the beta since they are primarily intended to allow experimentation to find the optimal values.

Users can attach informative key/value pairs to the backup. This option may be used multiple times to attach multiple annotations.

pg-primary ⇒ Perform a full backup with annotations

Annotations are output by the info command text output when a backup is specified with --set and always appear in the JSON output.

pg-primary ⇒ Get info for the demo cluster

Annotations included with the backup command can be added, modified, or removed afterwards using the annotate command.

pg-primary ⇒ Change backup annotations

The repo1-retention-full-type determines how the option repo1-retention-full is interpreted; either as the count of full backups to be retained or how many days to retain full backups. New backups must be completed before expiration will occur — that means if repo1-retention-full-type=count and repo1-retention-full=2 then there will be three full backups stored before the oldest one is expired, or if repo1-retention-full-type=time and repo1-retention-full=20 then there must be one full backup that is at least 20 days old before expiration can occur.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure repo1-retention-full

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
beta=y
repo1-block=y
repo1-bundle=y
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y

[global:archive-push]
compress-level=3

Backup repo1-retention-full=2 but currently there is only one full backup so the next full backup to run will not expire any full backups.

pg-primary ⇒ Perform a full backup

Archive is expired because WAL segments were generated before the oldest backup. These are not useful for recovery — only WAL segments generated after a backup can be used to recover that backup.

pg-primary ⇒ Perform a full backup

The 20230320-003926F full backup is expired and archive retention is based on the 20230320-003950F which is now the oldest full backup.

Set repo1-retention-diff to the number of differential backups required. Differentials only rely on the prior full backup so it is possible to create a “rolling” set of differentials for the last day or more. This allows quick restores to recent points-in-time but reduces overall space consumption.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure repo1-retention-diff

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
beta=y
repo1-block=y
repo1-bundle=y
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-diff=1
repo1-retention-full=2
start-fast=y

[global:archive-push]
compress-level=3

Backup repo1-retention-diff=1 so two differentials will need to be performed before one is expired. An incremental backup is added to demonstrate incremental expiration. Incremental backups cannot be expired independently — they are always expired with their related full or differential backup.

pg-primary ⇒ Perform differential and incremental backups

Now performing a differential backup will expire the previous differential and incremental backups leaving only one differential backup.

pg-primary ⇒ Perform a differential backup

Although pgBackRest automatically removes archived WAL segments when expiring backups (the default expires WAL for full backups based on the repo1-retention-full option), it may be useful to expire archive more aggressively to save disk space. Note that full backups are treated as differential backups for the purpose of differential archive retention.

Expiring archive will never remove WAL segments that are required to make a backup consistent. However, since Point-in-Time-Recovery (PITR) only works on a continuous WAL stream, care should be taken when aggressively expiring archive outside of the normal backup expiration process. To determine what will be expired without actually expiring anything, the dry-run option can be provided on the command line with the expire command.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure repo1-retention-diff

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
beta=y
repo1-block=y
repo1-bundle=y
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-diff=2
repo1-retention-full=2
start-fast=y

[global:archive-push]
compress-level=3

pg-primary ⇒ Perform differential backup

pg-primary ⇒ Expire archive

The 20230320-003954F_20230320-004004D differential backup has archived WAL segments that must be retained to make the older backups consistent even though they cannot be played any further forward with PITR. WAL segments generated after 20230320-003954F_20230320-004004D but before 20230320-003954F_20230320-004008D are removed. WAL segments generated after the new backup 20230320-003954F_20230320-004008D remain and can be used for PITR.

Since full backups are considered differential backups for the purpose of differential archive retention, if a full backup is now performed with the same settings, only the archive for that full backup is retained for PITR.

If a restore is run as a non-root user (the typical scenario) then all files restored will belong to the user/group executing pgBackRest. If existing files are not owned by the executing user/group then an error will result if the ownership cannot be updated to the executing user/group. In that case the file ownership will need to be updated by a privileged user before the restore can be retried.

If a restore is run as the root user then pgBackRest will attempt to recreate the ownership recorded in the manifest when the backup was made. Only user/group names are stored in the manifest so the same names must exist on the restore host for this to work. If the user/group name cannot be found locally then the user/group of the PostgreSQL data directory will be used and finally root if the data directory user/group cannot be mapped to a name.

Restore a Backup in Quick Start required the database cluster directory to be cleaned before the restore could be performed. The delta option allows pgBackRest to automatically determine which files in the database cluster directory can be preserved and which ones need to be restored from the backup — it also removes files not present in the backup manifest so it will dispose of divergent changes. This is accomplished by calculating a SHA-1 cryptographic hash for each file in the database cluster directory. If the SHA-1 hash does not match the hash stored in the backup then that file will be restored. This operation is very efficient when combined with the process-max option. Since the PostgreSQL server is shut down during the restore, a larger number of processes can be used than might be desirable during a backup when the PostgreSQL server is running.

pg-primary ⇒ Stop the demo cluster, perform delta restore

pg-primary ⇒ Restart PostgreSQL

There may be cases where it is desirable to selectively restore specific databases from a cluster backup. This could be done for performance reasons or to move selected databases to a machine that does not have enough space to restore the entire cluster backup.

To demonstrate this feature two databases are created: test1 and test2.

pg-primary ⇒ Create two test databases

Each test database will be seeded with tables and data to demonstrate that recovery works with selective restore.

pg-primary ⇒ Create a test table in each database

A fresh backup is run so pgBackRest is aware of the new databases.

pg-primary ⇒ Perform a backup

One of the main reasons to use selective restore is to save space. The size of the test1 database is shown here so it can be compared with the disk utilization after a selective restore.

pg-primary ⇒ Show space used by test1 database

If the database to restore is not known, use the info command set option to discover databases that are part of the backup set.

pg-primary ⇒ Show database list for backup

Stop the cluster and restore only the test2 database. Built-in databases (template0, template1, and postgres) are always restored.

pg-primary ⇒ Restore from last backup including only the test2 database

Once recovery is complete the test2 database will contain all previously created tables and data.

pg-primary ⇒ Demonstrate that the test2 database was recovered

The test1 database, despite successful recovery, is not accessible. This is because the entire database was restored as sparse, zeroed files. PostgreSQL can successfully apply WAL on the zeroed files but the database as a whole will not be valid because key files contain no data. This is purposeful to prevent the database from being accidentally used when it might contain partial data that was applied during WAL replay.

pg-primary ⇒ Attempting to connect to the test1 database will produce an error

Since the test1 database is restored with sparse, zeroed files it will only require as much space as the amount of WAL that is written during recovery. While the amount of WAL generated during a backup and applied during recovery can be significant it will generally be a small fraction of the total database size, especially for large databases where this feature is most likely to be useful.

It is clear that the test1 database uses far less disk space during the selective restore than it would have if the entire database had been restored.

pg-primary ⇒ Show space used by test1 database after recovery

At this point the only action that can be taken on the invalid test1 database is drop database. pgBackRest does not automatically drop the database since this cannot be done until recovery is complete and the cluster is accessible.

pg-primary ⇒ Drop the test1 database

Now that the invalid test1 database has been dropped only the test2 and built-in databases remain.

pg-primary ⇒ List remaining databases

A new host named repository is created to store the cluster backups.

The pgbackrest user is created to own the pgBackRest repository. Any user can own the repository but it is best not to use postgres (if it exists) to avoid confusion.

repository ⇒ Create pgbackrest user

pgBackRest needs to be installed from a package or installed manually as shown here.

repository ⇒ Install dependencies

repository ⇒ Copy pgBackRest binary from build host

pgBackRest requires log and configuration directories and a configuration file.

repository ⇒ Create pgBackRest configuration file and directories

repository ⇒ Create the pgBackRest repository

pgBackRest can use TLS with client certificates to enable communication between the hosts. It is also possible to use SSH, see Setup SSH.

pgBackRest expects client/server certificates to be generated in the same way as PostgreSQL. See Secure TCP/IP Connections with TLS for detailed instructions on generating certificates.

The repository host must be configured with the pg-primary host/user and database path. The primary will be configured as pg1 to allow a standby to be added later.

repository:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure pg1-host/pg1-host-user and pg1-path

[demo]
pg1-host=pg-primary
pg1-host-ca-file=/etc/pgbackrest/cert/ca.crt
pg1-host-cert-file=/etc/pgbackrest/cert/client.crt
pg1-host-key-file=/etc/pgbackrest/cert/client.key
pg1-host-type=tls
pg1-path=/var/lib/pgsql/11/data

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y
tls-server-address=*
tls-server-auth=pgbackrest-client=demo
tls-server-ca-file=/etc/pgbackrest/cert/ca.crt
tls-server-cert-file=/etc/pgbackrest/cert/server.crt
tls-server-key-file=/etc/pgbackrest/cert/server.key

The database host must be configured with the repository host/user. The default for the repo1-host-user option is pgbackrest. If the postgres user does restores on the repository host it is best not to also allow the postgres user to perform backups. However, the postgres user can read the repository directly if it is in the same group as the pgbackrest user.

pg-primary:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure repo1-host/repo1-host-user

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
log-level-file=detail
repo1-host=repository
repo1-host-ca-file=/etc/pgbackrest/cert/ca.crt
repo1-host-cert-file=/etc/pgbackrest/cert/client.crt
repo1-host-key-file=/etc/pgbackrest/cert/client.key
repo1-host-type=tls
tls-server-address=*
tls-server-auth=pgbackrest-client=demo
tls-server-ca-file=/etc/pgbackrest/cert/ca.crt
tls-server-cert-file=/etc/pgbackrest/cert/server.crt
tls-server-key-file=/etc/pgbackrest/cert/server.key

PostgreSQL configuration may be found in the Configure Archiving section.

Commands are run the same as on a single host configuration except that some commands such as backup and expire are run from the repository host instead of the database host.

The pgBackRest TLS server must be configured and started on each host.

repository ⇒ Setup pgBackRest Server

pg-primary ⇒ Setup pgBackRest Server

Create the stanza in the new repository.

repository ⇒ Create the stanza

Check that the configuration is correct on both the database and repository hosts. More information about the check command can be found in Check the Configuration.

pg-primary ⇒ Check the configuration

repository ⇒ Check the configuration

To perform a backup of the PostgreSQL cluster run pgBackRest with the backup command on the repository host.

repository ⇒ Backup the demo cluster

Since a new repository was created on the repository host the warning about the incremental backup changing to a full backup was emitted.

To perform a restore of the PostgreSQL cluster run pgBackRest with the restore command on the database host.

pg-primary ⇒ Stop the demo cluster, restore, and restart PostgreSQL

A new host named pg-standby is created to run the standby.

pgBackRest needs to be installed from a package or installed manually as shown here.

pg-standby ⇒ Install dependencies

pg-standby ⇒ Copy pgBackRest binary from build host

pgBackRest requires log and configuration directories and a configuration file.

pg-standby ⇒ Create pgBackRest configuration file and directories

A hot standby performs replication using the WAL archive and allows read-only queries.

pgBackRest configuration is very similar to pg-primary except that the standby recovery type will be used to keep the cluster in recovery mode when the end of the WAL stream has been reached.

pg-standby:*/etc/pgbackrest/pgbackrest.conf* ⇒ Configure pgBackRest on the standby

[demo]
pg1-path=/var/lib/pgsql/11/data

[global]
log-level-file=detail
repo1-host=repository
repo1-host-ca-file=/etc/pgbackrest/cert/ca.crt
repo1-host-cert-file=/etc/pgbackrest/cert/client.crt
repo1-host-key-file=/etc/pgbackrest/cert/client.key
repo1-host-type=tls
tls-server-address=*
tls-server-auth=pgbackrest-client=demo
tls-server-ca-file=/etc/pgbackrest/cert/ca.crt
tls-server-cert-file=/etc/pgbackrest/cert/server.crt
tls-server-key-file=/etc/pgbackrest/cert/server.key

pg-standby ⇒ Setup pgBackRest Server

Create the path where PostgreSQL will be restored.

pg-standby ⇒ Create PostgreSQL path

Now the standby can be created with the restore command.

pg-standby ⇒ Restore the demo standby cluster

The hot_standby setting must be enabled before starting PostgreSQL to allow read-only connections on pg-standby. Otherwise, connection attempts will be refused. The rest of the configuration is in case the standby is promoted to a primary.

pg-standby:*/var/lib/pgsql/11/data/postgresql.conf* ⇒ Configure PostgreSQL

archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
hot_standby = on
log_filename = 'postgresql.log'
max_wal_senders = 3
wal_level = replica

pg-standby ⇒ Start PostgreSQL

The PostgreSQL log gives valuable information about the recovery. Note especially that the cluster has entered standby mode and is ready to accept read-only connections.

pg-standby ⇒ Examine the PostgreSQL log output for log messages indicating success

An easy way to test that replication is properly configured is to create a table on pg-primary.

pg-primary ⇒ Create a new table on the primary

And then query the same table on pg-standby.

pg-standby ⇒ Query new table on the standby

So, what went wrong? Since PostgreSQL is pulling WAL segments from the archive to perform replication, changes won’t be seen on the standby until the WAL segment that contains those changes is pushed from pg-primary.

This can be done manually by calling pg_switch_wal() which pushes the current WAL segment to the archive (a new WAL segment is created to contain further changes).

pg-primary ⇒ Call pg_switch_wal()

Now after a short delay the table will appear on pg-standby.

pg-standby ⇒ Now the new table exists on the standby (may require a few retries)

Check the standby configuration for access to the repository.

pg-standby ⇒ Check the configuration

Instead of relying solely on the WAL archive, streaming replication makes a direct connection to the primary and applies changes as soon as they are made on the primary. This results in much less lag between the primary and standby.

Streaming replication requires a user with the replication privilege.

pg-primary ⇒ Create replication user

The pg_hba.conf file must be updated to allow the standby to connect as the replication user. Be sure to replace the IP address below with the actual IP address of your pg-standby. A reload will be required after modifying the pg_hba.conf file.

pg-primary ⇒ Create pg_hba.conf entry for replication user

The standby needs to know how to contact the primary so the primary_conninfo setting will be configured in pgBackRest.

pg-standby:*/etc/pgbackrest/pgbackrest.conf* ⇒ Set primary_conninfo

[demo]
pg1-path=/var/lib/pgsql/11/data
recovery-option=primary_conninfo=host=172.17.0.5 port=5432 user=replicator

[global]
log-level-file=detail
repo1-host=repository
repo1-host-ca-file=/etc/pgbackrest/cert/ca.crt
repo1-host-cert-file=/etc/pgbackrest/cert/client.crt
repo1-host-key-file=/etc/pgbackrest/cert/client.key
repo1-host-type=tls
tls-server-address=*
tls-server-auth=pgbackrest-client=demo
tls-server-ca-file=/etc/pgbackrest/cert/ca.crt
tls-server-cert-file=/etc/pgbackrest/cert/server.crt
tls-server-key-file=/etc/pgbackrest/cert/server.key

It is possible to configure a password in the primary_conninfo setting but using a .pgpass file is more flexible and secure.

pg-standby ⇒ Configure the replication password in the .pgpass file.

Now the standby can be created with the restore command.

pg-standby ⇒ Stop PostgreSQL and restore the demo standby cluster

By default RHEL 7-8 stores the postgresql.conf file in the PostgreSQL data directory. That means the change made to postgresql.conf was overwritten by the last restore and the hot_standby setting must be enabled again. Other solutions to this problem are to store the postgresql.conf file elsewhere or to enable the hot_standby setting on the pg-primary host where it will be ignored.

pg-standby:*/var/lib/pgsql/11/data/postgresql.conf* ⇒ Enable hot_standby

archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
hot_standby = on
log_filename = 'postgresql.log'
max_wal_senders = 3
wal_level = replica

pg-standby ⇒ Start PostgreSQL

The PostgreSQL log will confirm that streaming replication has started.

pg-standby ⇒ Examine the PostgreSQL log output for log messages indicating success

Now when a table is created on pg-primary it will appear on pg-standby quickly and without the need to call pg_switch_wal().

pg-primary ⇒ Create a new table on the primary

pg-standby ⇒ Query table on the standby

The asynchronous archive-push command offloads WAL archiving to a separate process (or processes) to improve throughput. It works by “looking ahead” to see which WAL segments are ready to be archived beyond the request that PostgreSQL is currently making via the archive_command. WAL segments are transferred to the archive directly from the pg_xlog/pg_wal directory and success is only returned by the archive_command when the WAL segment has been safely stored in the archive.

The spool path holds the current status of WAL archiving. Status files written into the spool directory are typically zero length and should consume a minimal amount of space (a few MB at most) and very little IO. All the information in this directory can be recreated so it is not necessary to preserve the spool directory if the cluster is moved to new hardware.

The [stanza]-archive-push-async.log file can be used to monitor the activity of the asynchronous process. A good way to test this is to quickly push a number of WAL segments.

pg-primary ⇒ Test parallel asynchronous archiving

Now the log file will contain parallel, asynchronous activity.

pg-primary ⇒ Check results in the log

The asynchronous archive-get command maintains a local queue of WAL to improve throughput. If a WAL segment is not found in the queue it is fetched from the repository along with enough consecutive WAL to fill the queue. The maximum size of the queue is defined by archive-get-queue-max. Whenever the queue is less than half full more WAL will be fetched to fill it.

Asynchronous operation is most useful in environments that generate a lot of WAL or have a high latency connection to the repository storage (i.e., S3 or other object stores). In the case of a high latency connection it may be a good idea to increase process-max.

The [stanza]-archive-get-async.log file can be used to monitor the activity of the asynchronous process.

pg-standby ⇒ Check results in the log

pg-primary ⇒ Fix streaming replication by changing the replication password