CODEWITHSUNDEEP
databasepostgresql
parthsavi
parthsavi

Reputation: 81

swapping production schemas in postgres

I have existing schema called current_schema.I receive fresh 3rd party data every few hours. I want to replace data in it every few hours and here's what I am planning to do

1- write that fresh data to postgres temp schema
2- rename current_schema to current_schema_del
3- rename temp to current_schema
4- delete current_schema_del

Now, what will happen if someone tries to access/modify data when I am performing step 2 and 3? Is this the right way to go about it?

Upvotes: 4

Views: 856

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

Your procedure should work fine.

As step 2, you can also delete the old schema right away.

Deleting will require ACCESS EXCLUSIVE locks on the schema and all objects in it, so it will have to wait until all earlier transactions accessing these objects are done.

One option is to employ a function (with SECURITY DEFINER and owned by a superuser) that kills all transactions that access such objects so that you don't have to wait for long running transactions to end.

Upvotes: 3

Related Questions