1

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.

1 Answer 1

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.

2
  • We have different 3rd party apps using postgres that we need to support and one of them is unfortunately using an older version (9.6.16). I'm getting this error so far: pg_basebackup: invalid xlog-method option "none", must be "fetch" or "stream".
    – dokgu
    Feb 16 at 15:19
  • Then the choice is obvious, isn't it? Feb 16 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.