pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: Moving a database
Date: Fri, 16 Dec 2005 23:09:14 -0500
Message-ID: <m3d5jw2wlx.fsf@mobile.int.cbbrowne.com> (raw)
References: <98f7d3480512140944s6763d22ao65b9565a55735459@mail.gmail.com>

> What are the correct steps to move an database and from an server
> running postgreslq 7.4.2 to another running 8.0.3?

I'll assume there are two hosts:
 - db7, running 7.4.2, on port 5432, and
 - db8, running 8.0.3 on port 5432.

The simplest method would be thus:

- Stop the applications accessing db7

- On db8, run the command:
   pg_dumpall -h db7 -p 5432 | psql -h localhost -p 5432 template1

That is likely to provide the most nearly "perfect fidelity" copy of
the database on db8.

You may discover that this takes longer than you want it to.

If that proves to be the case, you can shorten the cutover time by
using a replication system such as Slony-I to copy data from the one
host to the other.  That definitely involves more "moving parts" and
more complexity.  But it could turn an 8 hour outage into a 2 minute
one...

You should certainly do test runs of whatever approach you try so as
to minimize the number of surprises.
-- 
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/slony.html
"...Yet terrible as Unix addiction  is, there are worse fates. If Unix
is the heroin of operating systems, then VMS is barbiturate addiction,
the Mac is MDMA, and MS-DOS is sniffing glue. (Windows is filling your
sinuses  with  lucite and  letting  it set.)   You  owe  the Oracle  a
twelve-step program."  --The Usenet Oracle



view thread (5+ messages)

Message-ID: <m3d5jw2wlx.fsf@mobile.int.cbbrowne.com>
Permalink:  ../m3d5jw2wlx.fsf@mobile.int.cbbrowne.com/
Also on:    postgresql.org/message-id/m3d5jw2wlx.fsf@mobile.int.cbbrowne.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: Moving a database
  In-Reply-To: <m3d5jw2wlx.fsf@mobile.int.cbbrowne.com>
  References: <98f7d3480512140944s6763d22ao65b9565a55735459@mail.gmail.com> <m3d5jw2wlx.fsf@mobile.int.cbbrowne.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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