"Database does not exist" weirdness

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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, via an 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general