Thread: 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"

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.

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"

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 ?

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"