-----Mensaje original----- De: Edwin De La Cruz <edwinsp...@gmail.com> Enviado el: miércoles, 8 de diciembre de 2021 9:50 Para: pgsql-es-ayuda@lists.postgresql.org Asunto: Re: Porstgres 12 - PANIC: could not access status of transaction 0
Saludos cordiales. Hice una reinstalación de postgres 12 (Centos 7) y subí un backup de la base de datos, funcionó temporalmente hasta cuando empecé a cargar datos, entonces se volvió a corromper, el mensaje que tengo es el siguiente: Dec 08 06:14:46 server.it-empresarial.com systemd[1]: Starting PostgreSQL 12 database server... -- Subject: Unit postgresql-12.service has begun start-up -- Defined-By: systemd -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel -- -- Unit postgresql-12.service has begun starting up. Dec 08 06:14:46 server.it-empresarial.com postmaster[23319]: 2021-12-08 06:14:46.669 EST [23319] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc Dec 08 06:14:46 server.it-empresarial.com postmaster[23319]: 2021-12-08 06:14:46.671 EST [23319] LOG: listening on IPv4 address "0.0.0.0", port 5433 Dec 08 06:14:46 server.it-empresarial.com postmaster[23319]: 2021-12-08 06:14:46.671 EST [23319] LOG: listening on IPv6 address "::", port 5433 Dec 08 06:14:46 server.it-empresarial.com postmaster[23319]: 2021-12-08 06:14:46.674 EST [23319] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433" Dec 08 06:14:46 server.it-empresarial.com postmaster[23319]: 2021-12-08 06:14:46.681 EST [23319] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" Dec 08 06:14:46 server.it-empresarial.com postmaster[23319]: 2021-12-08 06:14:46.697 EST [23319] LOG: redirecting log output to logging collector process Dec 08 06:14:46 server.it-empresarial.com postmaster[23319]: 2021-12-08 06:14:46.697 EST [23319] HINT: Future log output will appear in directory "log". Dec 08 06:14:51 server.it-empresarial.com kernel: XFS (dm-3): writeback error on sector 247692288 Dec 08 06:14:51 server.it-empresarial.com abrt-hook-ccpp[23322]: Process 23321 (postgres) of user 26 killed by SIGABRT - dumping core Dec 08 06:14:51 server.it-empresarial.com systemd[1]: postgresql-12.service: main process exited, code=exited, status=1/FAILURE Dec 08 06:14:51 server.it-empresarial.com systemd[1]: Failed to start PostgreSQL 12 database server. -- Subject: Unit postgresql-12.service has failed -- Defined-By: systemd -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel -- -- Unit postgresql-12.service has failed. -- -- The result is failed. Dec 08 06:14:51 server.it-empresarial.com systemd[1]: Unit postgresql-12.service entered failed state. Dec 08 06:14:51 server.it-empresarial.com systemd[1]: postgresql-12.service failed. Si alguien más me puede orientar muchas gracias. Mis proyectos de software libre en: https://github.com/edwinspire Github - edwinspire El mar, 7 dic 2021 a las 10:00, Diego (<mrstephenam...@gmail.com>) escribió: > > Hola Edwin > > > sabes que hay en "PL/pgSQL function api.fn_access_point(json) line 30 > at EXECUTE" de chusma. > > Hubo algun problema de disco? quizas se lleno? > > mira esta linea: > 2021-12-05 08:10:20.767 EST [4044] DETAIL: Could not fsync file > "pg_multixact/members/0A4B": Input/output error. > > Me parece que ahí empezó el problema. > > En todo caso, hace un copy de la carpeta data, para poder volver atras > cualquier prueba que vayas a hacer. > > > > > On 5/12/21 11:22, Edwin De La Cruz wrote: > > 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 > > > > > > Lo que veo es un error en el filesystem " writeback error on sector 247692288" Saludos “El contenido del presente mensaje (y sus anexos) es privado, confidencial y de exclusivo uso para el destinatario referenciado; es de público conocimiento que las comunicaciones por medio de Internet no permiten asegurar ni garantizar la confidencialidad de los mensajes transmitidos, así como tampoco su integridad o su correcta recepción; es por ello que SOFSE no se responsabiliza de posibles perjuicios derivados de la captura, incorporaciones de virus o cualquier otra manipulación efectuada por terceros. Las opiniones expresadas en este mensaje y en los archivos adjuntos son propias del remitente y no representan la opinión o políticas de SOFSE, salvo que se diga expresamente y el remitente se encuentre autorizado para ello”
