Thread: How to I relocate the Postgresql data directory

PostgreSQL was installed as part of other software.
The data folder is a subfolder of D:\Program Files\
Specifically:
"D:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N
"pgsql-8.3" -D "D:\Program Files\PostgreSQL\8.3\data\"

For various reasons I would like the data directory to be:
G:\PostgreSQL\8.3\data\"

I created this directory, and changed the line in the service startup,
however the service threw an error on startup.

What is the correct procedure for re-positioning the data directory.

Thanks,
--Vorpal

On Wed, Oct 6, 2010 at 6:53 PM, Vorpal <vorpalswordsman@shaw.ca> wrote:
>
> PostgreSQL was installed as part of other software.
> The data folder is a subfolder of D:\Program Files\
> Specifically:
> "D:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N
> "pgsql-8.3" -D "D:\Program Files\PostgreSQL\8.3\data\"
>
> For various reasons I would like the data directory to be:
> G:\PostgreSQL\8.3\data\"
>
> I created this directory, and changed the line in the service startup,
> however the service threw an error on startup.

Almost...  What's the exact error?  I'm no windows guy but I'm
guessing we can figure it out from the error.

Vorpal wrote:
> PostgreSQL was installed as part of other software.
> The data folder is a subfolder of D:\Program Files\
> Specifically:
> "D:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N
> "pgsql-8.3" -D "D:\Program Files\PostgreSQL\8.3\data\"
>
> For various reasons I would like the data directory to be:
> G:\PostgreSQL\8.3\data\"
>
> I created this directory, and changed the line in the service startup,
> however the service threw an error on startup.
>
> What is the correct procedure for re-positioning the data directory.

If you're just wanting a brand new database cluster, you would use the
"initdb.exe" program to create it and then afterwards pg_ctl.exe can use it; the
latter doesn't create a cluster for you if it doesn't exist.

To make a new cluster, try something like this:

     "D:\Program Files\PostgreSQL\8.3\bin\initdb.exe" -D
"G:\PostgreSQL\8.3\data\" -U postgres -W

The "-U postgres" says that "postgres" is the name of the database cluster root
user, whom you'd login as to do everything else; it defaults to your OS user
name otherwise but I prefer a portable option.  The "-W" makes it prompt you for
the password for the root user rather than you specifying that on the command
line which could be insecure.

Otherwise, if you already have started using the "data" dir under "Program
Files" because your installer created a database cluster there, then clone the
"data" dir from the old location to the new one while Postgres isn't running,
using the ordinary file system tools.  I assume that would work, though I
haven't tried, so I don't know if a DB cluster contains absolute paths or not.

-- Darren Duncan

Vorpal, 07.10.2010 02:53:
>
> PostgreSQL was installed as part of other software.
> The data folder is a subfolder of D:\Program Files\
> Specifically:
> "D:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N
> "pgsql-8.3" -D "D:\Program Files\PostgreSQL\8.3\data\"
>
> For various reasons I would like the data directory to be:
> G:\PostgreSQL\8.3\data\"
>
> I created this directory, and changed the line in the service startup,
> however the service threw an error on startup.
>
> What is the correct procedure for re-positioning the data directory.
>

Did you make sure the Postgres Windows user (which is starting the service) has "full access" to the new directory?

Regards
Thomas