how to change PostgreSQL data directory?

Asked 23/3, 2014 at 19:57 Answered 19/1 at 19:42

I am having a problem changing the data directory on postgresql 9.2 on windows7:

i`m trying to change my data directory:

how can i change data directory on postgreSQL with pgAdmin?

Airwaves answered 23/3, 2014 at 19:57 Comment(0)

This not possible from within pgAdmin (or any other SQL client because you need to stop the Postgres server in order to move the data directory)

To move the directory, use these steps:

Stop Postgres (you can use the control panel to find the correct service name)
```
net stop <name_of_the_service>
```
Make sure Postgres is not running (e.g. using ProcessMonitor)

Remove the Windows service using

pg_ctl unregister -N <name_of_the_service>

make sure Postgres is not running
move the data directory to the new location (or maybe only copy it, so that you have a backup)
re-create the service using (this assigns postgres as the service name)
```
pg_ctl register -N postgres -D c:\new\path\to\datadir
```
start the service
```
net start postgres 
```
run psql to verify that Postgres is up and running
```
psql -U postgres
```
Verify the running server is using the new data directory
```
show data_directory;
```

Details on how to use pg_ctl can be found in the manual:
http://www.postgresql.org/docs/current/static/app-pg-ctl.html

Towroy answered 23/3, 2014 at 20:23 Comment(4)

Note that on newer PostgreSQL versions (IIRC 9.2 and above) PostgreSQL runs as NETWORKSERVICE not postgres. You should check the service definition before unregistering it, and make sure that you use the same username when you create the service again. – Jump 24/3, 2014 at 1:31

other solutions have you modify the registry manually. This solution uses the postgres official tool. The tip from Craig Ringer is good but I just let the tool default me to Local System account which seems to run fine. – Realty 29/12, 2020 at 16:14

Doesn't work on latest version. pg_ctl: unrecognized operation mode "unregister" Try "pg_ctl --help" for more information. – Teel 3/3, 2022 at 19:16

@RogerJBosCFA: works just fine for me on Windows 10 and Postgres 14 – Towroy 3/3, 2022 at 19:43

Stop service by either opening services window and find postgresql-x64-xx (xx for verion number postgresql-x64-11, postgresql-x64-15 etc.) or using command line

sc stop postgresql-x64-11

run the following command is enough (no need to unregister) Replace "C:\postgre\data" with your new data location

sc config postgresql-x64-11 binPath= "\"C:\Program Files\PostgreSQL\11\bin\pg_ctl.exe\" runservice -N \"postgresql-x64-11\" -D \"C:\postgre\data\" -w"

start the service from services window or command line

sc start postgresql-x64-11

Kowalski answered 5/11, 2022 at 21:43 Comment(0)

-1

Open component services, stop the service, right click on it, and click properties.

Copy the value from "Path to executable" and edit the path after the "-D" option.

sc config + the copied and edited string

Escape the double quotes with "\" (or tell ChatGPT to correct the command)

Open up CMD in Admin mode, enter the command and execute.

Start the service

Done

Thickleaf answered 19/1 at 19:42 Comment(0)

Recommended topics

Hot tags