Videos / Other Resources

No attachments were found.

Move Database to Another Hard Drive or Partition

Overview:
How can a PostgreSQL database (Hand Database) be moved to a new Hard Drive or Drive Partition?




Solution
:
If PostgreSQL is 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.

*Please note this is an advanced article and we do not provide support for these this method if you encounter issues but at the request of users we decided to make a FAQ for those who felt confident enough to try this method

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.

Create the following folder in the external hard drive: newParent The location should be F:\newParent

Create the following folder in the external hard drive: Program Files

Inside that folder create a \PostgreSQL folder and inside that create a \data folder. So if the external hard drive is on the I drive the path I:\Program Files\PostgreSQL\data would have been created.


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



Edit Registry:

Now tell PostgreSQL where the new data folder is located. To do this, edit the registry.

Windows XP
Go to Start > Run and type the following in the textbox: regedit
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 to view the following screen:

postgres-003.jpg

In the image above key that needs to changed is highlighted. Go to image path. Right click and choose modify to view 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, they have been moved to the external hard drive. Next, tell PostgreSQL the current location. Make sure to get the path exactly correct. The easiest way is to navigate to the actual Data folder on the external hard drive. Right click any file inside that folder. Choose properties to view this:

postgres-004.jpg

For location it shows the actual path, in this case it is the I:  drive but others might be different. Copy and paste it. If creating the key based on that screenshot the 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\"

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\" 

Next, setup permissions on the folder-
When moving a folder to a new PC, the old user priveleges may not be transferred. So it is necessary to make sure the user has sufficent priveleges. Otherwise a "cant connect to postgreSQL error" can occur.

To do this, go the Main folder (PostgreSQL in this case) and right click it. Choose Properties from the menu. Then go into the Security Tab. In there, click the Edit button.

editsecurity.png

Then for each user in the top pane, select that user. Click Full control in the bottom pane. When all the users have full control,  click Apply and OK.

tickeveryone.jpg



Now, move the Parent database using Xcopy

Open the Command Prompt and copy paste this command: Xcopy "%appdata%\holdemmanager\Database\*.*" f:\newParent /E /C /I /H /R /Y

**note** Be sure to also type in the quotation marks ('') or it will not work


Turn the Postgres service back on in Services.

Finally,
 Change path to Parent in Settings table of database
these 3 steps will completelly move ALL to another disk Change path to Parent in Settings table of database
change the path to the Parent in Settings table of database
  • To do this open Table Settings in PGAdmin to view the path.
  • Right-click on the Table "settings", and select--> view data---> view top 100 rows--> then select the value line (for DatabaseParentFolder) and you can edit the line to the 'f:\newParent' location you assigned previously

 

null.png


Folders are in place now. 


Close everything down and make sure to reboot the PC. 


If the moved DB is working fine, delete the original \data folder and Database folder on the C drive if you have not done so already.




| More

Related Articles


User Opinions (24 votes)

70% thumbs up 29% thumbs down

How would you rate this answer?



Thank you for rating this answer.

Continue