Re: Change Hard Disc Drive - Mailing list pgsql-general

On 13/01/2010 11:03 AM, Yan Cheng Cheok wrote:
> Currently, I am installing PostgreSQL in C:\Program Files\PostgreSQL, with all default settings during installation.
>
> I realize my D drive has a much larger space and I would like PostgreSQL to store all the databases in D drive.
>
> How can I do so through pgAdmin?

AFAIK you can't do it through PgAdmin. It'd be rather nice if the
service read its command line from an admin-writable-only text file in
%PROGRAMFILES%\PostgreSQL\version rather than embedding the command line
text directly in the service configuration.

Unfortunately, it doesn't. This means you need to alter the Windows
service directly. What you need to do is stop the PostgreSQL service,
then move your data directory (by default in C:\Program
Files\PostgreSQL\version\data where "version" is 8.4, 8.3, etc) to the
new location, and finally modify the command line used by the service so
Pg knows where to find its data directory.

The service name is postgresql-version eg postgresql-8.4 . You can
modify the service using sc.exe from the command line (for some bizarre
reason, the services MMC snap-in services.msc doesn't support changing
the service command line). If you're on Vista or above, you'll need to
launch the command line with "run as administrator" or you won't have
permission to do what you need to. On some older Windows versions you
may need to install the Resource Kit to get sc.exe .

Here's how to make the change.


1) get the service's current config:

C:\Windows\system32>sc qc postgresql-8.4
[SC] QueryServiceConfig SUCCESS

SERVICE_NAME: postgresql-8.4
         TYPE               : 10  WIN32_OWN_PROCESS
         START_TYPE         : 2   AUTO_START
         ERROR_CONTROL      : 1   NORMAL
         BINARY_PATH_NAME   : C:/Program
Files/PostgreSQL/8.4/bin/pg_ctl.exe runservice -N "postgresql-8.4" -D
"C:/Program Files/PostgreSQL/8.4/data" -w
         LOAD_ORDER_GROUP   :
         TAG                : 0
         DISPLAY_NAME       : postgresql-8.4 - PostgreSQL Server 8.4
         DEPENDENCIES       : RPCSS
         SERVICE_START_NAME : .\postgres



2) stop the service:

C:\Windows\system32>sc postgresql-8.4 stop

SERVICE_NAME: postgresql-8.4
         TYPE               : 10  WIN32_OWN_PROCESS
         STATE              : 3  STOP_PENDING
                                 (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
         WIN32_EXIT_CODE    : 0  (0x0)
         SERVICE_EXIT_CODE  : 0  (0x0)
         CHECKPOINT         : 0x0
         WAIT_HINT          : 0x2710



3) Move the data directory using Windows Explorer.



4) modify the service configuration to tell Pg where the new data
directory lives. Note the use of backslashes to escape innner quotes.
Make sure to use exactly the same command line as is shown in
BINARY_PATH_NAME above, except for changing the argument to -D to point
to the new data directory.

C:\Windows\system32>sc config postgresql-8.4 binPath= "\"C:/Program
Files/PostgreSQL/8.4/bin/pg_ctl.exe\" runservice -N \"postgresql-8.4\"
-D \"D:/NewPgDataDirPath\" -w"
[SC] ChangeServiceConfig SUCCESS



5) Start the service

C:\Windows\system32>sc start postgresql-8.4

SERVICE_NAME: postgresql-8.4
         TYPE               : 10  WIN32_OWN_PROCESS
         STATE              : 2  START_PENDING
                                 (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
         WIN32_EXIT_CODE    : 0  (0x0)
         SERVICE_EXIT_CODE  : 0  (0x0)
         CHECKPOINT         : 0x0
         WAIT_HINT          : 0xea60
         PID                : 3704
         FLAGS              :



6) Verify it's running

C:\Windows\system32>sc query postgresql-8.4

SERVICE_NAME: postgresql-8.4
         TYPE               : 10  WIN32_OWN_PROCESS
         STATE              : 4  RUNNING
                                 (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
         WIN32_EXIT_CODE    : 0  (0x0)
         SERVICE_EXIT_CODE  : 0  (0x0)
         CHECKPOINT         : 0x0
         WAIT_HINT          : 0x0


--
Craig Ringer