Restoring a database problem

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:

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

https://www.google.com/url?sa=t&amp;source=web&amp;cd=&amp;ved=2ahUKEwiB56D1k5LsAhXBAp0JHQkYA3MQFjAAegQIBxAB&amp;url=https%3A%2F%2Fstackoverflow.com%2Fquestions%2F35319597%2Fhow-to-stop-kill-a-query-in-postgresql&amp;usg=AOvVaw0NOCdUxbKHpDTqHulgubqC

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]Yes, I know that this doesn't affect connections through Unix sockets.. 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.
sockets.

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

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"