¿Cómo actualizar la tabla de Postgres desde un volcado de pg_dump?

04 Jun, 2023 Programación 0

Tengo 2 tablas de Postgres (mismo esquema) en diferentes servidores.

Una tabla tiene los datos más recientes, y la otra tiene datos antiguos que deben actualizarse con los datos más recientes.

Entonces, exporto la tabla con los datos más recientes:

pg_dump --table=table00 --data-only db00 > table00.sql

pg_dump --table=table00 --data-only --column-inserts db00 > table00.sql

Pero ahora, al intentar leer el archivo SQL de la exportación, obtengo errores sobre claves duplicadas.

psql postgresql://<username>:<password>@localhost:5432/db00 --file=table00.sql

El error desaparece si elimino primero la tabla con los datos antiguos, pero esto no solo es indeseable, sino que también es absurdo.

¿Cómo puedo actualizar una tabla de Postgres desde una exportación SQL entonces?

Sería bueno si pg_dump tuviera una opción --column-updates, donde, en lugar de declaraciones INSERT, obtienes declaraciones INSERT ON CONFLICT (column) DO UPDATE SET…

davy.ai

June 4, 2023 at 4:06 am

No existe una opción incorporada en pg_dump para generar declaraciones INSERT ON CONFLICT DO UPDATE. Sin embargo, se puede utilizar una combinación de comandos pg_dump y psql junto con algunos scripts para lograr el resultado deseado.

En primer lugar, es necesario modificar el comando pg_dump para generar un volcado con declaraciones UPSERT en lugar de declaraciones INSERT. Para esto, se puede utilizar un comando como este:

pg_dump --data-only --column-inserts --table=table00 --schema-only --no-owner --no-acl db00 | \
    sed 's|INSERT INTO|INSERT INTO table00 ON CONFLICT (id) DO UPDATE SET|g' | \
    sed 's/(|(/(table00.id,/g' | \
    sed 's/VALUES \(/\(SELECT \* FROM \(VALUES (/g' | \
    sed 's/);$//g' > table00_upsert.sql

Este comando crea un volcado de los datos y el esquema de la tabla table00 sin propietario ni listas de control de acceso (ACLs). Luego utiliza sed para transformar las declaraciones INSERT en declaraciones UPSERT con cláusulas ON CONFLICT (id) DO UPDATE SET. Se asume que la columna id es la columna clave primaria o única que determina el conflicto. El archivo resultante se guarda como table00_upsert.sql.

A continuación, es necesario ejecutar las declaraciones UPSERT en el archivo de volcado en la base de datos de destino. Se puede utilizar un comando como este:

psql --set ON_ERROR_STOP=on --single-transaction postgresql://<username>:<password>@localhost:5432/db00 < table00_upsert.sql

Este comando ejecuta las declaraciones SQL en el archivo table00_upsert.sql en la base de datos db00. Utiliza la opción --set ON_ERROR_STOP=on para detener la ejecución si hay algún error. También utiliza la opción --single-transaction para envolver las declaraciones en una transacción única, asegurando la consistencia.

Con este enfoque, se puede actualizar la tabla antigua de datos sin eliminarla y sin encontrarse con errores de clave duplicada. Sin embargo, es necesario tener cuidado con el orden de los datos en el archivo de volcado, ya que las declaraciones UPSERT pueden fallar si hay conflictos con los datos antiguos. Se puede ordenar los datos en el archivo de volcado por la columna clave primaria para minimizar el riesgo de conflictos.

Answer

davy.ai

June 4, 2023 at 4:06 am
No existe una opción incorporada en pg_dump para generar declaraciones INSERT ON CONFLICT DO UPDATE. Sin embargo, se puede utilizar una combinación de comandos pg_dump y psql junto con algunos scripts para lograr el resultado deseado.

En primer lugar, es necesario modificar el comando pg_dump para generar un volcado con declaraciones UPSERT en lugar de declaraciones INSERT. Para esto, se puede utilizar un comando como este:
```
pg_dump --data-only --column-inserts --table=table00 --schema-only --no-owner --no-acl db00 | \
    sed 's|INSERT INTO|INSERT INTO table00 ON CONFLICT (id) DO UPDATE SET|g' | \
    sed 's/(|(/(table00.id,/g' | \
    sed 's/VALUES \(/\(SELECT \* FROM \(VALUES (/g' | \
    sed 's/);$//g' > table00_upsert.sql
```
Este comando crea un volcado de los datos y el esquema de la tabla table00 sin propietario ni listas de control de acceso (ACLs). Luego utiliza sed para transformar las declaraciones INSERT en declaraciones UPSERT con cláusulas ON CONFLICT (id) DO UPDATE SET. Se asume que la columna id es la columna clave primaria o única que determina el conflicto. El archivo resultante se guarda como table00_upsert.sql.

A continuación, es necesario ejecutar las declaraciones UPSERT en el archivo de volcado en la base de datos de destino. Se puede utilizar un comando como este:
```
psql --set ON_ERROR_STOP=on --single-transaction postgresql://<username>:<password>@localhost:5432/db00 < table00_upsert.sql
```
Este comando ejecuta las declaraciones SQL en el archivo table00_upsert.sql en la base de datos db00. Utiliza la opción --set ON_ERROR_STOP=on para detener la ejecución si hay algún error. También utiliza la opción --single-transaction para envolver las declaraciones en una transacción única, asegurando la consistencia.

Con este enfoque, se puede actualizar la tabla antigua de datos sin eliminarla y sin encontrarse con errores de clave duplicada. Sin embargo, es necesario tener cuidado con el orden de los datos en el archivo de volcado, ya que las declaraciones UPSERT pueden fallar si hay conflictos con los datos antiguos. Se puede ordenar los datos en el archivo de volcado por la columna clave primaria para minimizar el riesgo de conflictos.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

¿Cómo actualizar la tabla de Postgres desde un volcado de pg_dump?

Answer

davy.ai