Thread: [GENERAL] "Database does not exist" weirdness
Hi folks,
Please excuse the question if I'm missing something stupid, but I seem to be stuck. I've created a postgres database,
viaan Elixir project setup, and it's showing up via psql's \l command, but I'm seeing "database does not exist"
messages.
Transcript of a session showing this follows below.
Anything I should be looking at, here? What am I missing?
Many thanks in advance,
Michael
pi@raspi ~ $ sudo -u postgres psql
psql (9.4.10)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------+----------+----------+-------------+-------------+---------------------------
testdb | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | testdb=CTc/postgres
postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# \q
pi@raspi ~ $ sudo -u postgres psql testdb
psql: FATAL: database "testdb" does not exist
pi@raspi ~ $ sudo -u postgres createdb testdb
createdb: database creation failed: ERROR: duplicate key value violates unique constraint "pg_database_datname_index"
DETAIL: Key (datname)=(testdb) already exists.
Sent from my iPhone
On Friday 17 February 2017 18:18:20 Michael Tyson wrote: > postgres=# \q > pi@raspi ~ $ sudo -u postgres psql testdb > psql: FATAL: database "testdb" does not exist > pi@raspi ~ $ sudo -u postgres createdb testdb > createdb: database creation failed: ERROR: duplicate key value violates > unique constraint "pg_database_datname_index" DETAIL: Key > (datname)=(testdb) already exists. > Something's seriously borked in your PostgreSQL data files. The message it should give you if the database exists is: createdb: database creation failed: ERROR: database "testdb" already exists It looks like it partially exists in the catalog but not really. I can't guess how you got to such a state, but you should probably nuke your data directory and start over with a fresh initdb.
Michael Tyson <michael@tyson.id.au> writes:
> Anything I should be looking at, here? What am I missing?
> postgres=# \l
> List of databases
> Name | Owner | Encoding | Collate | Ctype | Access privileges
> --------------+----------+----------+-------------+-------------+---------------------------
> testdb | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres +
> | | | | | postgres=CTc/postgres +
> | | | | | testdb=CTc/postgres
> postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
Given the weird spacing you're showing there, I'm suspicious that that
database isn't actually named "testdb", but has some nonprinting
character(s) in its name that confuse psql's formatting code.
An easy way to check for nonprinting characters is to do
set bytea_output to escape;
select datname::bytea from pg_database;
and see if bytea prints anything funny.
Having said that, I don't think that theory explains this result:
> pi@raspi ~ $ sudo -u postgres createdb testdb
> createdb: database creation failed: ERROR: duplicate key value violates unique constraint
"pg_database_datname_index"
> DETAIL: Key (datname)=(testdb) already exists.
There's definitely something weird there, because what you ought to get
in normal cases is
createdb: database creation failed: ERROR: database "testdb" already exists
There may be some corruption in pg_database_datname_index that is allowing
the initial search to not find "testdb" but then the insertion decides
there's a conflict.
regards, tom lane
On 02/16/2017 11:18 PM, Michael Tyson wrote: > Hi folks, > > Please excuse the question if I'm missing something stupid, but I seem to be stuck. I've created a postgres database, viaan Elixir project setup, and it's showing up via psql's \l command, but I'm seeing "database does not exist" messages. > > Transcript of a session showing this follows below. > > Anything I should be looking at, here? What am I missing? In addition to what has already been asked, I am assuming from the system prompts this is happening on a Raspberry Pi, correct? If so what is being used for storage and have there been any issues with said storage, eg someone pulling a SD card out at the wrong time? > > Many thanks in advance, > Michael > > > > pi@raspi ~ $ sudo -u postgres psql > psql (9.4.10) > Type "help" for help. > > postgres=# \l > List of databases > Name | Owner | Encoding | Collate | Ctype | Access privileges > --------------+----------+----------+-------------+-------------+--------------------------- > testdb | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/postgres + > | | | | | postgres=CTc/postgres + > | | | | | testdb=CTc/postgres > postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | > template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres + > | | | | | postgres=CTc/postgres > template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres + > | | | | | postgres=CTc/postgres > (4 rows) > > postgres=# \q > pi@raspi ~ $ sudo -u postgres psql testdb > psql: FATAL: database "testdb" does not exist > pi@raspi ~ $ sudo -u postgres createdb testdb > createdb: database creation failed: ERROR: duplicate key value violates unique constraint "pg_database_datname_index" > DETAIL: Key (datname)=(testdb) already exists. > > > > > > > > > Sent from my iPhone > > > > -- Adrian Klaver adrian.klaver@aklaver.com