2016-07-07 15:19 GMT+12:00 Sameer Kumar <sameer.ku...@ashnik.com>: > > > On Thu, Jul 7, 2016 at 11:02 AM Patrick B <patrickbake...@gmail.com> > wrote: > >> 2016-07-07 14:55 GMT+12:00 Sameer Kumar <sameer.ku...@ashnik.com>: >> >>> >>> >>> On Thu, 7 Jul 2016, 10:32 a.m. Patrick B, <patrickbake...@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.
