Will we face any runtime issue in application if pg_restore is in progress?

Question

I am new to postgres restore thing. We have an application in which multiple services are using postgres DB. We are adding support of Backup/Restore functionality for this application.

It will take dump using pg_dump. And when user triggers restore workflow, it will invoke "pg_restore".

So if "pg_restore" is running and at that time application is running some queries what will be the impact? Will it face any runtime errors? Will existing DB connections be dropped by postgres during pg_restore?

Answer 1

No, you have to disconnect the users before you run pg_restore.

pg_restore expects an empty database, but you can use the options --clean and --create to drop and re-create the database. Now DROP DATABASE will block until the last session has disconnected from the database.

So it is up to you to first disconnect the sessions, either by calling the pg_terminate_backend functoin on the appropriate sessoins or by using DROP DATABASE ... (FORCE), which exists from v13 on.