|
Hello Team, Can some one help us on this please , Actually we are blocked . Regards, Narresh From: TALLURI Nareshkumar SgscGtsPasDre
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 start....2020-07-14 08:50:42.273 CEST db:[] user:[] [] [00000] LOG: listening on IPv4 address "176.5.88.68", port 5433 2020-07-14 08:50:42.278 CEST db:[] user:[] [] [00000] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433" 2020-07-14 08:50:42.280 CEST db:[] user:[] [] [00000] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2020-07-14 08:50:42.546 CEST db:[] user:[] [] [00000] LOG: redirecting log output to logging collector process 2020-07-14 08:50:42.546 CEST db:[] user:[] [] [00000] 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/1 00:00:00 sudo su - postgres root 16569 16567 0 14:12 pts/1 00:00:00 su - postgres postgres 16571 16569 0 14:12 pts/1 00:00:00 -ksh postgres 23888 1 0 14:50 pts/1 00: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/1 00:00:00 ps -ef postgres 24690 16571 0 14:55 pts/1 00: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 | | | | | postgres=CTc/postgres | | | (4 rows)
But no schema and username Some how we could manage to create the user. (postgres@[local]:5433)# [PHKGAXMP003]create user axiomdevdb01 with password 'xxxxxxx'; CREATE ROLE Time: 7.373 ms (postgres@[local]:5433)# [PHKGAXMP003]\du List of roles Role name | Attributes | Member of --------------+------------------------------------------------------------+----------- axiomdevdb01 | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} (postgres@[local]:5433)# [PHKGAXMP003]grant connect on database axiom to axiomdevdb01; GRANT Time: 5.274 ms (postgres@[local]:5433)# [PHKGAXMP003]grant all privileges on database axiom to axiomdevdb01; GRANT Time: 1.677 ms (postgres@[local]:5433)# [PHKGAXMP003]\l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+-----------+---------+-------+---------------------------+---------+------------+-------------------------------------------- axiom | postgres | SQL_ASCII | C | C | =Tc/postgres +| 16 GB | pg_default | | | | | | postgres=CTc/postgres +| | | | | | | | axiomdevdb01=CTc/postgres | | | 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 | | | | | postgres=CTc/postgres | | | (4 rows) But not able to create the schema: (postgres@[local]:5433)# [PHKGAXMP003]create schema ax_system; ERROR: 23505: duplicate key value violates unique constraint "pg_namespace_nspname_index" DETAIL: Key (nspname)=(ax_system) already exists. SCHEMA NAME: pg_catalog TABLE NAME: pg_namespace CONSTRAINT NAME: pg_namespace_nspname_index LOCATION: _bt_check_unique, nbtinsert.c:434 Time: 17.752 ms But we could able to see the tables with schema name “NULL” (postgres@[local]:5433)# [PHKGAXMP003]select * from pg_tables; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity --------------------+------------------------------+--------------+------------+------------+----------+-------------+------------- [null]
| axiom_object_dependencies | axiomdevdb01 | [null] | t | f | f | f [null]
| axiom_archive_instances | axiomdevdb01 | [null] | t | f | f | f [null]
| axiom_report_history | axiomdevdb01 | [null] | t | f | f | f [null]
| axiom_task_history | axiomdevdb01 | [null] | t | f | f | f [null]
| event_number | axiomdevdb01 | [null] | t | f | f | f pg_catalog | pg_statistic | postgres | [null] | t | f | f | f pg_catalog | pg_type | postgres | [null] | t | f | f | f [null]
| axo_hb | axiomdevdb01 | [null] | t | f | f | f [null]
| axiom_object_search_index | axiomdevdb01 | [null] | t | f | f | f [null]
| axod_rce | axiomdevdb01 | [null] | t | f | f | f [null]
| axos_rce_mgmt_1_0 | axiomdevdb01 | [null] | t | f | f | f SO Please help us how to proceed with this?
Regards, Narresh ========================================================= Ce message et toutes les pieces jointes (ci-apres le "message") ========================================================= This message and any attachments (the "message") are confidential, ========================================================= |
