postgresql-setup --initdb with custom data directory

Question

I am trying to setup 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: unrecognized option '--pgdata=/data/pgsql'
FATAL: can't parse arguments

What is the best way to achieve this?

Answer 1

If you wish to place your data in a custom directory /pgdata/14/data, create the directory with the correct permissions:

mkdir -p /pgdata/14/data
sudo chown postgres:postgres /pgdata/14/data

Then get systemctl to create a service file:

sudo systemctl edit postgresql-14.service

As mentioned above, just add the PGDATA location:

[Service]
Environment=PGDATA=/pgdata/14/data

This will create a /etc/systemd/system/postgresql-14.service.d/override.conf file. This will be merged with the original service file.

To check its content:

# cat /etc/systemd/system/postgresql-14.service.d/override.conf
[Service]
Environment=PGDATA=/pgdata/14/data

Reload systemd:

# systemctl daemon-reload

Initialize the PostgreSQL data directory:

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

Start and enable the service:

sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

• Jason Goal: I assume that this does not work for the first installation of PostgreSQL? Since there is no postgres user before the initdb after installation. Is this true? If so, how can one specify cluster location right after the first installation of PostgreSQL?
• Rph: It does work, at least for postgresql14 on RH7 - the user postgres is present before running initdb
• EkriirkE: Works for pg 9 and 11

Answer 2

I've found the easiest way to move the database to its own drive is to move the postgres home folder prior to creating the db.

mv /var/lib/pgsql /home/postgres
usermod -d /home/postgres postgres
sed -i 's|/var/lib/pgsql|/home/postgres|g' /usr/lib/systemd/system/postgresql.service
sed -i 's|/var/lib/pgsql|/home/postgres|g' /usr/bin/postgresql-setup
systemctl daemon-reload
/usr/bin/postgresql-setup --initdb

While most of the commands are relatively self-explanatory, the sed -i 's|x|y|g' file is a simple one-line find x/replace with y using sed command.

What's sad is that even though the /usr/bin/postgresql-setup script has POSTGRES_HOMEDIR=/var/lib/pgsql variable to define the home, it still has /var/lib/pgsql hard-coded in other places. Even sadder that the script does not just read the configured home from /etc/passwd file.

Answer 3

/usr/pgsql-12/bin/initdb --pgdata=/data/pgsql

Answer 4

postgresql-setup doesn't create 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
Initializing 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