search

psycopg / psycopg2

PostgreSQL database adapter for the Python programming language
https://www.psycopg.org/
Other
3.31k stars 503 forks source link

pgoutput logical decoding plugin fails with "client sent proto_version=0 but server only supports protocol 1 or higher" #1690

Closed hgdeoro closed 2 months ago

hgdeoro commented 5 months ago

Please complete the following information:

Describe the bug Please let us know:

1: what you did

I can successfully create_replication_slot() and start_replication() when using the test_decoding output plugin:

    with conn.cursor() as cur:
        cur.create_replication_slot("pytest_logical", output_plugin="test_decoding")
        cur.start_replication(slot_name="pytest_logical", decode=False)

When trying to use the pgoutput plugin, create_replication_slot() succeeds, but start_replication() fails:

    with conn.cursor() as cur:
        cur.create_replication_slot("pytest_logical", output_plugin="pgoutput")
        cur.start_replication(slot_name="pytest_logical", decode=False)

2: what you expected to happen

I expect to be able to use the pgoutput plugin, at least to be able to consume but start_replication() fails.

The plugin is installed in both docker images that I tried postgres:16.2-alpine and postgres:16.2. I got the error in both flavors of PostgreSql 16.2

3: what happened instead

The error in Python is: 

>       self.start_replication_expert(
            command, decode=decode, status_interval=status_interval)
E       psycopg2.errors.FeatureNotSupported: client sent proto_version=0 but server only supports protocol 1 or higher
E       CONTEXT:  slot "pytest_logical", output plugin "pgoutput", in the startup callback

venv/lib/python3.11/site-packages/psycopg2/extras.py:616: FeatureNotSupported

and I can see this logs in PostgreSql:

db-1  | 2024-04-03 22:37:21.746 UTC [57] LOG:  logical decoding found consistent point at 0/1BED5D8
db-1  | 2024-04-03 22:37:21.746 UTC [57] DETAIL:  There are no running transactions.
db-1  | 2024-04-03 22:37:21.746 UTC [57] STATEMENT:  CREATE_REPLICATION_SLOT "pytest_logical" LOGICAL "pgoutput"
db-1  | 2024-04-03 22:37:21.746 UTC [57] LOG:  exported logical decoding snapshot: "00000003-00000028-1" with 0 transaction IDs
db-1  | 2024-04-03 22:37:21.746 UTC [57] STATEMENT:  CREATE_REPLICATION_SLOT "pytest_logical" LOGICAL "pgoutput"
db-1  | 2024-04-03 22:37:21.747 UTC [57] ERROR:  client sent proto_version=0 but server only supports protocol 1 or higher
db-1  | 2024-04-03 22:37:21.747 UTC [57] CONTEXT:  slot "pytest_logical", output plugin "pgoutput", in the startup callback
db-1  | 2024-04-03 22:37:21.747 UTC [57] STATEMENT:  START_REPLICATION SLOT "pytest_logical" LOGICAL 0/00000000

Since I'm using docker compose and some automated tests, I also tried PostgreSQL 14.11 (Debian 14.11-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit with same results.

If possible, provide a script reproducing the issue.

hgdeoro commented 5 months ago

For the record, using output_plugin="wal2json" works (I haven't consume any message, but start_replication() works)

    with conn.cursor() as cur:
        cur.create_replication_slot("pytest_logical", output_plugin="wal2json")
        cur.start_replication(slot_name="pytest_logical", decode=False)
dvarrazzo commented 5 months ago

Sorry for the silence. Unfortunately I am not very familiar with the replication support and I don't have the bandwidth to work on this issue.

hgdeoro commented 5 months ago

I'm new to replication too. Also, since wal2json worked, I don't know if this issue is caused by psycopg2. I'll try to run psycopg2 tests in these days and see if I discover something.

kdwyer commented 2 months ago

pgoutput only works with protocol versions 1-4 @hgdeoro, according to the Postgres docs. Nothing to do with psycopg2.