Re: general fear question about move PGDATA from one Disc to another - Mailing list pgsql-general

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