Copias de seguridad y restauración en PostgreSQL

En primer lugar hay que cambiar al usuario postgres y otro con los permisos adecuados para la base de datos que queramos manejar. En este caso el usuario alberto tiene permisos totales sobre la base de datos costtes01.

Para hacer un backup de la base de datos.

$ su postgres
postgres@debian:/$ pg_dump [conexión] [opciones] basededatos > ficherobackup

Cuando hacemos la copia con el usuario postgres debemos hacer la copia en el directorio temporal /tmp, después como usuario postres le cambiamos el propietario y finalmente con nuestro propio usuario la movemos a nuestra carpeta de copias de seguridad.

Ejemplo donde el usuario actual ya tiene privilegios en la base de datos.

$ pg_dump -v -F custom -d costtes01 -f costtes02-20150717-1.backup

Las opciones de conexión pueden ser estas:

-h, --host=hostname ► Indica el nombre de la máquina donde corre el servidor Postgresql.
-p, --port=port ► Indica el puerto. Por defecto es 5432.
-U, --username=username ► Indica el usuario con el que se conectará. Como ya hemos abierto sesión en consola con el usuario postgres, no es necesario volver a introducirlo.
-W, --password ► Fuerza a pg_dump a solicitar la contraseña del usuario.
-w, --no-password ► No solicita la contraseña de usuario. Se suele poner cuando la contraseña ya se envía por otros medios, como en este caso, donde ya se ha iniciado sesión con el usuario postgres.

También tendremos las siguientes opciones.

-v, --verbose ► Habilita la salida detallada en pantalla.
-a, --data-only ► Hace un backup solo de los datos y no del esquema.
-c, --clean ► Incluye en el backup instrucciones para eliminar los elementos antes de restaurarlos. De esta forma se evitan los errores de que los elementos ya existen cuando se restauran.
-C, --create ► Incluye las instrucciones para crear la base de datos. Esto es útil si pretendemos restaurar y crear la base de datos al mismo tiempo.
-f archivo, --file=archivo ► Escribe la salida en el archivo especificado, si no se indica nada se envía el volcado a la salida estándar.
-F formato, --format=formato ► Especifica el formato de salida. los tipos de formato pueden:
  p, plain = Texto plano.
  c, custom = Formato personalizado. En este tipo de formato siempre se hace compresión.
  t, tar = Crea una salida en formato tar.
-n nombre-esquema, --schema=nombre-esquema ► Realiza la copia unicamente de los esquemas indicados junto con todos los objetos que contengan. Si no se indica nada, se hace un dump de todos los esquemas que no sean del sistema. Para incluir múltiples esquemas se pueden añadir tantas opciones -n como sean necesarias.
-N nombre-esquema, --exclude-schema=nombre-esquema ► Omite los esquemas indicados a la hora de ralizar el backup. Si se desea excluir múltiples esquemas, se pueden añadir tantas opciones -N como sean necesarias.
-s, --schema-only ► Hace el backup solo del esquema, no incluye los datos.
-t nombre-tabla, --table=nombre-tabla ► Hace un backup solo de la tabla indicada. Si se desean incluir múltiples tablas, se pueden añadir tantas opciones -t como sean necesarias. Hay que tener en cuenta que pg_dump no tiene en cuenta las dependencias, por tanto se ha de prestar atención en incluir todas las tablas necesarias y que tengan relación con llaves primarias o foráneas y así garantizar que la posterior restauración se hace exitósamente.
-T nombre-tabla, --exclude-table=nombre-tabla ► Excluye del backup las tablas indicadas. Se pueden añadir tantas opciones -T como sean necesarias.
--inserts ► Utilizará inserts en lugar de copy en las instrucciones SQL.
--port=puerto ► Especifica el puerto. Por defecto es 5432.

Para restaurar una base de datos usando pg_restore

Atención. pg_restore solo debe usarse con los backups creados con pg_dump usando la opción de formato personalizable -F c

pg_restore opciones archivo

Opciones generales:

-d, --dbname=nombre ► Nombre de la base de datos a la que conectarse.
-f, --file=archivo ► Nombre del archivo de salida.
-F, --format=c|d|t ► Formato del volcado (debería ser automático).
-l, --list ► Imprime una tabla resumida de contenidos del archivador.
-v, --verbose ► Activa el modo verbose.
--help ► Muestra esta ayuda y termina.
--version ► Muestra información de la versión y termina.

Opciones que controlan la recuperación:

-a, --data-only ► Restablece solo los datos, no el esquema.
-c, --clean ► Limpia (drop) la base de datos antes de recrearla.
-C, --create ► Crea la base de datos de destino.
-e, --exit-on-error ► Abandonar al encontrar un error por omisión, se continúa la restauración.
-e, --exit-on-error ► Abandonar al encontrar un error por omisión, se continúa la restauración.
-l, --index=nombre ► Restablece el índice nombrado.
-j, --jobs=num ► Máximo de procesos paralelos para restaurar.
-L, --use-list=archivo ► Usa la tabla de contenido especificada para ordenar la salida de este archivo.
-n, --schema=name ► Restablece solo los objetos en este esquema.
-O, --no-owner ► No restablece los propietarios de los objetos.
-P, --function=nombre(args) ► Restablece la función nombrada.
-s, --schema-only ► Restablece el esquema unicamente, no los datos.
-S, --superuser=nombre ► Especifica el nombre del superusuario que se usa para deshabilitar los disparadores (trigers).
-t, --table=nombre ► Restablece la tabla nombrada.
-T, --trigger=nombre ► Restablece el disparador (triger) nombrado.
-x, --no-privileges ► No restablece los privilegios (grant/revoke).
-1, --single-transaction ► Restablece en una única transacción.
--disable-trigers ► Deshabilita los disparadores (trigers) durante el restablecimiento solo de datos.
--no-data-for-failed-tables ► No restablece datos de tablas que no pudieron ser creadas.
--no-secrity-labels ► No restaura etiquetas de seguridad.
--no-tablespaces ► No vuelca asignaciones de tablespace.
use-set-session-authorization ► Usa órdenes SET SESSION AUTHORIZATION en lugar de ALTER OWNER para restablecer propietarios.

Opciones de la conexión:

-h, --host=anfitrion ► Anfitrión de la base de datos o directorio del enchufe (socket).
-p, --port=puerto ► Número del puerto de la base de datos.
-U, --username=usuario ► Nombre del usuario con se conectará a la base de datos.
-w, --no-password ► Nunca pedir una contraseña.
-W, --password ► Fuerza un prompt para la contraseña (debería ser automático).
--role=role-name ► Hace un SET ROLE antes de restaurar.

Si no se especifica un archivo de entrada, se usa la entrada estándar.