pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase consistency check.
12+ messages / 7 participants
[nested] [flat]
* Database consistency check.
@ 2019-11-07 07:47 Asomba Djala <asombadjala8@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Asomba Djala @ 2019-11-07 07:47 UTC (permalink / raw)
To: Asomba Djala <asombadjala8@gmail.com>; +Cc: pgsql-admin
Hello,
I have a question, SQL Server has a tool DBCC checkdb and I believe Oracle
it's VerifyDB. Does Postgres have something similar and if not, does anyone
have steps or queries to do a sanity check or database integrity check with
PgAdmin4?
In advance,
Thank you for your help.
Aso.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-07 13:29 abbas alizadeh <ramkly@yahoo.com>
parent: Asomba Djala <asombadjala8@gmail.com>
1 sibling, 2 replies; 12+ messages in thread
From: abbas alizadeh @ 2019-11-07 13:29 UTC (permalink / raw)
To: Asomba Djala <asombadjala8@gmail.com>; +Cc: pgsql-admin
Hi,
For integrity you can use the -k option in initdb command while you initialize the new database cluster
Sent from my iPhone
> On Nov 7, 2019, at 11:18, Asomba Djala <asombadjala8@gmail.com> wrote:
>
>
> Hello,
>
> I have a question, SQL Server has a tool DBCC checkdb and I believe Oracle it's VerifyDB. Does Postgres have something similar and if not, does anyone have steps or queries to do a sanity check or database integrity check with PgAdmin4?
>
> In advance,
>
> Thank you for your help.
>
> Aso.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-07 19:16 Asomba Djala <asombadjala8@gmail.com>
parent: abbas alizadeh <ramkly@yahoo.com>
1 sibling, 0 replies; 12+ messages in thread
From: Asomba Djala @ 2019-11-07 19:16 UTC (permalink / raw)
To: abbas alizadeh <ramkly@yahoo.com>; +Cc: pgsql-admin
Thanks Abbas. Do you know of anything else for already initialized clusters?
On Thu, Nov 7, 2019, 7:29 AM abbas alizadeh <ramkly@yahoo.com> wrote:
> Hi,
> For integrity you can use the -k option in initdb command while you
> initialize the new database cluster
>
> Sent from my iPhone
>
> > On Nov 7, 2019, at 11:18, Asomba Djala <asombadjala8@gmail.com> wrote:
> >
> >
> > Hello,
> >
> > I have a question, SQL Server has a tool DBCC checkdb and I believe
> Oracle it's VerifyDB. Does Postgres have something similar and if not, does
> anyone have steps or queries to do a sanity check or database integrity
> check with PgAdmin4?
> >
> > In advance,
> >
> > Thank you for your help.
> >
> > Aso.
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-07 19:16 Asomba Djala <asombadjala8@gmail.com>
parent: abbas alizadeh <ramkly@yahoo.com>
1 sibling, 2 replies; 12+ messages in thread
From: Asomba Djala @ 2019-11-07 19:16 UTC (permalink / raw)
To: abbas alizadeh <ramkly@yahoo.com>; +Cc: pgsql-admin
Thank you. What about already initialized clusters?
On Thu, Nov 7, 2019, 7:29 AM abbas alizadeh <ramkly@yahoo.com> wrote:
> Hi,
> For integrity you can use the -k option in initdb command while you
> initialize the new database cluster
>
> Sent from my iPhone
>
> > On Nov 7, 2019, at 11:18, Asomba Djala <asombadjala8@gmail.com> wrote:
> >
> >
> > Hello,
> >
> > I have a question, SQL Server has a tool DBCC checkdb and I believe
> Oracle it's VerifyDB. Does Postgres have something similar and if not, does
> anyone have steps or queries to do a sanity check or database integrity
> check with PgAdmin4?
> >
> > In advance,
> >
> > Thank you for your help.
> >
> > Aso.
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-07 19:31 Peter Geoghegan <pg@bowt.ie>
parent: Asomba Djala <asombadjala8@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Peter Geoghegan @ 2019-11-07 19:31 UTC (permalink / raw)
To: Asomba Djala <asombadjala8@gmail.com>; +Cc: abbas alizadeh <ramkly@yahoo.com>; pgsql-admin
On Thu, Nov 7, 2019 at 11:18 AM Asomba Djala <asombadjala8@gmail.com> wrote:
> Thank you. What about already initialized clusters?
There is amcheck, which tests the consistency of B-Tree indexes:
https://www.postgresql.org/docs/current/amcheck.html
Recent versions have a "heapallindexed" option, too, which has the
tool test indexes against the table that they index. This is very
useful as a general purpose tool for detecting corruption.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-07 19:32 Asomba Djala <asombadjala8@gmail.com>
parent: Peter Geoghegan <pg@bowt.ie>
0 siblings, 0 replies; 12+ messages in thread
From: Asomba Djala @ 2019-11-07 19:32 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: abbas alizadeh <ramkly@yahoo.com>; pgsql-admin
Thank you very much.
On Thu, Nov 7, 2019, 1:31 PM Peter Geoghegan <pg@bowt.ie> wrote:
> On Thu, Nov 7, 2019 at 11:18 AM Asomba Djala <asombadjala8@gmail.com>
> wrote:
> > Thank you. What about already initialized clusters?
>
> There is amcheck, which tests the consistency of B-Tree indexes:
>
> https://www.postgresql.org/docs/current/amcheck.html
>
> Recent versions have a "heapallindexed" option, too, which has the
> tool test indexes against the table that they index. This is very
> useful as a general purpose tool for detecting corruption.
>
> --
> Peter Geoghegan
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-07 19:52 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Asomba Djala <asombadjala8@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Scott Ribe @ 2019-11-07 19:52 UTC (permalink / raw)
To: Asomba Djala <asombadjala8@gmail.com>; +Cc: abbas alizadeh <ramkly@yahoo.com>; pgsql-admin
> On Nov 7, 2019, at 12:16 PM, Asomba Djala <asombadjala8@gmail.com> wrote:
>
> Thank you. What about already initialized clusters?
I believe PG 12 adds the feature that checksums can be added to existing clusters???
Probably requires taking the cluster down, and won't detect existing corruption...
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-07 22:37 Grigory Smolkin <g.smolkin@postgrespro.ru>
parent: Asomba Djala <asombadjala8@gmail.com>
1 sibling, 3 replies; 12+ messages in thread
From: Grigory Smolkin @ 2019-11-07 22:37 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello!
pg_probackup has dedicated command for PostgreSQL instance verification,
which rely on amcheck, page checksum comparison and page header sanity
checks.
It can be used on running databases and can be used even if you do not
use pg_probackup for backup/restore purposes.
https://github.com/postgrespro/pg_probackup/blob/master/Documentation.md#checkdb
https://github.com/postgrespro/pg_probackup/blob/master/Documentation.md#verifying-a-cluster
On 11/7/19 10:47 AM, Asomba Djala wrote:
> Hello,
>
> I have a question, SQL Server has a tool DBCC checkdb and I believe
> Oracle it's VerifyDB. Does Postgres have something similar and if not,
> does anyone have steps or queries to do a sanity check or database
> integrity check with PgAdmin4?
>
> In advance,
>
> Thank you for your help.
>
> Aso.
--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-07 22:44 Vasanth Kumar Pediseti <vasanth_pediseti@rediffmail.com>
parent: Grigory Smolkin <g.smolkin@postgrespro.ru>
2 siblings, 0 replies; 12+ messages in thread
From: Vasanth Kumar Pediseti @ 2019-11-07 22:44 UTC (permalink / raw)
To: Grigory Smolkin <g.smolkin@postgrespro.ru>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Ref:WEB-25307261REMOVE email.From: Grigory Smolkin <g.smolkin@postgrespro.ru>Sent: Fri, 08 Nov 2019 04:07:26To: pgsql-admin@lists.postgresql.orgSubject: Re: Database consistency check.Hello!pg_probackup has dedicated command for PostgreSQL instance verification,which rely on amcheck, page checksum comparison and page header sanitychecks.It can be used on running databases and can be used even if you do notuse pg_probackup for backup/restore purposes.https://github.com/postgrespro/pg_probackup/blob/master/Documentation.md#checkdbhttps://github.com/p... 11/7/19 10:47 AM, Asomba Djala wrote:> Hello,>> I have a question, SQL Server has a tool DBCC checkdb and I believe> Oracle it's VerifyDB. Does Postgres have something similar and if not,> does anyone have steps or queries to do a sanity check or database> integrity check with PgAdmin4?>> In advance,>> Thank you for your help.>> Aso.--Grigory SmolkinPostgres Professional: http://www.postgrespro.comThe Russian Postgres Company
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-07 22:45 Vasanth Kumar Pediseti <vasanth_pediseti@rediffmail.com>
parent: Grigory Smolkin <g.smolkin@postgrespro.ru>
2 siblings, 0 replies; 12+ messages in thread
From: Vasanth Kumar Pediseti @ 2019-11-07 22:45 UTC (permalink / raw)
To: Grigory Smolkin <g.smolkin@postgrespro.ru>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
From: Grigory Smolkin <g.smolkin@postgrespro.ru>Sent: Fri, 08 Nov 2019 04:07:26To: pgsql-admin@lists.postgresql.orgSubject: Re: Database consistency check.Hello!pg_probackup has dedicated command for PostgreSQL instance verification,which rely on amcheck, page checksum comparison and page header sanitychecks.It can be used on running databases and can be used even if you do notuse pg_probackup for backup/restore purposes.https://github.com/postgrespro/pg_probackup/blob/master/Documentation.md#checkdbhttps://github.com/p... 11/7/19 10:47 AM, Asomba Djala wrote:> Hello,>> I have a question, SQL Server has a tool DBCC checkdb and I believe> Oracle it's VerifyDB. Does Postgres have something similar and if not,> does anyone have steps or queries to do a sanity check or database> integrity check with PgAdmin4?>> In advance,>> Thank you for your help.>> Aso.--Grigory SmolkinPostgres Professional: http://www.postgrespro.comThe Russian Postgres Company
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-12 05:41 Asomba Djala <asombadjala8@gmail.com>
parent: Grigory Smolkin <g.smolkin@postgrespro.ru>
2 siblings, 0 replies; 12+ messages in thread
From: Asomba Djala @ 2019-11-12 05:41 UTC (permalink / raw)
To: Grigory Smolkin <g.smolkin@postgrespro.ru>; +Cc: pgsql-admin@lists.postgresql.org
Thank you all for your help!
On Thu, Nov 7, 2019, 4:37 PM Grigory Smolkin <g.smolkin@postgrespro.ru>
wrote:
> Hello!
>
> pg_probackup has dedicated command for PostgreSQL instance verification,
> which rely on amcheck, page checksum comparison and page header sanity
> checks.
> It can be used on running databases and can be used even if you do not
> use pg_probackup for backup/restore purposes.
>
>
> https://github.com/postgrespro/pg_probackup/blob/master/Documentation.md#checkdb
>
> https://github.com/postgrespro/pg_probackup/blob/master/Documentation.md#verifying-a-cluster
>
> On 11/7/19 10:47 AM, Asomba Djala wrote:
> > Hello,
> >
> > I have a question, SQL Server has a tool DBCC checkdb and I believe
> > Oracle it's VerifyDB. Does Postgres have something similar and if not,
> > does anyone have steps or queries to do a sanity check or database
> > integrity check with PgAdmin4?
> >
> > In advance,
> >
> > Thank you for your help.
> >
> > Aso.
>
> --
> Grigory Smolkin
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Database consistency check.
@ 2019-11-14 14:45 Michael Banck <michael.banck@credativ.de>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 12+ messages in thread
From: Michael Banck @ 2019-11-14 14:45 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Asomba Djala <asombadjala8@gmail.com>; abbas alizadeh <ramkly@yahoo.com>; pgsql-admin
Hi,
On Thu, Nov 07, 2019 at 12:52:17PM -0700, Scott Ribe wrote:
> > On Nov 7, 2019, at 12:16 PM, Asomba Djala <asombadjala8@gmail.com> wrote:
> > Thank you. What about already initialized clusters?
>
> I believe PG 12 adds the feature that checksums can be added to existing clusters???
Right, it's the pg_checksums program.
> Probably requires taking the cluster down, and won't detect existing corruption...
It does require taking the instance down for checkusm activation and can
only checksum what is already there.
Another option is to pg_dump to /dev/null in order to check for logical
data consistency if you are not doing regular logical backups.
Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de
credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer
Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2019-11-14 14:45 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-07 07:47 Database consistency check. Asomba Djala <asombadjala8@gmail.com>
2019-11-07 13:29 ` abbas alizadeh <ramkly@yahoo.com>
2019-11-07 19:16 ` Asomba Djala <asombadjala8@gmail.com>
2019-11-07 19:16 ` Asomba Djala <asombadjala8@gmail.com>
2019-11-07 19:31 ` Peter Geoghegan <pg@bowt.ie>
2019-11-07 19:32 ` Asomba Djala <asombadjala8@gmail.com>
2019-11-07 19:52 ` Scott Ribe <scott_ribe@elevated-dev.com>
2019-11-14 14:45 ` Michael Banck <michael.banck@credativ.de>
2019-11-07 22:37 ` Grigory Smolkin <g.smolkin@postgrespro.ru>
2019-11-07 22:44 ` Vasanth Kumar Pediseti <vasanth_pediseti@rediffmail.com>
2019-11-07 22:45 ` Vasanth Kumar Pediseti <vasanth_pediseti@rediffmail.com>
2019-11-12 05:41 ` Asomba Djala <asombadjala8@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox