How to setup postgres initdb with a custom data directory?

4.1K Asked by AndreaBailey in SQL Server , Asked on Oct 6, 2022

I am trying to set up Postgres 12 on Centos 8. Postgres shall be installed in the default directory i.e. /var/lib/pgsql, however I want the data directory to be in /data/pgsql I want to use postgresql-setup as root, as I believe it will create systemd service files along with it, rather than using pg_ctl or running initdb as postgres user.

However, if I try

$ postgresql-setup --initdb --pgdata=/data/pgsql/

I will receive the following error.

postgresql-setup: unrecognised option '--pgdata=/data/pgsql'

FATAL: can't parse arguments

What is the best way to achieve this?

Answered by Amit Sinha

postgresql initdb setup doesn't create a systemd service file. You must create it from scratch /lib/systemd/system/postgresql-14.service or create config file for default systemd service file.

  For example /lib/systemd/system/db-test.service, which contain path to desired db

  # Location of database directory

Environment=PGDATA=/data/pgsql/db_test

then run

# systemctl daemon-reload

# systemctl db-test.service

# /usr/bin/postgresql-14-setup initdb db-test

Initialising database ... OK

and voilà

# ls -l /data/pgsql/db_test/pg_hba.conf 

-rw------- 1 postgres postgres 4577 Feb 25 15:19 /data/pgsql/db_test/pg_hba.conf

If you want use default service then create config /lib/systemd/system/postgresql-14.service.d/postgresql-14.conf for service /lib/systemd/system/postgresql-14.service

For example

# cat /lib/systemd/system/postgresql-14.service.d/postgresql-14.conf

[Service]

Environment=PGDATA=/data/pgsql/postgresql-14

Your Answer

Answer (1)

Ranjana

To set up PostgreSQL initdb with a custom data directory, you'll need to follow these steps:

Choose or Create a Custom Data Directory:

Decide on the location where you want to store your PostgreSQL data. Choose a directory with appropriate permissions. For example:

mkdir /path/to/custom/data/directory

Initialize the Database Cluster:

Use the initdb command to initialize a new PostgreSQL database cluster with your custom data directory. Specify the location of your custom data directory using the -D or --pgdata option. For example:

initdb -D /path/to/custom/data/directory

Customize Initialization Parameters (Optional):

You can customize the initialization parameters by modifying the PostgreSQL configuration files located in your data directory (postgresql.conf, pg_hba.conf, etc.) before starting the server. This step is optional but allows you to tailor the server configuration to your specific needs.

Start the PostgreSQL Server:

After initializing the database cluster, you can start the PostgreSQL server. Use the pg_ctl command with the -D option to specify the location of your custom data directory. For example:

  pg_ctl -D /path/to/custom/data/directory start

Connect to the Database:

Once the server is running, you can connect to the PostgreSQL database using a PostgreSQL client tool like psql. Specify the database name, username, and host as needed.

For example:

  psql -d mydatabase -U myusername -h localhost

Verify Installation:

After connecting to the database, you can verify that everything is set up correctly by running SQL queries or commands like l to list databases, dt to list tables, etc.

Make sure to replace /path/to/custom/data/directory with the actual path to your custom data directory. By following these steps, you can set up PostgreSQL initdb with a custom data directory and start using your database with the desired configuration.

6 Months