2

So, how it started, DB started throwing errors about "database with OID 1"

2014-02-07 22:54:46.561 GET >ERROR:  database is not accepting commands to avoid wraparound data loss in database with OID 1
2014-02-07 22:54:46.561 GET >HINT:  Stop the postmaster and use a standalone backend to vacuum that database.
        You might also need to commit or roll back old prepared transactions.

OK, let's find DB with OID = 1:

backend> select oid,datname from pg_database where oid=1
         1: oid (typeid = 26, len = 4, typmod = -1, byval = t)
         2: datname     (typeid = 19, len = 64, typmod = -1, byval = f)
        ----
backend>

result = nothing. I've checked all DB's I've got and all of them have unique OID, none with value of "1".

Configuration

Software:

CentOS 6 x86_64 (always fresh updates), postgresql 9.3.2 / RPM's from postgresql.org. fsync=on, autovacuum=on, rest is default (commented).

Block layers:

SAS Disks (write cache off) -> Dell Perc RAID (RAID 10, battery backed write cache) -> LVM LV for pgsql -> ext4 (defaults). No errors reported on block level by OS or HW RAID consistency check, no power failures (never occured).

DB with OID=1

By default, when running initdb, postgres creates database with template0 and template1. DB OID for template1 = 1, but in my case for some reason - it's not.

10
  • If you don't have an extremely fresh backup, stop the DB and back it up, at the file system level, before continuing. You're probably OK at this point, but a backup is still a really good thing to have right now. This looks extremely suspicious; a DB should never be able to have oid=1, so I really suspect you've got some DB corruption going on here. Feb 9, 2014 at 23:46
  • Please show: your exact PostgreSQL version, your OS, relevant parts of your postgresql.conf - in particular the autovacuum parameters and the fsync parameter. Look in the log files for prior messages about wraparound and show any you find. Check dmesg for I/O errors, and the kernel logs. Describe any recent crashes, power losses or unexpected restarts (pg doesn't mind these IF your server's storage system works OK). Feb 9, 2014 at 23:47
  • Describe your server and storage subsystem (disk type/configuration, direct attached, using internal HBA, SAN; write-back or write-through cache, LVM, md, filesystem type, filesystem mount options, etc). Feb 9, 2014 at 23:51
  • Also, try starting PostgreSQL with use of system indexes disabled, and run your query for the DB with oid 1 again. See the docs for how to start it. Feb 9, 2014 at 23:52
  • 1
    OK, and if you run the same query with a standalone backend using the flags -P -F i ? (see postgresql.org/docs/current/static/app-postgres.html) . Oh, and what's the output of pg_controldata /path/to/database/directory and of select oid, datname, datfrozenxid, datminmxid FROM pg_catalog.pg_database? Feb 10, 2014 at 1:03

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.