Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eo9Jm-0007o8-Pk for pgsql-admin@arkaria.postgresql.org; Tue, 20 Feb 2018 14:54:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eo9Jl-0005KO-9u for pgsql-admin@arkaria.postgresql.org; Tue, 20 Feb 2018 14:54:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eo9Jl-0005KE-2z for pgsql-admin@lists.postgresql.org; Tue, 20 Feb 2018 14:54:53 +0000 Received: from tamriel.snowman.net ([96.255.250.162]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1eo9Ji-0006IL-2r for pgsql-admin@postgresql.org; Tue, 20 Feb 2018 14:54:52 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id C6F525F79C; Tue, 20 Feb 2018 09:54:47 -0500 (EST) Date: Tue, 20 Feb 2018 09:54:47 -0500 From: Stephen Frost To: pavan95 Cc: pgsql-admin@postgresql.org Subject: Re: Script to find out 'Page Corruption' and 'Fragmentation' in PostgreSQL. Message-ID: <20180220145447.GL2416@tamriel.snowman.net> References: <1519120261856-0.post@n3.nabble.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="BF29R9apNbBxJHu8" Content-Disposition: inline In-Reply-To: <1519120261856-0.post@n3.nabble.com> User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --BF29R9apNbBxJHu8 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Greetings, * pavan95 (pavan.postgresdba@gmail.com) wrote: > I am looking for a script and its explanation of how to calculate *page > corruption* and* fragmentation* of a relation in PostgreSQL. When it comes to fragmentation, you might look at pg_freespacemap. If you'd like a tool which checks all of the page-level checksums (assuming you have them enabled..), then you might look at pgBackRest which verifies the page-level checksums during backups. You could do a partial check with pg_dump but that will generally only look at heap pages and not index pages. There can be other corruption in a page, of course, and between the heap and indexes- you might consider looking at amcheck to see about checking indexes. Thanks! Stephen --BF29R9apNbBxJHu8 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJajDa3AAoJEO1sijiDR2RVoUIP/ih6TFrzicDHhZAw1xThk1av 3dxgMFNc5nsRf/oswtVhrO/a6p6Zbek5rwAubH7qpWhCpbZWPJyDIqQ6JfUercVP x/t3dM7YlxUBHJODjIEz0XheJGEiSiQ54NjFTGx/8gjoVWHNtK/xIL5UPFiFvqWC i1qSgoDO+T+owdC6w5i+HE8/TgqJf1fZck8pzGMhGQBr5twA6FaItxStlikXJl4D jx6g+b/YQGKaiQGC+D2SnC8TvjcJAuvrcfFO95DAibjSU/YTz7UMwDoAnRfz2qa8 DQdCJk6tJ1AVbsLe9sDizsTFPm/x22h7aplIJA8s7b0LLWyWaCRrj/RxaQRvU6/6 QY9+rciJBbZgVhyH0bgo81FdLNUmvBLkBn3a+wCeu+cLGXsN0BG3Mc6DODsuWsOt qZfDT8gYYb9NDrvX8SMYdA8H1vmwn+LU2V/ld3xtUdyLnrpmvwpBPCUjdIORoT7I n5caw5oC1COh6PCWcwYO9WSvYhKQb1J+rjntNkIcqfCMjxF5J2sotnjmsb2Ge4b9 FdDlPxOyCr0lBRQpB1wrH5BK3O03Ls7hsUwqW6QFdyED4zJKyjzAbc0MOaJd96c2 o+dwNIYIF5wxeJSDZKAHdLASkGsZoY3PSmGt+O+O00/EMOcaU8dBGlQILmMFRte8 FPq91wSjvKr7/TKW1/go =ATDM -----END PGP SIGNATURE----- --BF29R9apNbBxJHu8--