Tags: create, database, directory, directory3, env, location, moving, mysql, oracle, pgdata, postgresql, postgresql2, sql, this1, windows

Moving PostgreSQL data directory on Windows

On Database » PostgreSQL

15,853 words with 9 Comments; publish: Fri, 23 May 2008 10:18:00 GMT; (25066.41, « »)

Hello,

I would like to move the data directory to another location. I have done thi

s:

1. Stop PostgreSQL

2. Move data directory

3. Create a PGDATA env. variable to the new location

4. Start PostgreSQL

And it cannot start, because it cannot find postgresql.conf. (in Event log)

What should I do now?

Thanks,

Otto

--(end of broadcast)--

TIP 2: Don't 'kill -9' the postmaster

All Comments

Leave a comment...

  • 9 Comments
    • Ott Havasvlgyi <havasvolgyi.otto.postgresql.todaysummary.com.gmail.com> writes:

      > Hello,

      > I would like to move the data directory to another location. I have done t

      his:

      > 1. Stop PostgreSQL

      > 2. Move data directory

      > 3. Create a PGDATA env. variable to the new location

      > 4. Start PostgreSQL

      > And it cannot start, because it cannot find postgresql.conf. (in Event log

      )

      > What should I do now?

      >

      Dumb question, but are you sure you exported the PGDATA variable? Is

      it pointing to the directory that actually contains postgresql.conf?

      If you still have problems, post the exact error message that appears

      in the logs.

      -Doug

      --(end of broadcast)--

      TIP 5: don't forget to increase your free space map settings

      #1; Fri, 23 May 2008 10:20:00 GMT
    • > Hello,

      > I would like to move the data directory to another location.

      > I have done this:

      > 1. Stop PostgreSQL

      > 2. Move data directory

      > 3. Create a PGDATA env. variable to the new location 4. Start

      > PostgreSQL

      > And it cannot start, because it cannot find postgresql.conf.

      > (in Event log) What should I do now?

      Are you running it as a service:? If so you need to reconfigure the service.

      Unfortunatly,the only way to do this is using regedit. Go to

      HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl

      Set\Services\pgsql-8.1

      Open the value "ImagePath" and change the data directory.

      //Magnus

      --(end of broadcast)--

      TIP 4: Have you searched our list archives?

      http://archives.postgresql.org

      #2; Fri, 23 May 2008 10:21:00 GMT
    • Doug McNaught <doug.postgresql.todaysummary.com.mcnaught.org> writes:

      > Dumb question, but are you sure you exported the PGDATA variable? Is

      > it pointing to the directory that actually contains postgresql.conf?

      > If you still have problems, post the exact error message that appears

      > in the logs.

      Duh, I didn't read the subject line and assumed it was on Unix.

      -Doug

      --(end of broadcast)--

      TIP 3: Have you checked our extensive FAQ?

      http://www.postgresql.org/docs/faq

      #3; Fri, 23 May 2008 10:22:00 GMT
    • "Magnus Hagander" <mha.postgresql.todaysummary.com.sollentuna.net> writes:

      [vbcol=seagreen]

      > Are you running it as a service:? If so you need to reconfigure the servic

      e. Unfortunatly,the only way to do this is using regedit. Go to

      > HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl

      Set\Services\pgsql-8.1

      Is this documented anywhere? Certainly our SGML manuals don't talk

      about anything except the Unix case. Perhaps there needs to be a

      chapter in the Server Administration section about managing a Windows

      PG server. Or is all that in the installer docs?

      regards, tom lane

      --(end of broadcast)--

      TIP 2: Don't 'kill -9' the postmaster

      #4; Fri, 23 May 2008 10:23:00 GMT
    • Magnus,

      It's a service, and it is 8.0.6. I have fixed the registry to this

      value: "C:\Program Files\PostgreSQL\8.0\bin\pg_ctl.exe" runservice -N

      "pgsql-8.0" -D "E:\PostgreSQL\data\"

      The service does not start.

      This new directory definetely exists. But now there is no Event log

      entry about the error. The log file says nothing because it is in the

      data/pg_log directory.

      Thanks,

      Otto

      2006/1/13, Magnus Hagander <mha.postgresql.todaysummary.com.sollentuna.net>:

      > Are you running it as a service:? If so you need to reconfigure the servic

      e. Unfortunatly,the only way to do this is using regedit. Go to

      > HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl

      Set\Services\pgsql-8.1

      > Open the value "ImagePath" and change the data directory.

      > //Magnus

      >

      --(end of broadcast)--

      TIP 6: explain analyze is your friend

      #5; Fri, 23 May 2008 10:24:00 GMT
    • Directory permissions for user the user running the service?

      Ott Havasvlgyi wrote:

      >Magnus,

      >It's a service, and it is 8.0.6. I have fixed the registry to this

      >value: "C:\Program Files\PostgreSQL\8.0\bin\pg_ctl.exe" runservice -N

      >"pgsql-8.0" -D "E:\PostgreSQL\data\"

      >The service does not start.

      >This new directory definetely exists. But now there is no Event log

      >entry about the error. The log file says nothing because it is in the

      >data/pg_log directory.

      >Thanks,

      >Otto

      >

      >2006/1/13, Magnus Hagander <mha.postgresql.todaysummary.com.sollentuna.net>:

      >

      >--(end of broadcast)--

      >TIP 6: explain analyze is your friend

      >

      >

      --(end of broadcast)--

      TIP 3: Have you checked our extensive FAQ?

      http://www.postgresql.org/docs/faq

      #6; Fri, 23 May 2008 10:25:00 GMT
    • Ok, two things:

      First, when using this method, yo uhave to reboot after editing the registry

      . If you didn't do that, then that's your problem. Sorry, forgot to mention

      that. If not, then looking at permissions as Rodrigo suggested is the next s

      tep.

      Second, there is of course a nicer way of doing this than editing the regist

      ry, that I compleetley forgot about. It's still not as nice as you would wan

      t, but it should work. You use pg_ctl unregister to remove the service, and

      then pg_ctl register to reg

      ister a new one with the new data directory. Doing this you won't hvae to re

      boot.

      Perhaps this is something we should add to pg_ctl for 8.2? "pg_ctl reconfigu

      re" or something like that? What do others think?

      //Magnus

      > --Original Message--

      > From: Ott Havasvlgyi [mailto:havasvolgyi.otto.postgresql.todaysummary.com.gmail.com]

      > Subject: Re: [GENERAL] Moving PostgreSQL data directory on Windows

      > Magnus,

      > It's a service, and it is 8.0.6. I have fixed the registry to this

      > value: "C:\Program Files\PostgreSQL\8.0\bin\pg_ctl.exe"

      > runservice -N "pgsql-8.0" -D "E:\PostgreSQL\data\"

      > The service does not start.

      > This new directory definetely exists. But now there is no

      > Event log entry about the error. The log file says nothing

      > because it is in the data/pg_log directory.

      > Thanks,

      > Otto

      >

      > 2006/1/13, Magnus Hagander <mha.postgresql.todaysummary.com.sollentuna.net>:

      > regedit. Go to

      >

      --(end of broadcast)--

      TIP 9: In versions below 8.0, the planner will ignore your desire to

      choose an index scan if your joining column's datatypes do not

      match

      #7; Fri, 23 May 2008 10:26:00 GMT
    • Hello,

      Ok. I did not set any security on the machine. Only I have access to

      it. Now I have done this:

      0. Registry is modified (yesterday)

      1. Restart today

      2. Modify registry to the old value

      3. Start PostgreSQL -> pg_ctl and postmaster are running (they are in

      the task list), but no postgres

      4. "pg_ctl status" says that neither postmaster nor postgres are

      running. No new entry in event log.

      5. Stop with "pg_ctl stop" -> it says "Could not send stop signal (PID

      3624): no such process". There is really no such PID.

      6. Restart Windows -> the same as before

      7. Stop PostgreSQL from the Sart menu -> it took a half minute, and

      now only postmaster is running.

      8. pg_ctl unregister -N "pgsql-8.0" -> seems it succeeded

      9. Register on the new location

      10. Start with "net start "pgsql-8.0"" -> The service could not be

      started, no event log entry. Perhaps because a postmaster isrunning.

      11. Kill postmaster, I cannot stop it in other way.

      12. Start from Start menu and "net start", but nothing happens. The

      same message as in step 10.

      13. Start from Control panel - Services -> Message: "The service

      started then stopped."

      Now I really don't know how to start this.

      Perhaps reinstaling Postgres is easier.

      14. Modified all this with the installer, but PostgreSQL does not start.

      15. Backup data directory

      16. Uninstall PostgreSQL

      17. Install PostgreSQL 8.0.6 (this was installed before), as service,

      set the same passwords.

      18. Stop PostgreSQL

      19. Move the backuped data directory to the location PostgreSQL uses.

      20. Start PostgreSQL now, but it does not start this way.

      What can be done to use my old data? It's not that critical, only tests.

      Thanks,

      Otto

      2006/1/14, Magnus Hagander <mha.postgresql.todaysummary.com.sollentuna.net>:

      > Ok, two things:

      > First, when using this method, yo uhave to reboot after editing the regist

      ry. If you didn't do that, then that's your problem. Sorry, forgot to mentio

      n that. If not, then looking at permissions as Rodrigo suggested is the next

      step.

      > Second, there is of course a nicer way of doing this than editing the registry, th

      at I compleetley forgot about. It's still not as nice as you would want, but it shou

      ld work. You use pg_ctl unregister to remove the service, and then pg_ctl register t

      o r

      egister a new one with the new data directory. Doing this you won't hvae to reboot.

      >

      > Perhaps this is something we should add to pg_ctl for 8.2? "pg_ctl reconfi

      gure" or something like that? What do others think?

      > //Magnus

      >

      >

      --(end of broadcast)--

      TIP 5: don't forget to increase your free space map settings

      #8; Fri, 23 May 2008 10:27:00 GMT
    • Hi,

      I was just doing the same thing this morning, well somewhat the same;

      anyways, what I did was

      1. uninstall Postgres, and run the installation again(don't use the

      upgrade.bat), same major version.

      2. on the initial installation window, choose a new data directory by

      clicking on browse, point to your new location of data directory (the

      directory where you moved the data)

      3. make sure you enable the run as service

      4. don't initialize cluster db, uncheck it.

      5. continue with installation.

      6. you should be able to start the service from the services msc.

      Regards,

      Jonel Rienton

      I know not english well, but I know 9 computer languages.

      Pinoy Ako!

      --Original Message--

      From: pgsql-general-owner.postgresql.todaysummary.com.postgresql.org

      [mailto:pgsql-general-owner.postgresql.todaysummary.com.postgresql.org] On Behalf Of Ott Havasvlgy

      i

      Sent: Saturday, January 14, 2006 7:38 AM

      To: Magnus Hagander

      Cc: Pg - General

      Subject: Re: [GENERAL] Moving PostgreSQL data directory on Windows

      Hello,

      Ok. I did not set any security on the machine. Only I have access to it. Now

      I have done this:

      0. Registry is modified (yesterday)

      1. Restart today

      2. Modify registry to the old value

      3. Start PostgreSQL -> pg_ctl and postmaster are running (they are in the

      task list), but no postgres 4. "pg_ctl status" says that neither postmaster

      nor postgres are running. No new entry in event log.

      5. Stop with "pg_ctl stop" -> it says "Could not send stop signal (PID

      3624): no such process". There is really no such PID.

      6. Restart Windows -> the same as before 7. Stop PostgreSQL from the Sart

      menu -> it took a half minute, and now only postmaster is running.

      8. pg_ctl unregister -N "pgsql-8.0" -> seems it succeeded 9. Register on the

      new location 10. Start with "net start "pgsql-8.0"" -> The service could not

      be started, no event log entry. Perhaps because a postmaster isrunning.

      11. Kill postmaster, I cannot stop it in other way.

      12. Start from Start menu and "net start", but nothing happens. The same

      message as in step 10.

      13. Start from Control panel - Services -> Message: "The service started

      then stopped."

      Now I really don't know how to start this.

      Perhaps reinstaling Postgres is easier.

      14. Modified all this with the installer, but PostgreSQL does not start.

      15. Backup data directory

      16. Uninstall PostgreSQL

      17. Install PostgreSQL 8.0.6 (this was installed before), as service, set

      the same passwords.

      18. Stop PostgreSQL

      19. Move the backuped data directory to the location PostgreSQL uses.

      20. Start PostgreSQL now, but it does not start this way.

      What can be done to use my old data? It's not that critical, only tests.

      Thanks,

      Otto

      2006/1/14, Magnus Hagander <mha.postgresql.todaysummary.com.sollentuna.net>:

      > Ok, two things:

      > First, when using this method, yo uhave to reboot after editing the

      registry. If you didn't do that, then that's your problem. Sorry, forgot to

      mention that. If not, then looking at permissions as Rodrigo suggested is

      the next step.

      > Second, there is of course a nicer way of doing this than editing the

      registry, that I compleetley forgot about. It's still not as nice as you

      would want, but it should work. You use pg_ctl unregister to remove the

      service, and then pg_ctl register to register a new one with the new data

      directory Doing this you won't hvae to reboot..

      >

      > Perhaps this is something we should add to pg_ctl for 8.2? "pg_ctl

      reconfigure" or something like that? What do others think?

      > //Magnus

      >

      >

      --(end of broadcast)--

      TIP 5: don't forget to increase your free space map settings

      No virus found in this incoming message.

      Checked by AVG Free Edition.

      Version: 7.1.371 / Virus Database: 267.14.17/228 - Release Date: 1/12/2006

      --(end of broadcast)--

      TIP 3: Have you checked our extensive FAQ?

      http://www.postgresql.org/docs/faq

      #9; Fri, 23 May 2008 10:28:00 GMT