Thread: general fear question about move PGDATA from one Disc to another
Dear listmembers, I need to move /var/lib/postgresql/8.4/main from the / partion to another disc. Based on the http://wiki.postgresql.org/wiki/Simple_Configuration_Recommendation#File_System_Layouts I plan the following. 0.) Mount new pgroot, are there any hints for the mount command? mount -t ext4 -o noatime,nodiratime,nodev,noexec,nosuid /dev/sde1 /pgroot output from mount /dev/sde1 on /pgroot type ext4 (rw,nosuid,nodev,noexec,noatime,nodiratime,barrier=1,data=ordered) 1.) create a full backup of the current DB /usr/bin/pg_dumpall --file=/tmp/backup_before_part_move.tar --format=tar --oids --verbose --username=postgres 2.) Stop the database and the Apps which use the DB /etc/init.d/postgresql stop .... 3.) copy the current /var/lib/postgresql/8.4/main to the new dir as user postgres cd /var/lib/postgresql/ tar cvf - . | (cd /pgroot/pgdata && tar xvf -) 4.) sync filesystems sync 5.) change data_directory to /pgroot/pgdata/8.4/main 6.) Start postgres /etc/init.d/postgresql start OS: ubuntu 11.04 PG: postgresql-8.4 8.4.14-0ubuntu11.04 Please can anybody take a look about my planned process and tell me if I have forgotten something and maybe point me to the right Doc, thanks. Thanks for feedback. Best regards Aleks
On 11/13/2012 11:26 PM, Aleksandar Lazic wrote: > Dear listmembers, > > I need to move > > /var/lib/postgresql/8.4/main > > from the / partion to another disc. If so, you're probably using `pg_wrapper` for cluster management. Confirm that with `pg_lsclusters`. If the command exists and it shows an 8.4 installation with the data directory you mentioned above, then you're using pg_wrapper to manage Pg. pg_wrapper reads /etc/postgresql/[version]/[clustername]/postgresql.conf to locate the cluster. For example, yours will be /etc/postgresql/8.4/main/postgresql.conf . This means you don't have to edit any init script settings on your system to get Pg to start properly next boot. > 0.) Mount new pgroot, are there any hints for the mount command? > > mount -t ext4 -o noatime,nodiratime,nodev,noexec,nosuid /dev/sde1 > /pgroot > > output from mount > > /dev/sde1 on /pgroot type ext4 > (rw,nosuid,nodev,noexec,noatime,nodiratime,barrier=1,data=ordered) Unless you add this to fstab as well, the file system won't mount at the next boot and PostgreSQL will fail to start. > 3.) copy the current /var/lib/postgresql/8.4/main to the new dir as > user postgres > cd /var/lib/postgresql/ > tar cvf - . | (cd /pgroot/pgdata && tar xvf -) What an odd way to do the copy. I'd use `cp -aR`, or at least use the preserve flag (-p) to tar. If you like you can have the new file system (assuming it's dedicated to just PostgreSQL) mount where the old data directory was, so there's no change visible in the system. edit /etc/fstab and add a line like: UUID=b4d54649-a9b5-4a57-aa22-291791ad7a3c /var/lib/postgresql/ ext4 defaults,noatime 0 0 Replace the UUID shown with the UUID of your new file system, determined with the vol_id command on older systems, or blkid on newer ones. Or just use the device node for the partition, like /dev/sdx9 Make a full pg_dumpall backup. Now stop all your PostgreSQL clusters with pg_ctlcluster and: mv /var/lib/postgresql/ mv /var/lib/postgresql.old mkdir /var/lib/postgresql mount /var/lib/postgresql chown postgres:postgres /var/lib/postgresql shopt -s dotglob cp -aR /var/lib/postgresql.old/* /var/lib/postgresql/ Start the cluster with pg_ctlcluster You've just migrated the files from the old file system to the new one without having to change the logical location, by mounting the new file system where the system expected it to be already. Again, you can remove /var/lib/postgresql.old when you're sure it's all gone fine. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Dear Craig, Am 14-11-2012 00:44, schrieb Craig Ringer: > On 11/13/2012 11:26 PM, Aleksandar Lazic wrote: >> Dear listmembers, >> >> I need to move >> >> /var/lib/postgresql/8.4/main >> >> from the / partion to another disc. > > If so, you're probably using `pg_wrapper` for cluster management. > Confirm that with `pg_lsclusters`. If the command exists and it shows > an > 8.4 installation with the data directory you mentioned above, then > you're using pg_wrapper to manage Pg. pg_lsclusters Version Cluster Port Status Owner Data directory Log file 8.4 main 5432 online postgres /var/lib/postgresql/8.4/main custom > pg_wrapper reads > /etc/postgresql/[version]/[clustername]/postgresql.conf > to locate the cluster. For example, yours will be > /etc/postgresql/8.4/main/postgresql.conf . This means you don't have > to > edit any init script settings on your system to get Pg to start > properly > next boot. This was also a option for me. I wanted not to much changes in the system but it looks that this is the best way. >> 0.) Mount new pgroot, are there any hints for the mount command? >> >> mount -t ext4 -o noatime,nodiratime,nodev,noexec,nosuid >> /dev/sde1 >> /pgroot >> >> output from mount >> >> /dev/sde1 on /pgroot type ext4 >> (rw,nosuid,nodev,noexec,noatime,nodiratime,barrier=1,data=ordered) > > Unless you add this to fstab as well, the file system won't mount at > the > next boot and PostgreSQL will fail to start. ;-) I added this to the fstab, of course. >> 3.) copy the current /var/lib/postgresql/8.4/main to the new dir as >> user postgres >> cd /var/lib/postgresql/ >> tar cvf - . | (cd /pgroot/pgdata && tar xvf -) > > What an odd way to do the copy. I'd use `cp -aR`, or at least use the > preserve flag (-p) to tar. Well it is old fashioned way. > If you like you can have the new file system (assuming it's dedicated > to > just PostgreSQL) mount where the old data directory was, so there's > no > change visible in the system. > > edit /etc/fstab and add a line like: > > UUID=b4d54649-a9b5-4a57-aa22-291791ad7a3c /var/lib/postgresql/ ext4 > defaults,noatime 0 0 > > Replace the UUID shown with the UUID of your new file system, > determined > with the vol_id command on older systems, or blkid on newer ones. Or > just use the device node for the partition, like /dev/sdx9 I have written the following into the fstab. /dev/sde1 /var/lib/postgresql ext4 noatime,nodiratime,nodev,noexec,nosuid 0 2 > Make a full pg_dumpall backup. > > Now stop all your PostgreSQL clusters with pg_ctlcluster and: > > mv /var/lib/postgresql/ mv /var/lib/postgresql.old > mkdir /var/lib/postgresql > mount /var/lib/postgresql > chown postgres:postgres /var/lib/postgresql > shopt -s dotglob > cp -aR /var/lib/postgresql.old/* /var/lib/postgresql/ > Start the cluster with pg_ctlcluster Thanks for the description and explanation. Best regards Aleks