Testing postgresql 9.4 streaming replication

Question

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.

Answer 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.)