Postgresql – Why is postgresql still consuming space on the other drive

postgresql

I am running postgreSQL 8.4 on Ubuntu 10.0.4.

I recently needed to import a large amount of data (approx 300G) into a database. I purchased an additional drive, prepared it for use on my machine, and turned off logging altogether by setting:

log_destination='stderr'

in my postgresql.conf file

I then stopped the current run postgresql service, and restarted it, specifying the data directory. I did that using the following steps:

  1. sudo su – postgres
  2. /usr/lib/postgresql/8.4/bin/pg_ctl -D /path/to/dir/on/new/drive -l logfile start &

I run the psql command line utility and run SHOW DATA_DIRECTORY to confirm that the running postgres service was indeed pointing to the right directory.

I then started my data import, but periodically run df -h to check disk usage on my machine. Although data was being stored to my new drive (as expected), disk space was being consumed at a much faster rate on my old drive.

I don't understand why that would be, since postgreSQL should (AFAIK) have no reason to write anything to my old drive.

Does anyone know why this may be happening, and how to fix this? (i.e. force PG to save data in the specified location only?)

Note:

I did NOT change the data_directory setting in my postgresql.conf. It was still pointing to my old drive – however, I had explicitly restarted the postgres service and specified a new data directory location.

Best Answer

I do not have all information for pointing to the source of your problem, but I think you should create your postgresql cluster in a different way. Instead of using the postgresql generic command pg_ctl, you should use the Debian/Ubuntu provided command pg_createcluster. This way you will have a working setup that survives reboot, you may specify your data directory and your log directory, you may also specify the start policy in order to activate it automatically on boot. And you may use all cluster commands: pg_lsclusters, pg_upgradecluster, and so on.

Every cluster uses three directories:

  • /etc/postgresql/8.4/CLUSTERNAME, for its configuration
  • your data directory, for all your data and WAL (default /var/lib/postgres/8.4/CLUSTERNAME)
  • your log directory, for your logs (default /var/log/postgresql)
Related Question