Volcado de SQL de Postgresql

25.1.1. Restaurando el vertedero

25.1.2. Usando pg_dumpall

25.1.3. Manejo de grandes bases de datos

La idea detrás de este método de volcado es generar un archivo con comandos SQL que, cuando se devuelvan al servidor, volverán a crear la base de datos en el mismo estado que estaba en el momento del volcado. PostgreSQL proporciona el programa de utilidad pg_dump para este propósito. El uso básico de este comando es:

pg_dump dbname >dumpfile

Como puede ver, pg_dump escribe su resultado en la salida estándar. Veremos a continuación cómo esto puede resultar útil. Mientras que el comando anterior crea un archivo de texto, pg_dump puede crear archivos en otros formatos que permiten el paralelismo y un control más detallado de la restauración de objetos.

pg_dump es una aplicación cliente de PostgreSQL normal (aunque particularmente inteligente). Esto significa que puede realizar este procedimiento de copia de seguridad desde cualquier host remoto que tenga acceso a la base de datos. Pero recuerde que pg_dump no opera con permisos especiales. En particular, debe tener acceso de lectura a todas las tablas de las que desea realizar una copia de seguridad, por lo que para realizar una copia de seguridad de toda la base de datos, casi siempre debe ejecutarla como superusuario de la base de datos. (Si no tiene suficientes privilegios para realizar una copia de seguridad de toda la base de datos, aún puede realizar una copia de seguridad de partes de la base de datos a las que tiene acceso mediante opciones como -n schema o -t table.)

Para especificar con qué servidor de base de datos pg_dump debe contactar, use las opciones de la línea de comando -h host y -p port. El host predeterminado es el host local o cualquiera que sea su PGHOST especifica la variable de entorno. Del mismo modo, el puerto predeterminado está indicado por el PGPORT variable de entorno o, en su defecto, por el valor predeterminado compilado. (Convenientemente, el servidor normalmente tendrá el mismo valor predeterminado compilado).

Como cualquier otra aplicación cliente de PostgreSQL, pg_dump se conectará de forma predeterminada con el nombre de usuario de la base de datos que es igual al nombre de usuario actual del sistema operativo. Para anular esto, especifique el -U opción o establecer la variable de entorno PGUSER. Recuerde que las conexiones pg_dump están sujetas a los mecanismos normales de autenticación del cliente (que se describen en Capítulo 20).

Una ventaja importante de pg_dump sobre los otros métodos de copia de seguridad que se describen más adelante es que la salida de pg_dump generalmente se puede volver a cargar en versiones más nuevas de PostgreSQL, mientras que las copias de seguridad a nivel de archivo y el archivado continuo son extremadamente específicos de la versión del servidor. pg_dump es también el único método que funcionará al transferir una base de datos a una arquitectura de máquina diferente, como pasar de un servidor de 32 bits a uno de 64 bits.

Los volcados creados por pg_dump son internamente consistentes, lo que significa que el volcado representa una instantánea de la base de datos en el momento en que pg_dump comenzó a ejecutarse. pg_dump no bloquea otras operaciones en la base de datos mientras está funcionando. (Las excepciones son aquellas operaciones que necesitan operar con un bloqueo exclusivo, como la mayoría de las formas de ALTER TABLE.)

25.1.1. Restaurando el vertedero

Los archivos de texto creados por pg_dump están destinados a ser leídos por el programa psql. El formulario de comando general para restaurar un volcado es

psql dbname <dumpfile

dónde dumpfile es el archivo generado por el comando pg_dump. La base de datos dbname no será creado por este comando, por lo que debe crearlo usted mismo desde template0 antes de ejecutar psql (por ejemplo, con createdb -T template0 dbname). psql admite opciones similares a pg_dump para especificar el servidor de base de datos al que conectarse y el nombre de usuario que se utilizará. Consulte la página de referencia de psql para obtener más información. Los volcados de archivos que no son de texto se restauran mediante la utilidad pg_restore.

Antes de restaurar un volcado de SQL, todos los usuarios que poseen objetos o que recibieron permisos sobre objetos en la base de datos volcada ya deben existir. Si no es así, la restauración no podrá volver a crear los objetos con la propiedad o los permisos originales. (A veces esto es lo que desea, pero generalmente no lo es).

De forma predeterminada, el script psql continuará ejecutándose después de que se encuentre un error de SQL. Es posible que desee ejecutar psql con el ON_ERROR_STOP variable configurada para alterar ese comportamiento y hacer que psql salga con un estado de salida de 3 si se produce un error de SQL:

psql --set ON_ERROR_STOP=on dbname < dumpfile

De cualquier manera, solo tendrá una base de datos parcialmente restaurada. Alternativamente, puede especificar que todo el volcado debe restaurarse como una sola transacción, por lo que la restauración se completa o se revierte por completo. Este modo se puede especificar pasando el -1 o --single-transaction opciones de línea de comandos para psql. Cuando utilice este modo, tenga en cuenta que incluso un error menor puede revertir una restauración que ya se ha ejecutado durante muchas horas. Sin embargo, eso podría ser preferible a limpiar manualmente una base de datos compleja después de un volcado parcialmente restaurado.

La capacidad de pg_dump y psql para escribir o leer desde tuberías hace posible volcar una base de datos directamente de un servidor a otro, por ejemplo:

pg_dump -h host1 dbname | psql -h host2 dbname

Importante

Los volcados producidos por pg_dump son relativos a template0. Esto significa que cualquier idioma, procedimiento, etc. agregado a través de template1 también será descargado por pg_dump. Como resultado, al restaurar, si está utilizando un template1, debe crear la base de datos vacía desde template0, como en el ejemplo anterior.

Después de restaurar una copia de seguridad, es aconsejable ejecutar ANALYZE en cada base de datos para que el optimizador de consultas tenga estadísticas útiles; consulte la Sección 24.1.3 y la Sección 24.1.6 para obtener más información. Para obtener más consejos sobre cómo cargar grandes cantidades de datos en PostgreSQL de manera eficiente, consulte la Sección 14.4.

25.1.2. Usando pg_dumpall

pg_dump vuelca solo una base de datos a la vez, y no vuelca información sobre roles o espacios de tabla (porque son para todo el clúster en lugar de por base de datos). Para admitir el volcado conveniente de todo el contenido de un clúster de base de datos, se proporciona el programa pg_dumpall. pg_dumpall realiza una copia de seguridad de cada base de datos en un clúster determinado y también conserva los datos de todo el clúster, como las definiciones de roles y espacios de tabla. El uso básico de este comando es:

pg_dumpall >dumpfile

El volcado resultante se puede restaurar con psql:

psql -f dumpfile postgres

(En realidad, puede especificar cualquier nombre de base de datos existente para comenzar, pero si está cargando en un clúster vacío, entonces postgres (normalmente debería usarse). Siempre es necesario tener acceso de superusuario a la base de datos al restaurar un volcado pg_dumpall, ya que es necesario para restaurar la información del rol y del espacio de tabla. Si utiliza espacios de tabla, asegúrese de que las rutas de espacio de tabla en el volcado sean adecuadas para la nueva instalación.

pg_dumpall funciona emitiendo comandos para recrear roles, espacios de tabla y bases de datos vacías, luego invocando pg_dump para cada base de datos. Esto significa que, si bien cada base de datos será coherente internamente, las instantáneas de diferentes bases de datos no están sincronizadas.

Los datos de todo el clúster se pueden volcar solos usando pg_dumpall --globals-only opción. Esto es necesario para realizar una copia de seguridad completa del clúster si se ejecuta el comando pg_dump en bases de datos individuales.

25.1.3. Manejo de grandes bases de datos

Algunos sistemas operativos tienen límites máximos de tamaño de archivo que causan problemas al crear archivos de salida pg_dump de gran tamaño. Afortunadamente, pg_dump puede escribir en la salida estándar, por lo que puede usar herramientas estándar de Unix para solucionar este problema potencial. Hay varios métodos posibles:

Utilice volcados comprimidos. Puede utilizar su programa de compresión favorito, por ejemplo gzip:

pg_dump dbname | gzip > filename.gz

Recargar con:

gunzip -c filename.gz | psql dbname

o:

cat filename.gz | gunzip | psql dbname

Usar split. los split El comando le permite dividir la salida en archivos más pequeños que son aceptables en tamaño para el sistema de archivos subyacente. Por ejemplo, para hacer trozos de 1 megabyte:

pg_dump dbname | split -b 1m - filename

Recargar con:

cat filename*| psql dbname

Utilice el formato de volcado personalizado de pg_dump. Si PostgreSQL se creó en un sistema con la biblioteca de compresión zlib instalada, el formato de volcado personalizado comprimirá los datos a medida que los escribe en el archivo de salida. Esto producirá tamaños de archivo de volcado similares al uso gzip, pero tiene la ventaja adicional de que las tablas se pueden restaurar de forma selectiva. El siguiente comando vuelca una base de datos utilizando el formato de volcado personalizado:

pg_dump -Fc dbname > filename

Un volcado de formato personalizado no es un script para psql, sino que debe restaurarse con pg_restore, por ejemplo:

pg_restore -d dbname filename

Consulte las páginas de referencia pg_dump y pg_restore para obtener más detalles.

Para bases de datos muy grandes, es posible que deba combinar split con uno de los otros dos enfoques.

Utilice la función de volcado paralelo de pg_dump. Para acelerar el volcado de una base de datos grande, puede usar el modo paralelo de pg_dump. Esto volcará varias tablas al mismo tiempo. Puede controlar el grado de paralelismo con el -j parámetro. Los volcados paralelos solo son compatibles con el formato de archivo “directorio”.

pg_dump -j num -F d -f out.dir dbname

Puedes usar pg_restore -j para restaurar un vertedero en paralelo. Esto funcionará para cualquier archivo del modo de archivo “personalizado” o “directorio”, ya sea que se haya creado o no con pg_dump -j.