Cómo trabajar con bases de datos PostgreSQL

Tabla de contenido

Listado de bases de datos
Volcar una base de datos
Volcar todas las bases de datos
Subvenciones de volcado
Eliminar o descartar una base de datos
Eliminar una subvención
Restaurar una base de datos
Restaurar concesión

PostgreSQL es un excelente motor de base de datos alternativo a MySQL o MariaDB, pero administrar esas bases de datos es muy diferente, especialmente para aquellos administradores de sistemas que están muy acostumbrados a usar MySQL desde la línea de comandos o PHPMyAdmin. Muchos CMS y aplicaciones ya saben cómo interactuar con las bases de datos de PostgreSQL desde el primer momento, pero al igual que MySQL, aún dependen de usted para mantener esas bases de datos.

Para este artículo, supondremos que está conectado a SSH en su servidor como usuario ‘raíz’ y ha instalado el servidor PostgreSQL con la configuración predeterminada, que requiere conectarse como usuario ‘postgres’. Por lo tanto, la mayoría de los comandos también tomarán la opción -U postgres. Estos comandos serán generalmente idénticos entre CentOS y Ubuntu en la mayoría de las versiones modernas, pero se probaron principalmente en un servidor CentOS 7 VPS y un servidor Ubuntu 16.04 que ejecuta PostgreSQL 9.2.

La creación de bases de datos y subvenciones es bastante compleja, pero si usa cPanel, Plesk u otro panel de control que admita la integración de PostgreSQL, configurarlo es sencillo. La creación de estas bases de datos y concesiones desde la línea de comandos está fuera del alcance de este artículo en particular, por lo que también supondremos que ya tiene sus bases de datos ejecutándose y en uso.

Listar bases de datos en PostgreSQL

Es útil conocer los nombres exactos de las bases de datos en las que trabajará, así como hacer una doble verificación para asegurarse de que está trabajando en el servidor correcto. Enumeremos primero las bases de datos PostgreSQL en nuestra máquina:
psql -l -U postgres

El resultado de este comando es una tabla de nombres de bases de datos, propietarios y privilegios de acceso. Usaremos los datos de la primera columna cuando describamos los nombres de las bases de datos para futuros comandos. Para este artículo, usaremos una base de datos llamada “ nombre de la base de datos ”.

Si solo necesita los nombres de las bases de datos y ninguna otra información, puede eliminar el exceso de información realizando una consulta exacta de los nombres de las bases de datos:
psql -U postgres -tA -c 'select datname from pg_database;'

Este comando ejecuta un comando (descrito por el -C bandera) de seleccionar la nombre de fecha columna de la base_de_datos_pg tabla mundial. los -t marca enumera solo tuplas (filas de resultados) y oculta el exceso de descriptores como el encabezado y el recuento de resultados, y el -A flag eliminará la alineación, imprimiendo una base de datos por línea sin caracteres delante o detrás de los nombres. La salida de este comando es particularmente útil para crear bucles para volcados de bases de datos.

Volcar una base de datos

El comando de volcado tomará una copia completa de una base de datos, incluidas todas las tablas, esquemas y datos, y la almacenará en un solo archivo comprimido:
pg_dump -c -Fc -U postgres database_name > database_name.psql

Hay algunas banderas importantes para este comando. -C o -limpio dice pg_dump para agregar sentencias DROP a la salida, y -Fc o –formato=personalizado comprime y organiza la salida para ser utilizada con pg_restaurar mas tarde. El formato personalizado tiene la mayor flexibilidad y también almacena ventajosamente la copia de seguridad en un solo archivo.

Volcar todas las bases de datos

Probablemente sería mejor sacar cada una de las bases de datos en su propio archivo ejecutando el comando anterior varias veces. Haremos referencia a nuestro segundo comando de listado, eliminando algunas bases de datos innecesarias, para hacer nuestros volcados individuales.
for db in $(psql -U postgres -tA -c 'select datname from pg_database;' | grep -E -v “^(template1|template0|postgres)$”); do
echo $db
pg_dump -c -Fc -U postgres $db > $db.psql
done

Este bucle for pasará una lista de todas las bases de datos, además de las plantillas predeterminadas y la base de datos de postgres, al pg_dump comando para hacer un .psql archivo para cada uno en el directorio actual.

Nota: También es posible realizar un volcado de todas las bases de datos en un solo archivo, aunque con grandes conjuntos de datos, esto puede volverse difícil de manejar y difícil de restaurar. Por lo tanto, no recomiendo este método, aunque es menos complejo y puede funcionar en algunas situaciones.

pg_dumpall --clean -U postgres > pg.all.psql

Subvenciones de volcado

Las concesiones se almacenan en el esquema de PostgreSQL. Puede volcar el esquema, obteniendo así todas las concesiones disponibles para el sistema, usando el comando pg_dump:
pg_dumpall -U postgres -s > pg.schema.psql

Esto volcará no solo las concesiones, sino también el resto del esquema del servidor, incluidas las declaraciones de creación de bases de datos y tablas, la propiedad y la membresía de roles, y la asignación de ACL, básicamente, todo excepto los datos.

Si solo necesita usuarios (roles) y concesiones, puede recopilarlos usando este comando:
pg_dumpall -U postgres -s | egrep -e '^(REVOKE|GRANT)' -e '^(CREATE|ALTER) ROLE' > pg.grants.psql

Esto aprovecha el volcado de esquema completo, pero solo toma las declaraciones de creación de roles y las declaraciones de concesión/revocación para ensamblar usuarios y concesiones.

Eliminar o descartar una base de datos

Para destruir una base de datos, la sintaxis es muy sencilla. ¡Tenga cuidado al escribir este comando, ya que no le pide que lo confirme!
psql -U postgres -c ‘drop database database_name’

Si la base de datos existe, ya no existirá. También hay un contenedor de línea de comando para esta misma tarea:
dropdb -U postgres database_name

Eliminar una subvención

Las concesiones se eliminan de los usuarios al revocarlas. Primero es importante saber qué permisos tiene un usuario específico, así que vamos a buscarlos volcando el esquema y encontrando líneas relacionadas con nuestro usuario.
pg_dumpall -U postgres -s | egrep -e '^(REVOKE|GRANT)' | grep database_user

En mi caso, hay una subvención que se ve así:
GRANT ALL ON DATABASE database_name TO database_user;

Para combatir esto, revocaremos el mismo descriptor:
psql -U postgres -c ‘revoke all on database database_name from database_user;’

Nota: tenga en cuenta los pequeños cambios, GRANT se ha cambiado a REVOKE y TO se ha cambiado a FROM. Este comando está entre comillas simples y se pasa a psql con el indicador -c para ejecutar el comando en PostgreSQL. Esto reemplazará la instrucción GRANT original con REVOKE en el esquema. Ejecutar el primer comando en esta sección nuevamente ahora muestra solo la línea que acabamos de pasar:

REVOKE ALL ON DATABASE database_name FROM database_user;

Restaurar una base de datos

Para las bases de datos que se volcaron con el método pg_dump utilizado en este artículo, podemos restaurar toda la base de datos con este comando:
pg_restore -U postgres -c -C -O -d database_name database_name.psql

¡Se necesitan bastantes banderas más aquí! -C o -limpio , como antes, elimina los objetos de la base de datos antes de escribir desde el archivo en PostgreSQL. Agregamos esto en caso de que no se haya utilizado para el comando de volcado. -C o -crear , creará la base de datos nombre_de_la_base de datos durante la restauración si no existe. Si la base de datos ya existe, el -C la bandera lo destruirá antes -C lo recrea. A continuación, hay -O o -sin dueño , que elimina al propietario de la base de datos. Esto permite cambiar una copia de seguridad de cualquier fuente al propietario del usuario que realiza la restauración, que en nuestro caso es postgres. -D nombre de la base de datos describe el nombre de la base de datos a la que restaurará. Este puede ser un nombre diferente al que se creó la copia de seguridad, si es necesario. El argumento final es el nombre de nuestro archivo de copia de seguridad, nombre_base_datos.psql .

Restaurar una subvención

Dado que todas las concesiones se almacenan en nuestro archivo pg.grants.psql, debemos seleccionar solo el usuario que necesitamos restaurar. En nuestro caso, restauraremos database_user:
grep database_user pg.grants.psql | psql -U postgres

Dado que el archivo pg.grants.psql tiene funciones y concesiones, este comando recreará al usuario con sus privilegios y contraseña originales, y luego le otorgará acceso a las bases de datos y esquemas requeridos.

Related Posts