pg_dump with SSL for Azure PostgreSQL

Question

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?

Answer 1

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

Answer 2

Hi Michiteru Sugitani •,

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

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

Answer 3

Hi @SSingh-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?