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

Thank you for your response.

So finally there's no script to determine corruption well in advance??
Correct??

Regards,
Pavan

On Mon, Jun 11, 2018, 11:20 PM Rui DeSousa <rui(dot)desousa(at)icloud(dot)com> wrote:

>
>
> > On Jun 11, 2018, at 12:58 PM, Pavan Teja <pavan(dot)postgresdba(at)gmail(dot)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…