New options for pg_basebackup in PostgreSQL 15

If, and you should, are doing base backups of your PostgreSQL instances you’ve probably used pg_basebackup. Another use case for pg_basebackup is to create a starting point for replicas when you are setting up streaming replication. Up to now, pg_basebackup will send the result always to the node/machine where you started it. That means: If you started pg_basebackup on your client or backup machine, then the resulting base backup will go there. No other options are available in recent versions of PostgreSQL. This will change with PostgreSQL 15.

The commit which implemented this is this (don’t be confused by the commit date, it really happened yesterday, not in 2021):

commit 3500ccc39b0dadd1068a03938e4b8ff562587ccc (HEAD -> master, origin/master, origin/HEAD)
Author: Robert Haas 
Date:   Tue Nov 16 15:20:50 2021 -0500

    Support base backup targets.
    
    pg_basebackup now has a --target=TARGET[:DETAIL] option. If specfied,
    it is sent to the server as the value of the TARGET option to the
    BASE_BACKUP command. If DETAIL is included, it is sent as the value of
    the new TARGET_DETAIL option to the BASE_BACKUP command.  If the
    target is anything other than 'client', pg_basebackup assumes that it
    will now be the server's job to write the backup in a location somehow
    defined by the target, and that it therefore needs to write nothing
    locally. However, the server will still send messages to the client
    for progress reporting purposes.
    
    On the server side, we now support two additional types of backup
    targets.  There is a 'blackhole' target, which just throws away the
    backup data without doing anything at all with it. Naturally, this
    should only be used for testing and debugging purposes, since you will
    not actually have a backup when it finishes running. More usefully,
    there is also a 'server' target, so you can now use something like
    'pg_basebackup -Xnone -t server:/SOME/PATH' to write a backup to some
    location on the server. We can extend this to more types of targets
    in the future, and might even want to create an extensibility
    mechanism for adding new target types.
    
    Since WAL fetching is handled with separate client-side logic, it's
    not part of this mechanism; thus, backups with non-default targets
    must use -Xnone or -Xfetch.
    
    Patch by me, with a bug fix by Jeevan Ladhe.  The patch set of which
    this is a part has also had review and/or testing from Tushar Ahuja,
    Suraj Kharage, Dipesh Pandit, and Mark Dilger.
    
    Discussion: http://postgr.es/m/CA+TgmoaYZbz0=Yk797aOJwkGJC-LK3iXn+wzzMx7KdwNpZhS5g@mail.gmail.com

To demonstrate the feature I’ve setup two nodes. This is my client:

postgres@debian11latest:/u01/app/postgres/product/DEV/db_0/bin$ ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: enp1s0:  mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:00:ca:ce:73 brd ff:ff:ff:ff:ff:ff
    inet 192.168.100.240/24 brd 192.168.100.255 scope global dynamic enp1s0
       valid_lft 3155sec preferred_lft 3155sec
    inet6 fe80::5054:ff:feca:ce73/64 scope link 
       valid_lft forever preferred_lft forever

… and this is my server:

postgres@debian11pg:/u01/app/postgres/product/DEV/db_0/ [pgdev] ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: enp1s0:  mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:00:c7:93:6c brd ff:ff:ff:ff:ff:ff
    inet 192.168.100.241/24 brd 192.168.100.255 scope global dynamic enp1s0
       valid_lft 2278sec preferred_lft 2278sec
    inet6 fe80::5054:ff:fec7:936c/64 scope link 
       valid_lft forever preferred_lft forever

What happens if I start a base backup on the client is, that the data is send from the server to my client and I’ll have the base backup locally stored on the client:

postgres@debian11latest:/u01/app/postgres/product/DEV/db_0/bin$ mkdir /var/tmp/backup
postgres@debian11latest:/u01/app/postgres/product/DEV/db_0/bin$ ./pg_basebackup -h 192.168.100.241 -F t -D /var/tmp/backup/
postgres@debian11latest:/u01/app/postgres/product/DEV/db_0/bin$ ls -l /var/tmp/backup/
total 39624
-rw------- 1 postgres postgres   138469 Jan 21 07:41 backup_manifest
-rw------- 1 postgres postgres 23652864 Jan 21 07:41 base.tar
-rw------- 1 postgres postgres 16778752 Jan 21 07:41 pg_wal.tar

What the above introduces is to tell the server to store the backup. The option for this is this one:

postgres@debian11latest:/u01/app/postgres/product/DEV/db_0/bin$ ./pg_basebackup --help | grep -A 1 TARGET
  -t, --target=TARGET[:DETAIL]
                         backup target (if other than client)

So now, again executing pg_basebackup from the client, we can do this:

postgres@debian11latest:/u01/app/postgres/product/DEV/db_0/bin$ ssh 192.168.100.241 'mkdir /var/tmp/bb'
postgres@debian11latest:/u01/app/postgres/product/DEV/db_0/bin$ ./pg_basebackup -h 192.168.100.241 -X none -t server:/var/tmp/bb/
postgres@debian11latest:/u01/app/postgres/product/DEV/db_0/bin$ ssh 192.168.100.241 'ls -l /var/tmp/bb/'
total 23236
-rw------- 1 postgres postgres   138469 Jan 21 07:57 backup_manifest
-rw------- 1 postgres postgres 23654400 Jan 21 07:57 base.tar

Now the backup is generated on the server without sending it to the client. For testing purposes there is also the “blackhole” target, which just throws away the backup:

postgres@debian11latest:/u01/app/postgres/product/DEV/db_0/bin$ ./pg_basebackup -h 192.168.100.241 -X none -t blackhole
postgres@debian11latest:/u01/app/postgres/product/DEV/db_0/bin$ 

Why is that feature cool? Because now there is the infrastructure to implement other targets, maybe S3, or whatever.

Another feature that just got committed is this: Extend the options of pg_basebackup to control compression.

This gives you more options for compression:

postgres@debian11pg:/u01/app/postgres/product/DEV/db_0/bin/ [pgdev] ./pg_basebackup --help | grep -A 1 LEVEL
  -Z, --compress={gzip,none}[:LEVEL] or [LEVEL]
                         compress tar output with given compression method or level

For the moment there is only “none” and “gzip”:

postgres@debian11pg:/u01/app/postgres/product/DEV/db_0/bin/ [pgdev] mkdir /var/tmp/aa
postgres@debian11pg:/u01/app/postgres/product/DEV/db_0/bin/ [pgdev] ./pg_basebackup -D /var/tmp/aa/ -Z none
postgres@debian11pg:/u01/app/postgres/product/DEV/db_0/bin/ [pgdev] ls /var/tmp/aa/
backup_label     base              global        pg_dynshmem  pg_ident.conf  pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf
backup_manifest  current_logfiles  pg_commit_ts  pg_hba.conf  pg_log         pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postgresql.conf
postgres@debian11pg:/u01/app/postgres/product/DEV/db_0/bin/ [pgdev] rm -rf /var/tmp/aa/*
postgres@debian11pg:/u01/app/postgres/product/DEV/db_0/bin/ [pgdev] ./pg_basebackup -D /var/tmp/aa/ -F t -Z gzip:4
postgres@debian11pg:/u01/app/postgres/product/DEV/db_0/bin/ [pgdev] ls -l /var/tmp/aa/
total 3316
-rw------- 1 postgres postgres  138314 Jan 21 08:39 backup_manifest
-rw------- 1 postgres postgres 3235329 Jan 21 08:39 base.tar.gz
-rw------- 1 postgres postgres   17075 Jan 21 08:39 pg_wal.tar.gz

The same applies here: The infrastructure is now in place, and other options can be added. Nice features, thanks to all involved.