Pg Sync

[ndrean]

Real-time sync for Postgres-backed Phoenix applications.

Seems cool!

I may try this with https://github.com/dwyl/PWA-Liveview instead of using SQLite and y_ex+ Yjs ??

[ndrean]

spike: The simplest possible experiment to explore potential solutions to a problem 💡

eg a "Todo" example? : https://github.com/electric-sql/electric/tree/main/examples/phoenix-liveview

My concern is deploying this. Cloud solution: Fly.io and Postgres..?

[ndrean]

My current setup for on/offline collaborative Stock Manger (very pompous denomination I agree 😬)

flowchart LR
  A[Client <br> offline reactivity] -->|Yjs updates<br> WS| B[YjsChannel <br> Business rules]
  B -->|Save to SQLite| C[(SQLite)]
  C -->|Poll| B
  B -->|Broadcast<br>WS| A

Loading

• client reactivity (on/offline) using a JS framework
• [Yjs+y-indexeddb] using CRDT to merge updates and persist state client-side
• communication via custom websocket to dissociate it from the LiveSocket
• the channel holds the business logic, and then broadcasts accepted changes transparently
• state is persisted in an SQLite database

---

What I understand so far when using ElectricSQL:

Firstly, probably use the embedded mode for Phoenix_sync:

Phoenix_sync __embedded__: 
where Electric is included as an application dependency 
and Phoenix.Sync consumes data internally using Elixir APIs

flowchart LR
  A[Client<br> WASM SQLite] -->|ElectricSQL sync <br> WS| B[ElectricSQL Server <br> Business rules <br> before write]
  B -->|accepted writes|C[(Postgres)]
  C -->|streams| D[Phoenix_sync]
  D --> |PubSub to|E[LiveView]
  E-->|Re-render| A

Loading

• ElectricSQL clients:
  • you bring into each client an embedded SQLite instance (WASM based in the browser or mobile) augmented with CRDT capabilities cr-sqlite

When you use Postgrex, you already can use Postgrex.Notifications, the LISTEN/NOTIFY.
Logical replication is abour row changes.
An example of using Postgrex.Notifications in this same repo

[ndrean]

Multi repos with Phoenix/Ecto

The first time I use 2 repos.
My containers crash, table not found....

I prefer to use Postgres as a container because you need to modify the Postgres config for logical replication.

After a while... because I am using containers (reason in the next comment), I see that my migration(s) does not create tables in fact. So maybe I should use the real repo module name instead of just "Repo"?
=> Yes

‼️ Prefix the module name with the correct repo !

A helper: -r to specify the repo name

mix ecto.gen.migration -f MyApp.RepoOne create_table

will generate the correct module name:

defmodule MyApp.RepoOne.Migrations.CreateTables
  [...]

So to use 2 repos with Phoenix:

defmodule LiveviewPwa.PgRepo do
  use Ecto.Repo,
    otp_app: :liveview_pwa,
    adapter: Ecto.Adapters.Postgres
end

defmodule LiveviewPwa.Sql3Repo do
  use Ecto.Repo,
    otp_app: :liveview_pwa,
    adapter: Ecto.Adapters.SQLite3
end

# config/config.exs
config :liveview_pwa,
  ecto_repos: [LiveviewPwa.PgRepo, LiveviewPwa.Sql3Repo]

config :liveview_pwa, LiveviewPwa.Sql3Repo,
  adapter: Ecto.Adapters.SQLite3,
  [...]

config :liveview_pwa, LiveviewPwa.PgRepo,
  adapter: Ecto.Adapters.Postgres,
  [...]

# the migration files
defmodule LiveviewPwa.Sql3Repo.Migrations.CreateTables do
[...]
end

defmodule LiveviewPwa.PgRepo.Migrations.CreateTables do
[...]
end

and run:

# Application.ex
LiveviewPwa.Release.migrate()

where you iterate over the array of repos:

# LiveviewPwa.Release

load_app()

for repo <- repos() do
   Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :up, all: true))
end

⚠️ When you run a PG container and an "app" container, set a healthcheck in the PG container (❗️ user should be postgres as root), and a service_healthy in the "app" container to run successfully the migration.

pg:
  healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"] #<- admin user ie POSTGRES_USER
      interval: 5s
      timeout: 5s
      retries: 10
app:
  depends_on:
      pg:
        condition: service_healthy

You should see something like this:

I did not run the migrations SQL script in the "docker-entrypoint-initdb" volume to which you can attach an SQL file of your migration but instead let Ecto do this for us via Ecto.Migrator

pg17  | 2025-05-16 17:01:46.084 UTC [54] LOG:  database system is ready to accept connections
pg17  |  done
pg17  | server started
pg17  | CREATE DATABASE
pg17  |
pg17  | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/* <- ! not used here
pg17  | PostgreSQL init process complete; ready for start up.
pg17  | 2025-05-16 17:01:46.459 UTC [1] LOG:  database system is ready to accept connections
web   | 17:01:48.463 [info] == Running 20250516014259 LiveviewPwa.PgRepo.Migrations.CreateTables.change/0 forward
web   | 18:41:48.463 [info] create table electric_counts
web   | 18:41:48.463 [info] create index elect_id_key_unique
web   | 18:41:48.463 [info] == Migrated 20250516014259 in 0.0s
web   | 17:01:48.505 [info] == Running 20250408185725 LiveviewPwa.Sql3Repo.Migrations.CreateTables.change/0 forward
web   | 17:01:48.505 [info] create table if not exists airports
web   | 17:01:48.505 [info] create table if not exists yjs_documents

[ndrean]

To make things easy, the package electric does not compile with mix compile, on OSX...

Plenty of bad messages.

So I tried to run this in Docker, in a Debian system. And it compiles!

It may not be the best dev environment but it works.

A good tool for this is Tilt.

[EDIT]:

Issue

{:electric, "~> 1.0 and >= 1.0.13", override: true},

[ndrean]

I knew LISTEN/NOTIFY but learnt about PUBLICATION/SuBSCRIPTION.

Source: Amazon

[ndrean]

Configure your Postgres container to use logical replication

Amazon (link above) says:

In the source database, edit the postgresql.conf file to add the following parameters.

wal_level = 'logical'
max_replication_slots = 10
max_wal_senders = 10

Make sure that you set `wal_level` to `logical`. 
This adds information necessary to support logical decoding. 

Also, make sure that you set `max_replication_slots` to
at least the number of subscriptions expected to connect,
plus some reserve for table synchronization. 

Finally, make sure that you set `max_wal_senders` to at least the same value
as `max_replication_slots` plus the number of physical replicas that are connected at the same time.

I run a Postgres container with my docker-compose where I set wal_level to logical:

services:
  pg:
    image: postgres:17
    container_name: pg17
    environment:
      # PostgreSQL environment variables are in the form POSTGRES_*
      POSTGRES_PASSWORD: 1234
      POSTGRES_USER: postgres
      POSTGRES_DB: elec_prod
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"] # <- !! admin user is "postgres"
      interval: 5s
      timeout: 5s
      retries: 10
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5000:5432"
    
    command: # <- set variables via a command
      - -c
      - listen_addresses=*
      - -c
      - wal_level=logical
      - -c
      - max_wal_senders=10

This ports settings opens the PG server port to the localhost:5000 to connect via the psql client.
In another terminal, I do:

> PGPASSWORD=1234 psql -h localhost -U postgres -d elec_prod -p 5000

# psql (17.5 (Postgres.app))
# Type "help" for help.

elec_prod=#

and paste the command to check:

elec_prod=# select name, setting from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries');
           name           | setting
--------------------------+---------
 max_replication_slots    | 10
 max_wal_senders          | 10
 max_worker_processes     | 8
 shared_preload_libraries |
 wal_level                | logical
(5 rows)

👍 Looks good so far.