Re: move databases files location

Richard Huxton wrote:
> 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
>
apperently PGDATA is set in .bash_profile and points to /var/lib/pgsql/data:
[postgres@server ~]
$ cat .bash_profile
PGDATA=/var/lib/pgsql/data
[ -f $PGDATA/../initdb.i18n ] &&  source $PGDATA/../initdb.i18n
export PGDATA

So I suppose I'll have to stop postgres, move (or copy to be safe ..)
/var/lib/pgsql/data to /data2/pgsql/data (/data2 is a file system with
plenty of space) , set PGDATA=/data2/pgsql/data and restart postgres,
that's all  ?

thanks for a confirmation.