Restoring a database problem
I have had to do this so rarely and it has almost always been in a bit of a panic so may well be missing something really obvious. What I want to know is how to quiese a database to that I can restore it. I need to close all existing connections and the prevent people/processes from connecting again until the restore has completed. Currently I have been logging into a bunch of servers and stopping various daemons, then on the database server killing processes until the database is apparently idle then dropping the database and doing the restore. Then restarting the daemons etc. I am sure I am not doing this the right way so advice gratefully received. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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"
Re: Restoring a database problem
> I need to close all existing connections and the prevent people/processes from > connecting again until the restore has completed. I was hoping there was a command in psql that would do both, that is, kick the exisiting connections and stop new ones. It was being a bit optimistic I think, but such a command would be really useful, IMHO. Modifying pg_hba.conf, is a little complicated. The daemons are using the db owner's credential and the backups are on a different server so I still need to be able to connect to do the restore. I guess I will need something like local mydb all reject host mydb postgres 0.0.0.0/0 password host mydb postgres ::0/0 password host mydb all 0.0.0.0/0 reject host mydb all ::0/0 reject then systemctl reload postgresql-10 then from the server with the backup on it pg_restore -h db-server -Upostgres -c -C -d mydb mydb-backup Modifying pg_hba isnt going to kick the existing connections, so I will need to do that either using psql and the commands in the article Rob posted, (I think I have used that method somewhere already). or kill them on the DB server -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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" signature.asc Description: OpenPGP digital signature
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"
Re: Moving the master to a new server
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"
Re: Moving the master to a new server
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"
