Migration from or to a Managed Service for PostgreSQL® cluster

​

Steps

1. Prepare a target cluster.
2. Migrate data.

​

Prerequisites

• The PostgreSQL version in the source cluster is the same as in the target cluster. You can check the versions supported by Managed PostgreSQL on the cluster creation page in the web console.
• The pg_dump and pg_restore utilities have the same version. You can install these utilities together with psql:
  • macOS
  • Ubuntu

  Copy

  Ask AI

  brew install libpq
  echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc
  

  After that, restart the terminal.

  Copy

  Ask AI

  sudo apt update && sudo apt install postgresql-client
  
• The major version of the utilities must be greater than or equal to the PostgreSQL version in the target cluster. For example, if the cluster is created with version 16, the major version of the utilities can be 16 or 17.

​

Prepare a target cluster

1. Create a target cluster with the same username as in the source cluster. For details on how to create a Managed Service for PostgreSQL cluster, see How to create clusters.
2. Make sure that the target cluster has at least the same list of installed PostgreSQL extensions as the source cluster does:
   1. Connect to the DB in the source cluster and then check what extensions are installed:
      Copy

      Ask AI

      SELECT * FROM pg_available_extensions;
      
   2. Enable these extensions on a target cluster.
3. To secure connections to databases, install the Nebius AI Cloud certificate:
   Copy

   Ask AI

   mkdir -p ~/.postgresql \
   curl "https://storage.eu-north1.nebius.cloud/msp-certs/ca.pem" \
      -o ~/.postgresql/root.crt \
   chmod 0600 ~/.postgresql/root.crt
   
   This command creates the .postgresql folder, downloads the certificate into it and then sets the access rights to the certificate file.

​

Migrate data

1. Switch the source DB to read-only mode:
   Copy

   Ask AI

   ALTER USER <username> SET default_transaction_read_only = on
   
   Run this statement for every DB user.
2. Create the source DB dump:
   Copy

   Ask AI

   pg_dump --host=<source_host_address> \
      --port=<port> \
      --dbname=<DB_name> \
      --username=<username> \
      --jobs=<number_of_CPU_cores> \
      --format=d \
      --file=db_dump
   
   Specify the following parameters in the command:
   • --host: The IP address or FQDN of the primary host of the source cluster.
   • --port: The port to access the source cluster.
   • --dbname: The source DB name.
   • --username: The username in the source cluster.
   • --jobs (optional): To speed up the dump creation and activate a multithreaded mode, specify the number of vCPU cores on which dump creation threads run in parallel.
3. Restore the data from the dump to the target cluster:
   Copy

   Ask AI

   pg_restore --host=<target_host_address> \
      --dbname=<DB_name> \
      --username=<username> \
      --port=5432 \
      --format=d \
      --verbose \
      --file=db_dump \
      --single-transaction \
      --no-privileges
   
   Specify the following parameters in the command:
   • --host: The IP address or FQDN of the primary host of the target cluster.
   • --dbname: The target DB name.
   • --username: The username in the target cluster. Must match the username in the source cluster. If you restore all the data from the source DB, specify the DB owner username. If you only restore a single schema, add the --schema=<schema_name> parameter. In this case, the DB owner rights are not required.
   If the specified user does not have rights to create or update the PostgreSQL extensions and this causes errors during restoration, remove the --single-transaction parameter from the command. The service ignores the errors in this case:
   Copy

   Ask AI

   pg_restore: warning: errors ignored on restore: 3
   
   However, make sure that these errors only apply to the extensions.
4. Connect to the target DB and check the integrity of the migrated data:
   • To check the list of tables, run \dt.
   • To check the list of schemas, run \dn.