How to migrate PostgreSQL data in Windows Server

PostgreSQL-data-migration

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... 

EventLog alert notification
NetFlow Analyzer - End Users Traffic Analysis

Contact

Mailing and Visiting Address:
Soneco d.o.o.
Makenzijeva 24/VI, 11000 Belgrade, Serbia
Phone: +381.11.6356319
Fax: +381.11.2455210
sales@netvizura.com | support@netvizura.com

CONNECT WITH US:

linkedin facebook facebook