Change the default PostgreSQL data directory on Windows
Our database which is storing every minute telemetry data is now getting bigger and need to be relocated to another newly mounted drive. So, I need to relocate the old data to a new disk. Actually, I don’t need to do this if my drive is a dynamic drive.
As we already know, PostgresSQL for Windows installs the PGDATA directory by default into “C:\Program Files\PostgreSQL\8.3\data”. This mini-HOWTO explains how to change the default PGDATA directory to another location. Note that 8.3 is the version number of my current PostgreSQL installation. It could be varied based on your installed version.
Step 1: Stop The PostgreSQL Service
Close all applications 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-8.3″
. Double click on “ImagePath” and change the diectory 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 the new 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.
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. The new directory should be granted permissions to PostgreSQL service to access it.
Directory Properties Dialog
Add username which PostgreSQL service use.
And then, set some required permissions PostgreSQL operations.
Now, you can go ahead and start the service.
Tags: database, pgsql.exe, postgreSQL, relocate database directory, relocate directory, Telemetry, windows