Upgrade Azure Database for PostgreSQL from version 9.6 to 11

SHABOU Malek 46 Reputation points
2021-02-22T11:16:26.013+00:00

Hello,

we are planning to upgrade our Azure Database for Postgres (single server) from the version 9.6 to 11.

we wish to use DMS, but in the DMS documentation: https://learn.microsoft.com/fr-fr/azure/dms/tutorial-azure-postgresql-to-azure-postgresql-online-portal

it is said that the Migration from Azure Database for PostgreSQL is supported for PostgreSQL version 10 and later

" Important

Migration from Azure Database for PostgreSQL is supported for PostgreSQL version 10 and later. You can also use this tutorial to migrate from one Azure Database for PostgreSQL instance to another Azure Database for PostgreSQL instance or Hyperscale (Citus) instance."

do DMS compatble with Azure postgresql 9.6 as source ?

Best regards.

Azure Database Migration service
Azure Database for PostgreSQL
0 comments
Accepted answer
  1. Anurag Sharma 17,571 Reputation points
    2021-02-23T04:49:29.787+00:00

    Hi @SHABOU Malek , welcome to Microsoft QnA forum.

    You can upgrade the azure PostgreSQL from version 9.6 using dump and restore process as mentioned in below article:
    Upgrade your PostgreSQL database using dump and restore

    Coming back to DMS for upgradation, as per the same document:

    "The migration activity window appears, and the Status of the activity should update to show as Backup in Progress. You may encounter the following error when upgrading from Azure DB for PostgreSQL 9.5 or 9.6:
    A scenario reported an unknown error. 28000: no pg_hba.conf entry for replication connection from host "40.121.141.121", user "sr""

    I have not tried it myself but it looks with certain changes it is doable as well.

    Please let us know if this helps.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. SHABOU Malek 46 Reputation points
    2021-02-23T08:34:26.167+00:00

    Hi,

    The database is about 1-2TB, dump and restore scenario is not usable for us.

    we will try the DMS replication on our pre-production.

    Best regards,

    1 person found this answer helpful.
  2. SHABOU Malek 46 Reputation points
    2022-04-30T21:30:19.173+00:00

    the best way is to use Azure Database Migration Service (DMS), the limit is that all tables must have a primary key.
    the downtime is very short.

    • it take only the time to activate all the foreign constraint (you can do it in parallel)
    • update the sequences.

    i use this method to migrate a 3TB database from 9.6 single server to 13 flexible server.

    Malek,

    0 comments
  3. si li 20 Reputation points
    2023-07-23T13:59:31.0433333+00:00

    there are 3 methods to upgrade PostgreSQL major version:

    1. Upgrade PostgreSQL database using pg_dump and pg_restore.
    2. Upgrade PostgreSQL database using pg_upgrade.
    3. Upgrade PostgreSQL database using logical replication.
    0 comments