Change data path for PostgreSQL

Question

I run PostgreSQL as a Service.

In Administration - Services, I can see the following path

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

-D "C:\Program Files\PostgreSQL\8.4.3-3.1C\data\" is used for the data path.

But how can I change this? I want to start this service in the following way

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

How can I change it?

Solution 1

It is easier if you reinstall Postgre and select data folder during install.

Otherwise you have to read about pg_ctl: http://www.postgresql.org/docs/current/static/app-pg-ctl.html

Solution 2

• Shutdown the service
• Unregister the windows service using pg_ctl unregister -N YourServiceName (the current service name is shown in the "services" app of the control panel)
• Move the data directory to the new location
• Make sure the permissions of the new directory allow the postgres user to access the files
• Verify a second time the permissions of the new directory are correct
• Register the windows service usingy
  pg_ctl register -N PostgreSQL -U windowsuser -P windowspassword -D c:\somedir\newdata
• Start the new service using net start PostgreSQL

All this is described in the manual (see the link jorani posted)

Solution 3

If you don't want to reinstall, you can also probably change this through the registry through the following keys:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\postgresql-9.0\ImagePath HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\services\postgresql-9.0\ImagePath HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\postgresql-9.0\ImagePath

Change the version number for the version you're using.

These keys of the command that is actually executed. Reboot your system after moving the data directory where you want and setting permissions and you should be good to go. Modifying registry is the safest way to go though I'll admit.