Migrate from Unmanaged Postgres to Neon

Questions / Help
1

We’re looking to migrate off of fly unmanaged postgres. Unfortunately fly’s managed postgres option is not suitable for our needs so we’re looking to migrate to Neon.

In order to ensure a smooth migration we need to set up local replication from our unmanaged postgres to Neon, which means Neon needs to be able to connect securely to our unmanaged postgres.

However we’ve encountered a couple of blocking issues in this regard:

  • Exposing an unmanaged postgres with external IPs allows for plaintext connections and doesn’t force clients to use TLS.
  • When a client does connect with the sslmode=require option set, it errors out with the following: ssl error tlsv1 alert no application protocol

Ideally we’d just connect to the instance through wireguard but that’s not an option for Neon; so we’re left stuck trying to figure out how to get this connection set up.

I know unmanaged postgres is not supported any more, but surely some support in migrating off of this unsupported platform will be needed else we’ll be stuck on the unsupported platform indefinitely.

2

Yeah, this is a known limitation, alas. (Another user complained about it in the forum a while back, but I haven’t been able to dig up that post again—so far.)

Both of your questions are really about the Fly Proxy’s pg_tls handler, though, which as far as I know is covered by Fly Support.

(I don’t work for Fly.io or speak for them in any way, however.)

Or if you do want to try your luck here in the community forum, it would help to say more about which exact clients weren’t able to connect. Some don’t send the mandatory SNI, for example…

3

Yes, I tested this using the psql client mostly and confirmed with wireshark that SNI is being sent.

Here’s an example conversation from wireshark which clearly shows the SNI being sent

No. Time Source Destination Protocol Type Length Info
3 0.007589538 10.0.0.108 37.16.23.73 TCP 66 42578 → 5432 [ACK] Seq=1 Ack=1 Win=64512 Len=0 TSval=4275671388 TSecr=2393985786
4 0.007653715 10.0.0.108 37.16.23.73 PGSQL 74 >?
5 0.015528802 37.16.23.73 10.0.0.108 TCP 66 5432 → 42578 [ACK] Seq=1 Ack=9 Win=65536 Len=0 TSval=2393985794 TSecr=4275671388
6 0.017434149 37.16.23.73 10.0.0.108 PGSQL 67 <
7 0.017479431 10.0.0.108 37.16.23.73 TCP 66 42578 → 5432 [ACK] Seq=9 Ack=2 Win=64512 Len=0 TSval=4275671398 TSecr=2393985796
8 0.022565989 10.0.0.108 37.16.23.73 TLSv1.3 1648 Client Hello (``SNI=MY_APPLICATION_NAME.fly.dev``)
9 0.030550587 37.16.23.73 10.0.0.108 TCP 66 5432 → 42578 [ACK] Seq=2 Ack=1591 Win=69632 Len=0 TSval=2393985809 TSecr=4275671403
10 0.031118553 37.16.23.73 10.0.0.108 TLSv1.3 195 Server Hello
11 0.031119236 37.16.23.73 10.0.0.108 TLSv1.3 74 Change Cipher Spec
12 0.031119480 37.16.23.73 10.0.0.108 TLSv1.3 90 Application Data

After that 12th packet gets ack’d the connection is closed

1 Like
4

I’ve also tried the following:

  1. Swap out the pg_tls handler for plain tls
  2. Use the connection parameter sslnegotiation=direct which tells the client to establish a TLS session immediately, like a regular TLS service rather than requiring a postgres connection to be established first.

This has the benefit of clients not setting ssl=require sslnegoation=direct being rejected because now TLS is required for connections. However it shows the same error on clients with the option set.

In Wireshark I can confirm that we get TCP SYN followed immediately by a Client Hello with SNI and a TLS session being initiated, but it still closes immediately. Might see if a postgres→postgres setup works or not

Update connecting from Neon while using CREATE SUBSCRIPTION fails with the same error with these options applied.

5

Thanks for the details… I just tried this with a throwaway database on my own account, and it worked fine…

$ psql -d 'postgres://postgres:********@mayailuridb.fly.dev:5432/?sslmode=require'
psql (15.13 (Debian 15.13-0+deb12u1), server 17.2 (Ubuntu 17.2-1.pgdg24.04+1))
WARNING: psql major version 15, server major version 17.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "mayailuridb.fly.dev" (address "204.10.***.***") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

Which version pair have you been trying? (Your forum handle is fairly old, and the instructions in the official docs may have only had the newer PG Flex in mind…)

6

This is on postgres flex, but an older version (15.8) – part of need to migrate is to get onto a newer postgres version too.

I’m using a dedicated fork of my production DB app for testing this so I can update it to 15.10 to see what happens; but if needs be I may end up having to first do a migration internally from Postgres 15 to Postgres 17 between unmanaged fly Postgreses (Postgresen?) and then the migration out of unmanaged fly. Bit of a headache though.

7

I’ve also just tried this with a Flex 17.2 database (after testing migration from 15.8 to 17.2 between unmanaged fly postgres clusters), and I get the same issue… its very odd because it looks like TLS is getting set up correctly but then it fails, looks like maybe an ALPN issue?

8

Maybe… Can you try with a downgraded client? Someone else was reporting that they saw it starting with psql v16.4:

https://www.postgresql.org/message-id/5fc24305-136e-4a6e-993e-b10dc2300403%40headcrashing.eu

(that mailing-list server is a little flakey, so give it a couple tries if it 503s you initially.)

9

I’ve tried it using psql v16.0 (compiled locally from source), same error (when connecting to Postgres 17.2)

Going to try with v15.8 but getting that compiled with TLS support is proving difficult, even when specifying --with-ssl=openssl in ./configure, trying to use sslmode=require in psql tells me that it hasn’t been compiled with SSL support :face_with_spiral_eyes:

10

Interesting… The following prepackaged binary is what succeeded for me, using an ephemeral Machine on Fly.io itself as the client:

$ fly m run --shell --region ewr debian:bookworm
# apt-get update
# apt-get install --no-install-recommends postgresql-client
# psql -d 'postgres://postgres:********@mayailuridb.fly.dev:5432/?sslmode=require'

(According to the mailing-list thread above, the version of libpq might also matter, :thinking:.)

11

it might work better to install pgloader (or similar) directly on a fly postgres machine and syncing to your Neon database from there.

12

Fair! I was assuming all this was in pursuit of near-zero downtime, like with the other recent poster, but I could be wrong.

I don’t know how officially supported this kind of thing is, but I did finally manage to get a newer version of psql to connect, by adding an ALPN entry analogous to the Traefik one from the mailing list:

[[services.ports]]
  port = 5432
  handlers = ['pg_tls']
  tls_options = { alpn = ['postgresql'] }

And then…

$ fly console --region ewr --image debian:trixie
# psql -d 'postgres://postgres:********@mayailuridb.fly.dev:5432/sslmode=require'
psql (17.6 (Debian 17.6-0+deb13u1), server 15.8 (Debian 15.8-1.pgdg120+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

postgres=#

That same client-side image was failing with tlsv1 alert no application protocol prior.

13

Amazing, this seems to have done the trick. Combined with changing the handler on the fly proxy to tls and using the sslnegotiation=direct option too which ensures only TLS connections are used. So far only tried it with psql and pgcli, but will check to see how it works with a logical replication client too.