-1

My PostgreSQL installation by default uses /var/lib/pgsql directory to store data, backups and configuration files. As my disk space is running out, I have installed new hardware and mounted it in the /data directory. Having this done, I would like now to move the data and backups there.

What is the best way to do this transition correctly? Note that I do not require atomic transition – the PostgreSQL service can be offline while the data transfer is in progress.

Thanks in advance,

Pete.

1
  • Why the downvote? Dec 13, 2015 at 13:05

2 Answers 2

0

There are a couple of options really. The most straightforward may be to shut the server down and then copy the whole tree under /var/lib/pgsql to the new disk then bind mount or mount it back to /var/lib/pgsql. I would probably choose the latter ymmv.

mkdir /data/pgsql
cd /var/lib/pgsql
cp -aRp * /data/pgsql
cd ..
mv pgsql pgsql.safe
mkdir pgsql
mount -obind /data/pgsql /var/lib/pgsql

Carry out your testing and then if that works add a suitable entry to /etc/fstab.

Similarly you could copy the entire contents of /var/lib/pgsql to /data then unmount /data and mount it on /var/lib/pgsql (having moved the old one out of the way and recreated the directory). Again run your tests and make suitable changes to fstab.

2
  • I like your approach better since it does not require that the entire disk is used for the PostgreSQL server. I've slightly modified your shell script and am about to execute it momentarily. I'll get back once it completes. Dec 13, 2015 at 13:27
  • I've been looking over the logs and it looks like the transfer was completed successfully. The server's now running good and everything seems OK. Thanks for your post! Dec 15, 2015 at 21:59
2

Depend on how your server was configured, you can simply change your PG_DATA variable.

But you can easily do:

service postgresql stop
mv /var/lib/pgsql/ /data/pgsql
ln -s /data/pgsql /var/lib/pgsql
service postgresql start

It will do the job.

1
  • I like this solution but what about data_directory directive in the postgresql.conf file ? If i change its value it should work ? data_directory = '/my/new/path' Jul 24, 2020 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.