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 using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my PostgreSQL data to the different drive. I am still in development mode so I can delete the old data if it makes transfer easier at this point of time. What is the best way to do this?

Is PostgreSQL tablespace something which I should look at?

share|improve this question
1  
Tablespaces would be the way to go if you wanted to move part of your database(s) to a separate drive. –  Erwin Brandstetter Nov 19 '12 at 23:42
    
This answer suggests that tablespaces should NOT go on an external hard drive: dba.stackexchange.com/a/62037/41823 Not sure I understand the risk if you're already putting the data on an external? –  Statwonk Jun 29 at 0:14

1 Answer 1

up vote 6 down vote accepted

The following should do it:

  1. Shut down PostgreSQL
  2. Make sure PostgreSQL does not run any longer
  3. Check that PostgreSQL is really stopped
  4. Copy the old data directory to the new drive
    This is usually defined through a commandline parameter (-D) for your service or through the PGDATA environment variable.
  5. Update your PostgreSQL configuration (service, environment var) to point to the new data directory.
  6. If you copied the data, rename the old data directory to make sure PostgreSQL cannot find it any longer. That way you'll get an error message if you didn't get the configuration right.
  7. Start the service
  8. If everything is running fine, you can delete the old directory.
share|improve this answer
    
In 4th point, what do you mean by 'This is usually defined through a commandline parameter...'? –  codecool Nov 19 '12 at 15:52
    
@codecool: there are several different ways how this could be defined. Usually it's part of the service definition. But it depends on your operating system, distribution and personal changes. –  a_horse_with_no_name Nov 19 '12 at 15:54
1  
Thanks. To change directory I just needed to run postgres -D /location after following steps mentioned by you. :) –  codecool Nov 19 '12 at 17:32
    
@codecool That won't change the scripts that automatically start the server when the instance starts. If you have any, that is. It'd really help to know how you installed PostgreSQL and what your OS/distro is. They're things that should be in every question you post anyway. –  Craig Ringer Nov 20 '12 at 1:57

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.