Documentación de Oracle Cloud Infrastructure

Guía de actualización de versión principal para OCI Database with PostgreSQL

Actualice las versiones principales de OCI Database with PostgreSQL mediante el enfoque pg_dump/pg_restore o pglogical.

OCI Database with PostgreSQL es un servicio totalmente gestionado para ejecutar PostgreSQL en Oracle Cloud, diseñado para reducir la sobrecarga operativa asociada con la gestión de la infraestructura subyacente. Optimiza la administración diaria mediante el aprovisionamiento automatizado, la aplicación de parches soportada, las copias de seguridad automatizadas y las capacidades de supervisión integradas.

Mantener los entornos actualizados de OCI PostgreSQL es importante para aprovechar las últimas mejoras de rendimiento, actualizaciones de seguridad y nuevas funciones.

Las principales versiones de PostgreSQL soportadas son 14, 15, 16 y 17.

Los siguientes enfoques comúnmente adoptados para las actualizaciones de versiones principales ofrecen métodos estructurados y repetibles para migrar datos y realizar la transición de aplicaciones a versiones más recientes.

Consejo

Consulte el video Approaches for Upgrading OCI PostgreSQL Database.

pg_dump y pg_restore

La actualización de OCI PostgreSQL mediante pg_dump y pg_restore es un enfoque sencillo y fiable, especialmente cuando se realizan actualizaciones de versiones principales o se migra entre entornos. Este método garantiza una migración limpia y controlada de objetos y datos de la base de datos. Con un manejo adecuado de roles, paralelismo y validación, este método garantiza un proceso de migración fluido y controlado.

Este documento describe todo el proceso de actualización mediante los comandos exactos.

Notas importantes

  • pg_dump y pg_restore son herramientas de copia de seguridad lógica.
  • Adecuado para actualizaciones de versión principales.
  • Requiere tiempo de inactividad durante el switchover final.
  • Los roles siempre se deben manejar por separado.

Actualizar flujo de trabajo

El proceso de cambio de versión que utiliza pg_dump y pg_restore consta de los siguientes pasos.

  1. Paso 1: Volcar objetos globales (roles)

    Este paso ayuda a garantizar que se conservan todos los roles, permisos y controles de acceso, lo que permite a los usuarios y aplicaciones mantener un acceso coherente después de la actualización.

  2. Paso 2: Volcar la base de datos

    Este paso crea una copia de seguridad lógica completa de la base de datos, lo que proporciona una forma fiable de transferir datos al entorno actualizado.

  3. Paso 3: Modificar el archivo de volcado global

    Este paso nos permite revisar y ajustar el volcado según sea necesario, lo que ayuda a garantizar la compatibilidad con la versión de destino de PostgreSQL y un proceso de restauración más sencillo.

  4. Paso 4: Restauración de objetos globales

    La restauración de roles en primer lugar ayuda a garantizar que los usuarios y permisos adecuados estén en su lugar antes de que se vuelvan a introducir los datos, lo que permite un entorno coherente y seguro.

  5. Paso 5: Restauración de la base de datos

    Este paso lleva los datos al sistema actualizado, completando la transición y haciendo que la base de datos esté lista para su uso en la nueva versión de PostgreSQL.

Paso 1: Volcar objetos globales (roles)

En primer lugar, exporte roles y objetos globales de la base de datos origen (versión anterior):

/usr/lib/postgresql/bin/pg_dumpall -U psql -h <IP_of_Source_DB> -g --no-role-passwords --no-tablespaces -f <all_roles>.sql

  • -g: volca objetos globales, como roles y usuarios

    Esta opción se centra en la extracción de objetos de todo el cluster, como roles, usuarios y sus privilegios asociados, en lugar de datos específicos de la base de datos. La captura de estos elementos por separado ayuda a garantizar que los controles de acceso se puedan volver a crear de forma coherente en el entorno de destino.

  • --no-role-passwords: evita los problemas relacionados con la contraseña

    Esto excluye las definiciones de contraseña de rol del volcado, lo que puede ayudar a simplificar el proceso de restauración, especialmente cuando las políticas de contraseñas o los métodos de autenticación difieren entre los entornos. También proporciona flexibilidad para volver a configurar las credenciales según sea necesario después de la actualización.

  • --no-tablespaces: evita problemas de dependencia de tablespace

    Esto omite las definiciones de tablespace del volcado, lo que ayuda a evitar dependencias en configuraciones de almacenamiento específicas que podrían no existir en el entorno de destino. Esto puede hacer que el proceso de restauración sea más portátil y fácil de adaptar al almacenamiento gestionado por OCI.

  • -f <all_roles>.sql: escribe la salida en un archivo para reutilizarlo

    Esto dirige la salida a un archivo SQL, lo que facilita la revisión, la versión y la reutilización durante la fase de restauración. Tener un archivo independiente para objetos globales también soporta un proceso de actualización más controlado y por pasos.

Paso 2: Volcar la base de datos

Realice un volcado de base de datos con formato de directorio:

/usr/lib/postgresql/bin/pg_dump -v -h < IP_of_Source_DB > -U <username> -d <databasename> -Fd -C -j <Num of parallel jobs> -Z0 -f sampledb_dir_format

  • -Fd: formato de directorio (crea varios archivos)

    La opción -Fd (formato de directorio) se selecciona porque soporta volcados paralelos (-j), lo que mejora el rendimiento de las bases de datos grandes y permite restauraciones más granulares y flexibles de objetos individuales. Este formato almacena el volcado como un directorio que contiene varios archivos en lugar de un único archivo. Soporta el procesamiento paralelo y proporciona una mayor flexibilidad durante la restauración, especialmente para bases de datos más grandes.

    De manera alternativa, se puede utilizar el formato personalizado (-Fc) cuando se prefiere una copia de seguridad de un solo archivo, al tiempo que se admiten capacidades de compresión y restauración selectiva.

  • -C: incluye el comando de creación de base de datos

    Esta opción agrega las sentencias necesarias para crear la base de datos durante la restauración, lo que ayuda a simplificar el proceso de configuración en el entorno de destino.

  • -j : permite trabajos paralelos para un volcado más rápido

    Esto permite que la operación de volcado ejecute varios trabajos simultáneamente, lo que puede reducir significativamente el tiempo general necesario para juegos de datos grandes.

  • -Z0: sin compresión (mejora el rendimiento)

    La desactivación de la compresión puede mejorar el rendimiento del volcado al reducir la sobrecarga de la CPU, lo que puede resultar beneficioso cuando la velocidad es una prioridad y las restricciones de almacenamiento son mínimas.

  • -v: modo detallado para supervisar el progreso

    Esta opción proporciona una salida detallada durante la ejecución, lo que facilita el seguimiento del progreso e identifica cualquier problema en las primeras etapas del proceso de volcado.

Paso 3: Modificar el archivo de volcado global

Antes de restaurar roles:

Modifique el archivo <all_roles>.sql según sea necesario según las instrucciones siguientes.

Elimine lo siguiente del archivo de volcado de roles:

  • Todas las sentencias CREATE, ALTER ROLE,GRANT para los roles que comienzan con oci_*
  • Cualquier atributo de rol no soportado (especialmente en servicios gestionados como OCI PostgreSQL)
  • Todas las sentencias CREATE y ALTER ROLE para el usuario administrador que se creó durante la configuración inicial de la base de datos OCI PostgreSQL, ya que se debe volver a crear al aprovisionar la base de datos de la versión posterior. Si tiene previsto utilizar un nombre de usuario de administrador diferente, asegúrese de que se ha especificado el usuario de administrador adecuado durante la creación de la base de datos PostgreSQL de OCI actualizada.

Además, actualice el archivo de volcado de roles para eliminar o modificar los comandos que requieren privilegios SUPERUSER, ya que estos no están soportados en entornos gestionados.

Por ejemplo:

Original:

ALTER ROLE test WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'test';

Modificado:

ALTER ROLE/USER test WITH CREATEROLE CREATEDB LOGIN PASSWORD 'test';

Paso 4: Restauración de objetos globales

Restaurar roles en el sistema de base de datos de destino (versión posterior):

/usr/lib/postgresql/bin/psql -U <username> -d <databasename> -h <IP_of_Target_Database_System> -f <all_roles>.sql

Este paso garantiza:

  • Se crean todos los usuarios y roles
  • Los permisos están disponibles antes de la restauración de datos

Paso 5: Restauración de la base de datos

Ignore lo siguiente del archivo de volcado toc.dat:

  • Todas las sentencias CREATE, ALTER ROLE y GRANT fallan para los roles cuyos nombres empiezan por oci_*.

Restaure la base de datos en el sistema de la base de datos destino mediante:

/usr/lib/postgresql/bin/pg_restore -v -h <IP_of_Target_Database_System> -U <username> -j <Num of parallel jobs> -C -d <databasename> sampledb_dir_format
  • -C: cree la base de datos, conéctese a ella y realice restauraciones en la base de datos
  • -j: restauración en paralelo para una ejecución más rápida
  • -v: salida detallada para realizar un seguimiento del progreso

Este paso garantiza:

  • Compruebe la salida detallada de los mensajes ERROR: y asegúrese de que el comando se ha completado con un código de salida de shell de 0. Los errores relacionados con los usuarios oci_* se pueden ignorar.
Nota

Para entornos con varias bases de datos, recomendamos ejecutar los comandos anteriores por separado para cada base de datos para garantizar un proceso de cambio de versión completo y coherente.

Consideraciones sobre el rendimiento

  • Utilizar valores -j superiores en función de la CPU disponible
  • Utilizar -Z0 durante el volcado para un rendimiento más rápido
  • Garantizar suficiente capacidad de E/S de disco
  • Supervisar el progreso de restauración mediante logs detallados

Validación posterior a la restauración

Después de completar la restauración, realice las siguientes tareas.

  • Verificar recuentos de filas

    Esta tarea ayuda a confirmar que todos los datos se han migrado correctamente y que no hay discrepancias entre las bases de datos de origen y destino.

  • Comprobar conectividad de aplicación

    Esta tarea garantiza que las aplicaciones puedan conectarse e interactuar con la base de datos actualizada como se esperaba.

  • Validar roles y permisos

    Esta tarea ayuda a confirmar que los usuarios y roles tienen el acceso y los privilegios adecuados en el nuevo entorno.

  • Ejecute el siguiente comando para cada base de datos
    VACUUM ANALYZE <database_name>;

    Este comando ayuda a actualizar las estadísticas internas que utiliza el planificador de consultas, lo que permite a OCI PostgreSQL generar planes de ejecución más eficientes basados en los datos actuales. También puede ayudar a optimizar el rendimiento general después de la restauración, especialmente para bases de datos o cargas de trabajo más grandes con consultas complejas.

pglogico

La extensión pglogical permite la replicación lógica en varios sistemas de base de datos PostgreSQL de Oracle Cloud Infrastructure (OCI). Esta extensión admite la replicación de PostgreSQL entre versiones, lo que la convierte en un enfoque adecuado y sólido para realizar actualizaciones de base de datos con un tiempo de inactividad mínimo. Al activar la replicación lógica entre los sistemas de origen y destino, puede migrar datos entre versiones sin problemas, al tiempo que mantiene la disponibilidad de la aplicación.

pglogical es especialmente útil para las siguientes situaciones:

  • Actualizaciones de versión principales
  • Migraciones entre entornos

pglogical permite una sincronización de datos perfecta entre:

  • Instancias de PostgreSQL en diferentes regiones y dominios de disponibilidad de OCI
  • Bases de datos desplegadas en diferentes redes virtuales en la nube (VCN)
  • Servicios PostgreSQL gestionados en varios proveedores de nube
  • Instancias PostgreSQL autogestionadas (en la nube o locales)

Esta flexibilidad convierte a pglogical en una potente solución para actualizaciones de bases de datos, migraciones e implementaciones híbridas.

Requisitos de conectividad de red

  • Si ambos sistemas de base de datos están dentro de la misma VCN, la conectividad está disponible automáticamente.
  • Si las bases de datos están en diferentes redes virtuales en la nube dentro de la misma región, configure el gateway de intercambio de tráfico local (LPG) para establecer la comunicación.
  • Si las bases de datos están en diferentes redes virtuales en la nube dentro de la región diferente, configure Gateway de rutas dinámicas (DRG) para establecer la comunicación.

Activar extensión pglogical en el sistema de base de datos de origen y destino

Realice los siguientes pasos en las bases de datos origen (versión anterior) y destino (versión posterior):

  1. Conéctese a la consola de Oracle Cloud y navegue al sistema de base de datos OCI PostgreSQL.
  2. Modifique la configuración accediendo al archivo de configuración.
  3. Seleccione Copiar configuración y actualice los parámetros necesarios. En User Variables (lectura/escritura):
    • wal_level = logical
    • track_commit_timestamp = 1
  4. Enable Extension (Activar extensión): en Configure Extensions, seleccione pglogical y cree la configuración.
  5. Vaya al sistema de base de datos, seleccione Editar en Configuración y aplique la configuración creada anteriormente al sistema de base de datos.

    El estado del sistema de base de datos es inicialmente Actualizando. Espere hasta que se convierta en Activo antes de continuar.

    Esta configuración prepara ambos entornos para la replicación lógica al activar la configuración y las extensiones necesarias, lo que permite a pglogical capturar y sincronizar los cambios de forma fiable entre las bases de datos de origen y destino durante el proceso de actualización.

  6. Una vez que el sistema de base de datos esté activo, conéctese a la base de datos y verifique las extensiones activadas mediante la siguiente consulta.
    SHOW oci.admin_enabled_extensions;
  7. Cree la extensión pglogical con el siguiente comando:
    CREATE EXTENSION pglogical;

Configurar Base de Datos Origen

Conéctese como usuario administrador (el usuario especificado durante la creación de la base de datos PostgreSQL de OCI) y otorgue los privilegios necesarios para activar la replicación lógica.

alter role xxx with replication; 
grant EXECUTE on FUNCTION pg_catalog.pg_replication_origin_session_reset() to xxx ; 
grant EXECUTE on FUNCTION pg_catalog.pg_replication_origin_session_setup to xxx ; 
grant all on FUNCTION pg_catalog.pg_replication_origin_session_setup to xxx;

xxx es el usuario de ejemplo creado durante el aprovisionamiento del sistema de base de datos.

Configurar pglogical en el origen (editor)

  1. Cree el nodo de editor en la base de datos de origen.
    SELECT pglogical.create_node(node_name := 'provider1', dsn := 'host=<source_database_fqdn> port=5432 user=xxx password=xxxxx dbname=pglogical_source');
    • node_name: especifique el nombre del editor que se va a crear en la base de datos origen.
    • host: introduzca el nombre de dominio completo (FQDN) de la base de datos origen.
    • port_number: proporcione el puerto en el que se está ejecutando la base de datos de origen.
    • database_name: especifique la base de datos en la que crear la publicación.
  2. Agregue todas las tablas del esquema public al juego de replicaciones por defecto.
    SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

Configurar base de datos de destino

Conéctese como usuario administrador (el usuario especificado durante la creación de la base de datos PostgreSQL de OCI) y otorgue los privilegios necesarios para activar la replicación lógica.

alter role xxx with replication;
grant EXECUTE on FUNCTION pg_catalog.pg_replication_origin_session_reset() to xxx ;
grant EXECUTE on FUNCTION pg_catalog.pg_replication_origin_session_setup to xxx ;
grant all on FUNCTION pg_catalog.pg_replication_origin_session_setup to xxx;

xxx es el usuario de ejemplo creado durante la configuración de la base de datos.

Nota

Ignore el comando alter role ...replication para el usuario administrador en la base de datos PostgreSQL de OCI versión 16 y posterior, ya que el usuario administrador hereda el rol de replicación por defecto a partir de OCI PostgreSQL 16.

Antes de continuar, asegúrese de que:

  • Todos los esquemas y objetos necesarios existen en el destino

    Los elementos incluyen esquemas, tablas, índices, extensiones y cualquier objeto de base de datos necesario. Tenerlos en su lugar ayuda a garantizar que las operaciones de replicación o restauración puedan continuar sin errores de dependencias faltantes. La existencia se puede confirmar mediante la comparación de listas de esquemas (por ejemplo, mediante \dn y \dt en psql) y la validación de que las extensiones necesarias se instalan tanto en el origen como en el destino.

  • La estructura de la base de datos coincide con el origen

    La base de datos de destino debe alinearse estrechamente con el origen en términos de definiciones de esquema, nombres de objetos y estructura general. Esta consistencia ayuda a facilitar la migración de datos y reduce la probabilidad de conflictos o incoherencias durante la sincronización. Verifique mediante la revisión de definiciones de esquema (como el uso de pg_dump --schema-only) o mediante la comparación de estructuras de tabla y recuentos de objetos entre los sistemas de origen y de destino.

Configurar pglogical en el destino (suscriptor)

  1. Cree el nodo de suscriptor en la base de datos destino:
    SELECT pglogical.create_node(node_name := 'subscriber1', dsn := 'host=<target_database_fqdn> port=5432 user=xxx password=xxxxx dbname=pglogical_target');
    • node_name: defina el nombre del suscriptor en la base de datos destino.
    • host: introduzca el nombre de dominio completo (FQDN) de la base de datos de destino.
    • port_number: introduzca el puerto en el que se está ejecutando la base de datos destino.
    • database_name: proporcione el nombre de la base de datos en la que se creará la suscripción
  2. Cree la suscripción para iniciar los procesos de replicación y sincronización en segundo plano:
    SELECT pglogical.create_subscription(subscription_name := 'subscription1', provider_dsn := 'host=<source_database_fqdn> port=5432 user=xxx password=xxxxxx dbname=pglogical_source sslmode=require');
    • subscription_name: proporcione el nombre de la suscripción.
    • host: proporcione el FQDN de la base de datos origen.
    • port_number: proporcione el puerto en el que se está ejecutando la base de datos de destino.
    • database_name: proporcione el nombre del origen de las bases de datos.

Verificar replicación

  1. Compruebe el estado de la suscripción (destino): ejecute la siguiente sentencia para comprobar el estado de la suscripción en la base de datos de destino.
    SELECT * FROM pglogical.show_subscription_status();
  2. Compruebe el estado de replicación (origen): ejecute la siguiente sentencia para comprobar el estado de la replicación en la base de datos de origen.
    SELECT * FROM pg_stat_replication;

Si ambas comprobaciones indican un estado activo sin demora o cambios pendientes, este resultado sugiere que la replicación ha finalizado y que la base de datos de destino está totalmente sincronizada con el origen.

Para obtener más información sobre pglogical, incluidos pasos y explicaciones detallados, consulte Sincronización entre regiones de OCI Database with PostgreSQL mediante la extensión pglogical

Conclusión

En conclusión, las actualizaciones de versiones principales que utilizan pg_dump/pg_restore y pglogical ofrecen ventajas distintas según el caso de uso. El enfoque pg_dump/pg_restore es sencillo y fiable para bases de datos más pequeñas o cuando el tiempo de inactividad es aceptable, pero puede llevar mucho tiempo para grandes conjuntos de datos. Por el contrario, pglogical permite actualizaciones casi sin tiempo de inactividad replicando datos entre versiones, lo que lo hace más adecuado para sistemas grandes o esenciales.

La elección del método adecuado depende de factores como el tamaño de la base de datos, el tiempo de inactividad aceptable y la complejidad operativa. Independientemente del enfoque, la planificación exhaustiva, las pruebas y la validación posterior a la actualización son fundamentales para garantizar la integridad de los datos y una transición fluida a la versión más reciente de OCI PostgreSQL.