Re: Clustering postgresql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

On Mon, 6 Sep 2004, [ISO-8859-1] Björn Voigt wrote:

> has postgresql the functionality for clustering, load balancing
> and failover. I have to setup two redundant web-servers with and
> want run a dbms cluster on this machines.
>
> My webapps should only see one dbms, but there should be two
> redundant dbms. I know that mysql 4.1 supports this feature, but
> mysql is not a ... ;-) so I want use PostgreSQL

If you haven't written your application yet, consider using C-JDBC:

http://c-jdbc.objectweb.org/
"C-JDBC provides a flexible architecture that allows you to achieve
scalability, high availability and failover with your database tiers.
C-JDBC instantiates the concept of RAIDb : Redundant Array of Inexpensive
Databases. The database is distributed and replicated among several nodes
and C-JDBC load balance the queries between these nodes."

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org devrim.gunduz~linux.org.tr
http://www.tdmsoft.com
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBRLDitl86P3SPfQ4RArv7AKCYsJL24XbITEAcHjJNPeJxtt4AhACdHVZS
8WJsurXkzVaiwliJLXw96cY=
=6SL7
-----END PGP SIGNATURE-----
>From pgsql-general-owner(at)postgresql(dot)org Mon Sep 13 09:12:55 2004
X-Original-To: pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (unknown [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id CA9CC329E15
for <pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Mon, 13 Sep 2004 09:12:51 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 89692-01
for <pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Mon, 13 Sep 2004 08:12:46 +0000 (GMT)
Received: from webmail.muc.domeus.com (mail.ecircle.de [195.140.186.200])
by svr1.postgresql.org (Postfix) with ESMTP id 61C98329D9C
for <pgsql-general(at)postgresql(dot)org>; Mon, 13 Sep 2004 09:12:45 +0100 (BST)
Received: from deimos.muc.ecircle.de (deimos.muc.ecircle.de [192.168.1.4])
by webmail.muc.domeus.com (READY) with SMTP id 24763ACE;
Mon, 13 Sep 2004 10:12:44 +0200 (CEST)
Received: from [192.168.1.110] ([192.168.1.110]) by deimos.muc.ecircle.de with Microsoft SMTPSVC(5.0.2195.6713);
Mon, 13 Sep 2004 10:12:51 +0200
Subject: Re: Best practices for migrating a development database
From: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
To: "Thomas F.O'Connell" <tfo(at)sitening(dot)com>
Cc: Collin Peters <cpeters(at)mcrt(dot)ca>,
Postgres general mailing list <pgsql-general(at)postgresql(dot)org>
In-Reply-To: <58EA6FBC-03C4-11D9-8609-000D93AE0944(at)sitening(dot)com>
References: <8Mm0d(dot)175462$X12(dot)62363(at)edtnps84>
<58EA6FBC-03C4-11D9-8609-000D93AE0944(at)sitening(dot)com>
Content-Type: text/plain
Message-Id: <1095063156(dot)4582(dot)17(dot)camel(at)localhost(dot)localdomain>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2)
Date: Mon, 13 Sep 2004 10:12:37 +0200
Content-Transfer-Encoding: 7bit
X-OriginalArrivalTime: 13 Sep 2004 08:12:51.0607 (UTC) FILETIME=[76BF2270:01C49969]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level:
X-Archive-Number: 200409/573
X-Sequence-Number: 65779

Beside version controlled schema files we have a guy who writes
migration scripts based on the old schema and the new (development)
schema (frozen e.g. by branching in CVS).
Usually there are 3 steps involved:
- a pre-migration script, which prepares the data base for the new
schema, by adding the new structures needed for the data migration;
- a data migration script, which moves around data between the old and
the new structures;
- a finalization script, which removes the old structures not needed
anymore;

I think there's no way to make any of these steps automatically computed
as a diff between the old and new schemas...
We usually do it anyway so that after step 1 was executed, both the old
version of the application and the new version can work at the same
time, and the new version will only use the data migrated by step 2, but
I suppose our application is not very typical (we have lots of distinct
customers which live in the same data base but have distinct data).
This also means we try to do minimal changes to the data base and we try
to only have additions, no modifications, this makes migration easier.

HTH,
Csaba.

On Sat, 2004-09-11 at 09:29, Thomas F.O'Connell wrote:
> One thing I used to do (and I won't necessarily claim it as a best
> practice) was to maintain my entire data model (tables, functions,
> indexes, sequences) as SQL (plus postgres extensions) CREATE statements
> in text files that were version controlled (via CVS). I had an entire
> set of utilities that could modify the existing database as necessary
> to treat the SQL files as authoritative. For anything new, the create
> statements sufficed, but for modifications, some objects had to be
> regenerated. When it was time to release, we would export the textual
> SQL schema to the production server, make the necessary updates using
> my utilities, and then restart services.
>
> Since I'm deploying postgres in new environments now, and I left these
> utilities behind at another job (where they're still in use), I've been
> thinking more about the concept of schema version control. But I'm
> similarly interested in any concepts of best practices in this area.
>
> -tfo
>
> On Sep 10, 2004, at 1:55 PM, Collin Peters wrote:
>
> > I have searched the Internet... but haven't found much relating to
> > this.
> >
> > I am wondering on what the best practices are for migrating a
> > developmemnt database to a release database. Here is the simplest
> > example of my situation (real world would be more complex).
> >
> > Say you have two versions of your application. A release version and
> > a development version. After a month of developing you are ready to
> > release a new version. There have been many changes to the
> > development database that are not in the release database. However,
> > the release database contains all your real information (customers,
> > etc...). What is the best practice for migrating the development
> > database to the release database?
> >
> > I have thought of the following situations:
> > -Simply track all the changes you made to the development database and
> > make the same changes to the release database
> > -Back up the release database... overwrite it with the development
> > database... then copy all your real data back into the release
> > database (this last step is probably quite difficult)
> > -Perhaps some combination of the two
> >
> > Does anybody have any recommendations?
> >
> > Regards,
> > Collin Peters
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend