Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Jun 27 '12 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 Sep 7 '12 at 1:02
    
Have you moved your data files as well? Sorry if this sounds lame, but you didn't mention. –  dezso Oct 9 '12 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. –  Hz.Root Nov 24 '12 at 14:02
add comment

2 Answers 2

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)
share|improve this answer
    
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 Dec 4 '12 at 13:11
add comment

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.

share|improve this answer
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. –  dezso Jul 11 '12 at 7:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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