-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.

Petr Mánek
  • 103
  • 1
  • 4

2 Answers2

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.

otaviofcs
  • 178
  • 8
  • 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'` – Juan-Kabbali Jul 24 '20 at 20:37
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.

Community
  • 1
user9517
  • 114,104
  • 20
  • 206
  • 289
  • 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. – Petr Mánek Dec 13 '15 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! – Petr Mánek Dec 15 '15 at 21:59