Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm running Ubuntu 10.10 and have a separate partition for my root directory. The partition wasn't set up with large databases in mind, so I would like to put my 2+GB PostgreSQL database on the /home partition.

The first piece of advice I ran into was the initlocation command, but this is apparently for older versions of PostgreSQL.

Then I ran into the initdb command, but this doesn't appear to be available on Ubuntu 10.10... for some reason.

How should I do this really?

share|improve this question

migrated from stackoverflow.com Sep 12 '11 at 3:53

This question came from our site for professional and enthusiast programmers.

    
You may ask this kind of questions on DBA.stackexchange.com instead. –  Jonas Sep 8 '11 at 17:56
add comment

3 Answers

up vote 1 down vote accepted

That command initdb should be located at path /usr/lib/postgresql/{version}/bin/initdb (propably not included in your PATH), however you could use pg_createcluster wrapper, especially with -D option:

This option specifies the directory where the database cluster should be stored. This is the only information required by initdb, but you can avoid writing it by setting the PGDATA environment variable, which can be convenient since the database server (postgres) can find the database directory later by the same variable.

share|improve this answer
    
Somehow, by messing up my old install, I managed to use pg_createcluster to set up the default database cluster to just the place where I wanted it... a command which PostGre suggested itself when it failed to set things up correctly. So you certainly got this one right on. –  Richard Sep 8 '11 at 18:01
add comment

The files that describe the default cluster, called main, are located in the directory /etc/postgresql/8.4/main for 8.4 on my ubuntu 10.10 machine. Just stop postgresql, copy your data dir over to the new dir, set perms (700 on the root dir, and owned by the postgres user) edit the line in postgresql.conf that points to your data dir and restart postgres.

share|improve this answer
    
alternately you can use the pg_createcluster command to create a new cluster and import your data into that one. –  Scott Marlowe Sep 9 '11 at 7:08
add comment

I'm running Ubuntu 10.04 and PostgreSQL 8.4. Here's what I did to move my pgdata folder off the root partition, loosely based on instructions from linuxquestions.org.

  1. cd /mnt/workspace to get to the folder I want to hold my new pgdata folder.
  2. ls -l .. to make sure that the workspace folder is owned by root.
  3. grep data_directory /etc/postgresql/8.4/main/postgresql.conf to see where the current data directory is. In my case it was /var/lib/postgresql/8.4/main.
  4. sudo /etc/init.d/postgresql-8.4 stop to stop the service.
  5. sudo mv /var/lib/postgresql/8.4/main /mnt/workspace/pgdata to move the data folder to its new location.
  6. ls -l to make sure that the pgdata folder is still owned by the postgres user.
  7. I edited the /etc/postgresql/8.4/main/postgresql.conf file to point the data_directory entry at its new location.
  8. sudo /etc/init.d/postgresql-8.4 start to start the service.
  9. Try connecting to the database server to make sure it still works.
  10. df to see how much free space I now have on the root partition. Woot!
share|improve this answer
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.