Re: Corrupted Data ? - Mailing list pgsql-general

On 08/08/2016 10:06 AM, Ioana Danes wrote:
>
>
> On Mon, Aug 8, 2016 at 12:55 PM, Adrian Klaver>         75315811???
>
>
>     Corrupted index on db3?
>
> yes
>
>
>     Might want to look in the db3 logs to see if anything pops out.
>
> I checked the logs, no traces of errors or corruption.
>
>
>     I just do not know enough about Burcardo to be of much help beyond that.
>
>
>
> it is trigger based, it saves the ids of the inserted record in a delta
> table and then on sync it creates copy commands to the slave. Even if
> there is a bug or corruption in that process I don't see how that
> corrupts the index on db3...

It seems to do more then that:

https://bucardo.org/wiki/Bucardo/Documentation/Overview

That is why I suggested the post to the Burcardo list. Folks there will
have a better idea what goes under the hood.

There is also this from a previous post:

"Only one master is active at one time the other one is in stand by that
is a topic for another discussion but in our case that works well."

Have no idea how that interaction plays out.

At this point what I see is:

1) Data is entered on a master and is correct there.

2) Data is replicated to a single standby from one of two possible
sources via Bucardo and is no longer correct.

3) Now Bucardo uses Postgres to do its work so it is possible that
something in Postgres is at fault. Still the fact that the data is good
on the master but not in the standby tends to indicate that the act of
replication is the issue.

4) Exactly how that replication is accomplished is not obvious to me.



>
>
> So it is either replication bug + index corruption on db3 or data
> corruption on db3...
>
>
> In response to Melvin, the query returns no rows:
>
>
> SELECT n.nspname,
>                i.relname,
>                i.indexrelname,
>                CASE WHEN idx.indisprimary
>                          THEN 'pkey'
>                           WHEN idx.indisunique
>                           THEN 'uidx'
>                           ELSE 'idx'
>                   END AS type,
>                   'INVALID'
>   FROM pg_stat_all_indexes i
>     JOIN pg_class c     ON (c.oid = i.relid)
>     JOIN pg_namespace n ON (n.oid = c.relnamespace)
>     JOIN pg_index idx   ON (idx.indexrelid =  i.indexrelid )
> WHERE idx.indisvalid = FALSE
> ORDER BY 1, 2;
>
>  nspname | relname | indexrelname | type | ?column?
> ---------+---------+--------------+------+----------
> (0 rows)
>
>
> Thank you for your thoughts,
> ioana
>
>
>
>         Thanks,
>         ioana
>
>
>
>             --
>             Adrian Klaver
>             adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>         <mailto:adrian.klaver@aklaver.com
>         <mailto:adrian.klaver@aklaver.com>>
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com