Unable to drop Postgres database

Question

I am learning Postgres and went through the getting started documentation to create a database called testDb and a role called testUser. The testDb database was created with the owner testUser using my superuser account ciesinsg.

I want to delete testDb and testUser as I am no longer using them, but I am unable to delete the testDb database no matter what I try.

My \l and \du output is at the very bottom of this question if needed.

What I've Tried

DROP DATABASE

First, I tried connecting to my postgres database and running the DROP DATABASE command.

psql postgres
DROP DATABASE testDb;

This outputted ERROR: database "testdb" does not exist even though that is the name when I use \l.

Stack Overflow QA's

I searched through stack overflow for similar issues.

1. This QA advised that only a superuser or owner of the database can delete it, which is what my ciesinsg role is. As I understand it, this is the role used to login when I run psql postgres.

I tried instead logging in with psql -U ciesinsg postgres which worked, but when I ran DROP DATABASE testDb; it outputs the same error as before.

2. I found another QA which looked promising. This one had an answer that suggested to run:

sudo service postgresql stop
sudo service postgresql start

psql
DROP DATABASE DB_NAME;

On my setup, this looked like:

brew services stop postgresql@15
brew services start postgresql@15

psql postgres
DROP DATABASE testDb;

But this also outputted the same error.

Login as Owner

Next I tried to login as owner: psql -U testUser testDb which worked and it showed testDb=> where it expects new commands.

I tried:

testDb=> DROP DATABASE testDb;

which outputs:

testDb=> DROP DATABASE testDb;
ERROR:  database "testdb" does not exist

Using PGAdmin4

So next, I referred to this QA and tried to use PGAdmin4 to delete it. Unfortunately, the check mark is greyed out beside testDb so I was unable to select it for deletion.

Successful Solution

I am updating this with the solution that worked, but I am keeping the question open to understand why all of my other solutions failed.

After much searching, I found this QA which recommended to run dropdb 'database_name'. I tried this but it said one client was still accessing the database, so I ran:

brew services stop postgresql@15
brew services start postgresql@15
dropdb 'testDb'

After running \l I saw it was finally deleted.

Conclusion

After referring to the documentation, stack overflow, and trying numerous solutions, the only one that worked was the dropdb command.

I want to understand why all of the other methods I attempted failed as I was following documentation and answers. I want to know what I was doing wrong.

Database and Role

My output for \l and \du is below

postgres-# \l
                                                List of databases
    Name    |     Owner      | Encoding | Collate | Ctype | ICU Locale | Locale Provider |   Access privileges
------------+----------------+----------+---------+-------+------------+-----------------+-----------------------
 itsGallery | itsGalleryUser | UTF8     | C       | C     |            | libc            |
 postgres   | ciesinsg       | UTF8     | C       | C     |            | libc            |
 template0  | ciesinsg       | UTF8     | C       | C     |            | libc            | =c/ciesinsg          +
            |                |          |         |       |            |                 | ciesinsg=CTc/ciesinsg
 template1  | ciesinsg       | UTF8     | C       | C     |            | libc            | =c/ciesinsg          +
            |                |          |         |       |            |                 | ciesinsg=CTc/ciesinsg
 testDb     | testUser       | UTF8     | C       | C     |            | libc            |
(5 rows)

postgres-# \du
                                      List of roles
   Role name    |                         Attributes                         | Member of
----------------+------------------------------------------------------------+-----------
 ciesinsg       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 itsGalleryUser | Create role, Create DB, Replication                        | {}
 testUser       | Create DB                                                  | {}

Additional Info

• Macbook Pro M2 with Ventura 13.4.1
• Postgresql@15 and PGAdmin4 installed using homebrew
• The Homebrew formula sets my ciesinsg user up as a superuser, so there is no postgres user/role to my knowledge
• testDb and testUser created using CLI when learning to use Postgres
• PGAdmin4 connected to the database afterwards, so not prior to initial creation of database or user
• I am new to Postgres and still trying to learn the basics