[BUG] Syntax error CREATE_REPLICATION_SLOT when install electric-sql with docker

[dreamid27]

I encountered an error while running electric sql in Docker. I followed the steps one by one from this documentation, without missing any, and I tried almost 5 times, but it still fails.

Can you provide me with information about why this error is occurring?

OS : windows-11
Runtime: Docker
Database : Supabase

10:44:07.871 pid=<0.3086.0> origin=postgres_1 [info] Successfully initialized origin postgres_1 at extension version
10:44:07.871 pid=<0.3095.0> [info] Starting Proxy server listening on port 65432
10:44:07.871 pid=<0.3096.0> pg_producer=postgres_1 [info] Starting Elixir.Electric.Postgres.Extension.SchemaCache for postgres_1
10:44:07.872 pid=<0.3096.0> pg_producer=postgres_1 [warning] SchemaCache "postgres_1" registered as the global instance
10:44:07.872 pid=<0.3099.0> [info] Starting replication from postgres_1
10:44:07.872 pid=<0.3099.0> [info] Electric.Replication.Postgres.LogicalReplicationProducer.init(%{database: ~c"postgres", host: ~c"aws-0-ap-southeast-1.pooler.supabase.com", ip_addr: ~c"52.77.146.31", ipv6: true, nulls: [nil, :null, :undefined], password: ~c"******", port: 5432, replication: ~c"database", ssl: true, ssl_opts: [server_name_indication: ~c"aws-0-ap-southeast-1.pooler.supabase.com"], timeout: 5000, username: ~c"postgres.mbmrpbolsghkbcdcgjvj"})
10:44:07.925 pid=<0.3100.0> [warning] Description: ~c"Server authenticity is not verified since certificate path validation is not enabled"
     Reason: ~c"The option {verify, verify_peer} and one of the options 'cacertfile' or 'cacerts' are required to enable this."

10:44:08.071 pid=<0.3086.0> origin=postgres_1 [error] PostgresConnectorSup failed to start child :postgres_producer with reason: {:bad_return_value, {:error, {:create_replication_slot_syntax_error, "syntax error at or near \"CREATE_REPLICATION_SLOT\""}}}.
▓ ┌───────────────────────┐
▓ │  CONFIGURATION ERROR  │
▓ ┕━━━━━━━━━━━━━━━━━━━━━━━┙
▓
▓ Failed to establish replication connection to Postgres:
▓   syntax error at or near "CREATE_REPLICATION_SLOT"
▓
▓ Make sure the value of DATABASE_URL is a connection string that can be used
▓ to connect to your database directly, not through a connection pool.

••• Shutting down •••

[os_mon] cpu supervisor port (cpu_sup): Erlang has closed
[os_mon] memory supervisor port (memsup): Erlang has closed
PS C:\Users\Administrator>

[linear]

VAX-1868 [BUG] Syntax error CREATE_REPLICATION_SLOT when install electric-sql with docker

[alco]

Hey! The syntax error is raised by the Supabase connection pooler because it does not support replication connections. You need to configure Electric to connect directly to the Supabase DB. See https://electric-sql.com/docs/deployment/supabase#2-retrieving-the-connection-details

…

On 17 May 2024 at 13:49 +0300, linear[bot] ***@***.***>, wrote: VAX-1868 [BUG] Syntax error CREATE_REPLICATION_SLOT when install electric-sql with docker — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[dreamid27]

Hey! The syntax error is raised by the Supabase connection pooler because it does not support replication connections. You need to configure Electric to connect directly to the Supabase DB. See https://electric-sql.com/docs/deployment/supabase#2-retrieving-the-connection-details
…
On 17 May 2024 at 13:49 +0300, linear[bot] @.>, wrote: VAX-1868 [BUG] Syntax error CREATE_REPLICATION_SLOT when install electric-sql with docker — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you are subscribed to this thread.Message ID: @.>

Thanks man for the explain,

But i have tried to disable connection pool, but i facing different errors. i'm sure and have double check the DATABASE_HOST is not wrong.

2024-05-17 20:19:09 13:19:09.444 pid=<0.3374.0> origin=postgres_1 [error] Initialization of Postgres state failed with reason: {:error, :eaddrnotavail}.
2024-05-17 20:19:09 ▓ ┌────────────────────┐
2024-05-17 20:19:09 ▓ │  CONNECTION ERROR  │
2024-05-17 20:19:09 ▓ ┕━━━━━━━━━━━━━━━━━━━━┙
2024-05-17 20:19:09 ▓ 
2024-05-17 20:19:09 ▓ Failed to initialize Postgres state:
2024-05-17 20:19:09 ▓   {:error, :eaddrnotavail}
2024-05-17 20:19:09 ▓ 
2024-05-17 20:19:09 ▓ Double-check the value of DATABASE_URL and make sure your database
2024-05-17 20:19:09 ▓ is running and can be reached using the connection URL in DATABASE_URL.

[dreamid27]

I have check my ISP work on ipv6

[msfstef]

@dreamid27 you will probably have to configure an IPV6 subnet explicitly in your docker compose, or use docker directly rather than docker compose as compose has some issues with IPV6 support.

To confirm that the issue is docker IPV6 support, maybe try running curl -6 https://ifconfig.co from within the docker container (you might have to install curl with apt-get update && apt-get install curl) to see if you get an error. If you do, then I recommend messing around with setting up the subnet as I mentioned above.

[dreamid27]

@dreamid27 you will probably have to configure an IPV6 subnet explicitly in your docker compose, or use docker directly rather than docker compose as compose has some issues with IPV6 support.

To confirm that the issue is docker IPV6 support, maybe try running curl -6 https://ifconfig.co from within the docker container (you might have to install curl with apt-get update && apt-get install curl) to see if you get an error. If you do, then I recommend messing around with setting up the subnet as I mentioned above.

Hei @msfstef thank you man, i solved by adding ipv6 support.

but then, i got this error, i'm sure not using connection-pool

00:22:26.994 pid=<0.3059.0> [info] Starting ElectricSQL 0.11.1 in direct_writes mode.
00:22:26.997 pid=<0.3060.0> [info] Running Electric.Plug.Router with Bandit 1.1.3 at :::5133 (http)
00:22:28.112 pid=<0.3374.0> origin=postgres_1 [info] Postgres.Client.with_conn(%{database: ~c"postgres", host: ~c"db.mbmrpbolsghkbcdcgjvj.supabase.co", ip_addr: ~c"2406:da18:243:7403:9793:3bb8:4aab:ee09", ipv6: true, nulls: [nil, :null, :undefined], password: ~c"******", port: 5432, ssl: true, ssl_opts: [server_name_indication: ~c"db.mbmrpbolsghkbcdcgjvj.supabase.co"], tcp_opts: [:inet6], timeout: 5000, username: ~c"postgres"})
00:22:28.167 pid=<0.3378.0> [warning] Description: ~c"Server authenticity is not verified since certificate path validation is not enabled"
     Reason: ~c"The option {verify, verify_peer} and one of the options 'cacertfile' or 'cacerts' are required to enable this."

00:22:30.090 pid=<0.3374.0> origin=postgres_1 [info] Successfully initialized origin postgres_1 at extension version 
00:22:30.092 pid=<0.3385.0> [info] Starting Proxy server listening on port 65432
00:22:31.174 pid=<0.3392.0> [error] Postgrex.Protocol (#PID<0.3392.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (db.mbmrpbolsghkbcdcgjvj.supabase.co:5432): non-existing domain - :nxdomain
00:22:31.174 pid=<0.3394.0> [error] Postgrex.Protocol (#PID<0.3394.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (db.mbmrpbolsghkbcdcgjvj.supabase.co:5432): non-existing domain - :nxdomain
00:22:31.174 pid=<0.3398.0> [error] Postgrex.Protocol (#PID<0.3398.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (db.mbmrpbolsghkbcdcgjvj.supabase.co:5432): non-existing domain - :nxdomain
00:22:31.174 pid=<0.3395.0> [error] Postgrex.Protocol (#PID<0.3395.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (db.mbmrpbolsghkbcdcgjvj.supabase.co:5432): non-existing domain - :nxdomain
00:22:31.174 pid=<0.3390.0> [error] Postgrex.Protocol (#PID<0.3390.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (db.mbmrpbolsghkbcdcgjvj.supabase.co:5432): non-existing domain - :nxdomain
00:22:31.174 pid=<0.3393.0> [error] Postgrex.Protocol (#PID<0.3393.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (db.mbmrpbolsghkbcdcgjvj.supabase.co:5432): non-existing domain - :nxdomain
00:22:31.174 pid=<0.3389.0> [error] Postgrex.Protocol (#PID<0.3389.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (db.mbmrpbolsghkbcdcgjvj.supabase.co:5432): non-existing domain - :nxdomain
00:22:31.174 pid=<0.3391.0> [error] Postgrex.Protocol (#PID<0.3391.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (db.mbmrpbolsghkbcdcgjvj.supabase.co:5432): non-existing domain - :nxdomain
00:22:31.174 pid=<0.3397.0> [error] Postgrex.Protocol (#PID<0.3397.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (db.mbmrpbolsghkbcdcgjvj.supabase.co:5432): non-existing domain - :nxdomain
00:22:31.174 pid=<0.3396.0> [error] Postgrex.Protocol (#PID<0.3396.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (db.mbmrpbolsghkbcdcgjvj.supabase.co:5432): non-existing domain - :nxdomain
00:22:31.314 pid=<0.3052.0> [info] tzdata release in place is from a file last modified Fri, 22 Oct 2021 02:20:47 GMT. Release file on server was last modified Thu, 01 Feb 2024 18:40:48 GMT.
00:22:32.096 pid=<0.3374.0> origin=postgres_1 [error] PostgresConnectorSup failed to start child Electric.Satellite.ClientReconnectionInfo with reason: {%DBConnection.ConnectionError{message: "connection not available and request was dropped from queue after 2000ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:\n\n  1. Ensuring your database is available and that you can connect to it\n  2. Tracking down slow queries and making sure they are running fast enough\n  3. Increasing the pool_size (although this increases resource consumption)\n  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval\n\nSee DBConnection.start_link/2 for more information\n", severity: :error, reason: :queue_timeout}, [{DBConnection, :run, 3, [file: ~c"lib/db_connection.ex", line: 960, error_info: %{module: Exception}]}, {Electric.Satellite.ClientReconnectionInfo, :init, 1, [file: ~c"lib/electric/satellite/client_reconnection_info.ex", line: 855]}, {:gen_server, :init_it, 2, [file: ~c"gen_server.erl", line: 851]}, {:gen_server, :init_it, 6, [file: ~c"gen_server.erl", line: 814]}, {:proc_lib, :init_p_do_apply, 3, [file: ~c"proc_lib.erl", line: 240]}]}.
▓ ┌────────────────────────────────────────────────────────┐
▓ │  MODULE ERROR: Electric.Replication.PostgresConnector  │
▓ ┕━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┙
▓ 
▓ Failed to start child Electric.Satellite.ClientReconnectionInfo:
▓   {%DBConnection.ConnectionError{message: "connection not available and request was dropped from queue after 2000ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:\n\n  1. Ensuring your database is available and that you can connect to it\n  2. Tracking down slow queries and making sure they are running fast enough\n  3. Increasing the pool_size (although this increases resource consumption)\n  4. Allowing requests to wait longer by increasing :queue_target and :queue_interval\n\nSee DBConnection.start_link/2 for more information\n", severity: :error, reason: :queue_timeout}, [{DBConnection, :run, 3, [file: ~c"lib/db_connection.ex", line: 960, error_info: %{module: Exception}]}, {Electric.Satellite.ClientReconnectionInfo, :init, 1, [file: ~c"lib/electric/satellite/client_reconnection_info.ex", line: 855]}, {:gen_server, :init_it, 2, [file: ~c"gen_server.erl", line: 851]}, {:gen_server, :init_it, 6, [file: ~c"gen_server.erl", line: 814]}, {:proc_lib, :init_p_do_apply, 3, [file: ~c"proc_lib.erl", line: 240]}]}
▓ 
▓ Please file a new issue on GitHub[1], including the contents of this error.
▓ 
▓ [1]: https://github.com/electric-sql/electric/issues

[msfstef]

@dreamid27 good to hear IPv6 was sorted!

This second issue is known and is currently being worked on by @alco, you can follow along here: #1278

You can try using electric 0.10.x until this is resolved (with the respective 0.10.x TS client), hopefully that unblocks you right now.

Let me know if that covers you, if so we can close the issue in favour of the one above :)

[dreamid27]

@msfstef great ! thank you btw.