5

In an ubuntu 20.04 with Postgres 12 server, after a problem in HD, Postgres stopped working. Bellow my tests and the tries to solve the problem:

~$ psql
psql: error: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

When I try systemctl:

$ sudo systemctl start postgresql@12-main
Job for [email protected] failed because the service did not take the steps required by its unit configuration.
See "systemctl status [email protected]" and "journalctl -xe" for details.

Output for systemctl status [email protected]:

$ systemctl status [email protected][email protected] - PostgreSQL Cluster 12-main
     Loaded: loaded (/lib/systemd/system/[email protected]; enabled; vendor preset: enabled)
     Active: failed (Result: protocol) since Wed 2021-01-27 01:58:21 UTC; 1min 0s ago
    Process: 1075 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 12-main start (code=exited, status=1/FAILURE)

Jan 27 01:58:21 znserver postgresql@12-main[1075]: 2021-01-27 01:58:21.191 UTC [1096] LOG:  could not remove cache file "global/pg_internal.>
Jan 27 01:58:21 znserver postgresql@12-main[1075]: 2021-01-27 01:58:21.191 UTC [1096] PANIC:  replication checkpoint has wrong magic 5391227>
Jan 27 01:58:21 znserver postgresql@12-main[1075]: 2021-01-27 01:58:21.424 UTC [1095] LOG:  startup process (PID 1096) was terminated by sig>
Jan 27 01:58:21 znserver postgresql@12-main[1075]: 2021-01-27 01:58:21.424 UTC [1095] LOG:  aborting startup due to startup process failure
Jan 27 01:58:21 znserver postgresql@12-main[1075]: 2021-01-27 01:58:21.425 UTC [1095] LOG:  database system is shut down
Jan 27 01:58:21 znserver postgresql@12-main[1075]: pg_ctl: could not start server
Jan 27 01:58:21 znserver postgresql@12-main[1075]: Examine the log output.
Jan 27 01:58:21 znserver systemd[1]: [email protected]: Can't open PID file /run/postgresql/12-main.pid (yet?) after start: Operati>
Jan 27 01:58:21 znserver systemd[1]: [email protected]: Failed with result 'protocol'.
Jan 27 01:58:21 znserver systemd[1]: Failed to start PostgreSQL Cluster 12-main.
lines 1-15/15 (END)

with "service commands" I have:

$ sudo service postgresql start
(base) sidon@znserver:~$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2021-01-27 02:05:24 UTC; 4s ago
    Process: 1246 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1246 (code=exited, status=0/SUCCESS)

Jan 27 02:05:24 znserver systemd[1]: Starting PostgreSQL RDBMS...
Jan 27 02:05:24 znserver systemd[1]: Finished PostgreSQL RDBMS.

some Help?

2 Answers 2

7

After hours and hours of research without success I got the solution by trial and errors:

Short Answer

~$ sudo chown postgres.postgres /var/lib/postgresql/12/main/global/pg_internal.init
~$ sudo rm -rf 12/main/global/pg_internal.init
~$ sudo rm -rf /var/lib/postgresql/12/main/pg_logical/replorigin_checkpoint
~$ sudo -i -u postgres
~$ /usr/lib/postgresql/12/bin/pg_ctl restart -D /var/lib/postgresql/12/main

Long answer:

First I tried to restart with the sequence below

~$ sudo -i -u postgres
~$ /usr/lib/postgresql/12/bin/pg_ctl restart -D /var/lib/postgresql/12/main

I got the following result:

pg_ctl: PID file "/var/lib/postgresql/12/main/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2021-02-13 16:40:12.633 UTC [3806] LOG:  starting PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-02-13 16:40:12.636 UTC [3806] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-02-13 16:40:12.636 UTC [3806] LOG:  listening on IPv6 address "::", port 5432
2021-02-13 16:40:12.681 UTC [3806] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-02-13 16:40:12.824 UTC [3809] LOG:  database system was interrupted; last known up at 2021-01-07 10:56:38 UTC
2021-02-13 16:40:13.138 UTC [3809] LOG:  could not open directory "./global/pg_internal.init": Permission denied
2021-02-13 16:40:13.148 UTC [3809] LOG:  could not remove cache file "global/pg_internal.init": Is a directory
2021-02-13 16:40:13.148 UTC [3809] PANIC:  replication checkpoint has wrong magic 539122744 instead of 307747550
2021-02-13 16:40:13.38After hours and hours of research5 UTC [3806] LOG:  startup process (PID 3809) was terminated by signal 6: Aborted
2021-02-13 16:40:13.385 UTC [3806] LOG:  aborting startup due to startup process failure
2021-02-13 16:40:13.387 UTC [3806] LOG:  database system is shut down
stopped waiting
pg_ctl: could not start server
Examine the log output.

After a brief investigation, I found that the directory /var/lib/postgresql/12/main/global/pg_internal.init had the owner as root. I change the owner:

sudo chown postgres.postgres /var/lib/postgresql/12/main/global/pg_internal.init

Then I made another attempt (First step):

sudo -i -u postgres

And the result was slightly different:

/usr/lib/postgresql/12/bin/pg_ctl restart -D /var/lib/postgresql/12/main     
pg_ctl: PID file "/var/lib/postgresql/12/main/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2021-02-13 16:53:26.132 UTC [4024] LOG:  starting PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-02-13 16:53:26.132 UTC [4024] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-02-13 16:53:26.132 UTC [4024] LOG:  listening on IPv6 address "::", port 5432
2021-02-13 16:53:26.171 UTC [4024] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-02-13 16:53:26.314 UTC [4025] LOG:  database system was interrupted; last known up at 2021-01-07 10:56:38 UTC
2021-02-13 16:53:26.615 UTC [4025] LOG:  could not remove cache file "global/pg_internal.init": Is a directory
2021-02-13 16:53:26.615 UTC [4025] PANIC:  replication checkpoint has wrong magic 539122744 instead of 307747550
2021-02-13 16:53:26.851 UTC [4024] LOG:  startup process (PID 4025) was terminated by signal 6: Aborted
2021-02-13 16:53:26.851 UTC [4024] LOG:  aborting startup due to startup process failure
2021-02-13 16:53:26.852 UTC [4024] LOG:  database system is shut down
stopped waiting
pg_ctl: could not start server
Examine the log output.

So I decided to delete the file: 12/main/global/pg_internal.init

rm -rf 12/main/global/pg_internal.init

And again I performed step 1

/usr/lib/postgresql/12/bin/pg_ctl restart -D /var/lib/postgresql/12/main
pg_ctl: PID file "/var/lib/postgresql/12/main/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2021-02-13 17:00:33.310 UTC [4072] LOG:  starting PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-02-13 17:00:33.310 UTC [4072] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-02-13 17:00:33.310 UTC [4072] LOG:  listening on IPv6 address "::", port 5432
2021-02-13 17:00:33.348 UTC [4072] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-02-13 17:00:33.483 UTC [4073] LOG:  database system was interrupted; last known up at 2021-01-07 10:56:38 UTC
2021-02-13 17:00:33.792 UTC [4073] PANIC:  replication checkpoint has wrong magic 539122744 instead of 307747550
2021-02-13 17:00:34.030 UTC [4072] LOG:  startup process (PID 4073) was terminated by signal 6: Aborted
2021-02-13 17:00:34.030 UTC [4072] LOG:  aborting startup due to startup process failure
2021-02-13 17:00:34.031 UTC [4072] LOG:  database system is shut down
stopped waiting
pg_ctl: could not start server
Examine the log output.

So, I removed the file /var/lib/postgresql/12/main/pg_logical/replorigin_checkpoint

sudo rm -rf /var/lib/postgresql/12/main/pg_logical/replorigin_checkpoint

And again I performed step 1

/usr/lib/postgresql/12/bin/pg_ctl restart -D /var/lib/postgresql/12/main
pg_ctl: PID file "/var/lib/postgresql/12/main/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start....2021-02-13 17:08:02.913 UTC [4186] LOG:  starting PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-02-13 17:08:02.913 UTC [4186] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-02-13 17:08:02.913 UTC [4186] LOG:  listening on IPv6 address "::", port 5432
2021-02-13 17:08:02.952 UTC [4186] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-02-13 17:08:03.103 UTC [4187] LOG:  database system was interrupted; last known up at 2021-01-07 10:56:38 UTC
2021-02-13 17:08:03.412 UTC [4187] LOG:  database system was not properly shut down; automatic recovery in progress
2021-02-13 17:08:03.481 UTC [4187] LOG:  redo starts at 0/2FFEC58
2021-02-13 17:08:03.481 UTC [4187] LOG:  invalid record length at 0/2FFEC90: wanted 24, got 0
2021-02-13 17:08:03.481 UTC [4187] LOG:  redo done at 0/2FFEC58
2021-02-13 17:08:03.683 UTC [4186] LOG:  database system is ready to accept connections
done
server started

Ok, after this all is recovered, the postgres instalation and data!

1

Adding on @sidon answer: if you run into permissions problems like

... data directory /var/lib/postgresql/data  has invalid permissions
Permissions should be u=rwx (0700) or u=rwx,g=rx (0750)

first adjust data directory permissions:

sudo chmod 700 -R /var/lib/postgresql/data

Source: https://github.com/ClusterHQ/dvol/issues/45#issuecomment-370995983

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .