1

after registering the user in the postgres database(in ubuntu) we can execute some basic command like dropdb, createdb directly from the terminal to alter the database.

I basically was creating a shell script to renew the database. So, I thought doing this would suffice:

dropdb veganary_test && createdb veganary_test

as always, I was wrong. Since I had multiple connections to the database, db wouldn't drop. I also tried this:

    psql <database_name> -c "SELECT pg_terminate_backend(pg_stat_activity.pid)
              FROM pg_stat_activity
              WHERE pg_stat_activity.datname = '<database_name>'
              AND pid <> pg_backend_pid();"

and, yet it wasn't successful on disrupting pgadmin's connection. How can I disconnect every user connected to my <database_name> database from the terminal?(bash)

juztcode
  • 1,196
  • 2
  • 21
  • 46

1 Answers1

2

Upgrade to PostgreSQL v13 and use

dropdb --force veganary_test
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • the commands like `dropdb`, `createdb` , that can be executed directly from bash, does postgres have a specific term for them? or an exhaustive list of them somewhere? – juztcode Mar 01 '21 at 14:44
  • 1
    They are called [PostgreSQL client applications](https://www.postgresql.org/docs/current/reference-client.html). – Laurenz Albe Mar 01 '21 at 14:50
  • which this actually drops the table, I don't know if postgres maintains something like a stale reference, after dropping, and immediately setting up relations, I get issues, until I disconnect the database completely and then execute the command. Thats weird. Do you know how to force disconnect all users from all available databases? – juztcode Mar 01 '21 at 15:14
  • I basically just disconnected from localServer and then the command works – juztcode Mar 01 '21 at 15:14