{"id":2697,"date":"2024-11-05T02:41:36","date_gmt":"2024-11-05T02:41:36","guid":{"rendered":"https:\/\/bynatree.com\/?p=2697"},"modified":"2024-11-05T02:41:36","modified_gmt":"2024-11-05T02:41:36","slug":"postgresql-17s-new-backup-feature-incremental-backups","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2024\/11\/05\/postgresql-17s-new-backup-feature-incremental-backups\/","title":{"rendered":"PostgreSQL 17’s New Backup Feature: Incremental Backups"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

With the release of PostgreSQL 17, a game-changing feature has arrived: <\/span>incremental backups<\/b>. In prior versions, such as PostgreSQL 16 and earlier, users could only perform full backups. This approach, while reliable, had its drawbacks\u2014particularly the time it took to complete these backups and the storage they required.<\/span><\/p>\n

Previously, users relied on <\/span>WAL (Write-Ahead Logging)<\/b> or archive logs as a workaround to achieve incremental-style backups. However, this method comes with a major downside: <\/span>slow recovery times<\/b>. During restoration, each archived WAL segment had to be processed in sequence to replay every transaction, which extended the time required for a full database recovery.<\/span><\/p>\n

PostgreSQL 17\u2019s native support for incremental backups is designed to address these challenges by streamlining backup processes and enhancing recovery speed.<\/span><\/p>\n

How the incremental backups works<\/span><\/h2>\n

In PostgreSQL 17, incremental backups are designed to back up only the data changed since the last full backup, making backups faster and more efficient. This is achieved using the <\/span>pg_basebackup<\/strong> tool with the <\/span>–incremental<\/strong> option, which backs up all non-relation files completely while only capturing modified blocks and metadata required to reconstruct changed database blocks.<\/span><\/p>\n

How it Works<\/span><\/h3>\n
    \n
  • PostgreSQL uses <\/span>WAL summaries<\/b>, which are stored in the <\/span>pg_wal\/summaries<\/span> directory, to determine which blocks have changed since the last backup. These summaries cover the Log Sequence Numbers (LSNs) from the start of the previous backup to the start of the current one.<\/span><\/li>\n<\/ul>\n
      \n
    • By analyzing these WAL summaries, PostgreSQL identifies the modified blocks that need to be backed up, ensuring efficient incremental backups without re-backing up unchanged data.<\/span><\/li>\n<\/ul>\n

      Restoration Requirements<\/span><\/h2>\n

      To restore an incremental backup, all previous backups in the sequence are required to reconstruct the database. Each incremental backup builds on its predecessors, allowing PostgreSQL to recreate the database state efficiently by including only the necessary blocks in each backup.<\/span><\/p>\n

      PostgreSQL does not automatically track which backups are required as a foundation for restoring future incremental backups. This means that users must manage the relationships between their full and incremental backups manually, ensuring that earlier backups are retained if they may be needed for restoration.<\/span><\/p>\n

      When to Use Incremental Backups<\/span><\/h2>\n
        \n
      • Large Databases with Minimal Changes<\/b>: Incremental backups are most beneficial for sizable databases where a significant portion of the data remains unchanged or changes gradually. In these cases, incremental backups can save both time and storage.<\/span><\/li>\n
      • Small Databases<\/b>: For smaller databases, full backups are often simpler to manage and sufficient for most recovery needs, making incremental backups unnecessary.<\/span><\/li>\n
      • Heavily Modified Large Databases<\/b>: If a large database undergoes frequent, widespread modifications, incremental backups might offer limited benefits, as they won\u2019t be substantially smaller than full backups.<\/span><\/li>\n<\/ul>\n

        How to take incremental backups and restore<\/span><\/h2>\n

        Prerequisites<\/span><\/h3>\n

        To perform the incremental backups below configuration parameters need to be set<\/span><\/p>\n

          \n
        • wal_level<\/span><\/li>\n
        • max_wal_senders<\/span><\/li>\n
        • summarize_wal<\/span><\/li>\n<\/ul>\n
          [postgres@localhost ~]$ psql\npsql (17.0)\nType \"help\" for help.\n\npostgres=# alter system set summarize_wal to true ;\nALTER SYSTEM\npostgres=# select pg_reload_conf();\n pg_reload_conf\n----------------\n t\n(1 row)\n\npostgres=#\npostgres=# show summarize_wal ;\n summarize_wal\n---------------\n on\n(1 row)\n\npostgres=# show wal_level ;\n wal_level\n-----------\n replica\n(1 row)\n\npostgres=# show max_wal_senders ;\n max_wal_senders\n-----------------\n 10\n(1 row)\n\npostgres=#<\/pre>\n
          Steps to perform incremental backup and restore<\/span><\/h3>\n
          For this test I am creating a test table with some data.<\/span><\/p>\n
          template1=# create table test(id int, name varchar);\nCREATE TABLE\ntemplate1=#\ntemplate1=# insert into test values (1,'Anurag');\nINSERT 0 1\n\ntemplate1=# select * from test;\n id |  name\n----+--------\n  1 | Anurag\n(1 row)\n<\/pre>\n
          Take a full backup<\/span><\/h4>\n
          Now taking full backup using the pg_basebackup command. Pg_basebackup is a command line tool used to take online backups in PostgreSQL .<\/span><\/p>\n
          [postgres@localhost ~]$ pg_basebackup -D full_backup --verbose\npg_basebackup: initiating base backup, waiting for checkpoint to complete\npg_basebackup: checkpoint completed\npg_basebackup: write-ahead log start point: 3\/2000028 on timeline 1\npg_basebackup: starting background WAL receiver\npg_basebackup: created temporary replication slot \"pg_basebackup_209564\"\npg_basebackup: write-ahead log end point: 3\/200A2C0\npg_basebackup: waiting for background process to finish streaming ...\npg_basebackup: syncing data to disk ...\npg_basebackup: renaming backup_manifest.tmp to backup_manifest\npg_basebackup: base backup completed\n<\/pre>\n
          Now the full backup is completed. Logged into the database and inserted some more records to make changes to the database.<\/span><\/p>\n
          template1=# insert into test values (2,'Prathap');\nINSERT 0 1\ntemplate1=#\n\ntemplate1=# select * from test;\n id |  name\n----+---------\n  1 | Anurag\n  2 | Prathap\n(2 rows)\n<\/pre>\n
          Take an incremental backup<\/span><\/h4>\n
          To backup the changes made in the previous setup, take the incremental backup using the pg_basebackup command line tool. While taking the incremental backup the backup_manifest of the previous backup needs to be passed to the \u201c–incremental\u201d option.<\/span><\/p>\n
           <\/p>\n
          [postgres@localhost ~]$ pg_basebackup -D incremental_backup --incremental=full_backup\/backup_manifest --verbose\npg_basebackup: initiating base backup, waiting for checkpoint to complete\npg_basebackup: checkpoint completed\npg_basebackup: write-ahead log start point: 3\/4000028 on timeline 1\npg_basebackup: starting background WAL receiver\npg_basebackup: created temporary replication slot \"pg_basebackup_209755\"\npg_basebackup: write-ahead log end point: 3\/4000120\npg_basebackup: waiting for background process to finish streaming ...\npg_basebackup: syncing data to disk ...\npg_basebackup: renaming backup_manifest.tmp to backup_manifest\npg_basebackup: base backup completed\n<\/pre>\n
          Restore the backup<\/span><\/h4>\n
          To restore an incremental backup, you\u2019ll need to combine all previous backups since the last full backup using the <\/span>pg_combinebackup<\/span> tool. This tool rebuilds a complete backup by merging the full backup with each subsequent incremental backup.<\/span><\/p>\n
          Key Considerations for Restoration:<\/h4>\n
          Maintain Backup Sequence: Ensure backups are provided in the correct sequence, starting with the full backup followed by each incremental backup in order. Any deviation from this order will cause an error.<\/p>\n
          Complete Backup Chain Required: All incremental backups, starting with the initial full backup, must be available for successful restoration. Omitting any part of the sequence or skipping the full backup will result in an error<\/p>\n
          How to Use <\/b>pg_combinebackup<\/b>:<\/b><\/p>\n
            \n
          1. List all backups in sequence from oldest to newest, starting with the full backup, followed by each incremental backup in the order they were taken.<\/span><\/li>\n
          2. Specify the output directory where the combined full backup will be stored.<\/span><\/li>\n<\/ol>\n
            [postgres@localhost ~]$ pg_combinebackup full_backup incremental_backup --output=complete_backup\n[postgres@localhost ~]$ du -sh complete_backup\n39M     complete_backup\n<\/pre>\n
            After generating the complete backup, it\u2019s ready for use. To start the PostgreSQL server with this backup, point it to the backup directory and use a different port (e.g., 5434) to avoid conflicts with any existing instances.<\/span><\/p>\n
            Steps to Start the Server with the Backup<\/b>:<\/span><\/p>\n
              \n
            1. Navigate to the Backup Directory<\/b>: Ensure that the complete backup is located in the designated folder.<\/span><\/li>\n
            2. Start the PostgreSQL Server on Port 5434<\/b>: Use the <\/span>pg_ctl<\/span> command to initiate the server, specifying the data directory and port.<\/span><\/li>\n<\/ol>\n
              This setup allows you to run the restored instance alongside your main server instance, enabling you to verify the backup or perform other maintenance without interference.<\/span><\/p>\n
              [postgres@localhost ~]$ \/usr\/pgsql-17\/bin\/pg_ctl -D complete_backup start\nwaiting for server to start....2024-11-04 03:53:48.797 PST [210479] LOG:  redirecting log output to logging collector process\n2024-11-04 03:53:48.797 PST [210479] HINT:  Future log output will appear in directory \"log\".\n done\nserver started\n\n\n[postgres@localhost ~]$ psql -p 5434\npsql (17.0)\nType \"help\" for help.\n\npostgres=# c template1\nYou are now connected to database \"template1\" as user \"postgres\".\ntemplate1=#\ntemplate1=# select * from test;\n id |  name\n----+---------\n  1 | Anurag\n  2 | Prathap\n(2 rows)\n\n<\/pre>\n
               <\/p>\n
              \n
              \n
              \n
              \n
              Conclusion<\/h2>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
              \n
              \n
              \n
              \n
              \n
              \n
              The introduction of incremental backups in PostgreSQL 17 marks a significant advancement, making backups faster and more storage-efficient by capturing only changed data blocks. For administrators of large, stable databases, this feature offers a streamlined, cost-effective alternative to traditional full backups, enabling faster recovery and simpler backup management.<\/p>\n
              However, maintaining the correct backup sequence is essential for successful restoration. Testing incremental backups in a non-production setting can help ensure familiarity with the process and avoid errors.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
               <\/p>\n","protected":false},"excerpt":{"rendered":"
              Introduction With the release of PostgreSQL 17, a game-changing feature has arrived: incremental backups. In prior versions, such as PostgreSQL 16 and earlier, users could only perform full backups. This approach, while reliable, had its drawbacks\u2014particularly the time it took to complete these backups and the storage they required. Previously, users relied on WAL (Write-Ahead…<\/p>\n","protected":false},"author":1,"featured_media":2699,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[163,266],"class_list":["post-2697","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-incremental-backups","tag-postgresql","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2697","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=2697"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2697\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2699"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2697"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2697"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2697"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}