Postgresql change data directory windows

PostgreSQL for Windows installs the PGDATA directory by default into «C:\Program Files\PostgreSQL\some version\data». This mini-HOWTO explains how to change the default PGDATA directory to another location.

Step 1: Stop The PostgreSQL Service

Close all application that are currently connected to your database, then go to Windows Services Management and stop the PostgreSQL service:

Start->Settings->Control Panel->Administrative Tools->Services

You should check the task manager to see if any postgresql.exe instances are still running. If so, DO NOT TERMINATE them, instead close all applications that are still connected to the database. Sometimes services like webservers keep persistent connections. In this case you also should stop these services.

Step 2: Change Registry Values

Start the Windows Registry Editor (regedit.exe) and navigate to: «HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-some version».

Double click on «ImagePath» and change the directory after the «–D» option to your new location. If the path to your new location contains spaces, you should then enclose it with quotes.

Registry Editor

Step 3: Move the data folder to a new the location

Move your current PGDATA directory to the directory you just specified in Step 2. Please note that you should do this as Administrator on server machines.
You should also check user permissions in the new directory to ensure the username under which your PostgreSQL instance runs has permissions to do so in the new directory, otherwise PostgreSQL will not start.

Step 4: Restart The PostgreSQL Service

Before you can start your database you must close and re-open the Windows Services Management window. This refreshes the path you just changed in the registry without the need to restart your system.

After closing and re-opening the Services Management window, you should check the «path to executable» information of the PostgreSQL Database service. The path after «–D» option should be pointing to your new directory now.

Service properties

Now that the PGDATA directory has been re-located and the service startup parameter in the registry has been modified, you can go ahead and start the service. [[Category::Administration]]

Migrating the data cluster from C:\ to E:\

I’m new to working with large data in Windows, and ran into some unexpected difficulty when the PostgreSQL database ran out of space on my server’s C drive. It might also be a good idea to isolate your postgres database from a location other than the C:\Program Files\ folder for permissions/security/data integrity reasons (I had a reference for this but I lost it). Here’s how to move your PostgreSQL data directory

Basing myself on this dba.stackex answer and this wiki post

1. Stop the PostgreSQL service

Go to Start > Services. Scroll down to postgresql-x64-9.5 and right-click Stop

2. Copy data

I copied the data with File Explorer from C:\Program Files\PostgreSQL\9.5\data\ to E:\pg_db\data

3. Modify postgresql.conf (probably not necessary if you move everything to the new location)

I edited the following lines

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = 'E:\pg_db\data'		# use data in another directory
					# (change requires restart)
hba_file = 'C:\Program Files\PostgreSQL\9.5\data\pg_hba.conf'	# host-based authentication file
					# (change requires restart)
ident_file = 'C:\Program Files\PostgreSQL\9.5\data\pg_ident.conf'	# ident configuration file
					# (change requires restart)

4. Modify the command that starts PostgreSQL

If you Right Click to Properties on the PostgreSQL services in Services, you’ll notice that the startup command is

"C:\Program Files\PostgreSQL\9.5\bin\pg_ctl.exe" runservice -N "postgresql-x64-9.5" -D "C:\Program Files\PostgreSQL\9.5\data" -w

The -D "C:\Program Files\PostgreSQL\9.5\data" flag is indicating that the data directory is C:\Program Files\PostgreSQL\9.5\data

DO NOT EDIT THE REGISTRY

In any case, I didn’t have permission on the server I was using and it seems to be a Bad Idea(tm) see here

Direct registry modification should be avoided (because you can’t be sure what else Windows is changing when it modifies the path to exe, f.e.) unless you are absolutely sure what you do

Start the cmd prompt as an administrator by hitting the Windows button and typing cmd then right-clicking “Run as Administrator”

You can see the current configuration for PostgreSQL by typing sc qc postgresql-x64-9.5 1000 and hitting Enter.

Modify the configuration to the following with the sc command

sc config postgresql-x64-9.5 binPath= "\"C:\Program Files\PostgreSQL\9.5\bin\pg_ctl.exe\" runservice -N \"postgresql-x64-9.5\" -D \"E:\pg_db\data\" -w"

5. Change permissions for the new data directory

For the new data-dictionary folder: Right-click on it and click Properties. Under the Security Tab click “Edit...” and then “Add...”. Type “Network Service” and then click “Check Names”, make sure it has Modify and Full Control permissions and then click OK.
Equally important PostgreSQL needs to be able to “see” the data-directory (see my ServerFault.StackEx question), i.e. it needs to have read access to the parent directories above it. So Right-click on the pg_db folder and under the Security Permissions add Network Services again, but this time it only needs Read & Execute as well as List folder contents permissions.

6. Restart the Service

Go back to the Services window (if it was still open, refresh it) and Start the PostgreSQL service. You should be able to connect to it again in PGAdmin

postgresql