Hola a todos, hoy os mostraremos como migrar y configurar una base de datos PostgreSQL con tablespace personalizado.
En este documento explicamos cómo realizar la migración de un esquema de base de datos en PostgreSQL hacia un nuevo entorno, asegurando que todos sus datos se almacenen físicamente en una ruta y disco específicos utilizando un Tablespace.
- Base de datos de origen: Contiene el esquema que deseamos migrar (por ejemplo, db_matrix).
- Base de datos de destino: La nueva base de datos donde importaremos la información.
- Tablespace (tbs_iris_trinity_data): Es la regla en PostgreSQL que le indica al motor en qué carpeta o disco del servidor debe guardar los archivos físicos de la base de datos (en nuestro caso, /data/TABLESPACES).
- Formato de respaldo (.dump vs .sql): Utilizaremos el formato personalizado (.dump), ya que es más rápido, seguro y permite restaurar con mayor control.
Paso 1: Preparar el Servidor y la Carpeta de Destino
Antes de tocar la base de datos, debemos asegurarnos de que el servidor Linux tenga la carpeta lista y con los permisos correctos. De lo contrario, PostgreSQL rechazará la creación del almacenamiento.
- Abre la terminal del servidor destino con usuario administrador (root o sudo).
- Crea el directorio para el almacenamiento.
- Otorga la propiedad del directorio al usuario de PostgreSQL (postgres).
- Asigna los permisos restringidos requeridos por seguridad.
[root@server_trinity data]$ mkdir -p /data/TABLESPACES
[root@server_trinity data]$ chown -R postgres:postgres /data/TABLESPACES
[root@server_trinity data]$ chmod 700 /data/TABLESPACES
[root@server_trinity data]$ ls -l /data/TABLESPACES
total 0
[root@server_trinity data]$
Paso 2: Crear el Tablespace y la Nueva Base de Datos
Conectado a la consola de PostgreSQL (psql) con un usuario administrador (postgres), ejecuta los siguientes comandos:
- Crear el Tablespace.
- Crear el usuario en PostgreSQL en caso de que no exista.
- Asignar permisos al usuario de la aplicación.
- Crear la nueva base de datos configurada para usar este Tablespace por defecto.
[postgres@server_trinity TABLESPACES]$ psql
psql (16.12)
Digite «help» para obtener ayuda.
postgres=#
postgres=# CREATE USER user_trinity WITH PASSWORD 'user_trinity4321';
CREATE ROLE
postgres=# GRANT CREATE ON TABLESPACE tbs_iris_trinity_data TO user_trinity;
GRANT
postgres=# CREATE DATABASE db_trinity
WITH TABLESPACE = tbs_iris_trinity_data
OWNER = user_trinity;
CREATE DATABASE
postgres=#Nota: Al asociar el Tablespace a la base de datos desde su creación, cualquier tabla o índice nuevo que se cree dentro de ella irá automáticamente al PATH /data/TABLESPACES.
Paso 3: Exportar el Esquema (Dump)
Para evitar que la copia mantenga configuraciones antiguas de almacenamiento y forzar a que todo caiga en el nuevo Tablespace, usamos el parámetro –no-tablespaces.
- Moveremos el dump de Origen a Destino
- Otorgaremos permiso para que el dump pueda hacer el import
ORIGEN:
[postgres@server_matrix data]$ pg_dump -U user_matrix -d db_matrix -n public --no-tablespaces -F c -f dump_trinity_schema.dump
[postgres@server_matrix data]$
[postgres@server_matrix data]$ ls -l dump_trinity_schema.dump
-rw-r--r--. 1 postgres postgres 1537297 sep 8 10:57 dump_trinity_schema.dump
[postgres@server_matrix data]$
[postgres@server_matrix data]$ scp dump_trinity_schema.dump root@server_trinity:/BACKUPS
root@server_trinity 's password:
dump_trinity_schema.dump 100% 1501KB 106.3MB/s 00:00
[postgres@server_matrix data]$
DESTINO:
[root@server_trinity ~]# cd /BACKUPS
[root@server_trinity BACKUPS]# ls -l
-rw-r--r--. 1 root root 1537297 sep 8 11:00 dump_trinity_schema.dump
[root@server_trinity BACKUPS]# chown postgres:postgres /BACKUPS/dump_trinity_schema.dump
[root@server_trinity BACKUPS]#Explicación del comando:
-U usuario: Tu usuario de base de datos.
-h host_origen: Servidor donde está la base de datos actual.
-d db_matrix: Nombre de la base de datos origen.
-n public: Nombre del esquema específico que queremos copiar.
–no-tablespaces: Paso clave. Remueve las referencias de almacenamiento anteriores.
-F c: Formato comprimido/customizado.
-f dump_trinity_schema.dump: Nombre del archivo que contendrá el respaldo.
Paso 4: Importar el Esquema en la Nueva Base de Datos
Una vez transferido el archivo dump_trinity_schema.dump al servidor destino (en la ruta /BACKUPS/), procedemos a ejecutar la importación.
Para realizar una restauración totalmente limpia, sin advertencias de permisos antiguos o esquemas duplicados, utilizaremos los siguientes parámetros optimizados:
[postgres@server_trinity ~]$ pg_restore -U postgres -d db_trinity --no-tablespaces --no-owner --no-acl --clean --if-exists /BACKUPS/dump_trinity_schema.dump
[postgres@server_trinity ~]$ psql
psql (16.12)
Digite «help» para obtener ayuda.
postgres=# \l db_trinity
Listado de base de datos
Nombre | Dueño | Codificación | Proveedor de locale | Collate | Ctype | configuración ICU | Reglas ICU: | Privilegios
-------------+----------+--------------+---------------------+-------------+-------------+-------------------+-------------+-------------
db_trinity | user_trinity | UTF8 | libc | es_ES.UTF-8 | es_ES.UTF-8 | | |
(1 fila)
postgres=#Explicación de los parámetros utilizados:
- -U postgres: Usuario superusuario con el que realizamos la importación.
- -d db_trinity: Nombre de la nueva base de datos de destino.
- –no-tablespaces: Forzamos a que todas las tablas e índices se adapten al Tablespace predeterminado de la nueva base de datos (tbs_iris_trinity_data).
- –no-owner: Ignora los propietarios originales del respaldo y le asigna la propiedad de los objetos al dueño configurado en la nueva base de datos.
- –no-acl: Omite la restauración de permisos antiguos (GRANT/REVOKE) para evitar fallos por usuarios de solo lectura que no existan en el nuevo servidor.
- –clean: Elimina (DROP) los objetos preexistentes en la base de datos antes de crearlos nuevamente.
- –if-exists: Evita mensajes de error durante la limpieza previa si los objetos aún no existen.
Paso 5: Verificación Final
Para confirmar que las tablas se están guardando en la carpeta correcta (/data/TABLESPACES), conéctate a la nueva base de datos y ejecuta la siguiente consulta:
[postgres@server_trinity ~]$ psql
psql (16.12)
Digite «help» para obtener ayuda.
postgres=# \c db_trinity
Ahora está conectado a la base de datos «db_trinity» con el usuario «postgres».
db_trinity=# SELECT tablename, pg_tablespace_location(16451) AS ubicacion_tablespace
FROM pg_tables limit 5;
tablename | ubicacion_tablespace
----------------------------+----------------------
Clientes | /data/TABLESPACES
Empleados | /data/TABLESPACES
Empresas | /data/TABLESPACES
País | /data/TABLESPACES
Provincia | /data/TABLESPACES
(5 filas)
db_trinity=# \q
[postgres@server_trinity ~]$Nota: La función pg_tablespace_location(16451) devuelve directamente la ruta absoluta del almacenamiento físico configurado en PostgreSQL. Al obtener /data/TABLESPACES como resultado para cada una de las tablas, se confirma de manera definitiva que la totalidad del esquema, sus objetos y datos asociados se han guardado exitosamente en el disco y carpeta destino especificados.
En esta entrada hemos visto cómo migrar y configurar una base de datos PostgreSQL con Tablespace personalizado. Sin embargo, aunque lo mostramos paso a paso, requiere ciertos conocimientos que solo un DBA experto puede tener. Si tienes un caso parecido, o quieres que nos encarguemos nosotros, contáctanos sin compromiso. Puede ver más detalles sobre nuestro servicio de Soporte y mantenimiento PostgreSQL aquí.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn