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

Re corruption, For heap (i.e table) the standard prescription is just to
pg_dump sending output to /dev/null. This will bail if there are any
corrupted pages. Also (as probably mentioned elsewhere), enable data
checksums at initdb time and the above pg_dump will validate these too.

With respect to indexes - the latest versions of the amcheck extension
verify these.

Re fragmentation, the usual problem with a Postgres db is not really
classical 'fragmentation' but data bloat due to insufficient VACUUM. The
storage model for Postgres uses 'copy on write' so presents a different
problem path from pretty much every other DBMS.

Cheers

Mark

On 12/06/18 18:09, pavan95 wrote:
> Hi,
>
> Can we get any information regarding fragmentation/corruption in the
> database level from the system table
>
> "Information_schema.columns" ?
>
> Actually my strong belief is other databases like SQL server, Oracle &
> Mysql are providing some system views to detect corruption and fragmentation
> from the database. So thinking the same with the postgres.
>
> As I was asked to prepare a script well in advance to predict the
> forthcoming disasters
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>