Moving database cluster

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?

Thanks,
Fernando.

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(at)lelarge(dot)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(at)lelarge(dot)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