Moving database cluster

On Thursday 14 January 2010, Fernando Hevia elucidated thus:

An Ubuntu install creates a postgres cluster automatically on
/var/lib/postgresql/8.4/main
Whats the best procedure for moving this cluster to an other
location? Should I just rerun initdb? What happens then with the
default cluster or how could I delete it?

The easiest way is to shut down Pg, move the 'main' directory somewhere
else, and then point a symlink to the new location.

j

--
Joshua Kugler
Part-Time System Admin/Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/ �ID 0x14EA086E

Le 14/01/2010 21:40, Fernando Hevia a �crit :

An Ubuntu install creates a postgres cluster automatically on
/var/lib/postgresql/8.4/main
Whats the best procedure for moving this cluster to an other location?
Should I just rerun initdb? What happens then with the default cluster or
how could I delete it?

If you don't have any data on it, the best way is to drop it with
pg_dropcluster, and create a new one with pg_createcluster.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

-----Mensaje original-----
De: Guillaume Lelarge [mailto:guillaume@lelarge.info]

Le 14/01/2010 21:40, Fernando Hevia a écrit :

An Ubuntu install creates a postgres cluster automatically on
/var/lib/postgresql/8.4/main Whats the best procedure for

moving this

cluster to an other location?
Should I just rerun initdb? What happens then with the

default cluster

or how could I delete it?

If you don't have any data on it, the best way is to drop it
with pg_dropcluster, and create a new one with pg_createcluster.

Sound advice. Thanks!!

-----Mensaje original-----
De:

On Thursday 14 January 2010, Fernando Hevia elucidated thus:

An Ubuntu install creates a postgres cluster automatically on
/var/lib/postgresql/8.4/main Whats the best procedure for

moving this

cluster to an other location? Should I just rerun initdb?

What happens

then with the default cluster or how could I delete it?

The easiest way is to shut down Pg, move the 'main' directory
somewhere else, and then point a symlink to the new location.

Thanks for your reply.
I had considered this first but then I wasn't sure if there would be any
performance penalty.
The current main directory sits on a 'slow' RAID 1 volume while the new one
will sit on a 'fast' 12 disk RAID 10 volume.
I guess the symlink shouldn't be troublesome but I don't know if some PG
process will continually be reading this link encumbering somehow the RAID 1
disks.

Regards,
Fernando.

On Thursday 14 January 2010, Fernando Hevia elucidated thus:

The easiest way is to shut down Pg, move the 'main' directory
somewhere else, and then point a symlink to the new location.

Thanks for your reply.
I had considered this first but then I wasn't sure if there would be
any performance penalty.
The current main directory sits on a 'slow' RAID 1 volume while the
new one will sit on a 'fast' 12 disk RAID 10 volume.
I guess the symlink shouldn't be troublesome but I don't know if some
PG process will continually be reading this link encumbering somehow
the RAID 1 disks.

I'm not sure how Pg does it, but I remember reading several years ago
that MySQL would, upon startup, "resolve" the symlink, and use the real
path name for the rest of its execution. I would assume Pg does
something similar.

j

--
Joshua Kugler
Part-Time System Admin/Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/ �ID 0x14EA086E

On Thu, Jan 14, 2010 at 06:21:14PM -0300, Fernando Hevia wrote:
-
-
- > -----Mensaje original-----
- > De: Guillaume Lelarge [mailto:guillaume@lelarge.info]
- >
- > Le 14/01/2010 21:40, Fernando Hevia a �crit :
- > >
- > > An Ubuntu install creates a postgres cluster automatically on
- > > /var/lib/postgresql/8.4/main Whats the best procedure for
- > moving this
- > > cluster to an other location?
- > > Should I just rerun initdb? What happens then with the
- > default cluster
- > > or how could I delete it?
- > >
- >
- > If you don't have any data on it, the best way is to drop it
- > with pg_dropcluster, and create a new one with pg_createcluster.
- >
- >
-
- Sound advice. Thanks!!

in SLES, assuming you have no data in the DB, I'd go to /etc/sysconfig/postgresql
modify POSTGRES_DATADIR="/my/new/path"

and then (logout/log back in to get your PGDATA set correctly and )
initdb

No need to mess with symlinks.

I'm sure ubuntu has something similar to sysconfig

Dave