Store PostgreSQL Data on Another Drive (D: or E:)

Maulik Paneliya — Tue, 21 Jan 2025 09:40:42 +0000

Initialize a New Data Directory You can use the initdb command to create a new data directory on your desired drive.

Open Command Prompt as Administrator.
Run the following command to initialize a new data directory on your chosen drive:

initdb -D D:\PostgreSQLData

Replace D:\PostgreSQLData with the desired folder path on your other drive.

Update PostgreSQL Configuration to Use the New Data Directory You need to inform PostgreSQL to use this new location.

Stop the PostgreSQL service:

Open the Services Manager (press Win + R, type services.msc, and hit Enter).
Find the PostgreSQL service (e.g., postgresql-x64-17) and stop it.
Edit the PostgreSQL service to point to the new data directory:

Open a Command Prompt as Administrator.
Run this command:

pg_ctl register -N "postgresql-x64-17" -D "D:\PostgreSQLData"

Replace "D:\PostgreSQLData" with your new data directory path.
Update postgresql.conf if needed:

Locate the postgresql.conf file in your new data directory.
Check for any absolute paths that reference the old directory and update them to the new directory.

Restart the PostgreSQL Service Go back to the Services Manager and restart the PostgreSQL service. Alternative During Installation If you're installing PostgreSQL for the first time:

During installation, choose the "Advanced Options" step.
Change the data directory path to your preferred location (e.g., D:\PostgreSQLData).

Additional Notes
Ensure the drive you choose has sufficient space for your database needs.
Back up your data regularly to avoid accidental data loss, regardless of the drive used.

Fixing Services Issue

net stop postgresql-x64-17
cd C:\Program Files\PostgreSQL\17\bin
pg_ctl unregister -N "postgresql-x64-17
pg_ctl register -N "postgresql-x64-17" -D C:\Program Files\PostgreSQL\17\data
net start postgresql-x64-17

DUMP MSSQL DATABASE INTO POSTGRES

Maulik Paneliya — Fri, 26 Jul 2024 12:56:08 +0000

1. Install pgloader into system:

Download pgloader

OR Run

$ apt-get install pgloader

2. create config file and add below code to file: [ex: pgloader.conf]

load database from mssql://Username:Password@Host:Port/dbname into postgresql://Username:Password@Host:Port/dbname ALTER SCHEMA 'dbo' RENAME TO 'public';

3. run following command in terminal:

pgloader pgloader.conf

Click here for more info

Thank You

DEV Community: Maulik Paneliya

Store PostgreSQL Data on Another Drive (D: or E:)

Fixing Services Issue

DUMP MSSQL DATABASE INTO POSTGRES