Re: Does PostgreSQL check database integrity at startup?

Jan, all, * Jan Wieck (j...@wi3ck.info) wrote: > On Sat, Dec 30, 2017 at 10:27 AM, Stephen Frost wrote: > > The checksums included in PG are page-level and therefore there simply > > isn't one to look at if the file is zero bytes. > > And even if the file wasn't zero bytes

Re: Does PostgreSQL check database integrity at startup?

On Sat, Dec 30, 2017 at 10:27 AM, Stephen Frost wrote: > > The checksums included in PG are page-level and therefore there simply > isn't one to look at if the file is zero bytes. > And even if the file wasn't zero bytes you can't tell from the per page CRCs if you have all

Re: Sv: Re: Sv: Re: Does PostgreSQL check database integrity at startup?

Greetings Andreas, * Andreas Joseph Krogh (andr...@visena.com) wrote: > Here's a simple test-case: >   > createdb test > test=# create table foo(id serial primary key, name varchar not null); > test=# CREATE TYPE BigIntTuple2 AS (f1 bigint, f2 bigint); Hah, appears we allocate a relfilenode to

Sv: Re: Sv: Re: Does PostgreSQL check database integrity at startup?

På søndag 31. desember 2017 kl. 00:49:31, skrev Stephen Frost < sfr...@snowman.net >: * Andreas Joseph Krogh (andr...@visena.com) wrote: >  SELECT >    quote_ident(nsp.nspname) || '.' || quote_ident(c.relname), >    s.setting || '/base/' || db.oid || '/' ||

Re: Sv: Re: Does PostgreSQL check database integrity at startup?

* Andreas Joseph Krogh (andr...@visena.com) wrote: > SELECT >   quote_ident(nsp.nspname) || '.' || quote_ident(c.relname), >   s.setting || '/base/' || db.oid || '/' || c.relfilenode, >   (pg_stat_file(s.setting || '/base/' || db.oid || '/' || > c.relfilenode)).size as size > FROM >  

Sv: Re: Does PostgreSQL check database integrity at startup?

På lørdag 30. desember 2017 kl. 23:06:52, skrev Stephen Frost < sfr...@snowman.net >: Greetings, * Melvin Davidson (melvin6...@gmail.com) wrote: > My query works as designed and has done so > for two years. It shows the filenames for schemas and tables in the >

Re: Does PostgreSQL check database integrity at startup?

Greetings, * Melvin Davidson (melvin6...@gmail.com) wrote: > My query works as designed and has done so > for two years. It shows the filenames for schemas and tables in the > database. I'm glad to hear that it works in your specific use-case. Unfortunately, it doesn't work in the general case

Re: Does PostgreSQL check database integrity at startup?

On Sat, Dec 30, 2017 at 10:27 AM, Stephen Frost wrote: > Greetings Alban, > > * Alban Hertroys (haram...@gmail.com) wrote: > > In fact, I don't see it mentioned explicitly anywhere, but are we > actually looking at a problem? > > From the discussion in the google barman

Re: Does PostgreSQL check database integrity at startup?

Greetings Alban, * Alban Hertroys (haram...@gmail.com) wrote: > In fact, I don't see it mentioned explicitly anywhere, but are we actually > looking at a problem? From the discussion in the google barman group, it's clear that the file shouldn't be zero bytes in this specific case. > I'm not

Re: Does PostgreSQL check database integrity at startup?

Melvin, * Melvin Davidson (melvin6...@gmail.com) wrote: > >the query is outright wrong. > Really? I submit a query to help and all you can do is criticize? I explained exactly what was wrong with it, and how to write a proper query to answer this question, with the thought that you'd appreciate

Re: Does PostgreSQL check database integrity at startup?

> On Dec 29, 2017, at 6:14 PM, Melvin Davidson wrote: > > Edson's original request was for a query that shows the FILENAMEs for the > table. Which the query you provide does not do. > As for "qood" query, that is entirely an opinion. But a well informed one. Your

Re: Does PostgreSQL check database integrity at startup?

Melvin, * Melvin Davidson (melvin6...@gmail.com) wrote: > On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost wrote: > > * Melvin Davidson (melvin6...@gmail.com) wrote: > > > >There should be a "catalog" that point where tables are stored in > > physical > > > files... > > > > > >

Re: Does PostgreSQL check database integrity at startup?

On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost wrote: > Greetings, > > * Melvin Davidson (melvin6...@gmail.com) wrote: > > >There should be a "catalog" that point where tables are stored in > physical > > files... > > > > Here is the query that gives you that information. > >

Re: Does PostgreSQL check database integrity at startup?

Greetings, * Melvin Davidson (melvin6...@gmail.com) wrote: > >There should be a "catalog" that point where tables are stored in physical > files... > > Here is the query that gives you that information. > > SELECT c.oid, >n.nspname as schema, >c.relname as table,

Re: Does PostgreSQL check database integrity at startup?

>There should be a "catalog" that point where tables are stored in physical files... Here is the query that gives you that information. SELECT c.oid, n.nspname as schema, c.relname as table, (SELECT oid FROM pg_database WHERE datname =

Re: Does PostgreSQL check database integrity at startup?

Greetings, * Edson Carlos Ericksson Richter (rich...@simkorp.com.br) wrote: > There should be a "catalog" that point where tables are stored in physical > files (I think, at least, because at some point PostgreSQL need to know from > where to read the data). Yes, it's pg_class. Specifically,

Re: Does PostgreSQL check database integrity at startup?

Jan, all, * Jan Wieck (j...@wi3ck.info) wrote: > On Thu, Dec 28, 2017 at 1:26 PM, Stephen Frost wrote: > > * Brent Wood (pcr...@yahoo.com) wrote: > > > A tool to calculate a checksum of sorts based on the table (file) > > content would provide a better surety of duplication

Re: Does PostgreSQL check database integrity at startup?

Em 29/12/2017 22:14, Jan Wieck escreveu: On Thu, Dec 28, 2017 at 1:26 PM, Stephen Frost > wrote: Greetings Brent, * Brent Wood (pcr...@yahoo.com ) wrote: > A tool to calculate a checksum of sorts based on

Re: Does PostgreSQL check database integrity at startup?

On Thu, Dec 28, 2017 at 1:26 PM, Stephen Frost wrote: > Greetings Brent, > > * Brent Wood (pcr...@yahoo.com) wrote: > > A tool to calculate a checksum of sorts based on the table (file) > content would provide a better surety of duplication than simply checking > file size -

Re: Does PostgreSQL check database integrity at startup?

Greetings Brent, * Brent Wood (pcr...@yahoo.com) wrote: > A tool to calculate a checksum of sorts based on the table (file) content > would provide a better surety of duplication than simply checking file size - > like differently vacuumed tables in each copy could have the same content but >

Re: Does PostgreSQL check database integrity at startup?

Greetings, * Edson Carlos Ericksson Richter (rich...@simkorp.com.br) wrote: > Would be possible to include in future versions: > 1) After start standby, standby run all WAL files until it is > synchronized with master (current behavior) > 3) Before getting into "accept read only queries", check

Re: Does PostgreSQL check database integrity at startup?

ordered by primary key could work? Brent Wood From: Edson Carlos Ericksson Richter <rich...@simkorp.com.br> To: pgsql-general@lists.postgresql.org Sent: Friday, December 29, 2017 6:47 AM Subject: Re: Does PostgreSQL check database integrity at startup? Em 28/12/2017 10:16, Stephen

Re: Does PostgreSQL check database integrity at startup?

Em 28/12/2017 10:16, Stephen Frost escreveu: Alvaro, * Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote: For context: this was first reported in the Barman forum here: https://groups.google.com/forum/#!msg/pgbarman/3aXWpaKWRFI/weUIZxspDAAJ They are using Barman for the backups. A, I see. I

Re: Does PostgreSQL check database integrity at startup?

Alvaro, * Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote: > For context: this was first reported in the Barman forum here: > https://groups.google.com/forum/#!msg/pgbarman/3aXWpaKWRFI/weUIZxspDAAJ > They are using Barman for the backups. A, I see. I wasn't aware of that history. > Stephen

Re: Does PostgreSQL check database integrity at startup?

For context: this was first reported in the Barman forum here: https://groups.google.com/forum/#!msg/pgbarman/3aXWpaKWRFI/weUIZxspDAAJ They are using Barman for the backups. Stephen Frost wrote: > > But at some point in time, slave became corrupt (one of the base > > files are zero size where it

Re: Does PostgreSQL check database integrity at startup?

Edson, * Edson Carlos Ericksson Richter (rich...@simkorp.com.br) wrote: > Em 27/12/2017 15:02, Stephen Frost escreveu: > >* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > >>Stephen Frost wrote: > >> > >>>* Edson Carlos Ericksson Richter (rich...@simkorp.com.br) wrote: > Anyway, instead

Re: Does PostgreSQL check database integrity at startup?

Alvaro, * Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote: > Stephen Frost wrote: > > > It's entirely unclear to me what the problem being complained about in > > this thread actually is. > > As Edson explained, a relfilenode in the standby server is zero bytes > long when it is not that size in

Re: Does PostgreSQL check database integrity at startup?

Em 27/12/2017 15:02, Stephen Frost escreveu: Alvaro, * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Stephen Frost wrote: * Edson Carlos Ericksson Richter (rich...@simkorp.com.br) wrote: Anyway, instead digging into rsync functionality (or bugs - I doubt, but who knows?), I do prefer to

Re: Does PostgreSQL check database integrity at startup?

Alvaro, * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > Stephen Frost wrote: > > > * Edson Carlos Ericksson Richter (rich...@simkorp.com.br) wrote: > > > > Anyway, instead digging into rsync functionality (or bugs - I doubt, > > > but who knows?), I do prefer to have a script I can run to

Re: Does PostgreSQL check database integrity at startup?

Stephen Frost wrote: > * Edson Carlos Ericksson Richter (rich...@simkorp.com.br) wrote: > > Anyway, instead digging into rsync functionality (or bugs - I doubt, > > but who knows?), I do prefer to have a script I can run to check if > > there is obvious failures in standby servers. > > As

Re: Does PostgreSQL check database integrity at startup?

Em 26/12/2017 20:11, rob stone escreveu: Hello, On Tue, 2017-12-26 at 18:58 -0300, Alvaro Herrera wrote:Hello, David Steele wrote: pgBackRest will validate all page checksums (including indexes, etc.) in the cluster during backup. Full backups check everything, incr/differential backups

Re: Does PostgreSQL check database integrity at startup?

El 26/12/17 a las 14:46, Edson Carlos Ericksson Richter escribió: > > Usually, or by "pg_basebackup" or by "rsync" as described in PostgreSQL > Wiki. > Right now, I'm doing via pg_basebackup. > Nothing extraordinary. > > When using rsync: > > rsync -e "ssh -2 -C -p slave_ssh_port" --progress

Re: Does PostgreSQL check database integrity at startup?

Hello, On Tue, 2017-12-26 at 18:58 -0300, Alvaro Herrera wrote:Hello, > David Steele wrote: > > > pgBackRest will validate all page checksums (including indexes, > > etc.) in the > > cluster during backup. Full backups check everything, > > incr/differential > > backups check only the files

Re: Does PostgreSQL check database integrity at startup?

David Steele wrote: > pgBackRest will validate all page checksums (including indexes, etc.) in the > cluster during backup. Full backups check everything, incr/differential > backups check only the files that have changed. If a table or index file is of zero length when backed up, as in the

Re: Does PostgreSQL check database integrity at startup?

On 12/26/17 10:57 AM, Pavel Stehule wrote: 2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter I'm rebuilding the standby server for two days already, with 23% of completion status... If lost the database and backups because of that failure, it would be a giant disaster.

Re: Does PostgreSQL check database integrity at startup?

Em 26/12/2017 15:13, Alvaro Herrera escreveu: Edson Carlos Ericksson Richter wrote: I'm rebuilding the standby server for two days already, with 23% of completion status... So how do you build your standbys, exactly? Maybe there's a bug in your procedure, rather than a bug in the software.

Re: Does PostgreSQL check database integrity at startup?

Edson Carlos Ericksson Richter wrote: > I'm rebuilding the standby server for two days already, with 23% of > completion status... So how do you build your standbys, exactly? Maybe there's a bug in your procedure, rather than a bug in the software. -- Álvaro Herrera

Re: Does PostgreSQL check database integrity at startup?

Em 26/12/2017 13:57, Pavel Stehule escreveu: 2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter >: Em 26/12/2017 13:40, Pavel Stehule escreveu: 2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter

Re: Does PostgreSQL check database integrity at startup?

2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter < rich...@simkorp.com.br>: > Em 26/12/2017 13:40, Pavel Stehule escreveu: > > > > 2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter < > rich...@simkorp.com.br>: > >> Em 26/12/2017 12:25, Pavel Stehule escreveu: >> >> >> >>

Re: Does PostgreSQL check database integrity at startup?

Em 26/12/2017 13:40, Pavel Stehule escreveu: 2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter >: Em 26/12/2017 12:25, Pavel Stehule escreveu: 2017-12-26 14:44 GMT+01:00 Martin Marques

Re: Does PostgreSQL check database integrity at startup?

2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter < rich...@simkorp.com.br>: > Em 26/12/2017 12:25, Pavel Stehule escreveu: > > > > 2017-12-26 14:44 GMT+01:00 Martin Marques > : > >> El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió: >> >

Re: Does PostgreSQL check database integrity at startup?

Look into amcheck: https://github.com/petergeoghegan/amcheck -- Peter Geoghegan (Sent from my phone)

Re: Does PostgreSQL check database integrity at startup?

Em 26/12/2017 12:25, Pavel Stehule escreveu: 2017-12-26 14:44 GMT+01:00 Martin Marques >: El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió: > Recently I had a problem with a base file with size 0

Re: Does PostgreSQL check database integrity at startup?

2017-12-26 14:44 GMT+01:00 Martin Marques : > El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió: > > Recently I had a problem with a base file with size 0 in a standby > server. > > > > This raised one question: does PostgreSQL (9.6.6) check base

Re: Does PostgreSQL check database integrity at startup?

El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió: > Recently I had a problem with a base file with size 0 in a standby server. > > This raised one question: does PostgreSQL (9.6.6) check base integrity > at startup? > > At least if there are 0 byte size files in base dir? Or CRC?

Does PostgreSQL check database integrity at startup?

Recently I had a problem with a base file with size 0 in a standby server. This raised one question: does PostgreSQL (9.6.6) check base integrity at startup? At least if there are 0 byte size files in base dir? Or CRC? Something? Regards, -- *Edson Carlos Ericksson Richter* /SimKorp Ltda/