postgresql
Copia de seguridad y restaurar

Observaciones

Copia de seguridad de una base de datos

pg_dump -Fc -f DATABASE.pgsql DATABASE

-Fc selecciona el "formato de copia de seguridad personalizado" que le da más poder que el SQL sin formato; ver pg_restore para más detalles. Si desea un archivo SQL de vainilla, puede hacer esto en su lugar:

pg_dump -f DATABASE.sql DATABASE

o incluso

pg_dump DATABASE > DATABASE.sql

Restaurando copias de seguridad

psql < backup.sql

Una alternativa más segura usa -1 para envolver la restauración en una transacción. La -f especifica el nombre de archivo en lugar de usar la redirección de shell.

psql -1f backup.sql

Los archivos de formato personalizado deben restaurarse utilizando pg_restore con la opción -d para especificar la base de datos:

pg_restore -d DATABASE DATABASE.pgsql

El formato personalizado también se puede convertir de nuevo a SQL:

pg_restore backup.pgsql > backup.sql

Se recomienda el uso del formato personalizado porque puede elegir qué cosas restaurar y, opcionalmente, habilitar el procesamiento paralelo.

Es posible que deba realizar un pg_dump seguido de un pg_restore si actualiza de una versión postgresql a una más nueva.

Copia de seguridad de todo el grupo

$ pg_dumpall -f backup.sql

Esto funciona entre bastidores al hacer múltiples conexiones al servidor una vez para cada base de datos y ejecutar pg_dump en él.

A veces, puede tener la tentación de configurarlo como un trabajo cron, por lo que desea ver la fecha en que se tomó la copia de seguridad como parte del nombre de archivo:

$ postgres-backup-$(date +%Y-%m-%d).sql

Sin embargo, tenga en cuenta que esto podría producir archivos grandes diariamente. Postgresql tiene un mecanismo mucho mejor para copias de seguridad regulares: archivos WAL

La salida de pg_dumpall es suficiente para restaurar una instancia de Postgres configurada de forma idéntica, pero los archivos de configuración en $PGDATA ( pg_hba.conf y postgresql.conf ) no forman parte de la copia de seguridad, por lo que tendrá que hacer una copia de seguridad por separado.

postgres=# SELECT pg_start_backup('my-backup');
postgres=# SELECT pg_stop_backup();

Para realizar una copia de seguridad del sistema de archivos, debe usar estas funciones para asegurarse de que Postgres se encuentre en un estado constante mientras se prepara la copia de seguridad.

Usando Copiar para importar

Para copiar datos de un archivo CSV a una tabla

COPY <tablename> FROM '<filename with path>';

Para insertar en el user tabla desde un archivo llamado user_data.csv ubicado dentro de /home/user/ :

COPY user FROM '/home/user/user_data.csv';

Para copiar datos de un archivo separado por tuberías a la tabla

COPY user FROM '/home/user/user_data' WITH DELIMITER '|';

Nota: en ausencia de la opción with delimiter , el delimitador predeterminado es una coma ,

Para ignorar la línea del encabezado al importar el archivo

Utilice la opción de encabezado:

COPY user FROM '/home/user/user_data' WITH DELIMITER '|' HEADER;

Nota: Si se citan datos, los caracteres de comillas de datos predeterminados son comillas dobles. Si los datos se citan utilizando cualquier otro carácter, use la opción QUOTE ; sin embargo, esta opción solo está permitida cuando se utiliza el formato CSV.

Usando Copiar para exportar

Para copiar la tabla al estándar o / p

COPY <tablename> A STDOUT (DELIMITER '|');

Para exportar el usuario de la tabla a la salida estándar:

COPY user TO STUTUT (DELIMITER '|');

Para copiar tabla a archivo

COPIAR usuario DESDE '/ home / user / user_data' WITH DELIMITER '|';

Para copiar la salida de la sentencia SQL al archivo

COPY (sentencia sql) TO '<nombre de archivo con ruta>';

COPIAR (SELECCIONAR * DEL usuario WHERE nombre_usuario COMO "A%") A '/ home / user / user_data';

Para copiar en un archivo comprimido

COPY user TO PROGRAM 'gzip> /home/user/user_data.gz';

Aquí se ejecuta el programa gzip para comprimir los datos de la tabla de usuario.

Usando psql para exportar datos

Los datos se pueden exportar utilizando el comando de copia o utilizando las opciones de la línea de comandos del comando psql.

Para exportar datos csv del usuario de la tabla al archivo csv:

psql -p \<port> -U \<username> -d \<database> -A -F<delimiter> -c\<sql to execute> \> \<output filename with path>

psql -p 5432 -U postgres -d test_database -A -F, -c "select * from user" > /home/user/user_data.csv

Aquí la combinación de -A y -F hace el truco.

-F es especificar delimitador

-A or --no-align

Cambia al modo de salida no alineado. (De lo contrario, el modo de salida predeterminado está alineado.)