pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMoving 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