'Postgres cannot create database but can create a user
I am using Ubuntu Linux. I am trying to use Postgres as database. It is doing fine when I created a user:
CREATE USER username;
But when I try to create a database, it returns nothing:
CREATE DATABASE databasename;
What is happening with my Postgres?
datid | datname | pid | leader_pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query_id | query | backend_type
-------+----------+------+------------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+----------+---------------------------------+------------------------------
| | 8237 | | | | | | | | 2022-02-02 13:00:47.683187+07 | | | | Activity | AutoVacuumMain | | | | | | autovacuum launcher
| | 8239 | | 10 | postgres | | | | | 2022-02-02 13:00:47.70127+07 | | | | Activity | LogicalLauncherMain | | | | | | logical replication launcher
13726 | postgres | 8329 | | 10 | postgres | psql | | | -1 | 2022-02-02 13:08:52.250244+07 | 2022-02-02 13:09:10.651383+07 | 2022-02-02 13:09:10.651383+07 | 2022-02-02 13:09:10.651393+07 | Lock | object | active | | 740 | | CREATE DATABASE kong; | client backend
13726 | postgres | 8313 | | 10 | postgres | psql | | | -1 | 2022-02-02 13:04:57.265085+07 | 2022-02-02 13:10:40.097817+07 | 2022-02-02 13:10:40.097817+07 | 2022-02-02 13:10:40.09782+07 | | | active | | 740 | | SELECT * FROM pg_stat_activity; | client backend
| | 8235 | | | | | | | | 2022-02-02 13:00:47.664058+07 | | | | Activity | BgWriterHibernate | | | | | | background writer
| | 8234 | | | | | | | | 2022-02-02 13:00:47.654713+07 | | | | Activity | CheckpointerMain | | | | | | checkpointer
| | 8236 | | | | | | | | 2022-02-02 13:00:47.673631+07 | | | | Activity | WalWriterMain | | | | | | walwriter
(7 rows)
(END)
and for the pg_locks
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+------------------+---------+----------+-------------------------------
relation | 13726 | 12290 | | | | | | | | 7/17 | 8313 | AccessShareLock | t | t |
virtualxid | | | | | 7/17 | | | | | 7/17 | 8313 | ExclusiveLock | t | t |
virtualxid | | | | | 3/15 | | | | | 3/15 | 8329 | ExclusiveLock | t | t |
virtualxid | | | | | 6/12 | | | | | 6/12 | 8335 | ExclusiveLock | t | t |
virtualxid | | | | | 5/3 | | | | | 5/3 | 8266 | ExclusiveLock | t | t |
virtualxid | | | | | 4/1 | | | | | 4/1 | 8264 | ExclusiveLock | t | t |
object | 0 | | | | | | 1262 | 1 | 0 | 6/12 | 8335 | RowExclusiveLock | f | f | 2022-02-02 13:09:30.561821+07
object | 0 | | | | | | 1262 | 1 | 0 | 3/15 | 8329 | ShareLock | f | f | 2022-02-02 13:09:10.651571+07
object | 0 | | | | | | 1262 | 1 | 0 | 4/1 | 8264 | RowExclusiveLock | t | f |
relation | 0 | 1262 | | | | | | | | 3/15 | 8329 | AccessShareLock | t | f |
object | 0 | | | | | | 1262 | 1 | 0 | 5/3 | 8266 | RowExclusiveLock | t | f |
(11 rows)
(END)
Database info
postgres=# \l
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+---------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 8529 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 8377 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 8529 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
kong | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
Solution 1:[1]
Using the same database name and user is not good practice. This may result in various errors.
When you call the command
CREATE DATABASE databaseName;
PostgreSql creates a database. This may take some time. After creating the database, you will receive a message:
CREATE DATABASE
postgres=#
Solution 2:[2]
Problem solved by reinstalling the pg to its old version (was installed 14, downgrade to 12 and it solved). Thanks to everyone here who helped me
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | On_Luck |
| Solution 2 | ioiofadhil |