Realtime Postgres updates unreliable / not working

[colin-chadwick]

Bug report

Realtime is unable to broadcast most Postgres changes. It sometimes works, but is really unreliable. All other realtime events, for e. g. presence states or cursor positions work as expected.

To Reproduce

Simply deploy the stack on AWS, in my case I deployed it in eu-west-1 region. I chose micro Fargate task size.

Expected behavior

Listening to Postgres changes should work just like in the hosted version.

Screenshots

Maybe this has something to do with realtime not being able to correctly connect to the RDS database? These logs are shown in the realtime logs when trying to watch Postgres changes:

[colin-chadwick]

Why was this removed from the realtime service commands? /bin/sed -i -e "s/127.0.0.1/$(grep $HOSTNAME /etc/hosts | cut -f 1 -d " ")/" /app/releases/*/env.sh If this command isn't present, realtime fails with multiple tasks on Fargate, even using newer images.

This is how the realtime service should look like:

 const realtime = new AutoScalingFargateService(this, 'Realtime', {
      serviceName: 'realtime-dev',
      cluster,
      taskImageOptions: {
        image: ecs.ContainerImage.fromRegistry(realtimeImageUri.valueAsString),
        containerPort: 4000,
        environment: {
          PORT: '4000',
          DB_HOST: db.cluster.clusterEndpoint.hostname,
          DB_PORT: db.cluster.clusterEndpoint.port.toString(),
          DB_AFTER_CONNECT_QUERY: 'SET search_path TO realtime',
          DB_ENC_KEY: 'supabaserealtime',
          ERL_AFLAGS: '-proto_dist inet_tcp',
          FLY_APP_NAME: 'realtime',
          ENABLE_TAILSCALE: 'false',
          DNS_NODES: `realtime-dev.${namespaceName}`,
        },
        secrets: {
          DB_USER: ecs.Secret.fromSecretsManager(
            supabaseAdminSecret,
            'username',
          ),
          DB_PASSWORD: ecs.Secret.fromSecretsManager(
            supabaseAdminSecret,
            'password',
          ),
          DB_NAME: ecs.Secret.fromSecretsManager(supabaseAdminSecret, 'dbname'),
          API_JWT_SECRET: ecs.Secret.fromSecretsManager(jwtSecret),
          SECRET_KEY_BASE: ecs.Secret.fromSecretsManager(cookieSigningSecret),
        },
        entryPoint: ['/usr/bin/tini', '-s', '-g', '--'], // ignore /app/limits.sh
        command: [
          'sh',
          '-c',
          '/bin/sed -i -e "s/127.0.0.1/$(grep $HOSTNAME /etc/hosts | cut -f 1 -d " ")/" /app/releases/*/env.sh && /app/bin/migrate && /app/bin/realtime eval "Realtime.Release.seeds(Realtime.Repo)" && /app/bin/server',
        ],
      },
      highAvailability,
    })

This took me some hours to figure out.