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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
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:
... 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
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
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.
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