CODEWITHSUNDEEP
postgresql-9.2
user2504562
user2504562

Reputation: 51

how to change PostgreSQL data directory?

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?

Upvotes: 5

Views: 29586

Answers (3)

tretom
tretom

Reputation: 625

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

Upvotes: -1

ozanmut
ozanmut

Reputation: 3234

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

Upvotes: 1

user330315
user330315

Reputation:

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:

  1. Stop Postgres (you can use the control panel to find the correct service name)

    net stop <name_of_the_service>
  2. Make sure Postgres is not running (e.g. using ProcessMonitor)

  3. Remove the Windows service using

    pg_ctl unregister -N <name_of_the_service>
  4. make sure Postgres is not running
  5. move the data directory to the new location (or maybe only copy it, so that you have a backup)

  6. re-create the service using (this assigns postgres as the service name)

    pg_ctl register -N postgres -D c:\new\path\to\datadir

  7. start the service

    net start postgres

  8. run psql to verify that Postgres is up and running

    psql -U postgres

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

Upvotes: 24

Related Questions