pg_basebackup: Creating self-sufficient backups

If you are using transaction log shipping (or Point-In-Time-Recovery) in PostgreSQL to keep your data safe, you will surely already know about pg_basebackup. The idea behind pg_basebackup is to allow users to create a binary copy of the data, which can serve as the basis for Point-In-Time-Recovery.  However, recently we have seen some support cases, which were related to improper use of pg_basebackup.

What does pg_basebackup do?

Basically pg_basebackup is a way to “tar” data over a database connection. When you start pg_basebackup it will wait for the server to do a checkpoint and then start to copy the Postgres data directory over. It is especially noteworthy that data is copied while the “donor” server (usually the master but possible for replicas also) is fully operational. Thousands of transactions (reads and writes) might happen, while the base backup is running. What it means is: The data files you are about to copy over are incomplete, corrupted, and basically just not usable.

However, let us reflect a bit on those “errors”. The content of the basebackup is something like a mixture of the data as it was at the beginning of the basebackup and of the data as it was at the end of the basebackup. The point is simple: As there is a transaction log entry for every change, we can always repair those inconsistencies using the PostgreSQL transaction log. In other words: Corrupted base backup + WAL = consistent data.

Making PostgreSQL backups self-contained and ready for use

By default a base backup does not contain the WAL, which was created while the base backup was created. In other words: Unless NOTHING happened during the base backup (which is highly unlikely), the basebackup might appear to work without a WAL archive but as stated before – it “MIGHT”. There is no guarantee.

To make sure that the basebackup contains enough WAL to reach at least consistent state, it is recommended to add “–xlog-method=stream” when you call pg_basebackup. It will open a second stream fetching the WAL, which is created during the backup. The advantage is that the backup has now everything it needs to live without a WAL archive. You can start it directly or apply more WAL to do Point-In-Time-Recovery.

Here is my favorite method of running pg_basebackup:


pg_basebackup -D /target_dir -h master.example.com --checkpoint=fast \

--xlog-method=stream -R

-R will automatically create a config file if you are planning to run streaming replication. For Point-In-Time-Recovery this is not necessary.

Hans-Juergen Schoenig
Hans-Jürgen Schönig has 15 years of experience with PostgreSQL. He is consultant and CEO of the company „Cybertec Schönig & Schönig GmbH“ (www.cybertec.at, www.postgresql-support.de), which has served countless customers around the globe.