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