Re: Cascade streaming replication + wal_files - Pgsql 9.2

2016-07-07 14:55 GMT+12:00 Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>:

>
>
> On Thu, 7 Jul 2016, 10:32 a.m. Patrick B, <patrickbakerbr(at)gmail(dot)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?