Postgres CREATE DATABASE Not Working



  • I'm on a Mac using Monterey. It's a Mac M1 which had a bit of an issue using homebrew to install postgres when I first started working on this machine. I moved to the GUI postgres.app which just seemed like a front for the underlying postgres, so I didn't think much had changed.

    In any case the database (Postgres 12) is now running and I am seeing an issue with trying to create a new database. Same issue in a third party client as well as psql.

    rich=# create database sresearch
    rich-# \l
                                       List of databases
         Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
    --------------+----------+----------+-------------+-------------+-----------------------
     postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
     rich         | rich     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
     sresearch    | rich     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
     template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                  |          |          |             |             | postgres=CTc/postgres
     weather      | rich     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
    (5 rows)
    

    rich-# \c sresearch
    FATAL: no such database: sresearch

    The database cannot be made for some reason, even though it's reporting having done so. I have no clue why this is happening, but I want to fix it.

    I'm thinking I could make a backup and try to fix this by deleting both versions, then going back to homebrew to start again with Postgres 12. I'm not sure how to find out what the difference is between these two slightly different versions, which are essentially the same thing.

    How can I fix this? Obviously I need to be able to make new databases.

    Update: I just reinstalled the homebrew version and tried to import my alternative postgres.app export. It hung up on \connect for some reason but that was easy to manually get around. This installation as well gave me the same error on trying to create a new database. So both versions are showing the same, even from an export.

    I have no clue what to try.

    rich=# \c sres
    FATAL:  no such database: sres             # Indeed there is though
    Previous connection kept
    rich=#
    

    Updated: More info ==>

    rich=# \l
                                    List of databases
         Name     | Owner | Encoding |   Collate   |    Ctype    | Access privileges
    --------------+-------+----------+-------------+-------------+-------------------
     postgres     | rich  | UTF8     | C           | C           |
     rich         | rich  | UTF8     | C           | C           |
     sres         | rich  | UTF8     | C           | C           |
     sresearch    | rich  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
     template0    | rich  | UTF8     | C           | C           | =c/rich          +
                  |       |          |             |             | rich=CTc/rich
     template1    | rich  | UTF8     | C           | C           | =c/rich          +
                  |       |          |             |             | rich=CTc/rich
    (6 rows)
    

    rich=# \c sres;
    FATAL: no such database: sres
    Previous connection kept
    rich=# \s sresearch;
    Wrote history to file "sresearch".
    rich=#

    Here is the feedback from Sequel:

    Commands from Ruby

    Here are the results from Ruby

    enter image description here

    Updated. Even more of the same. ==>

    rich=# CREATE DATABASE alpha;
    CREATE DATABASE
    rich=# \c alpha;
    FATAL:  no such database: alpha
    Previous connection kept
    rich=# \l
                                    List of databases
         Name     | Owner | Encoding |   Collate   |    Ctype    | Access privileges
    --------------+-------+----------+-------------+-------------+-------------------
     alpha        | rich  | UTF8     | C           | C           |
     postgres     | rich  | UTF8     | C           | C           |
     rich         | rich  | UTF8     | C           | C           |
     template0    | rich  | UTF8     | C           | C           | =c/rich          +
                  |       |          |             |             | rich=CTc/rich
     template1    | rich  | UTF8     | C           | C           | =c/rich          +
                  |       |          |             |             | rich=CTc/rich
    (5 rows)
    

    rich=# DROP DATABASE alpha;
    DROP DATABASE
    rich=# \l
    List of databases
    Name | Owner | Encoding | Collate | Ctype | Access privileges
    --------------+-------+----------+-------------+-------------+-------------------
    postgres | rich | UTF8 | C | C |
    rich | rich | UTF8 | C | C |
    template0 | rich | UTF8 | C | C | =c/rich +
    | | | | | rich=CTc/rich
    template1 | rich | UTF8 | C | C | =c/rich +
    | | | | | rich=CTc/rich
    (4 rows)

    rich=# CREATE DATABASE alpha;
    CREATE DATABASE
    rich=# \l
    List of databases
    Name | Owner | Encoding | Collate | Ctype | Access privileges
    --------------+-------+----------+-------------+-------------+-------------------
    alpha | rich | UTF8 | C | C |
    postgres | rich | UTF8 | C | C |
    rich | rich | UTF8 | C | C |
    template0 | rich | UTF8 | C | C | =c/rich +
    | | | | | rich=CTc/rich
    template1 | rich | UTF8 | C | C | =c/rich +
    | | | | | rich=CTc/rich
    (5 rows)

    rich=# \c alpha;
    FATAL: no such database: alpha
    Previous connection kept
    rich=#



  • The culprit was pgbouncer and the .ini file, which needs individual entries for each listed database. Creating the database was ok, but then the .ini file didn't recognize the new database name.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2