pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Moving a database
5+ messages / 5 participants
[nested] [flat]

* Moving a database
@ 2001-12-10 14:52 Ewan Leith <ejl@man.fwltech.com>
  2001-12-10 15:40 ` Re: Moving a database Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Ewan Leith @ 2001-12-10 14:52 UTC (permalink / raw)
  To: pgsql-admin

Hi all,

we recently upgraded from 6.53 to 7.1.2, and at the same time tried to move
the database to a new filesystem.

However, while the upgrade was 100% successful using pg_dumpall, we see that
postgres is still reading some files from the old file system (though only
updating the new files).

An example is pg_shadow which is read on both file systems whenever someone
seems to authenticate, but only updated on the new file system.

Does anyone have any ideas? Is it possible to move the location of Postgres
in this manner?

Thanks,
Ewan



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

* Re: Moving a database
  2001-12-10 14:52 Moving a database Ewan Leith <ejl@man.fwltech.com>
@ 2001-12-10 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2001-12-10 15:40 UTC (permalink / raw)
  To: Ewan Leith <ejl@man.fwltech.com>; +Cc: pgsql-admin

Ewan Leith <ejl@man.fwltech.com> writes:
> An example is pg_shadow which is read on both file systems whenever someone
> seems to authenticate, but only updated on the new file system.

I don't believe it for a minute.  Please describe the actual problem
you're having, rather than jumping to conclusions about the mechanism.

			regards, tom lane



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

* Moving a database
@ 2005-12-14 17:44 Richard Bortolucci <richardbortolucci@gmail.com>
  2005-12-17 03:39 ` Re: Moving a database Jeff Frost <jeff@frostconsultingllc.com>
  2005-12-17 04:09 ` Re: Moving a database Christopher Browne <cbbrowne@acm.org>
  0 siblings, 2 replies; 5+ messages in thread

From: Richard Bortolucci @ 2005-12-14 17:44 UTC (permalink / raw)
  To: pgsql-admin

Hi,

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

--
Richard Bortolucci

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

* Re: Moving a database
  2005-12-14 17:44 Moving a database Richard Bortolucci <richardbortolucci@gmail.com>
@ 2005-12-17 03:39 ` Jeff Frost <jeff@frostconsultingllc.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Jeff Frost @ 2005-12-17 03:39 UTC (permalink / raw)
  To: Richard Bortolucci <richardbortolucci@gmail.com>; +Cc: pgsql-admin

If they are both up and running and reachable, then you likely just want to do 
something as simple as:

pg_dumpall -h <old host> | psql -h <new host> template1

That's assuming you want to move all the DBs on the first server to the 
second.

NOTE: pg_dumpall cannot dump large objects.  If you have these, you'll have to 
use another method of migration.

Another good method if you want to limit downtime and have primary keys on 
all your tables is to setup slony on both nodes, let the subscription get up 
to date and then change roles and bring down the old server.

On Wed, 14 Dec 2005, Richard Bortolucci wrote:

> Hi,
>
> What are the correct steps to move an database and from an server running
> postgreslq 7.4.2 to another running 8.0.3?
>
> --
> Richard Bortolucci
>

-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954



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

* Re: Moving a database
  2005-12-14 17:44 Moving a database Richard Bortolucci <richardbortolucci@gmail.com>
@ 2005-12-17 04:09 ` Christopher Browne <cbbrowne@acm.org>
  1 sibling, 0 replies; 5+ messages in thread

From: Christopher Browne @ 2005-12-17 04:09 UTC (permalink / raw)
  To: pgsql-admin

> 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



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


end of thread, other threads:[~2005-12-17 04:09 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-10 14:52 Moving a database Ewan Leith <ejl@man.fwltech.com>
2001-12-10 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-14 17:44 Moving a database Richard Bortolucci <richardbortolucci@gmail.com>
2005-12-17 03:39 ` Jeff Frost <jeff@frostconsultingllc.com>
2005-12-17 04:09 ` 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