Re: Replacing a production db

On 6/18/2014 3:05 PM, Nicolás Lichtmaier wrote:
> Is there a way to replace a production database with another as part of
> a new "release" of our website?
>
> Where I work we have a scheme of pushing code+db releases to "testing"
> and then to "production". Most of our databases use MySQL and I was told
> they can just rename the db and it works. We are adopting PostgreSQL for
> some new developments and we'd like to do something similar. I've tried
> loading the dump in a single transaction, but that has many problems as
> the database is fairly big for that (some GBs). Is there a trick I'm
> missing here?
>
> Thanks a lot!
>
> Nicolás.-

In the past I "enjoyed" using mysql on our website. (hopefully the
Secret Service sarcasm detector went off). I loved it when "mysql has
gone away", and when mysqldump created a dumpfile that couldn't be
restored. I loved how DDL was not transaction safe. There were many
times we had to switch off a website and fix the production database.

We use PG now, our website is 90% read-only, and we get two types of
updates. Full and partial.

I create an update shcema, and copy all the data into it. Once its
ready, depending on the type I:

Full Update:
begin;
drop table public.general;
alter table update.general set schema public;
.. drop next table
.. move it from update to public .. etc
commit;
drop schema update cascade;

Partial:
begin
delete from public.general where magickey in
(select magickey from update.general);
insert into public.general
select * from update.general;
... copy data for other tables ...
drop schema update cascade;

The updates are done in a single transaction so website visitors see
either the old data, or the new. Using this method, and PG, I have
never once had to show the "This website is being updated and will be
back in a moment" page. (In fact, I don't even have one of those pages
anymore).

-Andy