2

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.

Improve this question
4
  • Why don't you try changing the setting in your conf file too? Are you seeing data growth on your new drive as well?
    – swasheck
    Commented Jun 27, 2012 at 7:42
  • 1
    question: when it finished was the space released? Was the space released at intervals? Is the old drive where temporary files are stored?
    – Chris Travers
    Commented Sep 7, 2012 at 1:02
  • Have you moved your data files as well? Sorry if this sounds lame, but you didn't mention.
    – András Váczi
    Commented Oct 9, 2012 at 6:15
  • can you please check your tablespaces? may be you set some tables -or may be indexes to different tablespace,which belongs to that disk.
    – sftsz
    Commented Nov 24, 2012 at 14:02

2 Answers 2

Reset to default
1

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)
Improve this answer
1
  • Just one more check: you wrote that you set log_destination=stderr, so your postgresql is not supposed to write any log on disk, but this is not true if you also set silent_mode. Did you, by any chance, set silent_mode?
    – eppesuig
    Commented Dec 4, 2012 at 13:11
0

Make sure WAL archiving is turned off in postgresql.conf

archive_mode = off

and that there are no tablespaces that point to locations on your old drive used in the imported data.

Improve this answer
1
  • 1
    Why do you suggest turning off archiving? If archiving is the cause, I would suspect that archive_command sends the archives to the old disk.
    – András Váczi
    Commented Jul 11, 2012 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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