The PostgreSQL Global Development team released the second beta version of PostgreSQL 17 on June 27th, 2024, and it is now available for testing. In this beta version, we can explore the new features that will be released in the official final release of PostgreSQL 17. In this blog, we will discuss some of the powerful features introduced in the PostgreSQL 17 Beta 2.
1. Incremental pg_basebackup
2. pg_maintain role
3. inactive_since column in the pg_replication_slots view
4. Logical slot synchronization
1. Incremental pg_basebackup: Incremental pg_basebackup is a very impactful feature set to debut in PostgreSQL 17. Prior to PostgreSQL 17, we did not have the option to perform incremental pg_basebackup.
In incremental pg_basebackup, PostgreSQL takes a backup of only the changed blocks by using the WAL summaries file. Due to the implementation of incremental backup, the time needed to back up a large database has been significantly reduced.
Before initiating the incremental backup, the summarize_wal parameter must be enabled at the cluster level.
|
1 2 3 4 5 6 7 |
postgres=# show summarize_wal; summarize_wal --------------- on (1 row) postgres=# |
For taking the incremental pg_basebackup, PostgreSQL uses two files:
- Wal summaries: This file is located in the <DATA DIRECTORY>/pg_wal/ directory and keeps track of the changed blocks.
12345678910111213141516postgres@ip-172-100-100-100:~/postgres17/pg_wal$ psql -p 5433psql (17beta2)Type "help" for help.postgres=# show data_directory;data_directory--------------------------------/var/lib/postgresql/postgres17(1 row)postgres@ip-172-100-100-100:~/postgres17/pg_wal/summaries$ ls -ltr-rw------- 1 postgres postgres 32 Jun 22 18:15 000000010000000009000028000000000B000028.summary-rw------- 1 postgres postgres 32 Jun 22 18:20 00000001000000000B000028000000000C000060.summarypostgres@ip-172-100-100-100:~/postgres17/pg_wal/summaries$ pwd/var/lib/postgresql/postgres17/pg_wal/summariespostgres@ip-172-100-100-100:~/postgres17/pg_wal/summaries$ - Backup_manifest: This file, located in the backup directory, contains backup information such as path, size, modification date, checksum Algorithm (Supported algorithms are CRC32C, SHA224, SHA256, SHA384, and SHA512), and checksum. It must be provided when taking incremental backups.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
postgres@ip-172-100-100-100:~$ pg_basebackup -D full_backup_17 -p 5433 -U postgres postgres@ip-172-100-100-100:~$ cd full_backup_17 postgres@ip-172-100-100-100:~/full_backup_17$ tail -10 backup_manifest { "Path": "global/4183", "Size": 0, "Last-Modified": "2024-06-22 17:33:11 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "00000000" }, { "Path": "global/pg_filenode.map", "Size": 524, "Last-Modified": "2024-06-22 17:33:11 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "c74b6748" }, { "Path": "global/1262_vm", "Size": 8192, "Last-Modified": "2024-06-22 17:33:12 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "6f761243" }, { "Path": "global/1232", "Size": 8192, "Last-Modified": "2024-06-22 17:33:11 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "a18685ae" }, { "Path": "global/pg_control", "Size": 8192, "Last-Modified": "2024-06-22 18:14:28 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "43872087" } ], "WAL-Ranges": [ { "Timeline": 1, "Start-LSN": "0/9000028", "End-LSN": "0/9000120" } ], "Manifest-Checksum": "43e8146a6e169d12dee80c9b374a2a517db5ace0773aa845b6b8ad29f8b17ef6"} postgres@ip-172-100-100-100:~/full_backup_17$ pwd /var/lib/postgresql/full_backup_17 postgres@ip-172-100-100-100:~/full_backup_17$ |
Steps to take the Incremental pg_basebackup:
1. Take the FULL backup
|
1 |
postgres@ip-172-100-100-100:~$ pg_basebackup -D full_backup_17 -p 5433 -U postgres |
2. Take the INCREMENTAL backup
The backup_manifest file can be found in the full backup’s backup directory. To perform an incremental backup, use this file with the –incremental parameter.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
postgres@ip-172-100-100-100:~$ pg_basebackup -D incr_backup_17 -p 5433 -U postgres --incremental=/var/lib/postgresql/full_backup_17/backup_manifest -v pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 0/B000028 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: created temporary replication slot "pg_basebackup_50915" pg_basebackup: write-ahead log end point: 0/B000120 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: syncing data to disk ... pg_basebackup: renaming backup_manifest.tmp to backup_manifest pg_basebackup: base backup completed postgres@ip-172-100-100-100:~$ |
Note that we cannot use incremental backup directly for the restoration. We need to use the pg_combinebackup tool to combine the incremental backup and all its dependent backups, including the full backup. Below is an example for reference.
|
1 2 |
postgres@ip-172-100-100-100:~$ pg_combinebackup --output=combine_full_incr_backup full_backup_17 incr_backup_17 postgres@ip-172-100-100-100:~$ |
2. pg_maintain role: In PostgreSQL 17, a new maintenance role called pg_maintain will be introduced. Postgres DBAs can grant this role to a specific user to execute maintenance operations. The pg_maintain role is the predefined role.
This pg_maintain role will enable the postgres user to perform ANALYZE, VACUUM, REINDEX, CLUSTER, REFRESH MATERIALIZED VIEW, and LOCK TABLE on all relations.
3. inactive_since column in the pg_replication_slots view: In PostgreSQL 17, a new column called “inactive_since” will be added to the pg_replication_slots view. Before this update, there was no way to track when a replication slot became inactive. With the addition of the “inactive_since” column in PostgreSQL 17, you can now check when the replication slot became inactive using the pg_replication_slots view.
In addition to this column, PostgreSQL 17 will also include the invalidation_reason column to determine the reason for invalid slots.
In the output below, we can see that the inactive_since and invalidation_reason columns are present in PostgreSQL 17 but not in PostgreSQL 16.
Postgres 16:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
postgres=# select * from pg_replication_slots; -[ RECORD 1 ]-------+------------- slot_name | testing_slot plugin | slot_type | physical datoid | database | temporary | f active | f active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase | f conflicting | |
Postgres 17:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
postgres=# select * from pg_replication_slots; -[ RECORD 1 ]-------+------------------------------ slot_name | testing_slot plugin | slot_type | physical datoid | database | temporary | f active | f active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase | f <strong>inactive_since</strong> | 2024-06-23 08:54:08.612722+00 conflicting | <strong>invalidation_reason</strong> | failover | f synced | f |
4. Logical slot synchronization: PostgreSQL 17 introduces support for logical slot synchronization following a failover slot. This means that we can seamlessly continue logical replication from the new primary database after a failover occurs.
To leverage this feature, simply set the optional parameter “failover” to true in the pg_create_logical_replication_slot() function or in the CREATE SUBSCRIPTION command.
It’s crucial to note that logical replication can only be resumed if the pg_replication_slots.synced value for the synchronized slots on the standby is true at the time of failover. To resume logical replication after failover, updating the subscription’s ‘conninfo‘ to point to the new primary is necessary.
For more details about replication slot synchronization, visit https://www.postgresql.org/docs/17/logicaldecoding-explanation.html
Conclusion
We have discussed several significant new features that will be part of PostgreSQL 17. In addition to these features, PostgreSQL 17 will include a comprehensive set of enhancements. For example, it will no longer be necessary to drop logical replication slots when using pg_upgrade. It will also add the pg_wait_events view to show wait event types. Furthermore, vacuum reporting will display the progress of index vacuuming, and checkpointing details in the pg_stat_checkpointer view will be introduced.
Explore the official documentation below for a comprehensive overview of the exciting new features.
- https://www.postgresql.org/about/news/postgresql-17-beta-2-released-2885/
- https://www.postgresql.org/docs/17/release-17.html
<pYou’ve chosen PostgreSQL for its flexibility, performance, and cost savings—but even experienced IT leaders can hit avoidable pitfalls along the way. Here’s what to look out for.
Enterprise PostgreSQL Buyer’s Guide
