Re: Cascade streaming replication + wal_files - Pgsql 9.2

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

On Thu, Jul 7, 2016 at 11:02 AM Patrick B <patrickbakerbr@gmail.com> wrote:

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.

DMS is Database Migration Service from Amazon. :)

It allows you to setup a hybrid architecture like what you are planning to have.

oh ok.... thanks for the tip! 

 

 

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

Since you plan to use this for Production later on, better to use VPC. It also ensures a fixed IP.

I've just checked and is indeed a VPC with fixed IP.

 

 

 

 

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?

No, that's is not what I mean. I think you are mixing it up.

Slave02, with or without archives can always be promoted. I never saw that you will be doing a promotion.

Anyways, for your case, AWS DMS looks like the best option. I suggest that you explore that. 

If I understood it right, your aim is to create a stand alone test DB server restored using backup of slave01 and recovered to latest point in time using archives. Now getting these archives to EC2 is you challenge and hence you are thinking of ways to send archives from slave01 to EC2.

A. Can you not just start the server with the backup itself or do you really need to start EC2 stand alone server with latest transaction? if not then just drop the whole idea of getting the archives restored

Nope.. The EC2 server has to be updated with latest transaction. There will be more ideas around here, that's why we need it updated .

 

B. If you need latest data and transaction before EC2 server is promoted as master, take a look at DMS

Everything else we are discussing/discussed is not the best way and involves workaround

 

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.

yep. typo.

 

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

 

Yes. Infact, slave02 (I now prefer to call it EC2 instance) can be promoted to master even without archives. I assume you don't intend to run it (EC2) as a slave/standby for very long and will promote it to become a standalone test DB server. With that, I would not much worry about archives or timeline switch.

ok cool. 

To finalize:

- slave01 can't archive the wal_files by itself with postgres 9.2. archive_mode and archive_command will be there for nothing if setteled to on or a command(archive_command).

However, I still didn't understand this part. https://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION
Says it is possible: "The cascading replication feature allows a standby server to accept replication connections and stream WAL records to other standbys, acting as a relay. This can be used to reduce the number of direct connections to the master and also to minimize inter-site bandwidth overheads."

- EC2 server can be promoted to a master server at any time, with or without the archives (Just a base backup would be enough)

- slave01 is storing the wal_files from master, and EC2 server will copy them from there(slave01).. using RSYNC or something else.

- in the future, EC2 server will be a streaming replication server replicating the DB from slave01... nothing has to be done here as STREAMING CASCADING is already allowed on 9.2.

- If EC2 server in the future will need to get the wal_files via streaming from slave01, it will have to use the pg_receivexlog tool.