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

Question

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?

Not sure that renaming would really require a forced disconnect. I think (not tested) the rename would wait until all connections are gone. Of course the two renames should be done in a single transaction. — user330315
– user330315, Commented Dec 3, 2010 at 11:00
I think the traditional way to make large changes in production is to script schema and data updates, backup, test offline, and apply on the existing database. That doesn't require keeping two sets of data around, or having the old database read-only for a while. — Tobu
– Tobu, Commented Dec 5, 2010 at 15:12
Oh, read-only is ok. Any downtime is not. - It is hardly accessed for writing anyway. But updating the data over the existing ones is difficult, because the update needs to be atomic and no clear, granular mapping exists what to overwrite and what to delete. (specifically: having half a map update for geodata is a horrible idea. And you can't just replace/overwrite in small areas with new roads being added and old roads disappearing) That said, I might be missing something very simple. — relet
– relet, Commented Dec 5, 2010 at 17:07
@relet can I suggest calling your new schema something other than new? — user533832
– user533832, Commented Dec 10, 2010 at 9:01

score 1 · Accepted Answer · 2010-12-10 08:59:08Z

1

+50

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.

edited Dec 10, 2010 at 8:59

answered Dec 8, 2010 at 16:49

user533832

Sign up to request clarification or add additional context in comments.

Comments

Scott Marlowe · Accepted Answer · 2010-12-06 03:05:19Z

0

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

answered Dec 6, 2010 at 3:05

Scott Marlowe

9,0403 gold badges27 silver badges21 bronze badges

1 Comment

relet Over a year ago

Thanks. I agree this would work, and I have done that in the past - just that in our case 'the app' is a decent number of small, agile services distributed over several machines.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related