Thread: move databases files location
hello, 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 ? thanks.
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
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.