How can I move postgresql data to another directory on Ubuntu over Amazon EC2?

Question

We've been running postgresql 8.4 for quite some time. As with any database, we are slowly reaching our threshold for space. I added another 8 GB EBS drive and mounted it to our instance and configured it to work properly on a directory called /files

Within /files, I manually created

Correct me if I'm wrong, but I believe all postgresql data is stored in /var/lib/postgresql/8.4/main

I backed up the database and I ran sudo /etc/init.d/postgresql stop. This stops the postgresql server. I tried to copy and paste the contents of /var/lib/postgresql/8.4/main into the /files directory but that turned out be a HUGE MESS! due to file permissions. I had to go in and chmod the contents of that folder just so that I could copy and paste them. Some files did not copy fully because of root permissions. I modified the data_directory parameter in postgresql.conf to point to the files directory

 data_directory = '/files/postgresql/main'

and I ran sudo /etc/init.d/postgresql restart and the server failed to start. Again probably due to permission issues. Amazon EC2 only allows you to access the service as ubuntu by default. You can only access root from within the terminal which makes everything a lot more complicated.

Is there a much cleaner and more efficient step by step way of doing this?

Answer 1

First, stop the running server

Then copy the data directory (datadir) while retaining permissions:

cp -aRv

Rename you old datadir and keep it aside. Now, symlink the old path to the new location.