Sometimes we are in a need of migrating database to new disk, usually because of the lack of space on the former. PostgreSQL in Windows stores it's files in data folder, in Program Files, in it's usual installation procedure.
In this example, we will show you the procedure for migrating PostgreSQL data folder to another partition/disk, for pgSQL 9.6 version.
Stop the PostgreSQL service, and all dependent services (eg. Tomcat)
Copy C:\Program Files\PostgreSQL\data\*.* to the destination directory (ex. E:\pgdata\)
Open the Registry Editor:
Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\postgresql-x64-9.6 or depending on your version on PostgreSQL
Modify ImagePath:
From: C:\Program Files\PostgreSQL\9.6\bin\pg_ctl.exe" runservice -N " postgresql-x64-9.6"-D "C:\Program Files\PostgreSQL\9.6\data\" To: C:\Program Files\PostgreSQL\9.6\bin\pg_ctl.exe" runservice -N " postgresql-x64-9.6" -D "E:\pgdata\" (where E:\pgdata = your new PostgreSQL directory)
Go to HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.6
Modify Data Directory:
From: C:\Program Files\PostgreSQL\9.6\data\ To: E:\pgdata\ (where E:\pgdata = your new PostgreSQL directory)
Go to HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Services\ postgresql-x64-9.6
Modify Data Directory: From: C:\Program Files\PostgreSQL\9.6\data\ To: E:\pgdata\ (where E:\pgdata = your new PostgreSQL directory)
Open Windows Explorer:
Go to the new PostgreSQL data directory in Windows Explorer
Right-click and select Properties
Click on the Security
Click on Add, to add the PostgreSQL user
Give the PostgreSQL user the permission to Modify the directory
Start the PostgreSQL service and other depending services (eg. Tomcat)Most problems that arise from this migration are based on wrong permission set, which you can configure on line 17.
If the database isn't coming up after these configurations, be sure to check Event Viewer Application Logs for further messages.
Hopefully this helps. Good luck...
