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
