para - PostgreSQL: mejorando pg_dump, pg_restore performance

postgresql instalar

Question

Cuando comencé, usé pg_dump con el formato simple predeterminado. Yo no estaba iluminado.

La investigación me reveló mejoras de tamaño de archivo y tiempo con pg_dump -Fc | gzip -9 -c > dumpfile.gz pg_dump -Fc | gzip -9 -c > dumpfile.gz . Estaba iluminado.

Cuando llegó el momento de crear la base de datos de nuevo,

# create tablespace dbname location ''/SAN/dbname'';
# create database dbname tablespace dbname;
# alter database dbname set temp_tablespaces = dbname;
% gunzip dumpfile.gz # to evaluate restore time without a piped uncompression
% pg_restore -d dbname dumpfile # into a new, empty database defined above

Me sentí poco iluminado: la restauración tardó 12 horas en crear la base de datos que es solo una fracción de lo que será:

# select pg_size_pretty(pg_database_size(''dbname''));
47 GB

Debido a que hay predicciones de que esta base de datos será de unos pocos terabytes, ahora necesito mejorar el rendimiento.

Por favor iluminame.

Answer 1

Mejora el volcado y restauración de pg

PG_DUMP | siempre use el directorio de formateo con la opción -j

time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external

PG_RESTORE | use siempre la afinación para postgres.conf con el directorio de formato con la opción -j

work_mem = 32MB
shared_buffers = 4GB
maintenance_work_mem = 2GB
full_page_writes = off
autovacuum = off
wal_buffers = -1
time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/`

Para más información

https://github.com/YanarAssaf/PostgreSQL/wiki/Improve-pg-dump%7Crestore

Answer 2

Además de las otras sugerencias, no olvide ajustar su configuración, incluidos los cambios en maintenance_work_mem y checkpoint_segments .

Consulte esta página para obtener sugerencias de rendimiento para la inserción masiva de datos en PostgreSQL.

Answer 3

Como puede haber adivinado simplemente por el hecho de que la compresión de los resultados de la copia de seguridad en un rendimiento más rápido, su copia de seguridad está vinculada a E / S. Esto no debería ser una sorpresa, ya que las copias de seguridad casi siempre van a estar vinculadas a E / S. Al comprimir los datos, se intercambia la carga de E / S para la carga de la CPU y, dado que la mayoría de las CPU están inactivas durante las transferencias de datos de monstruos, la compresión sale como una ganancia neta.

Por lo tanto, para acelerar los tiempos de copia de seguridad / restauración, necesita una E / S más rápida. Más allá de reorganizar la base de datos para que no sea una gran instancia única, eso es todo lo que puedes hacer.

Answer 4

Dos temas / ideas:

1. Al especificar -Fc, la salida pg_dump ya está comprimida. La compresión no es máxima, por lo que puede ahorrar algo de espacio al usar "gzip -9", pero apostaría que no es suficiente para garantizar el tiempo adicional (y E / S) utilizado para comprimir y descomprimir la versión -Fc de la copia de seguridad .

2. Si está utilizando PostgreSQL 8.4.x, puede acelerar la restauración desde una copia de seguridad de .Fc con la nueva opción de línea de comando pg_restore "-jn" donde n = número de conexiones paralelas que se usarán para la restauración. Esto permitirá que pg_restore cargue más de los datos de una tabla o genere más de un índice al mismo tiempo.

Answer 5

Primero compruebe que obtiene un rendimiento IO razonable de la configuración de su disco. Luego verifique que la instalación de PostgreSQL esté ajustada adecuadamente. En particular, shared_buffers debería estar configurado correctamente, maintenance_work_mem debería aumentarse durante la restauración, full_page_writes debería estar desactivado durante la restauración, wal_buffers debería aumentarse a 16 MB durante la restauración, checkpoint_segments debería aumentarse a algo así como 16 durante la restauración, no debería tiene un inicio de sesión irrazonable (como el registro de cada instrucción ejecutada), auto_vacuum debe deshabilitarse durante la restauración.

Si estás en 8.4 también experimentas con restauración paralela, la opción --jobs para pg_restore.

Answer 6

Supongo que necesita una copia de seguridad, no una actualización importante de la base de datos.

Para la copia de seguridad de bases de datos grandes, debe configurar el archivado continuo en lugar de pg_dump .

1. Configurar el archivo WAL .

2. Haga sus copias de seguridad básicas, por ejemplo, todos los días mediante el uso de
   psql template1 -c "select pg_start_backup('' ` date +% F-% T``'') " rsync -a --delete / var / lib / pgsql / data / / var / backups / pgsql / base / psql template1 -c "select pg_stop_backup ()" `

Una restauración sería tan simple como restaurar la base de datos y los registros WAL no anteriores a pg_start_backup time desde la ubicación de la copia de seguridad e iniciar Postgres. Y será mucho más rápido.

Answer 7

zcat dumpfile.gz | pg_restore -d db_name

Elimina la escritura completa de los datos descomprimidos en el disco, que actualmente es su cuello de botella.