Re: Cascade streaming replication + wal_files - Pgsql 9.2

2016-07-07 14:55 GMT+12:00 Sameer Kumar <sameer.kumar@ashnik.com>:

On Thu, 7 Jul 2016, 10:32 a.m. Patrick B, <patrickbakerbr@gmail.com> wrote:

The slave02 server will be a copy of the DB into Amazon. There will be a migration to Amazon in the future, and the company's managers want a copy of the current DB there to test and do all the stuffs they need (migrating to 9.5, too).

Have you checked out Amazon's DMS?

Like I said.. it's gonna be a test server. Does not need to be powerful or to use tools.. a EC2 would be enough.

 

slave01 is already working as a streaming replication server.

The master server sends the wal_files to slave01 by archive_command.

The plan below isn't my idea, I would do different but isn't my call:

Been there :)

 

Current scenario:

master stores wal_files into slave01

slave02 does not exists

The plan is:

1. setup slave02 at amazon EC2 (just for testing and future master server for devs)

Is it EC2 Classic? Or is it EC2 Virtual Pvt Cloud (VPC)?

Have no idea lol - I believe it will be classic....

 

 

2. setup postgres on slave02 (9.2)

3. pg_basebackup will be run from slave01. This will split the base in files of 50GB each (example)

4. Send the splitted files from slave01 to slave02

5. restore/join the files

6. start postgres on the slave02 slave

7. restore the DB using the wal_files from slave01

Given that slave02 is a standby, how do you plan on doing your regression testing? It will be just a read only database. 

hmmm... do u mean by this, that I won't be able to turn slave02 as a master?

 

You can restore the wal_file by specifying resotre_command to copy from the archive generated by the master (rsync or scp to pull from your in-premise setup to EC2). This would be fairly simple if you are using VPC

ok. so a RSYNC would grab the wal-files from the current folder on the slave01 server, and send them to slave02.

easy

 

Question:

Is possible to make slave01 archive the wal_files?

If you really can not just live with archive generated on master itself, you need to try the options discussed up thread.

I'd prefer, but I can't lol

 

1. Copy the archives generated on master to a shared location or may be copy it to S3 bucket

as i said, the servers will be migrated to amazon, any change now will not be approved.

 

2. Archive generated on master is rsync (schedule basis) to EC2

3. pg_receivexlog running on EC2 to copy WAL from slave01

this is not needed, as the wal_files will be sent by RSYNC from slave01.

 

can just be a archive_command and archive_mode = on?

Setting these parameters on slave02 will have not any effect. 

slave01* not slave02.

With this scenario, slave02 will be able to turn up to a master server in the future?