alter table fails at "ELECTRIC"

[knownasilya]

Running PG via the docker command on the site. Table exists as seen in the screenshot.

[linear[bot]]

VAX-1996 alter table fails at "ELECTRIC"

[alco]

Hey @knownasilya :wave:

Please double-check that you connect your database client to Electric's Migrations proxy when running migrations. See https://electric-sql.com/docs/usage/data-modelling/migrations#migrations-proxy for details.

[knownasilya]

Hum, yes having trouble. Can connect via sql client, but electric doesn't seem to want to connect

I even tried pasting the connection url into the sql client and it connected fine.

[alco]

@knownasilya The IP address 127.0.0.1 refers to the localhost inside the Docker container whereas your Postgres database listening on port 5432 on the host OS. Use host.docker.internal as the hostname in your DATABASE_URL and add the --add-host host.docker.internal:host-gateway flag to your docker run invocation.

[knownasilya]

That helped, now getting ▓ Your Postgres database is not configured with wal_level=logical. Running pg with:

docker run \
        -e "POSTGRES_PASSWORD=password" \
        -e "wal_level=logical" \
        -p 5432:5432 \
        postgres

[knownasilya]

I switched it to

docker run \
        -e "POSTGRES_PASSWORD=password" \
        -p 5432:5432 \
        postgres -c \
        "wal_level=logical"

And that worked, but now seeing this (pg logs):

PostgreSQL init process complete; ready for start up.

2024-06-29 19:48:27.113 UTC [1] LOG:  starting PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-06-29 19:48:27.113 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-06-29 19:48:27.113 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2024-06-29 19:48:27.115 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-06-29 19:48:27.117 UTC [62] LOG:  database system was shut down at 2024-06-29 19:48:26 UTC
2024-06-29 19:48:27.119 UTC [1] LOG:  database system is ready to accept connections
2024-06-29 19:48:39.543 UTC [66] FATAL:  database "afd" does not exist
2024-06-29 19:49:36.762 UTC [71] ERROR:  publication "electric_publication" already exists
2024-06-29 19:49:36.762 UTC [71] STATEMENT:  CREATE PUBLICATION "electric_publication" FOR TABLE "electric"."ddl_commands", "electric"."electrified", "electric"."transaction_marker", "electric"."grants", "electric"."roles", "electric"."assignments", "electric"."acknowledged_client_lsns"
2024-06-29 19:49:36.805 UTC [82] LOG:  logical decoding found consistent point at 0/1A53970
2024-06-29 19:49:36.805 UTC [82] DETAIL:  There are no running transactions.
2024-06-29 19:49:36.805 UTC [82] STATEMENT:  CREATE_REPLICATION_SLOT "electric_replication_out_afd" LOGICAL pgoutput NOEXPORT_SNAPSHOT
2024-06-29 19:49:36.827 UTC [82] LOG:  starting logical decoding for slot "electric_replication_out_afd_rc"
2024-06-29 19:49:36.827 UTC [82] DETAIL:  Streaming transactions committing after 0/1A539A8, reading WAL from 0/1A53970.
2024-06-29 19:49:36.827 UTC [82] STATEMENT:  START_REPLICATION SLOT electric_replication_out_afd_rc LOGICAL 0/1A539A8 (proto_version '1', publication_names 'electric_publication', messages)
2024-06-29 19:49:36.827 UTC [82] LOG:  logical decoding found consistent point at 0/1A53970
2024-06-29 19:49:36.827 UTC [82] DETAIL:  There are no running transactions.
2024-06-29 19:49:36.827 UTC [82] STATEMENT:  START_REPLICATION SLOT electric_replication_out_afd_rc LOGICAL 0/1A539A8 (proto_version '1', publication_names 'electric_publication', messages)
2024-06-29 19:49:36.865 UTC [86] ERROR:  subscription "postgres_1" does not exist
2024-06-29 19:49:36.865 UTC [86] STATEMENT:  ALTER SUBSCRIPTION "postgres_1" DISABLE

I did create "afd" database

electric logs:

19:49:36.764 pid=<0.3375.0> [info] Starting Proxy server listening on port 65432
19:49:36.789 pid=<0.3402.0> pg_producer=postgres_1 [info] Starting Elixir.Electric.Postgres.Extension.SchemaCache for postgres_1
19:49:36.789 pid=<0.3402.0> pg_producer=postgres_1 [warning] SchemaCache "postgres_1" registered as the global instance
19:49:36.789 pid=<0.3405.0> pg_producer=postgres_1 [info] Starting replication with publication=electric_publication and slots=electric_replication_out_afd,electric_replication_out_afd_rc}
19:49:36.839 pid=<0.3409.0> pg_producer=postgres_1 [info] Starting Elixir.Electric.Replication.Postgres.MigrationConsumer using Elixir.Electric.Postgres.Extension.SchemaCache backend
19:49:36.853 pid=<0.3370.0> origin=postgres_1 [info] Successfully initialized Postgres connector "postgres_1".
19:49:36.853 pid=<0.3370.0> origin=postgres_1 [info] Postgres.Client.with_conn(%{timeout: 5000, port: 5432, ssl: false, host: ~c"host.docker.internal", ipv6: false, password: ~c"******", username: ~c"postgres", database: ~c"afd", ssl_opts: [server_name_indication: ~c"host.docker.internal"], nulls: [nil, :null, :undefined], ip_addr: ~c"192.168.65.254"})
19:49:36.866 pid=<0.3370.0> origin=postgres_1 [warning] couldn't stop postgres subscription: subscription "postgres_1" does not exist (code: 42704)

[alco]

@knownasilya Except for the database "afd" does not exist, the other errors are benign and can be ignored. I'm not sure what is FATAL about that error why it's logged. From the rest of the logs everything looks good. Do you experience any issues in setting up Electric with your local-first app now that you have it successfully connected to the database?

[knownasilya]

I didn't even try to connect, since I thought those were legit errors. Will give it a try tonight. Thanks for your help @alco

[knownasilya]

Guess the only issue is I still can't run ALTER TABLE places ENABLE ELECTRIC;, same error as before.

[alco]

@knownasilya Syntax error means the statement is processed directly by Postgres and not by Electric proxy. Which database URL are you using in your UI?