pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PostgreSQL Migration of databases 7.2 to 7.4
3+ messages / 3 participants
[nested] [flat]

* PostgreSQL Migration of databases 7.2 to 7.4
@ 2004-08-24 06:25  Mario Thomas <mario@alamar.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Mario Thomas @ 2004-08-24 06:25 UTC (permalink / raw)
  To: pgsql-admin

Hello All,

We have a server in our datacentre running PostgreSQL 7.2 with about 50
databases set up (for different customers). We have just commissioned a
much better specified server which is running PostgreSQL 7.4.

We need to migrate the databases from 7.2 to 7.4. The smallest database
contains about 200K of data whilst the largest database weighs in at
about 100Mb.

What's the best way to migrate the data? Would you suggest using the
pgdump tools? We've found them to be quite problematic in use and are
not entirely sure of the best command line switches to use.

We need to make sure the data and permissions get migrated, as the data
is being used for live web sites. So obviously speed of migration is
important as we will have a period of downtime to complete the
migrations.

Any suggestions/product ideas would be greatly appreciated.
Thanks in advance,

Mario






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PostgreSQL Migration of databases 7.2 to 7.4
@ 2004-08-24 17:04  Christopher Browne <cbbrowne@acm.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Christopher Browne @ 2004-08-24 17:04 UTC (permalink / raw)
  To: pgsql-admin

Quoth "Mario Thomas" <mario@alamar.net>:
> We have a server in our datacentre running PostgreSQL 7.2 with about 50
> databases set up (for different customers). We have just commissioned a
> much better specified server which is running PostgreSQL 7.4.
>
> We need to migrate the databases from 7.2 to 7.4. The smallest database
> contains about 200K of data whilst the largest database weighs in at
> about 100Mb.
>
> What's the best way to migrate the data? Would you suggest using the
> pgdump tools? We've found them to be quite problematic in use and are
> not entirely sure of the best command line switches to use.
>
> We need to make sure the data and permissions get migrated, as the data
> is being used for live web sites. So obviously speed of migration is
> important as we will have a period of downtime to complete the
> migrations.

The way to minimize the downtime is to use one of the replication
systems to replicate data between two database instances.  It may take
a while to set up the replica, but once it is in place, if you keep it
fairly up to date, the switchover between versions should just take
the few minutes needed to:

 a) Stop new data from going into the old database;
 b) Allowing all updates to get to the new database;
 c) Switching access over to the new database.

Unfortunately, the "latest and greatest" option, Slony-I, does not
support versions younger than 7.3.3, so you'd have to look at other
solutions such as ERServer.

Earlier in the year, this approach was used at Afilias, in conjunction
with ERServer, to migrate systems from 7.2.4 to 7.4.2.
-- 
output = ("cbbrowne" "@" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/internet.html
Epistemology in One Lesson
Reality ruthlessly selects out creatures that embody hypotheses too
inconsistent with reality. Our only choice is whether we participate
by being selected out, or (in Popper's great phrase) by "letting our
ideas die in our stead."
-- Mark Miller



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PostgreSQL Migration of databases 7.2 to 7.4
@ 2004-08-24 17:42  Bruno Wolff III <bruno@wolff.to>
  parent: Mario Thomas <mario@alamar.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Bruno Wolff III @ 2004-08-24 17:42 UTC (permalink / raw)
  To: Mario Thomas <mario@alamar.net>; +Cc: pgsql-admin

On Tue, Aug 24, 2004 at 07:25:46 +0100,
  Mario Thomas <mario@alamar.net> wrote:
> Hello All,
> 
> We have a server in our datacentre running PostgreSQL 7.2 with about 50
> databases set up (for different customers). We have just commissioned a
> much better specified server which is running PostgreSQL 7.4.
> 
> We need to migrate the databases from 7.2 to 7.4. The smallest database
> contains about 200K of data whilst the largest database weighs in at
> about 100Mb.
> 
> What's the best way to migrate the data? Would you suggest using the
> pgdump tools? We've found them to be quite problematic in use and are
> not entirely sure of the best command line switches to use.

If you use the pg_dump(all) client from 7.4 to dump the 7.2 database, you
will probably find that it works a lot better with handling dependencies
than the 7.2 pg_dump(all) client did.

> 
> We need to make sure the data and permissions get migrated, as the data
> is being used for live web sites. So obviously speed of migration is
> important as we will have a period of downtime to complete the
> migrations.

You might look at Slony I, though I think it only works on versions back
to 7.3. If it works back to 7.2, then it could be used to sync your new
system up, allowing for a very short cutover time.

> 
> Any suggestions/product ideas would be greatly appreciated.
> Thanks in advance,
> 
> Mario



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-08-24 17:42 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-24 06:25 PostgreSQL Migration of databases 7.2 to 7.4 Mario Thomas <mario@alamar.net>
2004-08-24 17:42 ` Bruno Wolff III <bruno@wolff.to>
2004-08-24 17:04 Re: PostgreSQL Migration of databases 7.2 to 7.4 Christopher Browne <cbbrowne@acm.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox