Re: PostgreSQL 'Corruption & Fragmentation' detection and resolution/fix

> On Jun 11, 2018, at 12:58 PM, Pavan Teja <pavan.postgresdba@gmail.com> wrote:
>
> Actually found some issues like segmentation fault with sigssv 11. I'm concerned about to make my database healthy against critical problems mainly in case of production environment.  Also what's the instant thing that one needs to do in case of corrupted data.
>

Postgres should not be experiencing segmentation faults… what is your system configuration as it appear you may have a problem with your system build?

Postgres should not corrupt due to a system crash — if it did; I wouldn’t be using it.  In fact Postgres files are always in an inconstant state and is why we have WAL files; with the data files and WAL files one ends up with a consistent database regardless of how or why Postgres crashed. 

What to do? That depends on the issue.  If you end up with a page corruption then you need to determine which objects are affected.  If it’s an index page you can simply rebuild it; if it’s a data page; then you need to go to you backups and extract the given data — and worse case a full restore.  And I would also question why you ended up with a corrupted page because that shouldn’t happen unless you have a faulty subsystem or a bug.

The error may report a oid and in that case you can use oid2name to get name; etc.

> Also it will be greatful if you can suggest me a script to find fragmentation
>

Do you mean bloat? There is a script floating around on Postgres’s Wiki that sorta works . 

Personally, I cringe at bloat seekers… you end up chasing a fictional issue that doesn’t exist.  It’s OK to have bloat… pages will get reused and vacuum full is not a production friendly task nor are the reorg solutions.  I well regulated system will have an own equilibrium; seeking bloat and full vacuums are counter productive. 

Reorg your database is a sure way to introduce data corruption — you need to trust that third party solution with your data.  I can tell you that I have inherited corrupted databases from so-called DBAs that have run some sort of reorg on the database and left the database in that state. 

Full vacuum should only be use to handle and problems cases where table fell out of its equilibrium due to bad query, data cleanup, etc…