Moving the master to a new server
I need to move my master postgresql deployment to a new server.
I am comfortable with stopping all connections then doing a pg_dumpall >
psql to move the databases, they are not huge so this completes in an
acceptable time and I am not expecting any data loss but I am unsure of
what impact this will have on the streaming replication. I will be
rebooting the new server with the old servers network configuration so I
am hoping that when I let connections back in, replication will just
restart but I cant find any documentation that says so.
Currently the slave serves as a read-only target for various services so
they should all just keep running.
The postgresql versions are 10.20 on the old and 10.17 on the new.
(Basically this is a CentOS7 to Rocky8 migration) I have been using the
PGDG rhel version so it is a little ahead of the appstream
Comments ?
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 4446 Ph +64 6 357 8168, Mob +64 27 542 4015
“Specialising in providing low-cost professional Internet Services since 1997"
On Tue, 2022-02-15 at 08:29 +1300, Glen Eustace wrote:
I need to move my master postgresql deployment to a new server.
I am comfortable with stopping all connections then doing a
pg_dumpall >
psql to move the databases, they are not huge so this completes in
an
acceptable time and I am not expecting any data loss but I am
unsure of
what impact this will have on the streaming replication.ￜ I will be
rebooting the new server with the old servers network configuration
so I
am hoping that when I let connections back in, replication will
just
restart but I cant find any documentation that says so.
pg_dump -> restore will break your streaming replication. You'll need
to set it up again.
If the PG version isn't changing and you're still on the same version
of Linux, rsync would be easier.
On 15/02/22 8:39 am, Alan Hodgson wrote:
pg_dump -> restore will break your streaming replication. You'll need
to set it up again.
That's what I thought might be the case.
If the PG version isn't changing and you're still on the same version
of Linux, rsync would be easier.
I did an ELevate upgrade on the slave from CentOS7 to Rocky8 and then
just rename 10/data to data and that seemed to work just fine.
But upgrading that way takes too long for the master so I build a new
server instead. So, if I shutdown both postgresql instances old and new,
rsync the data directory and restart on the new. I should be OK ?
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 4446 Ph +64 6 357 8168, Mob +64 27 542 4015
“Specialising in providing low-cost professional Internet Services since 1997"
On Tue, 2022-02-15 at 08:58 +1300, Glen Eustace wrote:
But upgrading that way takes too long for the master so I build a
new
server instead. So, if I shutdown both postgresql instances old and
new,
rsync the data directory and restart on the new. I should be OK ?
Should be, yeah.
Hi,
another way would be to, while everything running, you create a second
slave on the new machine on rocky8 with a pg_basebackup.
and start the new slave.
when low activity, you just stop the master, then promote the slave => new
master up
then modify the connection line in your recovery.conf file in the old
slave, and restart it.
maybe adding first:
recovery_target_timeline latest in the recovery.conf file
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
On Mon, Feb 14, 2022 at 8:59 PM Glen Eustace <geustace@godzone.net.nz>
wrote:
Show quoted text
On 15/02/22 8:39 am, Alan Hodgson wrote:
pg_dump -> restore will break your streaming replication. You'll need
to set it up again.That's what I thought might be the case.
If the PG version isn't changing and you're still on the same version
of Linux, rsync would be easier.I did an ELevate upgrade on the slave from CentOS7 to Rocky8 and then
just rename 10/data to data and that seemed to work just fine.But upgrading that way takes too long for the master so I build a new
server instead. So, if I shutdown both postgresql instances old and new,
rsync the data directory and restart on the new. I should be OK ?--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 4446 Ph +64 6 357 8168, Mob
+64 27 542 4015“Specialising in providing low-cost professional Internet Services since
1997"
On 16/02/22 1:58 am, Marc Millas wrote:
another way would be to, while everything running, you create a second
slave on the new machine on rocky8 with a pg_basebackup
Thanks, I did consider this as well. Last night I did the move using
the rsync approach and it worked very well.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 4446 Ph +64 6 357 8168, Mob +64 27 542 4015
“Specialising in providing low-cost professional Internet Services since 1997"