How to create a pg_basebackup for without streaming?

Question

I have a primary and a replica Postgres database that are never connected to each other. We have another layer of technology to store a base backup and the WAL files to and those will get synced to the other server eventually.

On the primary db, I set up the WAL files this way:

wal_level = logical
fsync = on
synchronous_commit = local
wal_sync_method = fsync
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/data/wal/%f && cp %p /var/lib/postgresql/data/wal/%f'
archive_timeout = 3600

This works and every hour a new WAL file gets created. Now I'm trying to create a base backup because I believe WAL files don't work by themselves. They need an initial base backup.

I tried running this command:

docker exec primary-db pg_basebackup -h localhost -p 5432 -U postgres -D /var/lib/postgresql/data/basebackup -Ft -z -Xs -P

But I'm getting the following error:

pg_basebackup: WAL streaming can only be used in plain mode

I believe I'm not doing WAL streaming because the two databases are never connected to each other.

Answer 1

The -Xs option to pg_basebackup makes it open a second replicatiom connection the database server and stream the WAL to be stored in the pg_wal directory of the base backup. That way, you can get a self-contained backup.

You cannot use that option together with -Ft, but then you need not. Since you don't stream WAL between primary and standby, you will use restore_command on the standby server, and that's where the WAL comes from.

Use -X none to not stream any WAL at all or -X fetch to fetch whatever WAL it present at the end of the backup.