I have a CentOS 7 minimal VM running PGSQL to do some testing, but somehow, after a while, the PGSQL service seems to fail or get corrupted, and I'm completely unable to restart it. I'm basically working on implementing point in time recovery using the WAL files, which I've been successful with. I've had to rebuild the install and database more than three times, which always seems to happen after I've managed to get WAL archiving working again on the new build. I'm not making insane changes to either the postgresql.conf or pg_hba.conf files, and the databases are not that large either (less than 2GB of data across 40 tables), so I can't imagine how I keep seemingly corrupting the data directory or service. I'd love some ideas on how to troubleshoot or defeat this error. Here's the output when I run systemctl status postgresql-9.2:
Job for postgresql-9.2.service failed because the control process exited with error code. See "systemctl status postgresql-9.2.service" and "journalctl -xe" for details.
[root@localhost bin]# systemctl status postgresql-9.2
● postgresql-9.2.service - PostgreSQL 9.2 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.2.service; enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Wed 2020-01-15 13:09:35 EST; 3min 34s ago
Process: 14407 ExecStart=/usr/pgsql-9.2/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=1/FAILURE)
Process: 14578 ExecStartPre=/usr/pgsql-9.2/bin/postgresql92-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)
Main PID: 10787 (code=exited, status=0/SUCCESS)
Jan 15 13:09:35 localhost.localdomain systemd[1]: Starting PostgreSQL 9.2 database s....
Jan 15 13:09:35 localhost.localdomain systemd[1]: postgresql-9.2.service: control pr...1
Jan 15 13:09:35 localhost.localdomain systemd[1]: Failed to start PostgreSQL 9.2 dat....
Jan 15 13:09:35 localhost.localdomain systemd[1]: Unit postgresql-9.2.service entere....
Jan 15 13:09:35 localhost.localdomain systemd[1]: postgresql-9.2.service failed.
Here's what the journalctl -xe command shows:
-- Unit postgresql-9.2.service has begun starting up.
Jan 15 13:09:35 localhost.localdomain postgresql92-check-db-dir[14578]: "/var/lib/pgsql/9.2/data/" is missing or empty.
Jan 15 13:09:35 localhost.localdomain postgresql92-check-db-dir[14578]: Use "/usr/pgsql-9.2/bin/postgresql92-setup initdb" to initialize the database cluster.
Jan 15 13:09:35 localhost.localdomain postgresql92-check-db-dir[14578]: See /usr/share/doc/postgresql92-9.2.24/README.rpm-dist for more information.
Jan 15 13:09:35 localhost.localdomain polkitd[715]: Unregistered Authentication Agent for unix-process:14572:4358382 (system bus name :1.215, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US.UTF-8) (disconnected from bus)
Jan 15 13:09:35 localhost.localdomain systemd[1]: postgresql-9.2.service: control process exited, code=exited status=1
Jan 15 13:09:35 localhost.localdomain systemd[1]: Failed to start PostgreSQL 9.2 database server.
-- Subject: Unit postgresql-9.2.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-9.2.service has failed.
--
-- The result is failed.
Jan 15 13:09:35 localhost.localdomain systemd[1]: Unit postgresql-9.2.service entered failed state.
Jan 15 13:09:35 localhost.localdomain systemd[1]: postgresql-9.2.service failed.
Jan 15 13:16:01 localhost.localdomain anacron[14339]: Job `cron.daily' started
Jan 15 13:16:01 localhost.localdomain run-parts(/etc/cron.daily)[14590]: starting logrotate
Jan 15 13:16:01 localhost.localdomain run-parts(/etc/cron.daily)[14595]: finished logrotate
Jan 15 13:16:01 localhost.localdomain run-parts(/etc/cron.daily)[14597]: starting man-db.cron
Jan 15 13:16:04 localhost.localdomain run-parts(/etc/cron.daily)[17612]: finished man-db.cron
Jan 15 13:16:04 localhost.localdomain anacron[14339]: Job `cron.daily' terminated
Jan 15 13:16:04 localhost.localdomain anacron[14339]: Normal exit (1 job run)
I can't find any logs specific to PGSQL (nothing in /var/log/ relating to PGSQL, nothing in the data directory either), so troubleshooting is getting pretty hard. Thank you for your help!
Troubleshooting tips:
Find the PostgreSQL log (perhaps somewhere under /var/lib/pgsql/9.2/data) and read it.
Try to start PostgreSQL by hand as user postgres with
postgres -D /path/to/the/data/directory
and see what you get.
Use a PostgreSQL version other than the ancient 9.2.