Moving the PostgreSQL Database to a New Location in MS Windows

From Observer GigaFlow Support | VIAVI Solutions Inc.
Jump to: navigation, search
1 - Stop the Postgres and Anuview services
2 - Copy C:\Program Files\PostgreSQL\data\*.* to the destination directory (ex. D:\pgdata\)
3 - Open the Registry Editor
NB:  when changing paths in registry keys, the path names are often case sensitive!!
4 - Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.4 (may be 9.0 or 9.1 depending on version installed)
  - Modify ImagePath:
    From:  C:\Program Files\PostgreSQL\bin\pg_ctl.exe" runservice -N "pgsql-8.4" -D "C:\Program Files\PostgreSQL\data\"
    To:    C:\Program Files\PostgreSQL\bin\pg_ctl.exe" runservice -N "pgsql-8.4" -D "d:\pgdata\"   (where d:\pgdata = your new PostgreSQL directory)
5 - Go to HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations   (OR HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432\PostgreSQL\Installations
  - Look for and open the alpha-numeric subfolder
  - Modify Data Directory:
    From:  C:\Program Files\PostgreSQL\data\
    To:    D:\pgdata\     (where d:\pgdata = your new PostgreSQL directory)
6 - Go to HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Services\pgsql-9.0  (OR HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432\PostgreSQL\Services\pgsql-8.4
  - Modify Data Directory:
    From:  C:\Program Files\PostgreSQL\data\
    To:    D:\pgdata\     (where d:\pgdata = your new PostgreSQL directory)
IMPORTANT:  The data directory requires proper permissions in order to allow the service to have access.
7 - 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 Postgres user
  - Give the Postgres user the permission to Modify the directory
8 - Restart the PostgreSQL and Anuview services from the Administrative Tools > Services panel on your server