Videos / Other Resources

No attachments were found.

Move Database to Another Hard Drive or Partition

Overview:
How do I move my PostgreSQL database (Hand Database) to a new Hard Drive or Drive Partition?




Solution
:
If you have PostgreSQL installed on the C drive where Windows is installed, but you want to move the Data folder i.e Database to a different partition or external hard drive to reclaim the space on your C drive, it can be done without having to reinstall.

We need to close down the PostgreSQL service while doing this.

Windows XP
Start > Control Panel > Administrative Tools (Use classic View) > Services

Windows 7
Start > Search > Services

This will bring up the following window:

postgres-001.jpg

Right click this and choose "Stop" to stop the service.

Then create the following folders in the external hard drive. Program Files and inside that folder create a PostgreSQL folder and inside that create a data folder. So if your external hard drive is on the I drive you would have created a path I:\Program Files\PostgreSQL\data

Then go to where you have the data folder which will be c:\program files\PostgreSQL\8.3\data by default unless you changed it. Copy (or Cut) all the files in here to the new data folder we just created above on the external hard drive. If this is a big database this may take some time.



Edit Registry:

Now we need to tell PostgreSQL where the new data folder is located and to do this we need to edit the registry.

Windows XP
Go to Start > Run and type the following in the textbox: regedit and then press enter and a new window should appear like in the screenshot below:

Windows Vista/W7
Go to Start > All Programs > Accessories > Run then type the following in the textbox: regedit and then press enter and a new window should appear like in the screenshot below:

postgres-002.jpg

Navigate the folders to this path: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\pgsql-8.3 and you should see the following screen:

postgres-003.jpg

In the image above we've highlighted the key we need to change. Go to image path and right click and choose modify and you should see the following:

"C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "C:\Program Files\PostgreSQL\8.3\data\"

This is the location that PostgreSQL looks for the database files but weve moved them to the external hard drive so we must tell it the location. Make sure you get the path exactly correct. The easiest way is to navigate to the actual Data folder on the external hard drive and right click any file inside that folder and choose properties and youll see this:

postgres-004.jpg

As you can see for location it shows the actual path, in my case its the I drive but yours might be different so just copy and paste it. If i was creating the key based on that screenshot my old key would be:

"C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "C:\Program Files\PostgreSQL\8.3\data\"

And i should change it to this:
"C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "I:\Program Files\PostgreSQL\8.3\data\"

Finally you need to setup permissions on the folder, when moving a folder to a new PC your old user priveleges may not be transferred so you need to make sure the user has sufficent priveleges otherwise you may get a "cant connect to postgreSQL error".

To do this go the Main folder (PostgreSQL in this case) and right click it and choose Properties from the menu and then go into the security TAB. In there click the edit button like in the screenshot below

editsecurity.png

Then for each user in the top pane you need to select that user and click full control in the bottom pane and when all the users have full control click apply and ok.

tickeveryone.jpg


Everything is in place now.  Close everything down and make sure to reboot the PC.


If the moved DB is working fine you can delete the original \data folder on the C drive if you haven't done so already.



| More

Related Articles


User Opinions (55 votes)

100% thumbs up 0% thumbs down

How would you rate this answer?



Thank you for rating this answer.

Visitor Comments

No visitor comments posted. Post a comment

Post a comment

To post a comment for this article, simply complete the form below. Fields marked with an asterisk are required.
   Name:
   Email:
* Comment:
* Enter the code below:
 

Continue