Re: PostgreSQL 'Corruption & Fragmentation' detection andresolution/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
againstcritical problems mainly in case of production environment.  Also what's the instant thing that one needs to do
incase of corrupted data. 
>

Postgres should not be experiencing segmentation faults… what is your system configuration as it appear you may have a
problemwith 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
alwaysin an inconstant state and is why we have WAL files; with the data files and WAL files one ends up with a
consistentdatabase 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
areaffected.  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
andextract the given data — and worse case a full restore.  And I would also question why you ended up with a corrupted
pagebecause 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…
pageswill get reused and vacuum full is not a production friendly task nor are the reorg solutions.  I well regulated
systemwill 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
thedatabase 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,
datacleanup, etc…