{"id":2857,"date":"2025-05-15T14:43:24","date_gmt":"2025-05-15T14:43:24","guid":{"rendered":"https:\/\/bynatree.com\/?p=2857"},"modified":"2025-05-15T14:43:24","modified_gmt":"2025-05-15T14:43:24","slug":"efficient-postgresql-backup-strategies-leveraging-pgbackrest-for-performance-recovery","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/05\/15\/efficient-postgresql-backup-strategies-leveraging-pgbackrest-for-performance-recovery\/","title":{"rendered":"Efficient PostgreSQL Backup Strategies: Leveraging pgBackRest for Performance & Recovery"},"content":{"rendered":"

In continuation<\/strong> of our blog series on effectively implementing backup strategies in a production environment, this article delves into the topic of Efficient PostgreSQL Backup Strategies: Leveraging pgBackRest for Performance & Recovery<\/strong> and how it enhances database reliability.<\/span><\/p>\n

pgBackRest is a robust, open-source backup and restore tool for PostgreSQL, designed for scalability and high-performance databases. It supports full, differential, and incremental backups, parallel processing, encryption, and compression. Integrated with PostgreSQL\u2019s archive logs, it enables Point-in-Time Recovery (PITR), ensuring minimal downtime and data loss for mission-critical systems.<\/span><\/p>\n

By leveraging pgBackRest\u2019s capabilities, organizations can streamline their backup processes, optimize storage usage, and ensure rapid data recovery when needed. Implementing an efficient strategy with pgBackRest strengthens PostgreSQL\u2019s resilience, making it a preferred solution for production environments.<\/span><\/p><\/blockquote>\n

Prerequisites<\/span><\/h2>\n

Before installing pgBackRest, certain dependencies need to be installed, including PostgreSQL libraries, compression utilities, and necessary system packages to ensure compatibility and proper functionality.<\/span><\/p>\n

Step01: Installing packages (postgresql16-libs postgresql16-devel) and dependencies (libssh2 libxml2-devel bzip2-devel)\u00a0 based on specific PostgreSQL versions.<\/span><\/h3>\n
    \n
  • \u00a0Ensure your repository has the correct PostgreSQL packages available.<\/span><\/li>\n<\/ul>\n
    sudo yum install postgresql16-libs postgresql16-devel libssh2 libxml2-devel bzip2-devel\n[root@mysql-02 pgsql]# sudo yum install epel-release<\/pre>\n
    Step01.1: Download the source package: http:\/\/pyyaml.org\/download\/libyaml\/yaml-0.2.5.tar.gz.<\/span><\/h4>\n
    To build and install LibYAML<\/span><\/p>\n
    [root@mysql-02 src]# wget http:\/\/pyyaml.org\/download\/libyaml\/yaml-0.2.5.tar.gz\n[root@mysql-02 src]# tar -xvf yaml-0.2.5.tar.gz<\/pre>\n
    Step02: Download build in\u00a0 2.54.2 release of pgbackrest in tar format and unzip using below command.<\/span><\/h3>\n
    [postgres@mysql-02 ~]$ which pg_config\n\/usr\/pgsql-16\/bin\/pg_config\n[root@mysql-02 pgsql]# mkdir -p \/build\n[root@mysql-02 pgsql]# wget -q -O - \\\n>        https:\/\/github.com\/pgbackrest\/pgbackrest\/archive\/release\/2.54.2.tar.gz | \\\n>        tar zx -C \/build<\/pre>\n
    Installing pgbackrest 2.54.2 using below command in centos 08<\/span><\/h3>\n
    [root@mysql-02 src]# cd \/build\/pgbackrest-release-2.54.2\/src && .\/configure && make<\/pre>\n
    Step03: After completing the installation, grant the necessary permissions to the directory and the PostgreSQL user to ensure seamless operations and secure database access.<\/span><\/h3>\n
    [root@mysql-02 src]# sudo mkdir -p \/etc\/pgbackrest\n[root@mysql-02 src]# sudo mkdir -p \/etc\/pgbackrest\/conf.d\n[root@mysql-02 src]# sudo touch \/etc\/pgbackrest\/pgbackrest.conf\n[root@mysql-02 src]# sudo chmod 640 \/etc\/pgbackrest\/pgbackrest.conf\n[root@mysql-02 src]# sudo chown postgres:postgres \/etc\/pgbackrest\/pgbackrest.conf<\/pre>\n
    Step04: Verify different option in pgbackrest using below command:<\/span><\/h3>\n
    [postgres@mysql-02 archive_status]$ pgbackrest --help\npgBackRest 2.54.2 - General help\n\nUsage:\n    pgbackrest [options] [command]\n\nCommands:\n    annotate        add or modify backup annotation\n    archive-get     get a WAL segment from the archive\n    archive-push    push a WAL segment to the archive\n    backup          backup a database cluster\n    check           check the configuration\n    expire          expire backups that exceed retention\n    help            get help\n    info            retrieve information about backups\n    repo-get        get a file from a repository\n    repo-ls         list files in a repository\n    restore         restore a database cluster\n    server          pgBackRest server\n    server-ping     ping pgBackRest server\n    stanza-create   create the required stanza data\n    stanza-delete   delete a stanza\n    stanza-upgrade  upgrade a stanza\n    start           allow pgBackRest processes to run\n    stop            stop pgBackRest processes from running\n    verify          verify contents of the repository\n    version         get version\n\nUse 'pgbackrest help [command]' for more information.<\/pre>\n
    Step05: Modify postgresql.conf<\/strong> to enable replication and archiving using pgBackRest.\u00a0<\/span><\/h3>\n
    Set wal_level = replica to support replication<\/span><\/p>\n
    archive_mode = on for backup management<\/span><\/p>\n
    archive_command to store WAL files efficiently.\u00a0<\/span><\/p>\n
    max_wal_senders to allow multiple standby connections, ensuring robust data integrity, disaster recovery, and optimized database performance.<\/span><\/p>\n
    [postgres@mysql-02 archive_status]vi \/var\/lib\/pgsql\/16\/data\/postgresql.conf\nwal_level = replica \narchive_mode = on \narchive_command = '\/var\/lib\/pgbackrest --stanza=my-stanza archive-push %p' \nmax_wal_senders = 10\nhot_standby = on<\/pre>\n
    Full Backup:<\/b><\/h2>\n
    The entire database cluster’s contents are copied to the backup via pgBackRest.\u00a0 A full backup is always the database cluster’s initial backup.\u00a0 A complete backup can always be directly restored with pgBackRest.\u00a0 No files outside of the whole backup are necessary for the full backup to remain consistent.<\/span><\/p>\n
    Execute the following command to perform a full backup in pgBackRest:<\/span><\/p>\n
    EXPLAIN:\u00a0<\/span><\/p>\n
    sudo -u postgres – Runs the command as the PostgreSQL user (postgres),\u00a0<\/span><\/p>\n
    – pgbackrest – This is the pgBackRest utility,<\/span><\/p>\n
    \u00a0–stanza=my-stanza – Defines the stanza, which is a named configuration set for managing backups.\u00a0<\/span><\/p>\n
    –start-fast – Speeds up the backup process by forcing PostgreSQL to switch to a new WAL (Write-Ahead Log) segment immediately instead of waiting for the current one to complete.<\/span><\/p>\n
    \u00a0–type=full backup – Specifies a full backup, meaning all database files will be included rather than an incremental or differential backup.<\/span><\/p>\n
    \u00a0<\/span>The main action, instructing pgBackRest to start the backup process.<\/b><\/p>\n
    \u00a0–log-level-console=info – Sets the logging level for the console output to info, ensuring relevant status updates are displayed during execution.<\/span><\/p>\n
    [postgres@mysql-02 archive_status]$ sudo -u postgres pgbackrest --stanza=my-stanza  --start-fast --type=full backup --log-level-console=info\n2025-04-11 13:45:09.900 P00   INFO: backup command begin 2.54.2: --archive-timeout=500 --exec-id=58043-653bcd8c --log-level-console=info --pg1-path=\/var\/lib\/pgsql\/16\/data --repo1-path=\/var\/lib\/pgbackrest --repo1-retention-full=2 --stanza=my-stanza --start-fast --type=full\n2025-04-11 13:45:09.950 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes\n2025-04-11 13:45:10.069 P00   INFO: backup start archive = 000000010000000000000033, lsn = 0\/33000028\n2025-04-11 13:45:10.069 P00   INFO: check archive for prior segment 000000010000000000000032\n2025-04-11 13:45:34.884 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive\n2025-04-11 13:45:35.009 P00   INFO: backup stop archive = 000000010000000000000033, lsn = 0\/33000138\n2025-04-11 13:45:35.016 P00   INFO: check archive for segment(s) 000000010000000000000033:000000010000000000000033\n2025-04-11 13:45:35.687 P00   INFO: new backup label = 20250411-134509F\n2025-04-11 13:45:35.998 P00   INFO: full backup size = 51.5MB, file total = 1969\n2025-04-11 13:45:36.000 P00   INFO: backup command end: completed successfully (26119ms)\n2025-04-11 13:45:36.001 P00   INFO: expire command begin 2.54.2: --exec-id=58043-653bcd8c --log-level-console=info --repo1-path=\/var\/lib\/pgbackrest --repo1-retention-full=2 --stanza=my-stanza\n2025-04-11 13:45:36.022 P00   INFO: repo1: 16-1 remove archive, start = 000000010000000000000025, stop = 000000010000000000000030\n2025-04-11 13:45:36.023 P00   INFO: expire command end: completed successfully (23ms)<\/pre>\n
    Differential Backup:<\/strong><\/h2>\n
    \u00a0A differential backup is useful when we want a balance between storage efficiency and recovery speed, as it requires the last full backup plus the latest differential backup to restore the system.<\/span><\/p>\n
    For differential backup in pgbackrest use below command.<\/span><\/p>\n
    EXPLAIN:<\/span><\/p>\n
    Here –type=diff backup – Instead of a full backup, this captures only the differences since the last full backup, reducing storage usage and backup time.<\/span><\/p>\n
    – Other parameters remain the same to ensure efficiency and fast processing<\/span><\/p>\n
    [postgres@mysql-02 archive_status]$ sudo -u postgres pgbackrest --stanza=my-stanza  --start-fast --type=diff backup --log-level-console=info\n2025-04-11 13:46:26.331 P00   INFO: backup command begin 2.54.2: --archive-timeout=500 --exec-id=58477-7ac8fddd --log-level-console=info --pg1-path=\/var\/lib\/pgsql\/16\/data --repo1-path=\/var\/lib\/pgbackrest --repo1-retention-full=2 --stanza=my-stanza --start-fast --type=diff\n2025-04-11 13:46:26.474 P00   INFO: last backup label = 20250411-134509F, version = 2.54.2\n2025-04-11 13:46:26.474 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes\n2025-04-11 13:46:26.615 P00   INFO: backup start archive = 000000010000000000000035, lsn = 0\/35000028\n2025-04-11 13:46:26.615 P00   INFO: check archive for prior segment 000000010000000000000034\n2025-04-11 13:46:28.235 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive\n2025-04-11 13:46:28.415 P00   INFO: backup stop archive = 000000010000000000000035, lsn = 0\/35000100\n2025-04-11 13:46:28.420 P00   INFO: check archive for segment(s) 000000010000000000000035:000000010000000000000035\n2025-04-11 13:46:28.780 P00   INFO: new backup label = 20250411-134509F_20250411-134626D\n2025-04-11 13:46:29.074 P00   INFO: diff backup size = 108KB, file total = 1969\n2025-04-11 13:46:29.074 P00   INFO: backup command end: completed successfully (2755ms)\n2025-04-11 13:46:29.074 P00   INFO: expire command begin 2.54.2: --exec-id=58477-7ac8fddd --log-level-console=info --repo1-path=\/var\/lib\/pgbackrest --repo1-retention-full=2 --stanza=my-stanza\n2025-04-11 13:46:29.078 P00   INFO: repo1: 16-1 no archive to remove\n2025-04-11 13:46:29.079 P00   INFO: expire command end: completed successfully (5ms)<\/pre>\n
    Incremental Backup:<\/b>\u00a0<\/span><\/h2>\n
    An incremental backup is beneficial for reducing storage requirements and backup time even further than a differential backup, but restoring from it requires multiple backups (the last full, the last differential if applicable, and all subsequent incremental backups).<\/span><\/p>\n
    For differential backup in pgbackrest use below command.<\/span><\/p>\n
    EXPLAIN:<\/span><\/p>\n
    Here –type=incr backup – Instead of a full or differential backup, this captures only the changes since the last backup (full or differential).<\/span><\/p>\n
    – The other parameters remain unchanged to ensure fast processing and accurate logging.<\/span><\/p>\n
    [postgres@mysql-02 ~]$ sudo -u postgres pgbackrest --stanza=my-stanza  --start-fast --type=incr backup --log-level-console=info\n2025-04-11 14:49:40.191 P00   INFO: backup command begin 2.54.2: --archive-timeout=500 --exec-id=80224-a7bc0d13 --log-level-console=info --pg1-path=\/var\/lib\/pgsql\/16\/data --repo1-path=\/var\/lib\/pgbackrest --repo1-retention-full=2 --stanza=my-stanza --start-fast --type=incr\n2025-04-11 14:49:40.379 P00   INFO: last backup label = 20250411-134509F_20250411-134626D, version = 2.54.2\n2025-04-11 14:49:40.380 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes\n2025-04-11 14:49:40.590 P00   INFO: backup start archive = 000000020000000000000038, lsn = 0\/38000060\n2025-04-11 14:49:40.590 P00   INFO: check archive for prior segment 000000020000000000000037\n2025-04-11 14:49:42.002 P00   WARN: a timeline switch has occurred since the 20250411-134509F_20250411-134626D backup, enabling delta checksum\n                                    HINT: this is normal after restoring from backup or promoting a standby.\n2025-04-11 14:49:45.607 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive\n2025-04-11 14:49:45.750 P00   INFO: backup stop archive = 000000020000000000000038, lsn = 0\/38000138\n2025-04-11 14:49:45.772 P00   INFO: check archive for segment(s) 000000020000000000000038:000000020000000000000038\n2025-04-11 14:49:46.503 P00   INFO: new backup label = 20250411-134509F_20250411-144940I\n2025-04-11 14:49:46.734 P00   INFO: incr backup size = 2.7MB, file total = 1969\n2025-04-11 14:49:46.735 P00   INFO: backup command end: completed successfully (6559ms)\n2025-04-11 14:49:46.735 P00   INFO: expire command begin 2.54.2: --exec-id=80224-a7bc0d13 --log-level-console=info --repo1-path=\/var\/lib\/pgbackrest --repo1-retention-full=2 --stanza=my-stanza\n2025-04-11 14:49:46.744 P00   INFO: repo1: 16-1 no archive to remove\n2025-04-11 14:49:46.746 P00   INFO: expire command end: completed successfully (11ms)<\/pre>\n
    Restoring Backup:\u00a0<\/strong><\/h2>\n
    For Restoring backup using below command:<\/span><\/p>\n
    [postgres@mysql-02 global]$ sudo -u postgres pgbackrest --stanza=my-stanza restore\n[postgres@mysql-02 global]$ sudo systemctl start postgresql-16.service\n[postgres@mysql-02 global]$ sudo systemctl status postgresql-16.service\n\u25cf postgresql-16.service - PostgreSQL 16 database server\n   Loaded: loaded (\/usr\/lib\/systemd\/system\/postgresql-16.service; enabled; vendor preset: disabled)\n   Active: active (running) since Fri 2025-04-11 14:11:41 EDT; 11s ago\n     Docs: https:\/\/www.postgresql.org\/docs\/16\/static\/\n  Process: 34945 ExecReload=\/bin\/kill -HUP $MAINPID (code=exited, status=0\/SUCCESS)\n  Process: 67027 ExecStartPre=\/usr\/pgsql-16\/bin\/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0\/SUCCESS)\n Main PID: 67036 (postgres)<\/pre>\n
    Verify:<\/span><\/p>\n
    postgres=# \\l+\n                                                                                         List of databases\n   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |\n          Description\n-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------+---------+------------+------\n--------------------------------------\n dvdrental | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |                       | 14 MB   | pg_default |\n master    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |                       | 7385 kB | pg_default |\n postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |                       | 7644 kB | pg_default | defau\nlt administrative connection database\n repmgr    | repmgr   | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |                       | 7545 kB | pg_default |\n template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c\/postgres          +| 7329 kB | pg_default | unmod\nifiable empty database\n           |          |          |                 |             |             |            |           | postgres=CTc\/postgres |         |            |\n template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c\/postgres          +| 7385 kB | pg_default | defau\nlt template for new databases\n           |          |          |                 |             |             |            |           | postgres=CTc\/postgres |         |            |\n(6 rows)<\/pre>\n
    Monitoring backup Process:<\/strong><\/h2>\n
    This PL\/pgSQL function allows monitoring pgBackRest within PostgreSQL, converting backup details into JSON format for direct queries. It retrieves data, stores it temporarily, and returns structured backup insights for efficient database analysis.<\/span><\/p>\n
    [postgres@mysql-02 ~]$ vi pgsql-pgbackrest-info.sql<\/span><\/p>\n
    -- An example of monitoring pgBackRest from within PostgreSQL\n-- Use copy to export data from the pgBackRest info command into the jsonb\n-- type so it can be queried directly by PostgreSQL.\n-- Create monitor schema\ncreate schema monitor;\n-- Get pgBackRest info in JSON format\ncreate function monitor.pgbackrest_info()\n    returns jsonb AS $$\ndeclare\n    data jsonb;\nbegin\n    -- Create a temp table to hold the JSON data\n    create temp table temp_pgbackrest_data (data text);\n\n    -- Copy data into the table directly from the pgBackRest info command\n    copy temp_pgbackrest_data (data)\n        from program\n            'pgbackrest --output=json info' (format text);\n\n    select replace(temp_pgbackrest_data.data, E'\\n', '\\n')::jsonb\n      into data\n      from temp_pgbackrest_data;\n\n    drop table temp_pgbackrest_data;\n\n    return data;\nend $$ language plpgsql;<\/pre>\n
    Get last successful backup for each stanza:<\/span><\/h4>\n
    [postgres@mysql-02 ~]$ vi pgsql-pgbackrest-query.sql<\/span><\/p>\n
    -- Get last successful backup for each stanza\n--\n-- Requires the monitor.pgbackrest_info function.\nwith stanza as\n(\n    select data->'name' as name,\n           data->'backup'->(\n               jsonb_array_length(data->'backup') - 1) as last_backup,\n           data->'archive'->(\n               jsonb_array_length(data->'archive') - 1) as current_archive\n      from jsonb_array_elements(monitor.pgbackrest_info()) as data\n)\nselect name,\n       to_timestamp(\n           (last_backup->'timestamp'->>'stop')::numeric) as last_successful_backup,\n       current_archive->>'max' as last_archived_wal\n  from stanza;<\/pre>\n
    Verifying Backup Process:<\/span><\/h4>\n
    postgres=# \\i pgsql-pgbackrest-query.sql\n    name     | last_successful_backup |    last_archived_wal\n-------------+------------------------+--------------------------\n \"my-stanza\" | 2025-04-11 13:46:28-04 | 000000010000000000000036\n(1 row)<\/pre>\n
    \n
    Reference<\/b>:<\/span>https:\/\/pgbackrest.org\/user-guide-rhel.html<\/span><\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
    In continuation of our blog series on effectively implementing backup strategies in a production environment, this article delves into the topic of Efficient PostgreSQL Backup Strategies: Leveraging pgBackRest for Performance & Recovery and how it enhances database reliability. pgBackRest is a robust, open-source backup and restore tool for PostgreSQL, designed for scalability and high-performance databases.…<\/p>\n","protected":false},"author":1,"featured_media":2858,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[53,81,97,102,107,119,144,151,154,158,162,237,251,257,258,266,270,295],"class_list":["post-2857","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-scalability","tag-backup-strategy","tag-compression","tag-data-recovery","tag-database-backup","tag-database-reliability","tag-differential-backup","tag-encryption","tag-full-backup","tag-high-performance-database","tag-incremental-backup","tag-parallel-processing","tag-pgbackrest","tag-pitr","tag-point-in-time-recovery","tag-postgresql","tag-postgresql-archive-logs","tag-production-environment","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2857","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2857"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2857\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2858"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2857"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2857"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2857"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}