Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
Thanks, I will check it out. On Fri 17 Jul, 2020, 7:42 AM Christophe Pettus > > > On Jul 16, 2020, at 19:08, Naresh Kumar wrote: > > > > Thanks Christopher, if you any such contacts can you share with us. > > The community maintains this page; I'm sure you can find someone who can > help you there: > > https://www.postgresql.org/support/professional_support/ > > -- > -- Christophe Pettus >x...@thebuild.com > >
Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
> On Jul 16, 2020, at 19:08, Naresh Kumar wrote: > > Thanks Christopher, if you any such contacts can you share with us. The community maintains this page; I'm sure you can find someone who can help you there: https://www.postgresql.org/support/professional_support/ -- -- Christophe Pettus x...@thebuild.com
Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
Thanks Christopher, if you any such contacts can you share with us. Regards, Narresh On Fri 17 Jul, 2020, 7:04 AM Christophe Pettus > > > On Jul 16, 2020, at 18:32, Naresh Kumar wrote: > > > > Can some one help us on this please. > > To be clear, what you almost certainly have here is serious data > corruption. You will need to find a (paid, commercial) specialist to help > you with the recovery. You are unlikely to get the level of support you > need on this list, for free. > > -- > -- Christophe Pettus >x...@thebuild.com > >
Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
> On Jul 16, 2020, at 18:32, Naresh Kumar wrote: > > Can some one help us on this please. To be clear, what you almost certainly have here is serious data corruption. You will need to find a (paid, commercial) specialist to help you with the recovery. You are unlikely to get the level of support you need on this list, for free. -- -- Christophe Pettus x...@thebuild.com
Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
Yes Karsten, Can some one help us on this please. Regards, Narresh On Wed 15 Jul, 2020, 10:55 PM Karsten Hilbert On Wed, Jul 15, 2020 at 10:03:06PM +0530, Naresh Kumar wrote: > > > Already I tried to connect with template0, template1, user database name, > > postgres, nothing is working getting same error message as mentioned in > > trail mail. > > > > For the second one, yes we don't have backups to restore, we don't have > any > > option apart from data directory(no wal files) > > At this point you are very likely in need of (highly) > specialized professional help. > > Karsten > -- > GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B > > >
Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
On Wed, Jul 15, 2020 at 10:03:06PM +0530, Naresh Kumar wrote: > Already I tried to connect with template0, template1, user database name, > postgres, nothing is working getting same error message as mentioned in > trail mail. > > For the second one, yes we don't have backups to restore, we don't have any > option apart from data directory(no wal files) At this point you are very likely in need of (highly) specialized professional help. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Re: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
Thanks Denial, Already I tried to connect with template0, template1, user database name, postgres, nothing is working getting same error message as mentioned in trail mail. For the second one, yes we don't have backups to restore, we don't have any option apart from data directory(no wal files) Regards, Narresh On Wed 15 Jul, 2020, 3:28 PM Daniel Verite TALLURI Nareshkumar wrote: > > > [0]postgres@axmdevhkdb008$ [PHKGAXMD008] psql > > psql: FATAL: database "postgres" does not exist > > That's not necessarily a problem. The "postgres" database is generally > empty and some installations don't even have one. > Use "psql -d template1" instead, or start postgres in single-user mode. > > > Since we are not able to connect to DB , we did a new initialization and > > copy the directories from base directory to new base directory(in new > data > > location) > > This can't work. If you have a backup from just before that point, > you should restore it and work from that without writing anything into > the data directory. > > > Best regards, > -- > Daniel Vérité > PostgreSQL-powered mailer: https://www.manitou-mail.org > Twitter: @DanielVerite > > >
RE: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
TALLURI Nareshkumar wrote: > [0]postgres@axmdevhkdb008$ [PHKGAXMD008] psql > psql: FATAL: database "postgres" does not exist That's not necessarily a problem. The "postgres" database is generally empty and some installations don't even have one. Use "psql -d template1" instead, or start postgres in single-user mode. > Since we are not able to connect to DB , we did a new initialization and > copy the directories from base directory to new base directory(in new data > location) This can't work. If you have a backup from just before that point, you should restore it and work from that without writing anything into the data directory. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: https://www.manitou-mail.org Twitter: @DanielVerite
RE: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
Hello Team, Can some one help us on this please , Actually we are blocked . Regards, Narresh From: TALLURI Nareshkumar SgscGtsPasDre Sent: Tuesday, July 14, 2020 5:20 PM To: pgsql-general@lists.postgresql.org Subject: psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index" Hello Postgres Support Team, Today we have an outage, our DB was wend down due to 100% space full at FS. We added space and could able to bring the cluster. DB version: psql (PostgreSQL) 10.12 OS version : Red Hat Enterprise Linux Server release 7.8 (Maipo) [0]postgres@axmdevhkdb008$ [PHKGAXMD008] pg_ctl start -D /AXMDEVHKDB008/postgres/PHKGAXMD008_bck waiting for server to start2020-07-14 08:50:42.273 CEST db:[] user:[] [] [0] LOG: listening on IPv4 address "176.5.88.68", port 5433 2020-07-14 08:50:42.278 CEST db:[] user:[] [] [0] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433" 2020-07-14 08:50:42.280 CEST db:[] user:[] [] [0] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2020-07-14 08:50:42.546 CEST db:[] user:[] [] [0] LOG: redirecting log output to logging collector process 2020-07-14 08:50:42.546 CEST db:[] user:[] [] [0] HINT: Future log output will appear in directory "log". done server started __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base [0]postgres@axmdevhkdb008$ [PHKGAXMD008] psql psql: FATAL: database "postgres" does not exist __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base [2]postgres@axmdevhkdb008$ [PHKGAXMD008] ps -ef|grep postgres root 16567 16513 0 14:12 pts/100:00:00 sudo su - postgres root 16569 16567 0 14:12 pts/100:00:00 su - postgres postgres 16571 16569 0 14:12 pts/100:00:00 -ksh postgres 23888 1 0 14:50 pts/100:00:00 /opt/rh/rh-postgresql10/root/usr/bin/postgres -D /AXMDEVHKDB008/postgres/PHKGAXMD008_bck postgres 23890 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: logger process postgres 23892 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: checkpointer process postgres 23893 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: writer process postgres 23894 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: wal writer process postgres 23895 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: autovacuum launcher process postgres 23896 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: stats collector process postgres 23897 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: bgworker: logical replication launcher postgres 24689 16571 0 14:55 pts/100:00:00 ps -ef postgres 24690 16571 0 14:55 pts/100:00:00 grep --color=auto postgres __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base We have the directories at Data location. [2]postgres@axmdevhkdb008$ [PHKGAXMD008] ls -lrt total 180 drwx-- 2 postgres postgres 4096 Jan 2 2020 1 drwx-- 2 postgres postgres 4096 Jan 2 2020 13211 drwx-- 2 postgres postgres 4096 Jul 13 09:51 pgsql_tmp drwx-- 2 postgres postgres 139264 Jul 13 21:02 16389 drwx-- 2 postgres postgres 12288 Jul 13 21:02 13212 drwx-- 2 postgres postgres 4096 Jul 14 11:08 13213 drwx-- 2 postgres postgres 12288 Jul 14 11:08 13214 __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base How to overcome this ? Note: we don't have any wal files, it is ok for us if we could bring it up with our recover. 2 nd one: Since we are not able to connect to DB , we did a new initialization and copy the directories from base directory to new base directory(in new data location) And update pg_catalog.pg_database table with the DB name (postgres@[local]:5433)# [PHKGAXMP003]INSERT INTO -- # pg_catalog.pg_database( -- # datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn, -- # datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl) -- # VALUES( -- # -- Write Your collation -- # 'axiom', 10, 0, 'C', 'C', -- # False, True, -1, 16389, '536', '1', 1663, Null); INSERT 16384 1 Time: 70.239 ms (postgres@[local]:5433)# [PHKGAXMP003]select oid from pg_database a where a.datname = 'axiom'; oid --- 16384 (1 row) After this we could able to see the DB axiom. Name| Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace |Description ---+--+---+-+---+---+-++ axiom | postgres | SQL_ASCII | C | C | | 16 GB | pg_default | postgres | postgres | SQL_ASCII | C | C | | 7647 kB | pg_default | default administrat
psql: FATAL: database "postgres" does not exist or ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index"
Hello Postgres Support Team, Today we have an outage, our DB was wend down due to 100% space full at FS. We added space and could able to bring the cluster. DB version: psql (PostgreSQL) 10.12 OS version : Red Hat Enterprise Linux Server release 7.8 (Maipo) [0]postgres@axmdevhkdb008$ [PHKGAXMD008] pg_ctl start -D /AXMDEVHKDB008/postgres/PHKGAXMD008_bck waiting for server to start2020-07-14 08:50:42.273 CEST db:[] user:[] [] [0] LOG: listening on IPv4 address "176.5.88.68", port 5433 2020-07-14 08:50:42.278 CEST db:[] user:[] [] [0] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433" 2020-07-14 08:50:42.280 CEST db:[] user:[] [] [0] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2020-07-14 08:50:42.546 CEST db:[] user:[] [] [0] LOG: redirecting log output to logging collector process 2020-07-14 08:50:42.546 CEST db:[] user:[] [] [0] HINT: Future log output will appear in directory "log". done server started __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base [0]postgres@axmdevhkdb008$ [PHKGAXMD008] psql psql: FATAL: database "postgres" does not exist __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base [2]postgres@axmdevhkdb008$ [PHKGAXMD008] ps -ef|grep postgres root 16567 16513 0 14:12 pts/100:00:00 sudo su - postgres root 16569 16567 0 14:12 pts/100:00:00 su - postgres postgres 16571 16569 0 14:12 pts/100:00:00 -ksh postgres 23888 1 0 14:50 pts/100:00:00 /opt/rh/rh-postgresql10/root/usr/bin/postgres -D /AXMDEVHKDB008/postgres/PHKGAXMD008_bck postgres 23890 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: logger process postgres 23892 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: checkpointer process postgres 23893 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: writer process postgres 23894 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: wal writer process postgres 23895 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: autovacuum launcher process postgres 23896 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: stats collector process postgres 23897 23888 0 14:50 ?00:00:00 postgres: PHKGAXMD008: bgworker: logical replication launcher postgres 24689 16571 0 14:55 pts/100:00:00 ps -ef postgres 24690 16571 0 14:55 pts/100:00:00 grep --color=auto postgres __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base We have the directories at Data location. [2]postgres@axmdevhkdb008$ [PHKGAXMD008] ls -lrt total 180 drwx-- 2 postgres postgres 4096 Jan 2 2020 1 drwx-- 2 postgres postgres 4096 Jan 2 2020 13211 drwx-- 2 postgres postgres 4096 Jul 13 09:51 pgsql_tmp drwx-- 2 postgres postgres 139264 Jul 13 21:02 16389 drwx-- 2 postgres postgres 12288 Jul 13 21:02 13212 drwx-- 2 postgres postgres 4096 Jul 14 11:08 13213 drwx-- 2 postgres postgres 12288 Jul 14 11:08 13214 __ [RRF]postgres@axmdevhkdb008:/AXMDEVHKDB008/postgres/PHKGAXMD008_bck/base How to overcome this ? Note: we don't have any wal files, it is ok for us if we could bring it up with our recover. 2 nd one: Since we are not able to connect to DB , we did a new initialization and copy the directories from base directory to new base directory(in new data location) And update pg_catalog.pg_database table with the DB name (postgres@[local]:5433)# [PHKGAXMP003]INSERT INTO -- # pg_catalog.pg_database( -- # datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn, -- # datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl) -- # VALUES( -- # -- Write Your collation -- # 'axiom', 10, 0, 'C', 'C', -- # False, True, -1, 16389, '536', '1', 1663, Null); INSERT 16384 1 Time: 70.239 ms (postgres@[local]:5433)# [PHKGAXMP003]select oid from pg_database a where a.datname = 'axiom'; oid --- 16384 (1 row) After this we could able to see the DB axiom. Name| Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace |Description ---+--+---+-+---+---+-++ axiom | postgres | SQL_ASCII | C | C | | 16 GB | pg_default | postgres | postgres | SQL_ASCII | C | C | | 7647 kB | pg_default | default administrative connection database template0 | postgres | SQL_ASCII | C | C | =c/postgres +| 7513 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | || template1 | postgres | SQL_ASCII | C | C | =c/postgres +| 7513 kB | pg_default | default template for new databases | | | |