Postgres pg_dump vuelca la base de datos en un orden diferente cada vez

Estoy escribiendo un script PHP (que también usa comandos bash de Linux) que se ejecutará a través de casos de prueba haciendo lo siguiente:

Estoy usando una base de datos PostgreSQL (8.4.2)…

1.) Crear una base de datos 2.) Modificar la base de datos 3.) Almacenar un volcado de base de datos de la base de datos (pg_dump)

4.) Realice pruebas de regresión siguiendo los pasos 1.) y 2.), y luego tome otro volcado de la base de datos y compárelo (diff) con el volcado de la base de datos original del paso número 3.)

Sin embargo, encuentro que pg_dump no siempre volcará la base de datos de la misma manera. Volcará las cosas en un orden diferente cada vez. Por lo tanto, cuando hago una diferencia en los dos volcados de la base de datos, la comparación dará como resultado que los dos archivos sean diferentes, cuando en realidad son iguales, solo que en un orden diferente.

¿Hay alguna forma diferente de hacer pg_dump?

¡Gracias!

Vale la pena distinguir esquema y datos aquí. El esquema se vuelca en un orden bastante determinista, la mayoría de los objetos en orden alfabético, limitado por las dependencias entre objetos. Hay algunos casos limitados en los que el orden no está completamente restringido y puede parecer aleatorio para un observador externo, pero eso puede arreglarse en la próxima versión.

Los datos, por otro lado, se vuelcan en el orden del disco. Por lo general, esto es lo que desea, porque desea que los volcados sean rápidos y no utilicen cantidades insensatas de recursos para realizar la clasificación. Lo que podría estar observando es que cuando “modifica la base de datos” está haciendo una ACTUALIZACIÓN, que en realidad eliminará el valor anterior y agregará el nuevo valor al final. Y eso, por supuesto, alterará su estrategia de diferencias.

Una herramienta que podría ser más adecuada para su propósito es pg_comparator.

Aquí hay un script útil para el preprocesamiento. pg_dump salida para que sea más adecuado para diferenciar y almacenar en el control de versiones:

https://github.com/akaihola/pgtricks

pg_dump_splitsort.py divide el volcado en los siguientes archivos:

• 0000_prologue.sql: todo hasta la primera COPIA
• 0001_<schema>.<table>.sql

  .
  .
  NNNN_<schema>.<table>.sql: datos para cada tabla ordenados por el primer campo

• 9999_epilogue.sql: todo después de la última COPIA

Los archivos para los datos de la tabla están numerados, por lo que se puede usar una concatenación ordenada simple de todos los archivos para volver a crear la base de datos:

$ cat *.sql | psql <database>

Descubrí que una buena manera de echar un vistazo rápido a las diferencias entre volcados es usar el meld herramienta en todo el directorio:

$ meld old-dump/ new-dump/

Almacenar el volcado en el control de versiones también brinda una visión decente de las diferencias. Aquí se explica cómo configurar git para usar el color en las diferencias:

# ~/.gitconfig
[color]
        diff = true
[color "diff"]
        frag = white blue bold
        meta = white green bold
        commit = white red bold

Nota: Si ha creado/eliminado/renombrado tablas, recuerde eliminar todas .sql archivos antes de posprocesar el nuevo volcado.

Es imposible forzar a pg_dump a volcar datos en un orden particular, ya que volca los datos en el orden del disco; es mucho más rápido de esta manera.

Puede usar las opciones “-a -d” para pg_dump y luego “ordenar” la salida, pero las nuevas líneas en los datos harán que la salida ordenada no se pueda usar. Pero para una comparación básica, si algo cambió, sería suficiente.

A partir de mayo de 2010 a parche para pg_dump existe que puede ser útil para todos los interesados ​​en este asunto: agrega la opción “–ordered” a esta utilidad:

El uso de –ordered ordenará los datos por clave principal o índice único, si existe, y utilizará el orden “más pequeño” (es decir, el menor número de columnas requeridas para un orden único).

Tenga en cuenta que –ordered podría aplastar su servidor de base de datos si intenta ordenar tablas muy grandes, así que utilícelo con prudencia.

No lo probé, pero creo que vale la pena intentarlo.

Si solo te interesa el esquema:

Puede hacer su tabla de diferencias por tabla usando una combinación de estas opciones para volcar el esquema para una sola tabla a la vez. Luego, puede compararlos individualmente o clasificarlos todos en un archivo en un orden conocido.

-s, --schema-only           dump only the schema, no data
-t, --table=TABLE           dump the named table(s) only

Para generar la lista de tablas para alimentar a la anterior, consulta information_schema.tables.

No es inusual que PostgreSQL se comporte de manera no determinista, tal vez procesos de reorganización activados por temporizador o algo así ocurre en segundo plano. Además, no conozco una forma de obligar a pg_dump a reproducir una salida idéntica en bits en ejecuciones sucesivas.

Sugiero cambiar su lógica de comparación porque es su comparación la que se comporta mal: informa diferencias mientras que ambos volcados representan el mismo estado de la base de datos. Por supuesto, esto significa algo de trabajo adicional pero, en mi opinión, es la forma correcta de atacar el problema.

Si el rendimiento es menos importante que el orden, podría usar:

COPY (select * from your_table order by some_col) to stdout
      with csv header delimiter ',';

Ver COPIAR (9.5)