Saludos cordiales.
Estimados, nuevamente necesito de su ayuda.
Tengo una instalación de PostgreSQL 12 en Centos 7 desde hace un poco
mas de un año funcionando sin novedad. Postgres lo uso para almacenar
logs de otras aplicaciones, donde tengo una tabla particionada por
años, la última vez que ví esa tabla pesaba unos 20 Gigas.
El día de hoy encontré el servicio de postgres detenido:
[root@server log]# systemctl status postgresql-12
● postgresql-12.service - PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service;
enabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Sun 2021-12-05 08:35:09
EST; 38min ago
Docs: https://www.postgresql.org/docs/12/static/
Process: 4985 ExecStart=/usr/pgsql-12/bin/postmaster -D ${PGDATA}
(code=exited, status=1/FAILURE)
Process: 4978
ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA}
(code=exited, status=0/SUCCESS)
Main PID: 4985 (code=exited, status=1/FAILURE)
Dec 05 08:34:56 server.it-empresarial.com postmaster[4985]: 2021-12-05
08:34:56.619 EST [4985] LOG: listening on IPv4 address "0.0.0.0",
port 5433
Dec 05 08:34:56 server.it-empresarial.com postmaster[4985]: 2021-12-05
08:34:56.619 EST [4985] LOG: listening on IPv6 address "::", port
5433
Dec 05 08:34:56 server.it-empresarial.com postmaster[4985]: 2021-12-05
08:34:56.624 EST [4985] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5433"
Dec 05 08:34:56 server.it-empresarial.com postmaster[4985]: 2021-12-05
08:34:56.630 EST [4985] LOG: listening on Unix socket
"/tmp/.s.PGSQL.5433"
Dec 05 08:34:56 server.it-empresarial.com postmaster[4985]: 2021-12-05
08:34:56.722 EST [4985] LOG: redirecting log output to logging
collector process
Dec 05 08:34:56 server.it-empresarial.com postmaster[4985]: 2021-12-05
08:34:56.722 EST [4985] HINT: Future log output will appear in
directory "log".
Dec 05 08:35:09 server.it-empresarial.com systemd[1]:
postgresql-12.service: main process exited, code=exited,
status=1/FAILURE
Dec 05 08:35:09 server.it-empresarial.com systemd[1]: Failed to start
PostgreSQL 12 database server.
Dec 05 08:35:09 server.it-empresarial.com systemd[1]: Unit
postgresql-12.service entered failed state.
Dec 05 08:35:09 server.it-empresarial.com systemd[1]:
postgresql-12.service failed.
Al reiniciarlo tengo la misma novedad.
En el log tengo lo siguiente:
PL/pgSQL function api.fn_access_point(json) line 30 at EXECUTE
2021-12-05 06:25:51.361 EST [20370] WARNING: terminating connection
because of crash of another server process
2021-12-05 06:25:51.361 EST [20370] DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2021-12-05 06:25:51.361 EST [20370] HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2021-12-05 06:25:51.361 EST [20370] CONTEXT: SQL statement "SELECT *
FROM events.eventtypes WHERE code = NEW.code LIMIT 1"
PL/pgSQL function events.check_before() line 17 at SQL statement
SQL statement "INSERT INTO events.datas(
dateevent, idaccount, priority, ideventtype, iduser,
idcontact, details, source, code)
VALUES (irow.dateevent, irow.idaccount, irow.priority,
irow.ideventtype, irow.iduser, irow.idcontact, irow.details,
irow.source, irow.code) RETURNING idevent"
PL/pgSQL function events.fn_event_insert_json(json) line 21 at
SQL statement
PL/pgSQL function api.fn_method00134(json) line 30 at assignment
SQL statement "SELECT api.fn_method00134($1)"
PL/pgSQL function api.fn_access_point(json) line 30 at EXECUTE
2021-12-05 06:25:51.363 EST [20436] WARNING: terminating connection
because of crash of another server process
2021-12-05 06:25:51.363 EST [20436] DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2021-12-05 06:25:51.363 EST [20436] HINT: In a moment you should be
able to reconnect to the database and repeat your command.
2021-12-05 06:25:51.363 EST [20436] CONTEXT: SQL statement "SELECT *
FROM events.eventtypes WHERE code = NEW.code LIMIT 1"
PL/pgSQL function events.check_before() line 17 at SQL statement
SQL statement "INSERT INTO events.datas(
dateevent, idaccount, priority, ideventtype, iduser,
idcontact, details, source, code)
VALUES (irow.dateevent, irow.idaccount, irow.priority,
irow.ideventtype, irow.iduser, irow.idcontact, irow.details,
irow.source, irow.code) RETURNING idevent"
PL/pgSQL function events.fn_event_insert_json(json) line 21 at
SQL statement
PL/pgSQL function api.fn_method00134(json) line 30 at assignment
SQL statement "SELECT api.fn_method00134($1)"
PL/pgSQL function api.fn_access_point(json) line 30 at EXECUTE
2021-12-05 06:25:51.402 EST [20464] FATAL: the database system is in
recovery mode
2021-12-05 06:25:51.404 EST [20465] FATAL: the database system is in
recovery mode
2021-12-05 06:25:51.404 EST [20466] FATAL: the database system is in
recovery mode
2021-12-05 06:25:51.405 EST [20463] FATAL: the database system is in
recovery mode
2021-12-05 06:25:51.484 EST [1480] LOG: all server processes
terminated; reinitializing
2021-12-05 06:25:52.141 EST [20480] FATAL: the database system is in
recovery mode
2021-12-05 06:25:52.141 EST [20481] FATAL: the database system is in
recovery mode
2021-12-05 06:25:52.142 EST [20483] FATAL: the database system is in
recovery mode
2021-12-05 06:25:52.143 EST [20486] FATAL: the database system is in
recovery mode
2021-12-05 06:25:52.144 EST [20484] FATAL: the database system is in
recovery mode
2021-12-05 06:25:52.145 EST [20488] FATAL: the database system is in
recovery mode
2021-12-05 06:25:52.147 EST [20490] FATAL: the database system is in
recovery mode
2021-12-05 06:25:52.148 EST [20479] LOG: database system was
interrupted; last known up at 2021-12-05 06:24:37 EST
2021-12-05 06:25:52.149 EST [20489] FATAL: the database system is in
recovery mode
2021-12-05 06:25:52.150 EST [20491] FATAL: the database system is in
recovery mode
2021-12-05 06:25:52.153 EST [20482] FATAL: the database system is in
recovery mode
2021-12-05 08:10:20.767 EST [4044] DETAIL: Could not fsync file
"pg_multixact/members/0A4B": Input/output error.
2021-12-05 08:10:20.767 EST [4044] CONTEXT: WAL redo at 3B/F648DB60
for MultiXact/ZERO_MEM_PAGE: 84336
2021-12-05 08:10:21.194 EST [4051] FATAL: the database system is starting up
2021-12-05 08:10:23.195 EST [4052] FATAL: the database system is starting up
2021-12-05 08:10:25.196 EST [4053] FATAL: the database system is starting up
2021-12-05 08:10:26.354 EST [4041] LOG: startup process (PID 4044)
was terminated by signal 6: Aborted
2021-12-05 08:10:26.354 EST [4041] LOG: aborting startup due to
startup process failure
2021-12-05 08:10:26.847 EST [4041] LOG: database system is shut down
2021-12-05 08:33:40.087 EST [4822] LOG: database system was
interrupted while in recovery at 2021-12-05 08:10:12 EST
2021-12-05 08:33:40.087 EST [4822] HINT: This probably means that
some data is corrupted and you will have to use the last backup for
recovery.
2021-12-05 08:33:40.285 EST [4822] LOG: database system was not
properly shut down; automatic recovery in progress
2021-12-05 08:33:40.296 EST [4822] LOG: redo starts at 3B/CE68DFA8
2021-12-05 08:33:40.733 EST [4823] FATAL: the database system is starting up
2021-12-05 08:33:42.735 EST [4824] FATAL: the database system is starting up
2021-12-05 08:33:44.737 EST [4825] FATAL: the database system is starting up
2021-12-05 08:33:46.737 EST [4826] FATAL: the database system is starting up
2021-12-05 08:33:48.273 EST [4822] PANIC: could not access status of
transaction 0
2021-12-05 08:33:48.273 EST [4822] DETAIL: Could not fsync file
"pg_multixact/members/0A4B": Input/output error.
2021-12-05 08:33:48.273 EST [4822] CONTEXT: WAL redo at 3B/F648DB60
for MultiXact/ZERO_MEM_PAGE: 84336
2021-12-05 08:33:48.742 EST [4829] FATAL: the database system is starting up
2021-12-05 08:33:50.744 EST [4830] FATAL: the database system is starting up
2021-12-05 08:33:52.743 EST [4831] FATAL: the database system is starting up
2021-12-05 08:33:53.867 EST [4819] LOG: startup process (PID 4822)
was terminated by signal 6: Aborted
2021-12-05 08:33:53.867 EST [4819] LOG: aborting startup due to
startup process failure
2021-12-05 08:33:54.364 EST [4819] LOG: database system is shut down
2021-12-05 08:34:56.762 EST [4989] LOG: database system was
interrupted while in recovery at 2021-12-05 08:33:40 EST
2021-12-05 08:34:56.762 EST [4989] HINT: This probably means that
some data is corrupted and you will have to use the last backup for
recovery.
2021-12-05 08:34:56.952 EST [4989] LOG: database system was not
properly shut down; automatic recovery in progress
2021-12-05 08:34:56.957 EST [4989] LOG: redo starts at 3B/CE68DFA8
2021-12-05 08:35:04.130 EST [4989] PANIC: could not access status of
transaction 0
2021-12-05 08:35:04.130 EST [4989] DETAIL: Could not fsync file
"pg_multixact/members/0A4B": Input/output error.
2021-12-05 08:35:04.130 EST [4989] CONTEXT: WAL redo at 3B/F648DB60
for MultiXact/ZERO_MEM_PAGE: 84336
2021-12-05 08:35:09.510 EST [4985] LOG: startup process (PID 4989)
was terminated by signal 6: Aborted
2021-12-05 08:35:09.510 EST [4985] LOG: aborting startup due to
startup process failure
2021-12-05 08:35:09.990 EST [4985] LOG: database system is shut down
Lamentablemente creé varias tablas y modifiqué otras en los últimos
días y no tengo backup actualizado. No me importa perder datos de la
tabla donde guardaba información de logs, lo que más me interesa son
las tablas de configuración y su estructura.
Hay alguna forma de poder recuperar algunas tablas o como podría
solventar este inconveniente, tomando en cuenta que no tengo backup?
Ya revisé el espacio en disco y tengo suficiente, postgresql no tiene
replicación ni nada parecido, es bastante básica la instalación.
De ante mano mil gracias a quienes me puedan orientar.
Saludos desde Ecuador.
Mis proyectos de software libre en:
https://github.com/edwinspire
Github - edwinspire
