Re: Cascade streaming replication + wal_files - Pgsql 9.2

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

>
>
> On Thu, Jul 7, 2016 at 11:02 AM Patrick B <patrickbakerbr(at)gmail(dot)com>
> wrote:
>
>> 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.
>>
>
> 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.