Thread: 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"
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
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 Thu, Oct 1, 2020 at 01:00:21PM +1300, Glen Eustace wrote: > 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. I would modify pg_hba.conf to block access temporarily. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
On Sep 30, 2020, at 6:11 PM, Bruce Momjian <bruce@momjian.us> wrote:
https://www.google.com/url?sa=t&source=web&cd=&ved=2ahUKEwiB56D1k5LsAhXBAp0JHQkYA3MQFjAAegQIBxAB&url=https%3A%2F%2Fstackoverflow.com%2Fquestions%2F35319597%2Fhow-to-stop-kill-a-query-in-postgresql&usg=AOvVaw0NOCdUxbKHpDTqHulgubqCOn Thu, Oct 1, 2020 at 01:00:21PM +1300, Glen Eustace wrote:I have had to do this so rarely and it has almost always been in a bit of apanic 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 fromconnecting again until the restore has completed.Currently I have been logging into a bunch of servers and stopping variousdaemons, then on the database server killing processes until the database isapparently idle then dropping the database and doing the restore. Thenrestarting the daemons etc. I am sure I am not doing this the right way soadvice gratefully received.
I would modify pg_hba.conf to block access temporarily.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On 9/30/20 7:11 PM, Bruce Momjian wrote: > On Thu, Oct 1, 2020 at 01:00:21PM +1300, Glen Eustace wrote: >> 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. > I would modify pg_hba.conf to block access temporarily. As would I; it's the first thing I thought of... -- Angular momentum makes the world go 'round.
On 2020-09-30 20:11:12 -0500, Ron wrote:
> On 9/30/20 7:11 PM, Bruce Momjian wrote:
> > On Thu, Oct 1, 2020 at 01:00:21PM +1300, Glen Eustace wrote:
> > > 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.
[...]
> > I would modify pg_hba.conf to block access temporarily.
>
> As would I; it's the first thing I thought of...
Interesting. The first thing I thought of was "iptables"[1]. Probably shows
that I'm a Linux guy first and a database guy second.
hp
[1] Yes, I know that this doesn't affect connections through Unix
sockets.
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment
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"