Thread: Replacing a production db

On Wed, Jun 18, 2014 at 05:05:22PM -0300, Nicolás Lichtmaier wrote:

> 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?

I guess I don't understand why you'd need to rename the database.
What is the problem you're trying to solve?

Is the idea that you have changes in the schema in the new database
that are not reflected in the old database?  If so, what do you do
with all the data?  Doesn't that have to be in the new schema somehow?

One thing you can do with PostgreSQL that you can't do with MySQL is
change the schema in a transaction.  So you could make the schema
changes that way.

If the idea is instead to run two schemas in parallel (so that you can
have old and new versions of the application running at the same
time), what you really want to do us use the schema (or namespace)
support in Postgres.  Be careful with this, however, as it is easy to
make a system so convoluted that nobody can understand it.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On Wed, 18 Jun 2014 17:05:22 -0300 Nicolás Lichtmaier <nico.lichtmaier@gmail.com> 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?

As someone who's fought with release engineering for a few years now,
let me start off by saying that you're probably doing it wrong.

That being said, you can rename databases in Postgres just like you
can in MySQL (in fact, it's more reliable in Postgres) so I don't
understand why you can't continue to do it that way.

--
Bill Moran <wmoran@potentialtech.com>

On 18/06/14 21:50, Nicolás Lichtmaier wrote:
> I'm probably doing this wrong, but I couldn't find any resources to
> learn how to get this right.
>
> Can the renaming be done without disruption to current connections?
> From what I've read you have to disconnect everyone before renaming a
> database.

I'd change the pointer instead; in your client or front-end application
at your release point, change the reference to which database to work
with to the new one.

--
Tim Clarke

On 6/18/2014 1: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?

we do updates of our production systems with .sql files that are version
deltas, eg, they alter existing tables and add new ones, possibly
including new 'static data'.   if the update requires code changes, then
we shut the app(s) down, run the database upgrade, install the new code,
and restart the app(s).   Usually we try to do our schema updates so the
old code will continue to work with the new schema, so the new code can
be cut in incrementally with virtually zero downtime.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

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