ERROR: source database DB_NAME is being accessed by other users (High Availability problem?)

Questions / Help
1

Hi, I am migrating our databases and applications from AWS to fly.io, and I recently set up a High Availability Postgres cluster (3x shared-cpu-2x, 4GB RAM). I uploaded dump files for our two main databases to the cluster, but when I try to drop a database or copy a database with CREATE DATABASE copy TEMPLATE src I get an error:

ERROR:  source database "DB_NAME" is being accessed by other users
DETAIL:  There is 1 other session using the database.

The only connection I have is with psql over fly proxy, but when I look at the app’s dashboard I see a minimum of three connections to all the databases on the cluster. And I’m wondering if this is because of the replication being done by the High Availability system? And if that is the case, what is the proper way to temporarily shut down replication?

2

It turned out that this was indeed a problem caused by the High Availability Postgres system that fly.io uses. Here is what I discovered:

  • If you need to dump a database with pg_dump it just works - no problems at all

  • If you need to drop a database the HA system will interfere, but I think you can just do:
    DROP DATABASE foo WITH (FORCE); // only works with newer Postgres versions
    However I did not personally verify this

  • If you need to copy a database, then you will need the following workaround:

    • In the fly.io Dashboard for the Postgres installation, click on the Monitoring tab and note the Machine IDs for the two replicas
    • kill the replica machines: fly machine stop MACHINE_ID_A MACHINE_ID_B
      Note that the primary will only have 2/3 health checks passing and will be marked as being in a zombie state, but it still operates normally
    • fly proxy -a YOUR_PG_APP - this assumes you have nothing running on port 5433, which is the port for the actual primary Postgres instance
    • psql -U postgres -h localhost -p 5433
    • UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'db_to_copy';
    • SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'db_to_copy';
    • CREATE DATABASE database_copy OWNER database_owner TEMPLATE db_to_copy;
    • UPDATE pg_database SET datallowconn = 'true' WHERE datname = 'db_to_copy';
    • quit psql: \q
    • kill the fly proxy with Ctrl-C if it is no longer needed
    • restart the replica machines: fly machine start MACHINE_ID_A MACHINE_ID_B
    • check the monitoring tab on the dashboard to make sure the postgres instance is again healthy
3

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