PostgreSQL 13 – Improved base backup

by

“PostgreSQL 13 has been released with more than 160 new features compared to its previous versions”

In this article we will try to understand what’s new about pg_basebackup in PostgreSQL 13

pg_basebackup is a widely used PostgreSQL backup tool that allows us to take an ONLINE and CONSISTENT file system level backup. These backups can be used for point-in-time-recovery or to set up a slave/standby.

PostgreSQL 13 – What’s new in pg_basebackup?

How do you take base backup?

base backup can be taken using below command

pg_basebackup -D /u01/basebackup -Ft -z –checkpoint=fast -P

The above command will take the backup to /u01/basebackup folder

[postgres@postgres03 pg_wal]$ pg_basebackup -D /u01/basebackup -Ft -z --checkpoint=fast -P
2879569/2879569 kB (100%), 1/1 tablespace
[postgres@postgres03 pg_wal]$

Now let me check the content of the folder

[postgres@postgres03 basebackup]$ ls -lrt
total 609508
-rw------- 1 postgres postgres 581022586 Sep 24 19:28 base.tar.gz
-rw------- 1 postgres postgres 185894 Sep 24 19:28 backup_manifest
-rw------- 1 postgres postgres 42913087 Sep 24 19:28 pg_wal.tar.gz

But what is that backup_manifest file?

OK, can I check the progress of backup of backup?

postgres=# select pid, phase, backup_total, backup_streamed from pg_stat_progress_basebackup;
  pid  |          phase           | backup_total | backup_streamed
-------+--------------------------+--------------+-----------------
 13504 | streaming database files |   2799572480 |       540401152
(1 row)

Can I do it with ps -ef | grep postgres?

[postgres@postgres03 pg_wal]$ ps -ef | grep postgres
....
postgres  4466  4044 62 19:23 pts/1    00:01:00 pg_basebackup -D /u01/basebackup -Ft -z --checkpoint=fast -P
postgres  4467  4458  4 19:23 ?        00:00:03 postgres: walsender postgres [local] sending backup "pg_basebackup base backup"
postgres  4468  4458  0 19:23 ?        00:00:00 postgres: walsender postgres [local] streaming 0/AAF36C08
postgres  4469  4466  8 19:23 pts/1    00:00:08 pg_basebackup -D /u01/basebackup -Ft -z --checkpoint=fast -P
...

Well, what two wal senders are doing here?

Well, The article is going to be updated soon with the detailed understanding on pg_basebackup in version 13

postgres=# show server_version_num;
server_version_num
--------------------
130000
(1 row)

postgres=#

Stay tuned !!

Leave a Comment