CODEWITHSUNDEEP
postgresqlpsqlpg-restorebackup-sqldatabase
Mohamed BOUAKKAZ
Mohamed BOUAKKAZ

Reputation: 45

How to lock Data Base when pg_restore runs

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

Upvotes: 0

Views: 1046

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246818

  • 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

Upvotes: 1

Julius Tuskenis
Julius Tuskenis

Reputation: 1620

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:

  1. Create a new database.
  2. Grant CONNECT permission to that database to a single user used in pg_restore command (--username=db-admin).
  3. Restore.
  4. 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

Upvotes: 1

Related Questions