I was recently asked to look at an interesting problem in which nothing can connect to Postgres — to any databases managed by Postgres. All connecting clients would get this rejection error message:
Connections to any database, including the template0 and template1, were rejected.
Searching Google and other search engines, you could find bits and pieces of information, but nothing that is easy to digest. In this article, I compiled the steps required to restore access to the databases without needing to restore from backup.
The Solution
If none of the databases will accept connections, then the easiest fix is to run Postgres in single-user mode. Follow this procedure to fix this:
1) Shutdown Postgres:
systemctl stop postgresql-112) start postgres up in single-user mode
3) quit single user mode and restart Postgres 4) use this for-loop to fix all the databases
This
for loop will loop through all databases and change datallowconn to true, which will re-enable logins to those databases.Last Words
I am not a DBA and I have not needed to run Postgres in single-user mode. This should be a useful mode to make repairs. If you find this useful, let me know.
