pg_dump with SSL for Azure PostgreSQL

Michiteru Sugitani 70 Reputation points
2023-11-22T09:33:40.33+00:00

Hi All

I would like to migrate data from Azure PostgreSQL single to flexible.

I tried to this using pg_dump.

When I did pg_dump I got an error like below.

pg_dump: server version: 11.18; pg_dump version: 10.6

pg_dump: aborting because of server version mismatch

So I downloaded source of PostgreSQL11.18 and compiled.

I did pg_dump again and then I got an error like below.

pg_dump: [archiver (db)] connection to database "xxxxx" failed: FATAL: SSL connection is required. Please specify SSL options and retry.

I researched on this and I got to know that there is a option like PGSSLMODE=allow.

I tested this but doesn't work.

Do you have any good idea on this?

Azure Database for PostgreSQL
{count} vote

3 answers

Sort by: Most helpful
  1. ShaktiSingh-MSFT 14,296 Reputation points Microsoft Employee
    2023-11-22T15:13:25.42+00:00

    Hi Michiteru Sugitani •,

    Welcome to Microsoft Q&A forum.

    As I understand, you are getting Fatal error of SSL connection in pg_dump.

    On Azure Database for PostgreSQL servers, TLS/SSL connections are on by default. If your PostgreSQL server requires TLS/SSL connections, but doesn't have them, set an environment variable PGSSLMODE=require so that the pg_restore tool connects with TLS. Without TLS, the error might read: "FATAL: SSL connection is required. Please specify SSL options and retry." In the Windows command line, run the command SET PGSSLMODE=require before running the pg_restore command. In Linux or Bash, run the command export PGSSLMODE=require before running the pg_restore command.

    Please try with above command and let us know if this helped.

    Thanks

    0 comments No comments

  2. ShaktiSingh-MSFT 14,296 Reputation points Microsoft Employee
    2023-11-23T08:28:52.5166667+00:00

    Hi Michiteru Sugitani •,

    The following table lists the different tools available for performing the migration from single server to flexible server.

    User's image

    Please refer https://learn.microsoft.com/en-us/azure/postgresql/migrate/concepts-single-to-flexible for more details.

    Let us know if this helps or if you have queries.

    Thanks

    0 comments No comments

  3. Michiteru Sugitani 70 Reputation points
    2023-12-01T08:16:25.6166667+00:00

    Hi @ShaktiSingh-MSFT

    I tried to do pg_dump and pg_restore.

    (pg_dump from PostgreSQL 11, pg_restore to PostgreSQL15)

    I got problem because version are different.

    I did pg_restore with version 11. but not suceeded.

    If I will try to pg_restore with version 15 will it OK?

    other problen is dump data is large. (22GB)

    I'm intredted in migration tool I wrote before.

    if version are diffrent, there is no problem on migration tool?

    0 comments No comments