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

On Fri, Sep 2, 2016 at 2:40 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
>

No. The issue in 9.2 ( or rather until version 9.2) is, the WAL generated
after the master is shutdown (which is supposed to have the last known
status of master) is not automatically transferred to slave which is very
important when you are swapping over master-slave roles. You can only do
that manually and there is no way postgres does it automatically and same
would be the issue when attempt to make master a new slave (without
building slave from scratch). When you promote slave .history file gets
generated with a new timeline id, which you need to make master a new
slave. All of this is fixed in 9.3.

I wanted to stress on this to point out that master-slave roles can be
reversed without actually needing to build replication from scratch and is
trickier in the versions 9.2 and earlier.

Anyways, this may not be that important now as you confirmed that you are
going for option 2

Regards,
Venkata B N

Fujitsu Australia