Postgresql 9.3 on Centos 7 with custom PGDATA

Question

I am trying to set up Postgresql 9.3 server on Centos 7 (installation via yum) inside a custom directory, which in my case is an encrypted partition (/custom_container/database) that is mounted on startup. For a certain reason Postgresql does not behave like it should in the manual and makes an error on service startup.

Note: It does not want to accept the PGDATA environment variable which I set, and when running

su - postgres -c '/usr/pgsql-9.3/bin/initdb'

(given that the PGDATA directory is owned by postgres:postgres) the cluster gets initialized inside the default directory /var/lib/pgsql/9.3/data/ The only way to change that is using

su - postgres -c '/usr/pgsql-9.3/bin/initdb --pgdata=$PGDATA'

Which initializes the directory inside the custom container I am using. This is something I could not figure out, as the docs say that PGDATA variable is taken on default.

Problem: When running

service postgresql-9.3 start

I get an error with the log

postgresql-9.3.service - PostgreSQL 9.3 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.3.service; disabled)
Active: failed (Result: exit-code) since Mon 2014-11-10 15:24:15 CET; 1s ago
Process: 2785 ExecStartPre=/usr/pgsql-9.3/bin/postgresql93-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

Nov 10 15:24:15 CentOS-70-64-minimal systemd[1]: Starting PostgreSQL 9.3 database server...
Nov 10 15:24:15 CentOS-70-64-minimal postgresql93-check-db-dir[2785]: "/var/lib/pgsql/9.3/data/" is missing or empty.
Nov 10 15:24:15 CentOS-70-64-minimal postgresql93-check-db-dir[2785]: Use "/usr/pgsql-9.3/bin/postgresql93-setup initdb" to initialize t...ster.
Nov 10 15:24:15 CentOS-70-64-minimal postgresql93-check-db-dir[2785]: See %{_pkgdocdir}/README.rpm-dist for more information.
Nov 10 15:24:15 CentOS-70-64-minimal systemd[1]: postgresql-9.3.service: control process exited, code=exited status=1
Nov 10 15:24:15 CentOS-70-64-minimal systemd[1]: Failed to start PostgreSQL 9.3 database server.
Nov 10 15:24:15 CentOS-70-64-minimal systemd[1]: Unit postgresql-9.3.service entered failed state.

Which means that Postgresql, even though the cluster is initialized in the new $PGDATA directory (/custom_container/database) still looks for the cluster in /var/lib/pgsql/9.3/data/

Did anyone experience this Postgresql behavior before? Could it be that I forgot certain configuration options or that the problem comes from Postgresql installation?

Thank you in advance!

Answer 1

You need to create a custom postgresql.service file in /etc/systemd/system/, which overrides the default PGDATA environment variable. Your custom service file can .include the default postgresql service file, so you only need to add what you want to change. That way, upgrades can still modify/improve? stuff in the default service file, while your change is preserved.

This is how I just did it in Centos 7:

cat <<END >/etc/systemd/system/postgresql.service
.include /lib/systemd/system/postgresql.service
[Service]
Environment=PGDATA=/mnt/postgres/data ## <== SET THIS TO YOUR WANTED $PGDATA
END

systemctl daemon-reload

systemctl restart postgresql.service

Verify :

ps -ax | grep [p]ostgres

Update:

Rather than manually creating the file and adding the .include line, you can also use the systemd built-in way:

systemctl edit postgresql.service

This will open your default editor and save your changes to /etc/systemd/system/postgresql.service.d/override.conf

Answer 2

It appears the real problem was setting the environment variables, which I got working in the following thread: Centos 7 environment variables for Postgres service The issue is the PGDATA variable set inside the custom /etc/systemd/system/postgresql-9.3.service which should be created from the contents of /usr/lib/systemd/system/postgresql-9.3.service which uses the default PGDATA var.

Answer 3

I think the most "CentOS 7 way" to do it is to copy the service file:

sudo cp /usr/lib/systemd/system/postgresql-9.6.service /etc/systemd/system/postgresql-9.6.service

Then edit the file /etc/systemd/system/postgresql-9.6.service:

# Location of database directory
Environment=PGDATA=/mnt/volume/var/lib/pgsql/9.6/data/

Then start it sudo systemctl start postgresql-9.6 and verify:

# sudo ps -ax | grep postmaster
32100 ?        Ss     0:00 /usr/pgsql-9.6/bin/postmaster -D /mnt/volume/var/lib/pgsql/9.6/data/

Answer 4

try this:

 ## Login with postgres user
 su - postgres
 export PGDATA=/your_path/data
 pg_ctl -D $PGDATA start &