Обсуждение: 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

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?

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

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

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

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.

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

...

 

You can actually reduce the time more by pre-syncing to the new location. 

something like:

rsync -va /var/lib/pgsql/ /var/lib/pgsql2/

service postgres stop

rsync -va /var/lib/pgsql/ /var/lib/pgsql2/

The second rsync will only copy the deltas from the first, it still has to go in and determine what needs to be copied/what changed but the bulk of it can be prepared/migrated before the actual downtime window.

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

Please include the mailing list in replies...

On 9/7/16 6:10 PM, David Gibbons wrote:

    That is NOT safe. The problem is it allows rsync to use mtime alone
    to decide that a file is in sync, and that will fail if Postgres
    writes to a file in the same second that the first rsync reads from
    it (assuming Postgres writes after rsync reads). You need to add the
    --checksum flag to rsync (which means it will still have to read
    everything that's in /var/lib/pgsql).


The checksum flag as you mention is not performant,

Definitely not. :/

If this is a concern, you're much better using the *--modify-window *flag:
When comparing two timestamps, rsync treats the timestamps as being
equal if they differ by no more than the modify-window value. This is
normally 0 (for an exact match), but you may find it useful to set this
to a larger value in some situations.

Hence, rsync -va --modify-window=1 would remove your concern about a
same second race condition without forcing the sync to read through all
the files.

Very interesting and useful!

On 9/7/16 6:10 PM, David Gibbons wrote:

    That is NOT safe. The problem is it allows rsync to use mtime alone
    to decide that a file is in sync, and that will fail if Postgres
    writes to a file in the same second that the first rsync reads from
    it (assuming Postgres writes after rsync reads). You need to add the
    --checksum flag to rsync (which means it will still have to read
    everything that's in /var/lib/pgsql).


The checksum flag as you mention is not performant,

Definitely not. :/

If this is a concern, you're much better using the *--modify-window *flag:
When comparing two timestamps, rsync treats the timestamps as being
equal if they differ by no more than the modify-window value. This is
normally 0 (for an exact match), but you may find it useful to set this
to a larger value in some situations.

Hence, rsync -va --modify-window=1 would remove your concern about a
same second race condition without forcing the sync to read through all
the files.

Very interesting and useful!

Isn't this heading in the wrong direction?   We need to be more precise than 0 (since 0 is computed off of rounded/truncated time stamps), not less precise than 0.

Cheers,

Jeff

On 9/8/16 3:29 PM, David Gibbons wrote:

    Isn't this heading in the wrong direction?   We need to be more
    precise than 0 (since 0 is computed off of rounded/truncated time
    stamps), not less precise than 0.

    Cheers,

    Jeff



Hmm, You may be right, reading it 4 more times for comprehension it
looks like it should be set to -1 not 1.

Not according to my man page:

       --modify-window
              When comparing two timestamps, rsync treats the timestamps as being equal if they differ by no more than the modify-window value.  This is normally 0 (for an exact match), but you
              may find it useful to set this to a larger value in some situations.  In particular, when transferring to or from an MS Windows FAT  filesystem  (which  represents  times  with  a
              2-second resolution), --modify-window=1 is useful (allowing times to differ by up to 1 second).

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sep 12, 2016 1:12 AM, "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
>
>
>
> Why not subscribe a new cluster on the same box with pg_basebackup?

+1.

Maybe he is afraid of (or doesn't know how to) configuring things to run on a non standard port, for testing?

On 9/8/16 3:29 PM, David Gibbons wrote:

    Isn't this heading in the wrong direction?   We need to be more
    precise than 0 (since 0 is computed off of rounded/truncated time
    stamps), not less precise than 0.

    Cheers,

    Jeff



Hmm, You may be right, reading it 4 more times for comprehension it
looks like it should be set to -1 not 1.

Not according to my man page:

       --modify-window
              When comparing two timestamps, rsync treats the timestamps as being equal if they differ by no more than the modify-window value.  This is normally 0 (for an exact match), but you
              may find it useful to set this to a larger value in some situations.  In particular, when transferring to or from an MS Windows FAT  filesystem  (which  represents  times  with  a
              2-second resolution), --modify-window=1 is useful (allowing times to differ by up to 1 second).