how to change PostgreSQL data directory?

Question

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?

score 21 · Answer 1 · 2015-01-29T18:03:12.520

21

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

edited Jan 29 '15 at 18:03

answered Mar 23 '14 at 20:23

2

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. – Craig Ringer Mar 24 '14 at 01: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. – Jim Cutler Dec 29 '20 at 16:14
Doesn't work on latest version. `pg_ctl: unrecognized operation mode "unregister" Try "pg_ctl --help" for more information.` – Roger J Bos CFA Mar 03 '22 at 19:16
1

@RogerJBosCFA: works just fine for me on Windows 10 and Postgres 14 – Mar 03 '22 at 19:43

score 0 · Answer 2 · answered Nov 05 '22 at 21: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

how to change PostgreSQL data directory?

2 Answers2

Linked