current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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.

share|improve this question
 
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. –  Craig Ringer Feb 9 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). –  Craig Ringer Feb 9 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). –  Craig Ringer Feb 9 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. –  Craig Ringer Feb 9 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? –  Craig Ringer Feb 10 at 1:03
show 5 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.