11

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!

Vitaly Stanchits
  • 658
  • 2
  • 7
  • 24

5 Answers5

6

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.

Community
  • 1
  • 1
Vitaly Stanchits
  • 658
  • 2
  • 7
  • 24
  • 1
    nice! and works with other versions too! I wonder if alternative init method could be used to initiate database, like with `postgresql95-setup`. Not sure how these two methods exactly differ but apart from running other stuff `postgresql95-setup` runs `initdb` internally. I couldn't however figure out how to feed `-D` switch to it, like: `usr/pgsql-9.5/bin/postgresql95-setup initdb -D ` raises error: `systemctl: invalid option -- 'D'` – msciwoj Aug 19 '16 at 13:44
5

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

mivk
  • 13,452
  • 5
  • 76
  • 69
3

try this:

 ## Login with postgres user
 su - postgres
 export PGDATA=/your_path/data
 pg_ctl -D $PGDATA start &
Bagus Trihatmaja
  • 805
  • 1
  • 9
  • 26
  • OK, this works after logging in and setting the variable manually. But after logging in again the PGDATA is reset. Is there a way to save it permanently? – Vitaly Stanchits Nov 10 '14 at 15:29
  • Thanks, now I can start postgres manually. But for some reason service postgresql-9.3 start still looks for the default directory and makes the same error as before. Is there anything that needs to be set especially for postgres to run as a service? – Vitaly Stanchits Nov 10 '14 at 15:38
  • here some good source to read: [postgresql change default data directory](http://serverfault.com/questions/273660/postgresql-change-default-data-path) – Bagus Trihatmaja Nov 10 '14 at 15:41
  • It appears the problem is the service functionality itself: http://unix.stackexchange.com/questions/44370/how-to-make-unix-service-see-environment-variables "service strips all environment variables but TERM, PATH and LANG which is a good thing" so I need to create a custom service for the pgsql database. – Vitaly Stanchits Nov 10 '14 at 15:58
  • ah yeah, you can modify postgresql service (backup first). Good luck! – Bagus Trihatmaja Nov 10 '14 at 16:03
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/
Dzmitry Plashchynski
  • 554
  • 5
  • 10
0

Try to edit file /etc/init.d/postgresql-9.3: PGDATA=/your/custom/path

Oleksii Karpeliuk
  • 1