Migración de una base de datos de PostgreSQL mediante volcado y restauración

  • Artículo

SE APLICA A: Azure Database for PostgreSQL: servidor único Azure Database for PostgreSQL: servidor flexible

Puede usar pg_dump para extraer una base de datos de PostgreSQL en un archivo de volcado. El método para restaurar la base de datos depende del formato del volcado de memoria que elija. Si el volcado de memoria se toma con el formato sin formato (que es el predeterminado -Fp, por lo que no es necesario especificar ninguna opción específica), la única opción para restaurarla es mediante psql, ya que genera un archivo de texto sin formato. Para los otros tres métodos de volcado: se deben usar los pg_restore personalizados, directorios y tar.

Importante

Las instrucciones y comandos proporcionados en este artículo están diseñados para ejecutarse en terminales bash. Esto incluye entornos como Subsistema de Windows para Linux (WSL), Azure Cloud Shell y otras interfaces compatibles con Bash. Asegúrese de que usa un terminal de Bash para seguir los pasos y ejecutar los comandos detallados en esta guía. El uso de un tipo diferente de entorno de terminal o shell puede dar lugar a diferencias en el comportamiento de los comandos y puede que no genere los resultados previstos.

En este artículo, nos centramos en los formatos de directorio y sin formato (predeterminados). El formato de directorio es útil, ya que permite usar varios núcleos para el procesamiento, lo que puede mejorar significativamente la eficacia, especialmente para bases de datos de gran tamaño.

Azure Portal simplifica este proceso a través de la hoja Conectar mediante la oferta de comandos preconfigurados que se adaptan al servidor, con valores sustituidos por los datos de usuario. Es importante tener en cuenta que la hoja Conectar solo está disponible para Azure Database for PostgreSQL: servidor flexible y no para servidor único. Aquí se muestra cómo puede usar esta característica:

  1. Acceso a Azure Portal: en primer lugar, vaya a Azure Portal y elija la hoja Conectar.

    Screenshot showing the placement of Connect blade in Azure portal.

  2. Seleccione la base de datos: en la hoja Conectar, encontrará una lista desplegable de las bases de datos. Seleccione la base de datos desde la que desea realizar un volcado.

    Screenshot showing the dropdown where specific database can be chosen.

  3. Elija el método adecuado: en función del tamaño de la base de datos, puede elegir entre dos métodos:

    • pg_dump & psql - usando un archivo de texto singular: ideal para bases de datos más pequeñas, esta opción utiliza un único archivo de texto para el proceso de volcado y restauración.
    • pg_dump & pg_restore - usando varios núcleos: para bases de datos más grandes, este método es más eficaz, ya que usa varios núcleos para controlar el proceso de volcado y restauración.

    Screenshot showing two possible dump methods.

  4. Comandos de copia y pegado: el portal proporciona comandos y listos para usarpg_dump.psqlpg_restore Estos comandos incluyen valores que ya se sustituyen según el servidor y la base de datos que ha elegido. Copie y pegue estos comandos.

Requisitos previos

Si usa un servidor único o no tiene acceso al portal de servidor flexible, lea esta página de documentación. Contiene información similar a la que se presenta en la hoja de Conectar para servidor flexible en el portal.

Para seguir esta guía, necesitará:

  • Un servidor de Azure Database for PostgreSQL que incluya reglas de firewall para permitir el acceso.
  • pg_dump, psql, pg_restore y pg_dumpall en caso de que quiera migrar con roles y permisos, utilidades de línea de comandos instaladas.
  • Decidir en la ubicación del volcado: elija el lugar desde el que desea realizar el volcado. Se puede hacer desde varias ubicaciones, como una máquina virtual independiente, Cloud Shell (donde las utilidades de la línea de comandos ya están instaladas, pero es posible que no estén en la versión adecuada, por lo que siempre compruebe la versión con, por ejemplo, psql --version) o su propio portátil. Tenga siempre en cuenta la distancia y la latencia entre el servidor postgreSQL y la ubicación desde la que se ejecuta el volcado o la restauración.

Importante

Es esencial usar las pg_dumputilidades , psqlpg_restore y pg_dumpall que son de la misma versión principal o una versión principal superior que el servidor de bases de datos al que exporta o importa datos. Si no lo hace, puede producirse una migración de datos incorrecta. Si el servidor de destino tiene una versión principal superior a la del servidor de origen, use utilidades que sean la misma versión principal o superior al servidor de destino.

Nota:

Es importante tener en cuenta que pg_dump solo puede exportar una base de datos a la vez. Esta limitación se aplica independientemente del método que haya elegido, ya sea mediante un archivo singular o varios núcleos.

Volcar usuarios y roles con pg_dumpall -r

pg_dump se usa para extraer una base de datos postgreSQL en un archivo de volcado de memoria. Sin embargo, es fundamental comprender que no volca roles ni definiciones de usuarios, ya que pg_dump se consideran objetos globales dentro del entorno de PostgreSQL. Para una migración completa, incluidos los usuarios y los roles, debe usar pg_dumpall -r. Este comando permite capturar toda la información de rol y usuario del entorno de PostgreSQL. Si va a migrar dentro de bases de datos en el mismo servidor, no dude en omitir este paso y pasar a la sección Crear una nueva base de datos .

pg_dumpall -r -h <server name> -U <user name> > roles.sql

Por ejemplo, si tiene un servidor denominado mydemoserver y un usuario denominado myuser ejecute el siguiente comando:

pg_dumpall -r -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql

Si usa un servidor único, el nombre de usuario incluye el componente de nombre de servidor. Por lo tanto, en lugar de myuser, use myuser@mydemoserver.

Volcar roles desde un servidor flexible

En un entorno de servidor flexible, las medidas de seguridad mejoradas significan que los usuarios no tienen acceso a la tabla pg_authid, que es donde se almacenan las contraseñas de rol. Esta restricción afecta a cómo se realiza un volcado de roles, ya que el comando estándar pg_dumpall -r intenta acceder a esta tabla para las contraseñas y produce un error debido a la falta de permiso.

Al volcar roles desde un servidor flexible, es fundamental incluir la opción en pg_dumpall el --no-role-passwords comando. Esta opción impide pg_dumpall que intente acceder a la pg_authid tabla, que no se puede leer debido a restricciones de seguridad.

Para volcar correctamente los roles desde un servidor flexible, use el siguiente comando:

pg_dumpall -r --no-role-passwords -h <server name> -U <user name> > roles.sql

Por ejemplo, si tiene un servidor denominado mydemoserver, un usuario denominado myuser, ejecute el siguiente comando:

pg_dumpall -r --no-role-passwords -h mydemoserver.postgres.database.azure.com -U myuser > roles.sql

Limpieza del volcado de roles

Al migrar el archivo roles.sql de salida, puede incluir determinados roles y atributos que no son aplicables o permitidos en el nuevo entorno. Esto es lo que debe tener en cuenta:

  • Quitar atributos que solo pueden establecer los superusuarios: si se migra a un entorno en el que no tiene privilegios de superusuario, quite atributos como NOSUPERUSER y NOBYPASSRLS del volcado de roles.

  • Exclusión de usuarios específicos del servicio: excluya usuarios de servicio de servidor único, como azure_superuser o azure_pg_admin. Son específicos del servicio y se crearán automáticamente en el nuevo entorno.

Use el siguiente sed comando para limpiar el volcado de roles:

sed -i '/azure_superuser/d; /azure_pg_admin/d; /azuresu/d; /^CREATE ROLE replication/d; /^ALTER ROLE replication/d; /^ALTER ROLE/ {s/NOSUPERUSER//; s/NOBYPASSRLS//;}' roles.sql

Este comando elimina las líneas que contienen azure_superuserlas líneas , azure_pg_admin, azuresu, a partir CREATE ROLE replication de y ALTER ROLE replicationy quita los atributos y NOBYPASSRLS de ALTER ROLE las NOSUPERUSER instrucciones .

Creación de un archivo de volcado con los datos que se van a cargar

Para exportar la base de datos postgreSQL existente localmente o en una máquina virtual a un archivo de script sql, ejecute el siguiente comando en el entorno existente:

pg_dump <database name> -h <server name> -U <user name> > <database name>_dump.sql

Por ejemplo, si tiene un servidor denominado mydemoserver, un usuario denominado myuser y una base de datos denominada testdb, ejecute el siguiente comando:

pg_dump testdb -h mydemoserver.postgres.database.azure.com -U myuser > testdb_dump.sql

Si usa un servidor único, el nombre de usuario incluye el componente de nombre de servidor. Por lo tanto, en lugar de myuser, use myuser@mydemoserver.

Restauración de los datos en la base de datos de destino

Restauración de roles y usuarios

Antes de restaurar los objetos de base de datos, asegúrese de haber volcado y limpiado correctamente los roles. Si va a migrar dentro de bases de datos en el mismo servidor, puede que no sea necesario volcar los roles y restaurarlos. Sin embargo, para las migraciones en distintos servidores o entornos, este paso es fundamental.

Para restaurar los roles y los usuarios en la base de datos de destino, use el siguiente comando:

psql -f roles.sql -h <server_name> -U <user_name>

Reemplace <server_name> por el nombre del servidor de destino y <user_name> por el nombre de usuario. Este comando usa la psql utilidad para ejecutar los comandos SQL contenidos en el roles.sql archivo, restaurando eficazmente los roles y los usuarios a la base de datos de destino.

Por ejemplo, si tiene un servidor denominado mydemoserver, un usuario denominado myuser, ejecute el siguiente comando:

psql -f roles.sql -h mydemoserver.postgres.database.azure.com -U myuser

Si usa un servidor único, el nombre de usuario incluye el componente de nombre de servidor. Por lo tanto, en lugar de myuser, use myuser@mydemoserver.

Nota:

Si ya tiene usuarios con los mismos nombres en el servidor único o en el servidor local desde el que va a migrar y el servidor de destino, tenga en cuenta que este proceso de restauración puede cambiar las contraseñas de estos roles. Por lo tanto, cualquier comando posterior que necesite ejecutar puede requerir las contraseñas actualizadas. Esto no se aplica si el servidor de origen es un servidor flexible, ya que el servidor flexible no permite el volcado de contraseñas para los usuarios debido a medidas de seguridad mejoradas.

Cree una nueva base de datos

Antes de restaurar la base de datos, es posible que tenga que crear una base de datos nueva y vacía. Para ello, el usuario que usa debe tener el CREATEDB permiso . Estos son dos métodos usados habitualmente:

  1. Uso de createdb la utilidad El createdb programa permite la creación de bases de datos directamente desde la línea de comandos de Bash, sin necesidad de iniciar sesión en PostgreSQL o dejar el entorno del sistema operativo. Por ejemplo:

    createdb <new database name> -h <server name> -U <user name>

    Por ejemplo, si tiene un servidor denominado mydemoserver, un usuario denominado myuser y la nueva base de datos que desea crear es testdb_copy, ejecute el siguiente comando:

    createdb testdb_copy -h mydemoserver.postgres.database.azure.com -U myuser

    Si usa un servidor único, el nombre de usuario incluye el componente de nombre de servidor. Por lo tanto, en lugar de myuser, use myuser@mydemoserver.

  2. Con el comando SQL Para crear una base de datos mediante un comando SQL, deberá conectarse al servidor postgreSQL a través de una interfaz de línea de comandos o una herramienta de administración de bases de datos. Una vez conectado, puede usar el siguiente comando SQL para crear una nueva base de datos:

CREATE DATABASE <new database name>;

Reemplace <new database name> por el nombre que desea asignar a la nueva base de datos. Por ejemplo, para crear una base de datos denominada testdb_copy, el comando sería:

CREATE DATABASE testdb_copy;

Restauración del volcado de memoria

Después de crear la base de datos de destino, puede restaurar los datos en esta base de datos desde el archivo de volcado de memoria. Durante la restauración, registre los errores en un errors.log archivo y compruebe su contenido si hay errores una vez finalizada la restauración.

psql -f <database name>_dump.sql <new database name> -h <server name> -U <user name> 2> errors.log

Por ejemplo, si tiene un servidor denominado mydemoserver, un usuario denominado myuser y una nueva base de datos denominada testdb_copy, ejecute el siguiente comando:

psql -f testdb_dump.sql testdb_copy -h mydemoserver.postgres.database.azure.com -U myuser 2> errors.log

Comprobación posterior a la restauración

Una vez completado el proceso de restauración, es importante revisar el errors.log archivo para ver si se han producido errores. Este paso es fundamental para garantizar la integridad y la integridad de los datos restaurados. Solucione los problemas encontrados en el archivo de registro para mantener la confiabilidad de la base de datos.

Optimización del proceso de migración

Al trabajar con bases de datos de gran tamaño, el proceso de volcado y restauración puede ser largo y puede requerir optimización para garantizar la eficacia y la confiabilidad. Es importante tener en cuenta los distintos factores que pueden afectar al rendimiento de estas operaciones y a tomar medidas para optimizarlas.

Para obtener instrucciones detalladas sobre cómo optimizar el proceso de volcado y restauración, consulte el artículo Procedimientos recomendados para pg_dump y pg_restore . Este recurso proporciona información completa y estrategias que pueden ser beneficiosas para controlar bases de datos de gran tamaño.

Pasos siguientes