CODEWITHSUNDEEP
pythondatabasepostgresqlrestore
user1253493
user1253493

Reputation: 67

Restore postrgres without ending connections

I run a number of queries for adhoc analysis against a postgres database. Many times I will leave the connection open through the day instead of ending after each query.

I receive a postgres dump over scp through a shell script every five minutes and I would like to restore the database without cutting the connections. Is this possible?

Upvotes: 0

Views: 1215

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248245

One of the few activities that you cannot perform while a user is connected is dropping the database.
So – if that is what you are doing during restore – you'll have to change your approach. Don't drop the database (don't use the -C option in pg_dump or pg_restore), but rather drop and recreate the schemas and objects that don't depend on a schema (like large objects).
You can use the -c flag of pg_dump or pg_restore for that.

The other problem you might run into is connections with open transactions (state “idle in transaction”). Such connections can hold locks that keep you from dropping and recreating objects, and you'll have to use pg_terminate_backend() to get rid of them.

Upvotes: 1

Related Questions