Re: 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

2016-09-02 15:36 GMT+12:00 Venkata B Nagothi <nag1010@gmail.com>:

On Fri, Sep 2, 2016 at 12:48 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

Hi guys,

I'll be performing a migration on my production master database server, which is running PostgreSQL 9.2 atm, from SATA disks to SSD disks.

I've got some questions about it, and it would be nice if u guys could share your experiences/thoughts:

SCENARIO:

I currently have one MASTER and two Streaming Replication Slaves servers...

master01
slave01 (Streaming replication + wal_files)
slave02 (Streaming replication + wal_files)

...Postgres is mounted on: /var/lib/pgsql/... The SSD disks will be installed only on my Master server, because my main problem is Writes and not reads.

The new SSD volume will be mounted on /var/lib/pgsql2/

• The slave02 server will loose the streaming replication connection to the master, once slave01 becomes the new master a new timeline will be settled? Will slave02 be able to connect to the slave01 server for streaming replication?

Yes, slave01 becomes new master with a new timeline id. Cascading replication is supported in 9.2, but, the dependency on WAL archives is a bit heavy. You need to ensure .history file is copied over to slave02. I think, you have WAL archiving enabled, so, should be fine.

hmmm.... is the .history files located into pg_xlog? I can't see none.... are they only generated when a new timeline id is created?

If so, I think it will be fine as they're already being shipped to the slaves

 

MIGRATION OPTIONS:

Migration Option 1: I know this option will work

1. Mount the new volume /var/lib/pgsql2/ on the master01 server
2. Turn slave01 into a master server
3. once I can confirm everything is working fine, I can go to step 4
4. Stop postgres on the master01, start copying the DB using pg_basebackup from slave02 to master01 (Will have to edit postgres to use /var/lib/pgsql2/ instead /var/lib/pgsql - Is that possible? Or I'd have to create a symbolic link?)
5. Start postgres on master01 server and check if all goes well as streaming replication server (Will test it for days)
6. Turn master01 into a master server and I'll have to re-copy the DB into slave01 to make it a streaming replication server again

@ Step 4, you can consider making master01 slave directly by building a new recovery.conf file and copying over slave02's history file by doing which, you can avoid re-build streaming replication from scratch. 

When you "edit postgres", did you mean changing postgresql.conf ? if yes, changing the parameter in postgresql.conf to use the new location should not be a problem.

@ Step 6, Once you turn master01 (new slave) back to master server, you can consider making slave01 (new master) a slave again by copying over the .history files and required WALs. You do not have to build replication from scratch.

Migration Option 2: I don't know if this is possible - IS THIS POSSIBLE????

1. Mount the new volume /var/lib/pgsql2/ on the master01 server
2. Stop postgres on the server (I won't stop postgres on the slave so the users will be able to use the server as read-only)
3. Copy the data from /var/lib/pgsql/ to /var/lib/pgsql2/
4. Configure postgres to start using the new volume(/var/lib/pgsql2/)

 This looks pretty straight forward. The only issue would be that, users will not be able to do writes. If you are bringing down master and starting up again, it should not a problem, slaves should be able catch up again.

What do you guys think? Is option possible? if so it would be much easier :)

Well, both the options work based on your expectations, Application requirements on downtime, SLAs etc. 

So is that really possible? Just copy the data between folders? if so, i'll probably chose option 2!!!

Even that is 2.5TB I don't think the copy will take longer than 20 minutes... and I'd still be able to perform reads...

I'll do some test to see if option 2 can be done :)

Thanks !!! :D