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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

2014-06-18 17:47 GMT-03:00 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general