Cómo alterar, renombrar, eliminar y copiar bases de datos en PostgreSQL

Este post examina algunos de los comandos disponibles para gestionar las bases de datos PostgreSQL desde el prompt PSQL. Revisaremos los métodos para alterar, renombrar, eliminar y copiar una base de datos.

Recapitulemos algunos comandos básicos de PostgreSQL:

1. Sintaxis de creación de bases de datos:

CREATE DATABASE name user_name ] template ] encoding ] lc_collate ] lc_ctype ] tablespace ] connlimit ] ]

2. Para obtener una lista de bases de datos en el cluster actual:

a. \l+

b. select * from pg_database ;

Sentencias ALTER database

Las sentencias ALTER database se pueden utilizar para cambiar los atributos existentes de la base de datos de destino.

Cambiar el propietario de la base de datos

Para alterar el propietario de la base de datos, el usuario conectado debe ser el propietario de la base de datos y también ser un miembro directo o indirecto del nuevo rol propietario, y el usuario conectado debe tener el privilegio CREATEDB.

Sintaxis

ALTER DATABASE <database_name> OWNER TO <new_owner>;

Ejemplo

ALTER DATABASE test OWNER TO user;

Configuración de la base de datos de plantillas

Por defecto, sólo tenemos dos bases de datos de plantillas presentes en un cluster-template0 y template1. Otras bases de datos no se puede utilizar como una plantilla por defecto. En caso de que necesite crear una plantilla de base de datos personalizada para su entorno, puede utilizar esta opción.

Sintaxis

ALTER DATABASE <database_name> WITH IS_TEMPLATE = true;

Ejemplo

ALTER DATABASE test WITH IS_TEMPLATE = true;

Restringir nuevas conexiones para una base de datos específica

Esta opción es muy útil para mantener la base de datos. Puede restringir las nuevas conexiones a una base de datos específica durante la ventana de mantenimiento de la base de datos. Para habilitar esta opción, ningún usuario se conecta a la base de datos de destino.

Sintaxis

ALTER DATABASE <database_name> WITH ALLOW_CONNECTIONS = false;

Ejemplo

ALTER DATABASE test2 WITH ALLOW_CONNECTIONS = false;

Restringir el límite de conexiones para una base de datos específica.

Para la seguridad y el rendimiento óptimo, puede limitar el número de conexiones que pueden conectarse a la base de datos de destino. Por defecto, el valor de este parámetro es ilimitado, es decir, -1, para todas las bases de datos.

Sintaxis

ALTER DATABASE <database_name> WITH CONNECTION LIMIT <max_number_of_connections>;

Ejemplo

ALTER DATABASE test2 WITH CONNECTION LIMIT 10;

Cambiar el tablespace por defecto de la base de datos

Puede mover físicamente los objetos del tablespace por defecto, como las tablas y los índices, a un nuevo tablespace personalizado. El nuevo tablespace por defecto debe estar vacío para esta base de datos, y nadie puede estar conectado a la base de datos durante esta actividad.

Nota: El propietario de la base de datos o un superusuario debe hacer este cambio, y el usuario debe tener privilegios CREATE para el nuevo tablespace.

Sintaxis

ALTER DATABASE <database_name> SET TABLESPACE <custom_tablespace_name>;

Ejemplo

ALTER DATABASE test SET TABLESPACE custom_tblspc;

Sentencias RENAME de la base de datos

Se puede cambiar el nombre de la base de datos actual utilizando RENAME. Sin embargo, no se puede cambiar el nombre de la base de datos mientras se está accediendo a ella por otras conexiones.

Nota: El propietario de la base de datos o el superusuario debe hacer este cambio, y el usuario debe tener privilegios CREATEDB.

Sintafonía

ALTER DATABASE <old_database_name> RENAME TO <new_database_name>;

Ejemplo

ALTER DATABASE test RENAME TO test123;

DROP Database statements

Puede DROP la base de datos de destino. Esto elimina las entradas del catálogo para la base de datos y borra el directorio que contiene los datos. Sólo puede ser ejecutado por el propietario de la base de datos. Además, no se puede ejecutar mientras usted o cualquier otra persona esté conectada a la base de datos de destino.

Nota: DROP DATABASE no se puede deshacer.

Sintaxis

DROP DATABASE <database_name>;

Ejemplo

DROP DATABASE test2;