Incremental Backups in PostgreSQL 17

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use.

This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies.

Step 1 - Enable WALL Summerization


The incremental backups require Write Ahead Log Summarization. It is likely not on your instance by default. But it is easy to enable.

demo=# alter system set summerize_wal = 'on';
ALTER SYSTEM
demo=# select pg_reload_conf();
pg_reload_conf 
----------------
 t
(1 row)

Step 2 - Full Backup

The examples below are much simpler than you will run into in real life.  The scenario is that we make a full backup on Sunday and do incrementals the rest of the week. Wednesday through Saturday are omitted below. 

stoker@ThinkPad:~$ mkdir full monday tuesday

stoker@ThinkPad:~$ pg_basebackup -D full --verbose
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 6/12000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_21051"
pg_basebackup: write-ahead log end point: 6/12000158
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
stoker@ThinkPad:~$ 


Step 3 - First Incremental (Monday)

On Monday, the first incremental backup is created. The manifest, with all the WAL information on what has changed, needed to be referenced.

stoker@ThinkPad:~$ pg_basebackup -D monday -i ./full/backup_manifest --verbose
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 6/14000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_22703"
pg_basebackup: write-ahead log end point: 6/14000120
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


Step 4 - Second Incremental (Tuesday)

On Tuesday, another incremental backup is created.  

We could have just recorded the changes since the last incremental backup by referring to the backup manifest in the Monday directory. In that case, to completely restore the data, we need to combine the full backup and both the Monday and Tuesday incremental backups. That is a very valid way of operating. If the data does not churn a great deal, this can save time making incrementals. 

My personal preference is to base incrementals on full backups.  This means only the full and Tuesday backups must be used for complete restoration. This reduction in logical steps may seem trivial, but tempting a Murphy's Law Ticket is something I avoid after too many restorations that are too early in the morning after catastrophic system failures. 

stoker@ThinkPad:~$ pg_basebackup -D tuesday -i ./full/backup_manifest --verbose
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 6/16000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_22870"
pg_basebackup: write-ahead log end point: 6/16000120
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

Step 5 - Combine Full, Monday, and Tuesday

Sometime after the Tuesday incremental backup, something horrible happens to your instance (probably someone with too many instance privileges made a DELETE query without a WHERE clause (AGAIN!?!?)). It is simple to create a new home for the data and combine the Full and Tuesday backup data into that new home.

stoker@ThinkPad:~$ mkdir newdatadir
stoker@ThinkPad:~$ /usr/lib/postgresql/17/bin/pg_combinebackup full tuesday -o newdatadir
stoker@ThinkPad:~$ 

Step 6 - Restart


Make sure you 'chmod 700' your new datadir and that your postgres account is the owner. Stop the running instance. You're now ready to use the restored data directory. Point pg_ctl at the new datadir, and you are done.

Summary


Incremental backups will make your life easier, and this feature is a big reason for those not on PostgreSQL 17 to upgrade. 


Comments

Post a Comment

Popular posts from this blog

Can Artificial Intelligence Created Better Tables Than You?

 Artificial Intelligence is one of those conundrums where the ability to have some tasks handled for you contrasts with a mix of ego and pride that it may be better than you at those tasks.  I recently wrote a blog using another database about an AI-generated SQL that was quite sophisticated . But what about asking an AI to create a table with a specification like a DBA/SRE/Analyst might receive? I used Grok and entered the following prompt: Write the SQL to create a table on a PostgreSQL version 17 server to record data on customers. The data will include a first name, a last name, an address, a birthdate, and a unique UUID primary key. Partition the data by the year of birth. And produce some test data of at least ten records. I am sure many of us have started large projects given less instruction.  Notice: I did not denote the format for the address (US Post Office's format, or UK, or other). Nor did I set a name length. I wanted to see what assumptions were made by t...
Read more

Saving The Output From psql

  Occasionally, you will need to capture data when working with a database. You can cut-n-paste or use a shell command like script . In typical PostgreSQL fashion, psql  has two ways to save your output. The First Way You must invoke psql with the --log-file-<filename> or -L <filename> option. The entire session will be recorded. stoker@ThinkPad:~$ psql --log-file=/tmp/psqllog demo Password for user stoker: psql (17.4 (Ubuntu 17.4-1.pgdg24.04+2)) Type "help" for help. demo=# \d ledger                    Table "public.ledger"      Column     |  Type   | Collation | Nullable | Default ----------------+---------+-----------+----------+---------  id             | integer |           |          |  tx_id          | integer |        ...
Read more

PostgreSQL's COPY and \COPY

PostgreSQL is equivalent to a Swiss Army Knife in the database world. There are things in PostgreSQL that are very simple to use, while in another database, they take many more steps to accomplish. But sometimes, the knife has too many blades, which can cause confusion. This is one of those cases. COPY and \COPY I needed a quick dump of a table as part of an ETL (Extract, Transform, and Load - official definition, Exorcism, Trauma, and Lost-sleep - unofficially) to port some data. The COPY command is a quick way to output and write the data to a file.  The two copy commands below illustrate adding a delimiter and then writing the output to a table. demo=# select * from z;  a | b  |  c   ---+----+-----  1 | 10 | 100  2 | 20 | 200  3 | 30 | 300  4 | 40 | 400 (4 rows) demo=# copy z to stdout (DELIMITER ','); 1,10,100 2,20,200 3,30,300 4,40,400 demo=# copy z to '/tmp/z' (DELIMITER ','); COPY 4 demo=#  The trick is that  you must...
Read more