postgresql
Copia de seguridad y restaurar
Buscar..
Observaciones
Copia de seguridad del sistema de archivos en lugar de usar pg_dumpall y pg_dump
Es muy importante que si usa esto, llame a la función pg_start_backup() antes y a la función pg_stop_backup() después. Hacer copias de seguridad del sistema de archivos no es seguro de lo contrario; incluso una instantánea de ZFS o FreeBSD del sistema de archivos respaldado sin esas llamadas de función colocará la base de datos en modo de recuperación y puede perder transacciones.
Evitaría hacer copias de seguridad del sistema de archivos en lugar de copias de seguridad regulares de Postgres, tanto por este motivo como porque los archivos de copia de seguridad de Postgres (especialmente en el formato personalizado) son extremadamente versátiles para admitir restauraciones alternativas. Ya que son archivos únicos, también son menos complicados de administrar.
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.)
