0

I am doing postgresql replication by issuing:

On the master:

>select pg_start_backup('clone', true);
>\q 
#tar -cz main -f main.tar.gz

(Explanation: main is the data directory on my system /var/lib/postgresql/9.6/main)

Then I transfer main.tar.gz to the slave....

On the slave:

--Uncompressing main.tar.gz.... 
#tar -zxf main.tar.gz 
--Copying previously created recovery.conf 
#cp recovery.conf main/
--And then just starts the slave
#service postgresql start

On the master:

--And stoping backup mode on the master:
>select pg_stop_backup(); 

What I get is that replication is working fine, and it really works nice.

But I am with doubt after reading the official documentation that after the copy I should copy pg_xlog in an additional step... https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

But I never do that, and never experienced a problem - do a lot of tests, like counting every table lines, in both master and slave and much more, but what worst can happen?

  • You can use pg_basebackup to do all that in a single step – a_horse_with_no_name Jun 27 '18 at 13:16
  • And what about the additional step ? should i do two pg_basebackup or only one? – Luciano Andress Martini Jun 27 '18 at 13:37
  • pg_basebackup can copy the needed WAL archives while it takes the backup To do that specify -X fetch or -X stream – a_horse_with_no_name Jun 27 '18 at 13:40
1

It works fine, until it doesn't. Then you get a backup which won't start, will start but won't connect the master to stream new data, or perhaps one which will start but is silently corrupted (unlikely, but I wouldn't rule it out). Have you tested this method for a very large server under very heavy load?

If you use pg_basebackup, it will either take care of it for you, or will notify you that the backup failed before you discover that the hard way, or notify you that you need to have a separate WAL archive. Depending on which version of PostgreSQL and which options you give to pg_basebackup.

  • But pg_basebackup will do exactly the same i am doing isn't? – Luciano Andress Martini Jun 28 '18 at 13:46
  • It depends on what version you use and what options you give it. On v10, the default will use a temporary replication slot to guarantee the WAL to be retained long enough. If you change to -X stream, it uses a 2nd connection to collect WAL as it is generated, so it is more likely to succeed than your method but is still not guaranteed under extremely heavy load. -X fetch is more or less the same as your method, except failures are detected at the time the backed is taken, rather than the time it is put into use. – jjanes Jun 28 '18 at 16:22

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.