Cannot write to clustered db

Questions / Help
, ,
1

This is a just-deployed elixir/phoenix project, and I want to restore the db with some seed data. the db is clustered over three instances and when I do

flyctl proxy 5454:5433 -a tyndale-app-db

then

pg_restore -p 5454 -h localhost -U postgres -d tyndale_app seeded.sql in another terminal I get:

pg_restore: error: could not execute query: ERROR: cannot execute CREATE EXTENSION in a read-only transaction

How do I write to the clustered db? I tried

ALTER DATABASE tyndale_app SET default_transaction_only TO off;

but that gave me the error:

ERROR: unrecognized configuration parameter "default_transaction_only"

regards,
Michael

2

Hi… Probably you just got randomly connected to one of the read replicas. By default,
flyctl proxy uses the .internal address, which doesn’t distinguish among the three.

Try specifying the Flycast address instead:

$ fly proxy 5454:5432 tyndale-app-db.flycast

(May need tweaking depending on local details.)

Hope this helps!

3

So for these settings:

Postgres cluster tyndale-app-db created
  Username:    postgres
  Password:    asdf
  Hostname:    tyndale-app-db.internal
  Flycast:     fdaa:0:3adb:0:1::2c
  Proxy port:  5432
  Postgres port:  5433
  Connection string: postgres://postgres:asdf@tyndale-app-db.flycast:5432

Strangely I see this:

fly proxy 5454:5433 -a tyndale-app-db.flycast
Error: Could not find App "tyndale-app-db.flycast"

fly doesn’t seem to be able to see tyndale-app-db.flycast

any ideas?

4

You actually want to remove the -a, since this is an address now rather than an app name.

(I’m assuming that you’re running this from within the directory that has the fly.toml for your Phoenix application, in which case it shouldn’t need any extra hints to find the right network.)

Also, be sure to use port 5432 instead of 5433; the former is what guarantees you a writable connection (which is why it’s in the Connection string provided above).

The addressing setup with Fly Postgres is a little complicated, :dragon:, in part because there are two layers of proxying and in part because it accumulated over time—and not all of the messages were subsequently re-optimized, etc.

7

@mayailurus
Thanks, that was it. If somebody else hits this thread here is a little recipe. YMMV

# swap-in dev db to prod
from 
/Applications/Postgres.app/Contents/Versions/16/bin/pg_dump -U postgres --no-owner --no-privileges --no-acl -W -Fc -C -d tv_dev > seeded.sql

fly postgres connect -a tyndale-app-db

psql> CREATE ROLE tycho WITH LOGIN SUPERUSER PASSWORD 'asdf';
psql> drop database tyndale_app with (force);
psql> create database tyndale_app;
psql> SET session_replication_role = replica;
\q

(first terminal)
fly proxy 5454:5432 tyndale-app-db.flycast

(another terminal)
/Applications/Postgres.app/Contents/Versions/16/bin/pg_restore -p 5454 -h localhost -U postgres -d tyndale_app seeded.sql

fly postgres connect -a tyndale-app-db

psql> SET session_replication_role = DEFAULT;
\q
1 Like
8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.