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

You can reduce the time much further by pre copying the files. Then during
the maintenance window only copy the deltas basically.

On Sep 1, 2016 9:43 PM, "Patrick B" <patrickbakerbr(at)gmail(dot)com> wrote:

>
>
> 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
>
>