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.

Are there any DBCC(Database consistency checker) commands in PostgreSQL? I could find SQL server DBCC commands but not for Postgres? I read that postgresql has inbuilt functionality of perfomance tuning and no DBCC commands are available for postgres. Is it true?

share|improve this question
3  
Postgres does not need a "consistency" check. What exactly does this DBCC thing do? What exactly are you trying to achieve? –  a_horse_with_no_name Dec 29 '13 at 13:15
    
@a_horse_with_no_name check this link –  user32207 Dec 29 '13 at 13:22
1  
@a_horse_with_no_name The main purpose of dbcc is to check for corruption caused by the IO subsystem. Though lots of random unconnected admin tasks also come under that umbrella. –  Martin Smith Dec 29 '13 at 16:50
2  
this earlier question seems to be closely related. –  Jack Douglas Dec 29 '13 at 16:52
1  
@JackDouglas - Yes. dbcc checkdb physical_only will validate checksums all pages in the file. Without the physical_only option it will also do more comprehensive checks of the logical structure too (to catch errors on pages without page checksums or errors introduced on the page before the checksum was calculated for example). The last category can happen due to bugs in the product or things running in process such as extended stored procedures. It also reports on the affected objects and has some repair functionality. –  Martin Smith Dec 29 '13 at 19:00

2 Answers 2

There is no built-in consistency check command or tool in PostgreSQL.

The general view is that one should not be necessary, as corruption and inconsistency should not be possible on a quality hardware/software stack. If problems do arise, there's no guarantee any kind of consistency check would find them, so it'd just create a false sense of security. I don't agree with that sentiment, but it's what seems to come out when this is periodically discussed on pgsql-hackers.

As usual, the underlying problem is that nobody particularly needs a consistency checker tool to meet their immediate needs, so nobody's spending the time to write one to scratch an itch and nobody's funding the development of one on a commercial contract or in-house basis. Volunteering? :p

PostgreSQL (until 9.3) didn't support block level checksums. So one of the main things that you're used to verifying did not exist and could thus not be verified. A tool to scan all relations and validate checksums does not exist in PostgreSQL 9.3, but would be desirable to add and may appear in a future version. In the mean time all you can do is SELECT * from each relation individually - but due to the fact that PostgreSQL uses operating system buffer cache for reads there's no gurantee that'll actually force a read of the underlying disk block anyway. A new tool would be needed to do this.

PostgreSQL tends to avoid redundantly storing information where possible, so there's often nothing to check against, just a single authorative source. A consistency checker can't do much unless the same information appears, or can be derived from, multiple different places.

It's also very hard to do any kind of useful checking concurrently, on a database that's still busy and active. Most installations aren't going to be willing to lock the entire database, or at least several major relations at a time, to run some kind of consistency check. So the checker would need to be able to operate on a database that's subject to concurrent modifications, making it even harder to write and able to detect fewer problems reliably.

There's still plenty that a validator tool could do if one were written, especially if it was allowed to take multiple relation exclusive locks:

  • Check that all tablespaces exist on disk.

  • Check that each pg_class entry has file(s) corresponding to its relfilenode in the correct tablespace.

  • Inspect visibility maps, free-space maps, etc, ensuring they're present when they should be, readable, and appear to match the relation they're associated with.

  • Report orphan on-disk file nodes. (These are normal due to transactional DDL and lazy unlinking, but a checker could force eager unlinking and lock all relations before running the check).

  • Read every block of each relation and look for obvious problems. For heap relations that'd be things like:

    • an xmin greater than xmax (after considering xid wrap-around)
    • Tuples created by transactions in the future
    • broken HOT chains / broken ctid chains
    • tuple structures that fail to match the table attributes
    • any Datum that doesn't round-trip its _in and _out functions unchanged or throws an error
    • NULL bitmap fields set on NOT NULL table attributes
    • Re-execution of CHECK constraints fails
  • Re-check foreign key and exclusion constraints after locking all tables involved

... and probably lots more I don't know enough about Pg's guts to figure out, such as attempts to detect torn pages, b-tree structure validation, sanity checking GIN and GiST indexes, sanity checking pg_control, and more I wouldn't know where to even start with.

If you're keen to have such a tool, the best thing to do is learn enough to come up with a concrete proposal about how it should work - and make the time to work on it, or to fund others to spend time on its development.

Personally I'd be really quite happy to have something that could check a stopped database cluster using a special startup mode for the postgres backend, so I could (somewhat) validate physical database copies taken with pg_basebackup, with pg_start_backup(), rsync and pg_stop_backup, with file-system level atomic snapshots, etc.

Alternately, you could do what most others do: Ensure your hardware and software stack is robust and properly configured, keep good backups, and monitor your logs. There's no substitute for proper testing of the whole stack before commissioning a server - and for good backups, both physical (streaming / PITR) and logical (dumps). Do plug-pull testing on a loaded database - repeatedly - before going live to make sure your supposedly reliable I/O subsystem really is. Use multiple forms of backup.

share|improve this answer

There is a project called pgCheck on pgFoundry. Note that the development status is 'Alpha' however.

I looks like the last activity was in early 2012.

It's been suggested elsewhere that

most people use a combination of either database wide vacuums, or doing select * from each table; ie. try to scan / process every row somehow

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.