From: | Glen Eustace <geustace(at)godzone(dot)net(dot)nz> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Restoring a database problem |
Date: | 2020-10-03 19:35:12 |
Message-ID: | 86ff9561-b8a7-501e-581e-4cecf3296b78@godzone.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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"
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Inder | 2020-10-04 16:54:03 | Writing WAL files |
Previous Message | Peter J. Holzer | 2020-10-03 11:30:50 | Re: Restoring a database problem |