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.0.0/0 password
host mydb postgres ::0/0
password
host mydb all 0.0.0.0/0 reject
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
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"
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
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"
Attachment:
signature.asc
Description: OpenPGP digital signature
