database - how - restore bak file postgresql

¿Puedo deshacer una transacción que ya he cometido?(pérdida de datos)

Question

Cometí una declaración de UPDATE incorrecta y perdí algunos datos.

¿Es posible revertir ahora, después de que ya me haya comprometido?

¿Alguna ayuda?

ROLLBACK

dice NOTICE: there is no transaction in progress .

Answer 1

No, no puede deshacer, deshacer o revertir una confirmación.

¡PARE LA BASE DE DATOS!

(Nota: si eliminó el directorio de datos del sistema de archivos, NO detenga la base de datos. El siguiente consejo se aplica a una confirmación accidental de un DELETE o similar, no a un escenario rm -rf /data/directory ).

Si estos datos fueron importantes, DETENGA SU BASE DE DATOS AHORA y no reinicie. Use pg_ctl stop -m immediate para que no se ejecute ningún punto de control en el cierre.

No puede revertir una transacción una vez que se haya comprometido. Deberá restaurar los datos de las copias de seguridad o usar la recuperación en un momento dado, que debe haberse configurado antes del accidente.

Si no tiene configurada ninguna configuración de archivo PITR / WAL y no tiene copias de seguridad, está en un verdadero problema.

Mitigación urgente

Una vez que se detiene la base de datos, debe crear un nivel de sistema de archivos de todo el directorio de datos: la carpeta que contiene base , pg_clog , etc. Copie todo en una nueva ubicación. No haga nada a la copia en la nueva ubicación, es su única esperanza de recuperar sus datos si no tiene copias de seguridad. Haga otra copia en algún almacenamiento extraíble, si puede, y luego desenchufe ese almacenamiento de la computadora. Recuerde, necesita absolutamente todas las partes del directorio de datos, incluido pg_xlog etc. Ninguna parte es importante.

Exactamente cómo hacer la copia depende de qué sistema operativo está ejecutando. El lugar donde se encuentra el directorio de datos depende del sistema operativo que esté ejecutando y de cómo instaló PostgreSQL.

Maneras en que algunos datos podrían haber sobrevivido

Si detiene su base de datos lo suficientemente rápido, podría tener la esperanza de recuperar algunos datos de las tablas. Esto se debe a que PostgreSQL usa el control de concurrencia de varias versiones (MVCC) para administrar el acceso simultáneo a su almacenamiento. A veces se escriben nuevas versiones de las filas que actualiza a la tabla, dejando las antiguas en su lugar pero marcadas como "eliminadas". Al cabo de un tiempo, aparece autovaccum y marca las filas como espacio libre, por lo que se pueden sobrescribir con un INSERT o UPDATE posterior. Por lo tanto, las versiones antiguas de las filas de UPDATE aún pueden estar ahí, presentes pero inaccesibles.

Además, Pg escribe en dos fases. Los primeros datos se escriben en el registro de escritura anticipada (WAL). Solo una vez que se ha escrito en el WAL y en el disco, se copia al "montón" (las tablas principales), posiblemente sobrescribiendo los datos antiguos que estaban allí. El contenido de WAL es copiado al montón principal por bgwriter y por puntos de control periódicos. Por defecto los puntos de control pasan cada 5 minutos. Si logra detener la base de datos antes de que ocurra un punto de control y lo detuvo matándola con fuerza, desconectando el enchufe de la máquina o utilizando pg_ctl en el modo immediate , es posible que haya capturado los datos antes de que ocurriera el punto de control, por lo que Es más probable que los datos todavía estén en el montón.

Ahora que ha hecho una copia completa a nivel de sistema de archivos del directorio de datos, puede iniciar una copia de seguridad de su base de datos si realmente lo necesita; los datos seguirán desapareciendo, pero has hecho todo lo posible para darte alguna esperanza de que tal vez los recuperes. Dada la opción, probablemente mantendría el DB cerrado para estar seguro.

Recuperación

Puede que ahora necesite contratar a un experto en las entrañas de PostgreSQL para que lo ayude en un intento de recuperación de datos. Esté preparado para pagar a un profesional por su tiempo, posiblemente un poco de tiempo.

Publiqué sobre esto en la lista de correo de Pg, y Виктор Егоров vinculado a la publicación de depesz en pg_dirtyread , que parece exactamente lo que quieres, aunque no recupera los datos de TOAST ed, por lo que es de utilidad limitada. Pruébalo, si tienes suerte podría funcionar.

Ver: pg_dirtyread en GitHub .

He eliminado lo que había escrito en esta sección porque está obsoleto por esa herramienta.

Ver también los fundamentos del almacenamiento de filas de PostgreSQL.

Prevención

Ver mi entrada de blog Prevención de la corrupción de la base de datos PostgreSQL .

En una nota lateral semi-relacionada, si estuviera usando un compromiso de dos fases , podría ROLLBACK PREPARED para una trascendencia preparada para el compromiso pero no comprometida por completo. Eso es lo más cercano a revertir una transacción ya confirmada, y no se aplica a su situación.