Seamlessly updating a postgres database - schemas, rename, how?

Actually a simple question, but I wasn't able to find any good conclusive开发者_开发技巧 answer.

Assuming a production database foo_prd, and a newer version of the same foo_new (on the same server) that is supposed to replace the old one. What is the cleanest way to seamlessly switch from _prd to _new?

RENAME-ing the databases would require to disconnect the current users via their pid. That would take down some requests, and new users might connect during the process. I was thinking of creating the tables of the new database as different SCHEMA and then change the search_path, e.g. from "$user",prd to "$user",new,prd.

What could possibly go wrong? Do you have any better suggestions? Am I taking the wrong approach altogether?

---

Do as you suggest: create the tables of the new database as different schema and then change the search_path.

But also create a user with the same name as the new schema and test everything before changing the search_path by logging in as this user with each of your apps - the new schema will be first in that user's search_path by default because the name matches.

Finally, take care when you come to drop the old schema - I suggest renaming first in case anything refers to it's objects using a qualified reference (eg prd.table or prd.function). After a few days/weeks it can then be dropped with confidence.

---

I would version my schema, and change my app to point to the new schema when ready.