0

I have setup the replication as per https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps.

And I see the slave is in streaming replication mode.

But I do have a question that, I have a bunch of databases in my master. Now should I create the same databases/tables and have the existing data in master, in slave, before testing to ensure my replication works?

Any suggestion highly appreciated. Thanks.

-1

Simply put, if the slave can be started up, both contents of master and slave are perfectly same.

I'll explain the details. The slave is restored from the base backup which is the physical database back up of the master by replaying WAL log (transaction log). So, the slave is a perfect replica of master and the difference between master and slave never occurs if slave can be ran. In other words, if there are any differences between master and slave, the slave cannot be ran.

The simplest test is to create a small table at the master and to confirm the table at the slave, which is mentioned at that document.

Master:

postgres=# create table tbl (data text); insert into tbl values ('test');

Slave:

postgres=# select * from tbl;

If you can find the new table at the slave, the databases of slave is a perfect replica of master data.

Additionally, this is a very rare case, but the piece of data of slave may be corrupted by a failure of the copy tool. If you want to avoid this failure, you should do VACUUM FULL statement for all tables at the master server. If you do VACUUM FULL, all tables will be reconstructed at the slave as well as the master. Therefore, the corrupted data of the slave will be restored. (It's a dirty trick.)

  • It is not necessary to be 100% up to date for the slave to be started (and, to be more precise, to accept connections). It should be in a consistent state, which might mean some or no delay to the master. Furthermore, there are functions to test if there is a delay. Also, I wouldn't be overly confident about this VACUUM FULL trickery. The streaming might fail for a number of conditions, normally not corrupting anything - but if corruption occurs, rebuilding the slave from a fresh base backup is the safe way to go. – dezso Apr 9 '16 at 11:25
  • @dezso, He doesn't care the delay. He only care the synchronization between master and slave at the first time (it is not in running state). So I omitted the descriptions of replication and WAL related functions. – shx Apr 9 '16 at 12:34
  • @dezso, I think you misunderstand about this question. You wrote if corruption occurs, rebuilding the slave from ...., however, he want to know how to detect the difference or corruption. Your comment does not provide any information and solution. I hope the constructive discussion. (So, I answered, if he wants to avoid that failure, do vacuum full. I know this is dirty trick, but there is no way to check between master and slave, except mutual check manually.) – shx Apr 9 '16 at 13:16

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.