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

2016-09-02 15:36 GMT+12:00 Venkata B Nagothi <nag1010(at)gmail(dot)com>:

>
> On Fri, Sep 2, 2016 at 12:48 PM, Patrick B <patrickbakerbr(at)gmail(dot)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