pg_probackup ARCHIVE questions

[hunterhuang8810]

Hi ,

from pg_probackup doc, we know

Making backups in PAGE backup mode, performing PITR, making backups with ARCHIVE WAL delivery mode and running incremental backup after timeline switch require continuous WAL archiving to be enabled.

and set continuous WAL archiving need to configure archive_command like :
archive_command = '/usr/bin/pg_probackup-12 archive-push -B /home/postgres/pg_backup --instance hunterdb --wal-file-name=%f '

you know, if without pg_probackup, we normally config archive_command like：
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

I decide to use pg_probackup as our production DB backup tool, and I want to backup DB on standby node rather than primary.
so I want to only config pg_probackup on the standby backup node, and config pg_probackup continuous WAL archiving, and will not config pg_probackup on primary node.
so as above, my archive_commond on the primary node and backup standby node will not same.

could I config archive_command as normal on all nodes
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

and when I need PITR, I register the wal files to pg_probackup.

I dun't want to config pg_probackup(init, add-instance ...etc) on primary node. and prefer to config archive_commad same on all cluster nodes.

thanks in advance!

[gsmolk]

Hello!
So you want to have at least two independent WAL archives? One filled by pg_probackup archive-push and other by cp ?

[hunterhuang8810]

Hello!
So you want to have at least two independent WAL archives? One filled by pg_probackup archive-push and other by cp ?

No.
I re-checked the doc, seems I can use cp to archive WAL instande of archive-push, referred from doc :

Note
Instead of using the archive-push command provided by pg_probackup, you can use any other tool to set up continuous archiving as long as it delivers WAL segments into backup_dir/wal/instance_name directory. If compression is used, it should be gzip, and .gz suffix in filename is mandatory.

I want to backup DB from standby instead of primary, and I want to config pg_probackup only on standby backup node. on primary node, I will not config pg_probackup , but I want to config same archive_command on both primary and standby.

so seems I can use cp command to archive WAL instead of archive-push when using pg_probackup.

[gsmolk]

so seems I can use cp command to archive WAL instead of archive-push when using pg_probackup.

Sure you can, but I would like to note, that using 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' command is dangerous and may lead to archive corruption.

1. There is no fsync, so in case of system crash you will get a corruption.
2. If cp process crashes in the mid-air, you will get a corruption.
3. It is not concurrent-safe, so the case of several processes trying to write into the same file may lead to corruption.
4. No overwrite protection - test -f && cp is not atomic.

Writing WAL to archive correctly is hard, so I would advise you to use archive-push.

[hunterhuang8810]

so seems I can use cp command to archive WAL instead of archive-push when using pg_probackup.

Sure you can, but I would like to note, that using 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' command is dangerous and may lead to archive corruption.

1. There is no fsync, so in case of system crash you will get a corruption.
2. If cp process crashes in the mid-air, you will get a corruption.
3. It is not concurrent-safe, so the case of several processes trying to write into the same file may lead to corruption.
4. No overwrite protection - test -f && cp is not atomic.

Writing WAL to archive correctly is hard, so I would advise you to use archive-push.

My concern is , I want only config backup in standby server, nor in primary.
So I config pg_probackup(init, add-instance...etc) on standby server, but you know there're cases that primary-standby switchover. after switchover, standby become primary.

I dunt want backup run on primary, and I prefer config same archive_command on primary and standby

[hunterhuang8810]

And more,
seems if I config archive_command as 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f', then pg_probackup can't complete, will always waiting:

INFO: Backup QO7BHO is going to be taken from standby
INFO: Wait for WAL segment /home/postgres/pg_backup/wal/pgsql/000000010000000000000017 to be archived
WARNING: By default pg_probackup assume WAL delivery method to be ARCHIVE. If continuous archiving is not set up, use '--stream' option to make autonomous backup. Otherwise check that continuous archiving works correctly.

actually I checked archive directory, 000000010000000000000017 already there, but backup can't continue

[gsmolk]

I dunt want backup run on primary, and I prefer config same archive_command on primary and standby

Why dont use pg_probackup archive-push on both primary and standby then?

actually I checked archive directory, 000000010000000000000017 already there, but backup can't continue

If you use cp, then it must copy file into /home/postgres/pg_backup/wal/pgsql/, or you can create symlink.

[hunterhuang8810]

I dunt want backup run on primary, and I prefer config same archive_command on primary and standby

Why dont use pg_probackup archive-push on both primary and standby then?
hunterhuang8810>> because I will backup on standby not on primary, so no need to config on primary , for convenience. I just install and config pg_probackup on standby , no need to install and config(init, add-instance...) on primary.

actually I checked archive directory, 000000010000000000000017 already there, but backup can't continue

If you use cp, then it must copy file into /home/postgres/pg_backup/wal/pgsql/, or you can create symlink.
hunterhuang8810: during test, I found even config use archive-push, will encounter waiting wal segment to be archived, and I run pg_switch_wal() , backup will continue.

[gsmolk]

because I will backup on standby not on primary, so no need to config on primary , for convenience. I just install and config pg_probackup on standby , no need to install and config(init, add-instance...) on primary.

So if standby does down, you will lose all your backups and WAL archive ?

during test, I found even config use archive-push, will encounter waiting wal segment to be archived, and I run pg_switch_wal() , backup will continue.

You can remedy that by setting on primary the archive_timeout PostgreSQL parameter to 4min (which is less than pg_probackup default archive timeout).

[hunterhuang8810]

because I will backup on standby not on primary, so no need to config on primary , for convenience. I just install and config pg_probackup on standby , no need to install and config(init, add-instance...) on primary.

So if standby does down, you will lose all your backups and WAL archive ?
hunterhuang8810>> if standby down , I will get alert msg, I will handle afterwards.

during test, I found even config use archive-push, will encounter waiting wal segment to be archived, and I run pg_switch_wal() , backup will continue.

You can remedy that by setting on primary the archive_timeout PostgreSQL parameter to 4min (which is less than pg_probackup default archive timeout).
hunterhuang8810>> ok, noted. how to check and set pg_probackup default archive timeout?

[gsmolk]

how to check and set pg_probackup default archive timeout?

1. Check: pg_probackup show-config -B backup_dir --instance instance_name | grep archive-timeout
2. Use --archive-timeout parameter

[gsmolk]

Please take into account the issues with test && cp I`ve described here:
#319 (comment)

[gsmolk]

Is there any more questions or we can close this issue?

[hunterhuang8810]

Is there any more questions or we can close this issue?

No more question, thanks for your update