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

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.

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

Regards,
Venkata B N

Fujitsu Australia