{"id":377383,"date":"2024-05-29T09:00:05","date_gmt":"2024-05-29T09:00:05","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=377383"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=377383","title":{"rendered":"PostgreSQL 17: Part 4 or Commitfest 2024-01<\/span>"},"content":{"rendered":"
<\/div>\n
\n
\n
\n
\n

<\/p>\n

<\/p>\n

Spring is in full swing as we bring you the hottest winter news of the January Commitfest. Let’s get to the good stuff right away!<\/p>\n

<\/p>\n

Previous articles about PostgreSQL 17: 2023-07<\/a>, 2023-09<\/a>, 2023-11<\/a>.<\/p>\n

<\/a> <\/p>\n

Incremental backup<\/a>
Logical replication: maintaining the subscription status when upgrading the subscriber server<\/a>
Dynamic shared memory registry<\/a>
EXPLAIN (memory): report memory usage for planning<\/a>
pg_stat_checkpointer: restartpoint monitoring on replicas<\/a>
Building BRIN indexes in parallel mode<\/a>
Queries with the IS [NOT] NULL condition for NOT NULL columns<\/a>
Optimization of SET search_path<\/a>
GROUP BY optimization<\/a>
Support planner functions for range types<\/a>
PL\/pgSQL: %TYPE and %ROWTYPE arrays<\/a>
sonpath: new data conversion methods<\/a>
COPY\u2026 FROM: ignoring format conversion errors<\/a>
to_timestamp: format codes TZ and OF<\/a>
GENERATED AS IDENTITY in partitioned tables<\/a>
ALTER COLUMN\u2026 SET EXPRESSION<\/a><\/p>\n

<\/a> <\/p>\n

Incremental backup<\/a><\/strong><\/p>\n

<\/p>\n

commit: 174c4805<\/a>, dc212340<\/a><\/p>\n

<\/p>\n

To quickly restore to a point in time in the past, backups should be made frequently so that there are fewer WAL segments to go through to get to the desired point. However, full backups of large databases take up a lot of space, take a long time to create, and create unwanted load on the system.<\/p>\n

<\/p>\n

PostgreSQL 17 solves this with incremental backups<\/a>, which save only changes made relative to another backup.<\/p>\n

<\/p>\n

Here’s how it works.<\/p>\n

<\/p>\n

It all starts with walsummarizer, a process that scans WALs and collects information about modified pages. The process needs the summarize_wal<\/a> parameter to be switched on. No restart is required to change the parameter value, just a configuration reload. You can also run the process on your replicas, not just on the primary machine.<\/p>\n

<\/p>\n

ALTER SYSTEM SET summarize_wal = on; SELECT pg_reload_conf();<\/code><\/pre>\n
  <\/p>\n
The new process appears in pg_stat_activity and starts recording page changes in pg_wal\/summaries in a compact form.<\/p>\n
  <\/p>\n
First, create a full backup.<\/p>\n
  <\/p>\n
$ pg_basebackup -c fast -D backups\/full<\/code><\/pre>\n
  <\/p>\n
Now, introduce some changes; for example, make a copy of a table.<\/p>\n
  <\/p>\n
CREATE TABLE tickets_copy AS SELECT * FROM tickets;<\/code><\/pre>\n
  <\/p>\n
Next, create an incremental backup relative to the full one. To do this, specify the full backup manifest file in the -i parameter.<\/p>\n
  <\/p>\n
$ pg_basebackup -c fast -D backups\/increment1 \\     -i backups\/full\/backup_manifest<\/code><\/pre>\n
  <\/p>\n
Make some more changes.<\/p>\n
  <\/p>\n
DELETE FROM tickets_copy;<\/code><\/pre>\n
  <\/p>\n
Create another incremental backup that will be based on the last incremental backup, not on the full one. Specify the incremental backup manifest file with the -i parameter.<\/p>\n
  <\/p>\n
$ pg_basebackup -c fast -D backups\/increment2 \\     -i backups\/increment1\/backup_manifest<\/code><\/pre>\n
  <\/p>\n
To recover from an incremental backup, first, you need to reconstruct the full backup using the pg_combinebackup tool. Here, to recover from the second incremental backup, we need to specify the two incremental backups, the full backup, and a location for the new reconstructed full backup.<\/p>\n
  <\/p>\n
$ pg_combinebackup backups\/full backups\/increment1 backups\/increment2 \\     -o backups\/full_combined<\/code><\/pre>\n
  <\/p>\n
Let’s look at the backup sizes. As expected, incremental copies are significantly smaller than full ones.<\/p>\n
  <\/p>\n
$ du -h -s backups\/*<\/code><\/pre>\n
  <\/p>\n
2,7G    backups\/full 2,7G    backups\/full_combined 411M    backups\/increment1 25M     backups\/increment2<\/code><\/pre>\n
  <\/p>\n
Start the server from the new full backup on a free port:<\/p>\n
  <\/p>\n
$ pg_ctl start -D backups\/full_combined \\     -o '-p 5400' \\     -l backups\/full_combined\/logfile<\/code><\/pre>\n
  <\/p>\n
Confirm that the changes from the second incremental backup have been restored.<\/p>\n
  <\/p>\n
$ psql -p 5400 -c 'SELECT count(*) FROM tickets_copy'<\/code><\/pre>\n
  <\/p>\n
 count -------      0 (1 row)<\/code><\/pre>\n
  <\/p>\n
Let’s summarize. The server architecture offers two new components to support incremental backup.<\/p>\n
  <\/p>\n