0

I am trying to setup streaming replication on Linux. pg_basebackup is failing due to huge data size i.e 7TB. It's failing because it's taking too long to backup i.e. 20 hrs. and the required WAL file is already gone from the master. So how do I fix this issue and how do I speed up the pg_basebackup process? I am using PostgreSQL 13. What do I need to change in the postgres.conf of master or slave, and what switches do I need to use to speed up the backup process? I am using the following command

pg_basebackup -h 192.168.100.81 -U repuser -p 5432 -D /var/lib/postgresql/13/main \
 -Fp -Xs -P -R -C -S Secondary01 --checkpoint=fast --max-rate=1024M
5
  • What make you think this is a configuration problem? Dec 26, 2021 at 13:33
  • 2
    Since you specify -C along with -S, you probably hit the max_slot_wal_keep_size configuration option. Check if you have this limit enabled. Otherwise, postgresql will never delete the old WAL, even when a DBMS stopped due to lack of free space.
    – Melkij
    Dec 26, 2021 at 14:11
  • I though about configuration issues because there is nothing much I can do/control from pg_basebackup utility. I am already using best available switches. Thanks
    – amanullah
    Dec 26, 2021 at 16:11
  • What is the bottleneck? Getting the data from the disk on the sending end, across the network, or onto the disk on the receiving end? You ask about the slave configuration, but you are creating the slave, it doesn't yet exist and so doesn't have a configuration, right?
    – jjanes
    Dec 26, 2021 at 17:32
  • And the hardware involved, @amanullah, have you thought about that? The network? What environment? Dec 26, 2021 at 21:12

1 Answer 1

1

As Melkij has suggested, the use of a slot should prevent the needed WAL from being removed. However, I suspect there is some timing bug in pg_basebackup which allows it to happen anyway. I've see it a few times "in the wild" but have never been able to get it when I was trying to, which makes it hard to investigate and solve.

If you fell afoul of max_slot_wal_keep_size as Melkij thinks, I think it would have failed with a different error message pg_basebackup: error: unexpected termination of replication stream: FATAL: terminating connection due to administrator command, which is why I think it is more likely you have hit the timing bug. When I have hit this bug, I got the error message very soon after the backup has started. But it is easy to miss this message, and then it proceeds to copy the entire data directory anyway, only to finally fail hard at the very end.

So one option is try again, keeping an eye for error messages at the start so you can manually abort the process, rather than letting it run for 20 useless hours.

While pg_basebackup is certainly convenient, it is not very good in several ways. So you might want to do an exclusive backup using a different method to copy the data, such as rsync. The compression option for pg_basebackup is kind of useless, it transfers data over the network in its uncompressed glory, and then compresses it on the client side. This is the opposite of what you want to stand up a replica. The -z option for rsync will compress data for transit, then uncompress it once it arrives. How beneficial this will be will depend on your data, but I've seen it improve transfer speed over a slow network by 15 fold.

Another advantage of an exclusive backup is that if it gets interrupted by a network glitch or something, you might be able to get rsync to pick up where it left off using the -c option, while pg_basebackup won't do that. But how efficient this will be will depend on how much of your data has turned over in the mean time.

Of course you will have to start out with WAL archiving turned on, or use pg_receivewal or something like that, to capture the WAL generated during the initial copy, as rsync won't do it for you the way that pg_basebackup tries to.

1
  • Thanks for your explanation @jjanes. So if, I don't want to turn on archiving and go for pg_receivewal, then what would be my steps in order to mimic streaming replication on secondary as an alternative.
    – amanullah
    Dec 27, 2021 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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