How to lock Data Base when pg_restore runs

Question

When running pg_restore we encounter some (unexpected) executions from some other programs resulting in errors in the restoration.

Is there a way to lock the DB while restoring to insure no modifications are done ?

This is the command executed :

pg_restore --clean --verbose --no-owner /home/postgres/backup/bkp --if-exists -j 7 -d restdb01 -p 5432

score 1 · Answer 1 · answered Mar 29 '21 at 10:52

PostgreSQL documentation of pg_restore does not provide any info of such functionality.

According to wiki postgresql does not support database locking.

I suggest using permission mechanism to prevent anyone connecting to the database beeing restored:

Create a new database.
Grant CONNECT permission to that database to a single user used in pg_restore command (--username=db-admin).
Restore.
Add all the required permissions to the database.

Unless you have applications connecting to your server as superusers, that will prevent applications from reading or changing

score 1 · Accepted Answer · answered Mar 29 '21 at 13:05

revoke connect permissions from the database or reject connections in pg_hba.conf (remember that by default, PUBLIC can connect to the database)
use pg_terminate_backend to cancel all existing connections
run pg_restore as a user that can still connect to the database

2 Answers2