Given the constraint (from customer side) to having only one database - how can I most effectively deploy new versions of data into a production-facing application.

The new data is packaged into a (-Fc) dump file. This data is supposed to replace the existing data, so restoring it must be done by first dropping the existing tables and then restoring the new ones.

The naive approach of just importing the dump file with pg_restore with --single-transaction will lock the whole database which will cause the application dependent on that data to halt/suspend queries.

My intended approach is to utilize schemas. Basically, the application will have set its connection string to use mydb.public. The import imports all new data into mydb.next.

Then, rename mydb.public to mydb.old, rename mydb.next to mydb.public and drop mydb.old.

Would this approach result in a shorter downtime than importing directly? I am not sure how renames of schemas work internally in postgres, but from my surface understanding, it should be a more efficient approach

  • I don't think --single-transaction will "lock the whole database". To my knowledge it only locks the tables that are being dropped.
    – user1822
    Commented Aug 26, 2021 at 5:55
  • @a_horse_with_no_name even so - during the restore of that table it will be unavailable for the application. with renaming, I expect (hope?) that time will be reduced compared to the transactional approach
    – F.P
    Commented Aug 26, 2021 at 5:59
  • If you rename the schema where the "active" tables are, those are unavailable to the application as well (unless you can change the search path for the databases user during the import)
    – user1822
    Commented Aug 26, 2021 at 6:09
  • Correct, but the overall operation should be faster because I don't need to wait for the data to be written..?
    – F.P
    Commented Aug 26, 2021 at 6:15
  • From my understanding its easy enough to test against your backend technology to see if implies undesired side effects like connections drops o unexpected locks. Special care on the search_path
    – Imanol Y.
    Commented Aug 26, 2021 at 13:37

1 Answer 1


I don't think that creating a new schema and later renaming it will be faster than dropping the tables and re-creating them, but it will certainly reduce the time during which the data are not available to the user.

Note that you have to cancel all other transactions and prevent users from starting new ones when you drop the old schema, otherwise DROP SCHEMA oldschema CASCADE will block until all concurrent transactions are done.

  • That's a good hint, thank you. I'm working on creating a local test setup to see the different methods in action.
    – F.P
    Commented Aug 27, 2021 at 5:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.