Error during pg_upgrade Process: 'database is being accessed by other users'

Question

During the pg_upgrade process, I encountered an error indicating that the "postgres" database is being accessed by other users, with one active session identified.

pg_restore: error: could not execute query: ERROR: database "postgres" is being accessed by other users DETAIL: There is 1 other session using the database. Command was: DROP DATABASE "postgres";

However, upon querying the active connections using "SELECT * FROM pg_stat_activity WHERE datname = 'postgres';", I observed two outputs: one representing the current active connection and another related to the foreign data wrapper (FDW) connection.

I'm uncertain whether the error is triggered by the FDW connection, as its state in the output from pg_stat_activity is not marked as active. Can someone please clarify why this error occurs?

(FYI: I am migrating from PG11 to PG14)

Answer 1

The problem are not active connections, any connection is a problem. You cannot drop a database to which you or anything else are connected. You'd have to connect to a different database first and terminate the FDW connection.

I am somewhat uncertain about what you want to do. If you want to upgrade with pg_upgrade, you have to shut down the old and the new cluster first, so there cannot be any connections anyway.

If you did all that and you still have the problem, it must be some extension that starts at server startup time and creates connections that disturb pg_upgrade. To avoid that, set shared_preload_libraries empty on both servers before running pg_upgrade.