Change Hard Disc Drive

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

On Wed, Jan 13, 2010 at 10:57 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

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.

Or... create a new tablespace on the larger drive using pgAdmin, and
then move large tables/indexes across by right-clicking them and
selecting Properties, and then choosing the new tablespace.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On 13/01/2010 1:31 PM, Dave Page wrote:

On Wed, Jan 13, 2010 at 10:57 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

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.

Or... create a new tablespace on the larger drive using pgAdmin, and
then move large tables/indexes across by right-clicking them and
selecting Properties, and then choosing the new tablespace.

Good point. The OP said "all databases" but that may actually not be
what they need.

In fact, having tablespaces spread across both drives should give them
some performance benefits (xlog on original drive, big tables on other
drive) ... though it *does* expose them to losing the entire cluster if
EITHER drive fails. Good backups become even more essential.

--
Craig Ringer