Re: move databases files location

jehan.procaccia wrote:
> my postgresql installation from redhat package:
> $ rpm -q postgresql
> postgresql-7.4.17-1.RHEL4.1
> is located in /var partition, unfortunalty it is nearly full
> $ df -H .
> Filesystem             Size   Used  Avail Use% Mounted on
> /dev/sda12              11G   8.2G   1.7G  84% /var
>
> How can I tell postgresql to record databases in an other partition ?
> For now they are in:
> [root@metarisk1 /var/lib/pgsql/data/base]
> $ ls
> 1      17142     18275591  26876456  537107  6123076  657110   802399
> 17141  17837306  18372925  533001    596944  61317    7321277  8500925
>
> will it be simply a move of the files, or should I have to dump&restore
> the databases ?

I don't think 7.4 had tablespaces (check the manual). In which case
you'll have to do it manually.

1. Stop the database server.
2. Create a new location for your db stuff /some/where/pgsql2/data/base
3. Move the directories you want over to the new location
4. Symlink each of the directories
    cd /var/lib/pgsql/data/base
    link -s /some/where/pgsql2/data/base/17142
5. Check ownership & permissions
6. Restart database

If you want to move all of them, rather than just individual databases
then you just need to move .../data and reset your PGDATA to point at
that. Can't remember where that gets set with the old RPMS - try in
/etc/postgresql or /etc/pgsql

--
   Richard Huxton
   Archonet Ltd