In PostgreSQL, the database files are typically stored in the data directory, which contains the configuration files, database clusters, and other essential files. The exact location of the data directory depends on your PostgreSQL installation and operating system.
Here are common locations for the PostgreSQL data directory:
1. Linux-based systems
- The data directory is usually located in:
/var/lib/postgresql/<version>/main/- Example:
/var/lib/postgresql/12/main/
You can find the location by checking the PostgreSQL service file or querying the database.
2. Windows
- On Windows, the default data directory might be:
C:\Program Files\PostgreSQL\<version>\data- Example:
C:\Program Files\PostgreSQL\13\data
3. macOS
- If PostgreSQL is installed via Homebrew, the data directory is typically:
/usr/local/var/postgres
4. Custom Installations
- If PostgreSQL was compiled from source or installed in a custom location, the data directory will be specified during the initialization of the database cluster using the
initdbcommand. In that case, it would be the path you specified.
Finding the Data Directory in PostgreSQL
You can determine the exact location of the data directory by running the following SQL query in the PostgreSQL environment:
postgers=# SHOW data_directory;
data_directory/var/lib/postgresql/12/mainTo change the location of the PostgreSQL data directory, you need to follow a series of steps carefully. The process involves stopping the PostgreSQL service, moving the data files to the new location, configuring PostgreSQL to use the new directory, and starting the service again.
Here’s a step-by-step guide:
1. Stop the PostgreSQL Service
Before moving any files, stop the PostgreSQL service to avoid any corruption or data loss.
On Linux (systemd):
sudo systemctl stop postgresql
- On Windows:
Stop the PostgreSQL service via the Services app (
services.msc), or run:
net stop postgresql
2. Copy the Data Directory to the New Location
Next, move the contents of the current data directory to the new location. Be sure to use a command that preserves file permissions and ownership.
On Linux:
sudo rsync -av /current/data/directory/ /new/data/directory/
- On Windows: Use file explorer or the command prompt to move the files:
xcopy "C:\current\data\directory" "D:\new\data\directory" /E /I /H /K /O /X /Y
3. Update PostgreSQL Configuration
Now, you need to update the PostgreSQL configuration to point to the new data directory.
For Linux (systemd):
- Open the PostgreSQL service configuration file (usually located in
/etc/postgresql/<version>/main/postgresql.conf). - Modify the
data_directoryparameter
- Open the PostgreSQL service configuration file (usually located in
data_directory = '/new/data/directory'
For Windows:
- Open the PostgreSQL configuration file (
postgresql.conf) located in the PostgreSQL installation directory. - Update the
data_directorysetting to point to the new location.
4. Set the Correct Ownership and Permissions
Ensure that the new data directory has the correct ownership and permissions.
On Linux: Change ownership to the PostgreSQL user (usually
postgres):
sudo chown -R postgres:postgres /new/data/directory
sudo chmod 700 /new/data/directory
On Windows: Ensure that the user running PostgreSQL (usually
NETWORK SERVICEorpostgres) has full control over the new directory.
5. Start PostgreSQL with the New Data Directory
Start PostgreSQL, and it should now use the new data directory.
On Linux (systemd):
sudo systemctl start postgresql
- On Windows: Start the service from the Services app or via command:
net start postgresql
6. Verify the New Data Directory
After starting PostgreSQL, verify that it’s using the new data directory by running the following command:
postgres=# SHOW data_directory;
data_directory
/new/data/directory
(1 row)
7. Clean Up (Optional)
After confirming that everything works correctly, you can remove the old data directory to free up space:
sudo rm -rf /old/data/directory
Be careful with this step#7 and make sure the new setup is fully functional before deleting the old files.
