Cannot login to 'postgres' database as 'postgres' user

Question 1

I am unable to login to 'postgres' database as 'postgres' user.OS : REHL Server release 6.3Postgresql version: 8.4There is a Database 'jiradb' which is used as a Backend for JIRA 6.0.8.

When I give the command

[root ~]#psql postgres postgres
Password for user postgres: *******
psql: FATAL: could not open relation with OID 2696

How do I fix this error and login to 'postgres' database. Please ask me if you need more details. I am new to postgres DB.

Thanks.

Question 2

Yourpostgres database is damaged. oid 2696 is a system reserved oid, so it's a system table, and their oids are stable across databases and across versions. Looking it up on my 9.4, it's:

regress=> select relname from pg_class where oid = 2696;             relname              ---------------------------------- pg_statistic_relid_att_inh_index(1 row)regress=> \d pg_statistic_relid_att_inh_indexIndex "pg_catalog.pg_statistic_relid_att_inh_index"   Column   |   Type   | Definition ------------+----------+------------ starelid   | oid      | starelid staattnum  | smallint | staattnum stainherit | boolean  | stainheritunique, btree, for table "pg_catalog.pg_statistic"

so you have amissing file in the data directory for the indexpg_statistic_relid_att_inh_index on the system tablepg_catalog.pg_statistic.

This should not happen. You have at lest limited data corruption in your datadir.

Your first action should be to stop the database and make a full filesystem-level copy of the entire data directory, perPostgreSQL wiki - corruption.

Then check for possible causes. Recent disk issues? Unexpected/sudden shutdowns followed byfsck, possibly on a system with a non-crashsafe file system, unsafe mount options (e.g. ext3/ext4data=writeback), unsafe configurations like ext[34]-on-LVM-on-md with barriers on older kernels, etc. Also make sure you're on the latest 8.4 point release.

Only once you have made a full file system level copy of the data directory to safe read-only storage, start the database back up (butnot the applications that use it) and see if you can connect tojiradb e.g.psql jiradb. If you can,promptly perform apg_dump ofjiradb and any other databases with data of value.

Do not keep on using the damaged data directory. Now is a good time to do a dump and reload - do apg_dumpall --globals-only, apg_dump -Fc of each database, then move the datadir aside, re-initdb, and start back up with a clean install. You might even want to upgrade to a less ancient PostgreSQL at the same time.

Note that it is generally possible to fix issues like this in-place. In this case, if your damaged database wasn't the unimportant-and-usually-emptypostgres database, you could start PostgreSQL up in single-user mode with system indexes disabled, thenREINDEX the damaged index.

Craig Ringer 329k84 gold badges742 silver badges820 bronze badges · Accepted Answer · 2015-04-22 00:31:16Z

Yourpostgres database is damaged. oid 2696 is a system reserved oid, so it's a system table, and their oids are stable across databases and across versions. Looking it up on my 9.4, it's:

regress=> select relname from pg_class where oid = 2696;             relname              ---------------------------------- pg_statistic_relid_att_inh_index(1 row)regress=> \d pg_statistic_relid_att_inh_indexIndex "pg_catalog.pg_statistic_relid_att_inh_index"   Column   |   Type   | Definition ------------+----------+------------ starelid   | oid      | starelid staattnum  | smallint | staattnum stainherit | boolean  | stainheritunique, btree, for table "pg_catalog.pg_statistic"

so you have amissing file in the data directory for the indexpg_statistic_relid_att_inh_index on the system tablepg_catalog.pg_statistic.

This should not happen. You have at lest limited data corruption in your datadir.

Your first action should be to stop the database and make a full filesystem-level copy of the entire data directory, perPostgreSQL wiki - corruption.

Then check for possible causes. Recent disk issues? Unexpected/sudden shutdowns followed byfsck, possibly on a system with a non-crashsafe file system, unsafe mount options (e.g. ext3/ext4data=writeback), unsafe configurations like ext[34]-on-LVM-on-md with barriers on older kernels, etc. Also make sure you're on the latest 8.4 point release.

Only once you have made a full file system level copy of the data directory to safe read-only storage, start the database back up (butnot the applications that use it) and see if you can connect tojiradb e.g.psql jiradb. If you can,promptly perform apg_dump ofjiradb and any other databases with data of value.

Do not keep on using the damaged data directory. Now is a good time to do a dump and reload - do apg_dumpall --globals-only, apg_dump -Fc of each database, then move the datadir aside, re-initdb, and start back up with a clean install. You might even want to upgrade to a less ancient PostgreSQL at the same time.

Note that it is generally possible to fix issues like this in-place. In this case, if your damaged database wasn't the unimportant-and-usually-emptypostgres database, you could start PostgreSQL up in single-user mode with system indexes disabled, thenREINDEX the damaged index.

Movatterモバイル変換

Collectives™ on Stack Overflow

Cannot login to 'postgres' database as 'postgres' user

1 Answer1

Comments

Your Answer

Sign up orlog in

Post as a guest

Related

Hot Network Questions

Subscribe to RSS