Re: [GENERAL] Corrupted Data ?

[Francisco Olarte]

Hi Adrian:

On Fri, Aug 12, 2016 at 9:01 PM, Adrian Klaver
 wrote:
> "Specially if this happens, you may have some slightly bad disks/ram/
> leading to this kind of problems."
>
> Trying to reconcile that with all the physical machine 1 VMs sharing the
> same RAM and physical disk, but the error only occurring on db3.
>
> Is the VM hosting db3 setup different from the VMs 1 & 2?

They probably share the disk, bus, ram and disk controllers, but they
surely do not share the disk SECTORS. "Weak" ( nearly failing ) tracks
can give this kind of problems ( although the disk CRC should catch
all odd number of bit errors , but with VMs in the mix who knows where
the messages could end up ).

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Adrian Klaver]

On 08/12/2016 11:51 AM, Ioana Danes wrote:



On Fri, Aug 12, 2016 at 2:50 PM, Adrian Klaver
> wrote:

On 08/12/2016 08:30 AM, Ioana Danes wrote:






The db3 database is on a different machine from all the other
databases you set up, correct?

Yes, they are all different vms first 3 dbs are on the same
cluster but
drdb is a remote machine,


This rattled around in my head and got less obvious with time:)
Probably because cluster has different meanings depending on whether
you are talking about Postgres or VMs. Right now I am imagining:

Physical machine 1|   Physical machine 2
  |
VM1 VM2   VM3 |   VM1
db1 db2   db3 |   drdb

where each VM has its own Postgres cluster

Is the above correct?

yes correct, I should  have been more clear,


Hmm.

I like Francisco's suggestion:

"Specially if this happens, you may have some slightly bad disks/ram/
leading to this kind of problems."

Trying to reconcile that with all the physical machine 1 VMs sharing the 
same RAM and physical disk, but the error only occurring on db3.


Is the VM hosting db3 setup different from the VMs 1 & 2?




--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

On Fri, Aug 12, 2016 at 2:50 PM, Adrian Klaver 
wrote:

> On 08/12/2016 08:30 AM, Ioana Danes wrote:
>
>>
>>
>>
>
>>
>> The db3 database is on a different machine from all the other
>> databases you set up, correct?
>>
>> Yes, they are all different vms first 3 dbs are on the same cluster but
>> drdb is a remote machine,
>>
>>
> This rattled around in my head and got less obvious with time:) Probably
> because cluster has different meanings depending on whether you are talking
> about Postgres or VMs. Right now I am imagining:
>
> Physical machine 1|   Physical machine 2
>   |
> VM1 VM2   VM3 |   VM1
> db1 db2   db3 |   drdb
>
> where each VM has its own Postgres cluster
>
> Is the above correct?
>
> yes correct, I should  have been more clear,

>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Corrupted Data ?

[Adrian Klaver]

On 08/12/2016 08:30 AM, Ioana Danes wrote:








The db3 database is on a different machine from all the other
databases you set up, correct?

Yes, they are all different vms first 3 dbs are on the same cluster but
drdb is a remote machine,



This rattled around in my head and got less obvious with time:) Probably 
because cluster has different meanings depending on whether you are 
talking about Postgres or VMs. Right now I am imagining:


Physical machine 1|   Physical machine 2
  |
VM1 VM2   VM3 |   VM1
db1 db2   db3 |   drdb

where each VM has its own Postgres cluster

Is the above correct?


--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

On Fri, Aug 12, 2016 at 12:22 PM, Adrian Klaver 
wrote:

> On 08/12/2016 08:49 AM, Ioana Danes wrote:
>
>>
>>
>> On Fri, Aug 12, 2016 at 11:44 AM, Ioana Danes > > wrote:
>>
>>
>>
> kvm
>>
>> Sorry I should add more info
>> kernel 4.7
>> and the filesystem is xfs vs ext3/ext4
>>
>
> Not following the above.
>
> Do you mean the physical and virtual machines have different filesystems?
>
> sorry the data partition where the postgres folder with all the postgres
files resides is on xfs and the root partition is on ext4

>
>
>>
>>
>>
>>
>> Thank you
>>
>>
>>
>> Thanks,
>> ioana
>>
>> Francisco Olarte.
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>> 
>> > >
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Corrupted Data ?

[Adrian Klaver]

On 08/12/2016 08:49 AM, Ioana Danes wrote:



On Fri, Aug 12, 2016 at 11:44 AM, Ioana Danes > wrote:





kvm

Sorry I should add more info
kernel 4.7
and the filesystem is xfs vs ext3/ext4


Not following the above.

Do you mean the physical and virtual machines have different filesystems?








Thank you



Thanks,
ioana

Francisco Olarte.




--
Adrian Klaver
adrian.kla...@aklaver.com

>




--
Adrian Klaver
adrian.kla...@aklaver.com 






--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

On Fri, Aug 12, 2016 at 11:44 AM, Ioana Danes  wrote:

>
>
> On Fri, Aug 12, 2016 at 11:34 AM, Adrian Klaver  > wrote:
>
>> On 08/12/2016 08:30 AM, Ioana Danes wrote:
>>
>>>
>>>
>>> On Fri, Aug 12, 2016 at 11:26 AM, Adrian Klaver
>>> > wrote:
>>>
>>> On 08/12/2016 08:10 AM, Ioana Danes wrote:
>>>
>>>
>>>
>>> On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte
>>> 
>>> >>
>>> wrote:
>>>
>>> CCing to the list...
>>>
>>> Thanks
>>>
>>>
>>> On Fri, Aug 12, 2016 at 4:10 PM, Ioana Danes
>>> 
>>> >>
>>> wrote:
>>> >> given 318220 and 318216 are just a bit away ( 4db08/4db0c
>>> ), and it
>>> >> repeats sporadically, have you ruled out ( by having page
>>> checksums or
>>> >> other mechanism ) a potential disk read/write error ?
>>> >>
>>> >>
>>> >> > Also the index is correct on db3 as the record in case
>>> (with
>>> drawid =
>>> >> > 318216) is retrieved if I filter by drawid = 318220
>>> >>
>>> >> Specially if this happens, you may have some slightly bad
>>> disks/ram/
>>> >> leading to this kind of problems.
>>> >>
>>> >
>>> > Could be. I also had some issues with an rsync between db3
>>> and
>>> drdb a week
>>> > ago that did not complete for bigger files (> 200MB) and
>>> gave me some
>>> > corruption messages. Then the system was revbooted and
>>> everything
>>> seemed
>>> > fine but apparently it is not.
>>> > I am planning to drop & create the table from a good
>>> backup and if
>>> that does
>>> > not fix the issue then I will rebuild the server.
>>>
>>> I would check whatever logs you can ( syslog or eventlog,
>>> smart log,
>>> etc.. ) hunting for disk errors ( sometimes they are
>>> reported ). This
>>> kind of problems, with programs as tested as postgres and
>>> rsync, tend
>>> to indicate controller/RAM/disk going bad ( in your case it
>>> could be
>>> caused by a single bit getting flipped in a sector for the
>>> data
>>> portion of the table, and not being propagated either
>>> because it
>>> happened after your sync of drdb or because it was synced
>>> from the WAL
>>> and not the table, or because it was read from the disk
>>> cache ).
>>>
>>> I agree, unfortunately I did not find any clues about corruption
>>> or any
>>> anomalies in the logs.
>>> I will work tonight to rebuild that table and see where I go
>>> from there.
>>>
>>>
>>> The db3 database is on a different machine from all the other
>>> databases you set up, correct?
>>>
>>> Yes, they are all different vms first 3 dbs are on the same cluster but
>>> drdb is a remote machine,
>>>
>>
>> Aah, another player in the mix.
>>
>> What virtualization technology are you using?
>>
>
> kvm
>
Sorry I should add more info
kernel 4.7
and the filesystem is xfs vs ext3/ext4



>
>>
>>> Thank you
>>>
>>>
>>>
>>> Thanks,
>>> ioana
>>>
>>> Francisco Olarte.
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>>
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

On Fri, Aug 12, 2016 at 11:34 AM, Adrian Klaver 
wrote:

> On 08/12/2016 08:30 AM, Ioana Danes wrote:
>
>>
>>
>> On Fri, Aug 12, 2016 at 11:26 AM, Adrian Klaver
>> > wrote:
>>
>> On 08/12/2016 08:10 AM, Ioana Danes wrote:
>>
>>
>>
>> On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte
>> 
>> >>
>> wrote:
>>
>> CCing to the list...
>>
>> Thanks
>>
>>
>> On Fri, Aug 12, 2016 at 4:10 PM, Ioana Danes
>> 
>> >>
>> wrote:
>> >> given 318220 and 318216 are just a bit away ( 4db08/4db0c
>> ), and it
>> >> repeats sporadically, have you ruled out ( by having page
>> checksums or
>> >> other mechanism ) a potential disk read/write error ?
>> >>
>> >>
>> >> > Also the index is correct on db3 as the record in case
>> (with
>> drawid =
>> >> > 318216) is retrieved if I filter by drawid = 318220
>> >>
>> >> Specially if this happens, you may have some slightly bad
>> disks/ram/
>> >> leading to this kind of problems.
>> >>
>> >
>> > Could be. I also had some issues with an rsync between db3
>> and
>> drdb a week
>> > ago that did not complete for bigger files (> 200MB) and
>> gave me some
>> > corruption messages. Then the system was revbooted and
>> everything
>> seemed
>> > fine but apparently it is not.
>> > I am planning to drop & create the table from a good
>> backup and if
>> that does
>> > not fix the issue then I will rebuild the server.
>>
>> I would check whatever logs you can ( syslog or eventlog,
>> smart log,
>> etc.. ) hunting for disk errors ( sometimes they are
>> reported ). This
>> kind of problems, with programs as tested as postgres and
>> rsync, tend
>> to indicate controller/RAM/disk going bad ( in your case it
>> could be
>> caused by a single bit getting flipped in a sector for the
>> data
>> portion of the table, and not being propagated either because
>> it
>> happened after your sync of drdb or because it was synced
>> from the WAL
>> and not the table, or because it was read from the disk cache
>> ).
>>
>> I agree, unfortunately I did not find any clues about corruption
>> or any
>> anomalies in the logs.
>> I will work tonight to rebuild that table and see where I go
>> from there.
>>
>>
>> The db3 database is on a different machine from all the other
>> databases you set up, correct?
>>
>> Yes, they are all different vms first 3 dbs are on the same cluster but
>> drdb is a remote machine,
>>
>
> Aah, another player in the mix.
>
> What virtualization technology are you using?
>

kvm



>
>
>> Thank you
>>
>>
>>
>> Thanks,
>> ioana
>>
>> Francisco Olarte.
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Corrupted Data ?

[Adrian Klaver]

On 08/12/2016 08:30 AM, Ioana Danes wrote:



On Fri, Aug 12, 2016 at 11:26 AM, Adrian Klaver
> wrote:

On 08/12/2016 08:10 AM, Ioana Danes wrote:



On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte

>>
wrote:

CCing to the list...

Thanks


On Fri, Aug 12, 2016 at 4:10 PM, Ioana Danes

>>
wrote:
>> given 318220 and 318216 are just a bit away ( 4db08/4db0c
), and it
>> repeats sporadically, have you ruled out ( by having page
checksums or
>> other mechanism ) a potential disk read/write error ?
>>
>>
>> > Also the index is correct on db3 as the record in case
(with
drawid =
>> > 318216) is retrieved if I filter by drawid = 318220
>>
>> Specially if this happens, you may have some slightly bad
disks/ram/
>> leading to this kind of problems.
>>
>
> Could be. I also had some issues with an rsync between db3 and
drdb a week
> ago that did not complete for bigger files (> 200MB) and
gave me some
> corruption messages. Then the system was revbooted and
everything
seemed
> fine but apparently it is not.
> I am planning to drop & create the table from a good
backup and if
that does
> not fix the issue then I will rebuild the server.

I would check whatever logs you can ( syslog or eventlog,
smart log,
etc.. ) hunting for disk errors ( sometimes they are
reported ). This
kind of problems, with programs as tested as postgres and
rsync, tend
to indicate controller/RAM/disk going bad ( in your case it
could be
caused by a single bit getting flipped in a sector for the data
portion of the table, and not being propagated either because it
happened after your sync of drdb or because it was synced
from the WAL
and not the table, or because it was read from the disk cache ).

I agree, unfortunately I did not find any clues about corruption
or any
anomalies in the logs.
I will work tonight to rebuild that table and see where I go
from there.


The db3 database is on a different machine from all the other
databases you set up, correct?

Yes, they are all different vms first 3 dbs are on the same cluster but
drdb is a remote machine,


Aah, another player in the mix.

What virtualization technology are you using?



Thank you



Thanks,
ioana

Francisco Olarte.




--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

On Fri, Aug 12, 2016 at 11:26 AM, Adrian Klaver 
wrote:

> On 08/12/2016 08:10 AM, Ioana Danes wrote:
>
>>
>>
>> On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte
>> > wrote:
>>
>> CCing to the list...
>>
>> Thanks
>>
>>
>> On Fri, Aug 12, 2016 at 4:10 PM, Ioana Danes > > wrote:
>> >> given 318220 and 318216 are just a bit away ( 4db08/4db0c ), and it
>> >> repeats sporadically, have you ruled out ( by having page
>> checksums or
>> >> other mechanism ) a potential disk read/write error ?
>> >>
>> >>
>> >> > Also the index is correct on db3 as the record in case (with
>> drawid =
>> >> > 318216) is retrieved if I filter by drawid = 318220
>> >>
>> >> Specially if this happens, you may have some slightly bad
>> disks/ram/
>> >> leading to this kind of problems.
>> >>
>> >
>> > Could be. I also had some issues with an rsync between db3 and
>> drdb a week
>> > ago that did not complete for bigger files (> 200MB) and gave me
>> some
>> > corruption messages. Then the system was revbooted and everything
>> seemed
>> > fine but apparently it is not.
>> > I am planning to drop & create the table from a good backup and if
>> that does
>> > not fix the issue then I will rebuild the server.
>>
>> I would check whatever logs you can ( syslog or eventlog, smart log,
>> etc.. ) hunting for disk errors ( sometimes they are reported ). This
>> kind of problems, with programs as tested as postgres and rsync, tend
>> to indicate controller/RAM/disk going bad ( in your case it could be
>> caused by a single bit getting flipped in a sector for the data
>> portion of the table, and not being propagated either because it
>> happened after your sync of drdb or because it was synced from the WAL
>> and not the table, or because it was read from the disk cache ).
>>
>> I agree, unfortunately I did not find any clues about corruption or any
>> anomalies in the logs.
>> I will work tonight to rebuild that table and see where I go from there.
>>
>
> The db3 database is on a different machine from all the other databases
> you set up, correct?
>
> Yes, they are all different vms first 3 dbs are on the same cluster but
drdb is a remote machine,

Thank you


>
>> Thanks,
>> ioana
>>
>> Francisco Olarte.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Corrupted Data ?

[Adrian Klaver]

On 08/12/2016 08:10 AM, Ioana Danes wrote:



On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte
> wrote:

CCing to the list...

Thanks


On Fri, Aug 12, 2016 at 4:10 PM, Ioana Danes > wrote:
>> given 318220 and 318216 are just a bit away ( 4db08/4db0c ), and it
>> repeats sporadically, have you ruled out ( by having page
checksums or
>> other mechanism ) a potential disk read/write error ?
>>
>>
>> > Also the index is correct on db3 as the record in case (with
drawid =
>> > 318216) is retrieved if I filter by drawid = 318220
>>
>> Specially if this happens, you may have some slightly bad disks/ram/
>> leading to this kind of problems.
>>
>
> Could be. I also had some issues with an rsync between db3 and
drdb a week
> ago that did not complete for bigger files (> 200MB) and gave me some
> corruption messages. Then the system was revbooted and everything
seemed
> fine but apparently it is not.
> I am planning to drop & create the table from a good backup and if
that does
> not fix the issue then I will rebuild the server.

I would check whatever logs you can ( syslog or eventlog, smart log,
etc.. ) hunting for disk errors ( sometimes they are reported ). This
kind of problems, with programs as tested as postgres and rsync, tend
to indicate controller/RAM/disk going bad ( in your case it could be
caused by a single bit getting flipped in a sector for the data
portion of the table, and not being propagated either because it
happened after your sync of drdb or because it was synced from the WAL
and not the table, or because it was read from the disk cache ).

I agree, unfortunately I did not find any clues about corruption or any
anomalies in the logs.
I will work tonight to rebuild that table and see where I go from there.


The db3 database is on a different machine from all the other databases 
you set up, correct?




Thanks,
ioana

Francisco Olarte.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte 
wrote:

> CCing to the list...
>
> Thanks


> On Fri, Aug 12, 2016 at 4:10 PM, Ioana Danes  wrote:
> >> given 318220 and 318216 are just a bit away ( 4db08/4db0c ), and it
> >> repeats sporadically, have you ruled out ( by having page checksums or
> >> other mechanism ) a potential disk read/write error ?
> >>
> >>
> >> > Also the index is correct on db3 as the record in case (with drawid =
> >> > 318216) is retrieved if I filter by drawid = 318220
> >>
> >> Specially if this happens, you may have some slightly bad disks/ram/
> >> leading to this kind of problems.
> >>
> >
> > Could be. I also had some issues with an rsync between db3 and drdb a
> week
> > ago that did not complete for bigger files (> 200MB) and gave me some
> > corruption messages. Then the system was revbooted and everything seemed
> > fine but apparently it is not.
> > I am planning to drop & create the table from a good backup and if that
> does
> > not fix the issue then I will rebuild the server.
>
> I would check whatever logs you can ( syslog or eventlog, smart log,
> etc.. ) hunting for disk errors ( sometimes they are reported ). This
> kind of problems, with programs as tested as postgres and rsync, tend
> to indicate controller/RAM/disk going bad ( in your case it could be
> caused by a single bit getting flipped in a sector for the data
> portion of the table, and not being propagated either because it
> happened after your sync of drdb or because it was synced from the WAL
> and not the table, or because it was read from the disk cache ).
>
> I agree, unfortunately I did not find any clues about corruption or any
anomalies in the logs.
I will work tonight to rebuild that table and see where I go from there.

Thanks,
ioana

Francisco Olarte.
>

Re: [GENERAL] Corrupted Data ?

[Francisco Olarte]

CCing to the list...

On Fri, Aug 12, 2016 at 4:10 PM, Ioana Danes  wrote:
>> given 318220 and 318216 are just a bit away ( 4db08/4db0c ), and it
>> repeats sporadically, have you ruled out ( by having page checksums or
>> other mechanism ) a potential disk read/write error ?
>>
>>
>> > Also the index is correct on db3 as the record in case (with drawid =
>> > 318216) is retrieved if I filter by drawid = 318220
>>
>> Specially if this happens, you may have some slightly bad disks/ram/
>> leading to this kind of problems.
>>
>
> Could be. I also had some issues with an rsync between db3 and drdb a week
> ago that did not complete for bigger files (> 200MB) and gave me some
> corruption messages. Then the system was revbooted and everything seemed
> fine but apparently it is not.
> I am planning to drop & create the table from a good backup and if that does
> not fix the issue then I will rebuild the server.

I would check whatever logs you can ( syslog or eventlog, smart log,
etc.. ) hunting for disk errors ( sometimes they are reported ). This
kind of problems, with programs as tested as postgres and rsync, tend
to indicate controller/RAM/disk going bad ( in your case it could be
caused by a single bit getting flipped in a sector for the data
portion of the table, and not being propagated either because it
happened after your sync of drdb or because it was synced from the WAL
and not the table, or because it was read from the disk cache ).

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

Hi Melvin,

On Fri, Aug 12, 2016 at 9:36 AM, Melvin Davidson 
wrote:

>
>
> On Fri, Aug 12, 2016 at 9:09 AM, Ioana Danes  wrote:
>
>> Hello Everyone,
>>
>> I have new information on this case.  I also open a post for Bucardo
>> because I am still not sure what triggers this problem.
>>
>> The problem happened again on the same table but on another field. Few
>> days ago I started a fourth database called drdb that is a PITR slave from
>> db3.
>>
>> DB1, DB2, DRDB:
>>
>> -[ RECORD 1 ]---+--
>> gameplayid   | 324455566
>> systemuserid| 515151
>> gameid | 41
>> transactionid | 666556533
>> drawid  | *318220*
>> 
>>
>> DB3:
>> -[ RECORD 1 ]---+--
>> gameplayid   | 32445556
>> systemuserid| 515151
>> gameid | 41
>> transactionid | 666556533
>> drawid  | *318216*
>>
>>
>>
>> Here are the facts I know:
>>
>> August 10 @ 11:10
>> - The record was created on db1 and replicated to db2 and db3
>> August 11 @ 2:30
>> - db1, db2 and db3 are in sync (I have a script that compares the
>> data for all 3 dbs every night @ 2:30 am)
>> August 12 @ 2:30
>> - db3 is out of sync because of this field (drawid)
>> - drdb (which is PITRed from db3) is in sync with db1 and db2?
>>
>>
>> Because drdb (PITR slave from db3) is in sync with db1 and db2 and
>> because the base backup was taken before the record in case was created, I
>> believe that the xlogs are fine and I have a data kind of corruption on db3
>> on the data file for that table that happened after August 11 @ 2:30
>> (because the compare script found the dbs in sync)...
>>
>> Also the index is correct on db3 as the record in case (with drawid =
>> *318216*) is retrieved if I filter by drawid = *318220*
>>
>> Any help is greatly appreciated,
>>
>> Thank you
>>
>>
>>
> Just out of curiosity, have you tried to REINDEX any of the indexes that
> might be involved on db3?
> Even though PostgreSQL considers them valid, it's possible that their
> pointers could be incorrect.
>
> Additionally, I would compare the EXPLAIN query on all 3 db's and see if
> there is any difference between the three.
>


The (drawid) index is correct:

There are too many records for that draw so I will create a temp table with
all the records for that draw and then retrieve the faulty one:

# create temporary table tmp_test as select * from abrazo.matchgameplay
where drawid = *318220 *order by datemodified;
SELECT 16611

# select * from tmp_test where drawid = *318216*; -- this query should
return nothing
-[ RECORD 1 ]---+--
gameplayid   | 159329792
systemuserid| 12797
gameid | 4
transactionid | 75006684
drawid  | *318216*

# explain select * from abrazo.matchgameplay where drawid = *318220 *order
by datemodified;
  QUERY PLAN
--
 Sort  (cost=1039.41..1058.65 rows=7696 width=103)
   Sort Key: datemodified
   ->  Index Scan using ix_matchgame_drawid on matchgameplay
(cost=0.44..542.64 rows=7696 width=103)
 Index Cond: (drawid = 318220)
(4 rows)


I will probably end up rebuilding the table from the other good dbs...

The only problem I have is that the values I see in these cases don't look
like corruption to be, they are valid information (for example this is an
id of another draw in the same period from another game... )

Thanks,
ioana


> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: [GENERAL] Corrupted Data ?

[Francisco Olarte]

On Fri, Aug 12, 2016 at 3:09 PM, Ioana Danes  wrote:

> drawid  | 318220
...
> drawid  | 318216

> Here are the facts I know:
>
> August 10 @ 11:10
> - The record was created on db1 and replicated to db2 and db3
> August 11 @ 2:30
> - db1, db2 and db3 are in sync (I have a script that compares the data
> for all 3 dbs every night @ 2:30 am)
> August 12 @ 2:30
> - db3 is out of sync because of this field (drawid)
> - drdb (which is PITRed from db3) is in sync with db1 and db2?

given 318220 and 318216 are just a bit away ( 4db08/4db0c ), and it
repeats sporadically, have you ruled out ( by having page checksums or
other mechanism ) a potential disk read/write error ?

> Also the index is correct on db3 as the record in case (with drawid =
> 318216) is retrieved if I filter by drawid = 318220

Specially if this happens, you may have some slightly bad disks/ram/
leading to this kind of problems.

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Melvin Davidson]

On Fri, Aug 12, 2016 at 9:09 AM, Ioana Danes  wrote:

> Hello Everyone,
>
> I have new information on this case.  I also open a post for Bucardo
> because I am still not sure what triggers this problem.
>
> The problem happened again on the same table but on another field. Few
> days ago I started a fourth database called drdb that is a PITR slave from
> db3.
>
> DB1, DB2, DRDB:
>
> -[ RECORD 1 ]---+--
> gameplayid   | 324455566
> systemuserid| 515151
> gameid | 41
> transactionid | 666556533
> drawid  | *318220*
> 
>
> DB3:
> -[ RECORD 1 ]---+--
> gameplayid   | 32445556
> systemuserid| 515151
> gameid | 41
> transactionid | 666556533
> drawid  | *318216*
>
>
>
> Here are the facts I know:
>
> August 10 @ 11:10
> - The record was created on db1 and replicated to db2 and db3
> August 11 @ 2:30
> - db1, db2 and db3 are in sync (I have a script that compares the data
> for all 3 dbs every night @ 2:30 am)
> August 12 @ 2:30
> - db3 is out of sync because of this field (drawid)
> - drdb (which is PITRed from db3) is in sync with db1 and db2?
>
>
> Because drdb (PITR slave from db3) is in sync with db1 and db2 and because
> the base backup was taken before the record in case was created, I believe
> that the xlogs are fine and I have a data kind of corruption on db3 on the
> data file for that table that happened after August 11 @ 2:30 (because the
> compare script found the dbs in sync)...
>
> Also the index is correct on db3 as the record in case (with drawid =
> *318216*) is retrieved if I filter by drawid = *318220*
>
> Any help is greatly appreciated,
>
> Thank you
>
>
> On Mon, Aug 8, 2016 at 1:25 PM, Adrian Klaver 
> wrote:
>
>> 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.kla...@aklaver.com 
>>> >> >
>>>
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>>

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

Hello Everyone,

I have new information on this case.  I also open a post for Bucardo
because I am still not sure what triggers this problem.

The problem happened again on the same table but on another field. Few days
ago I started a fourth database called drdb that is a PITR slave from db3.

DB1, DB2, DRDB:

-[ RECORD 1 ]---+--
gameplayid   | 324455566
systemuserid| 515151
gameid | 41
transactionid | 666556533
drawid  | *318220*


DB3:
-[ RECORD 1 ]---+--
gameplayid   | 32445556
systemuserid| 515151
gameid | 41
transactionid | 666556533
drawid  | *318216*



Here are the facts I know:

August 10 @ 11:10
- The record was created on db1 and replicated to db2 and db3
August 11 @ 2:30
- db1, db2 and db3 are in sync (I have a script that compares the data
for all 3 dbs every night @ 2:30 am)
August 12 @ 2:30
- db3 is out of sync because of this field (drawid)
- drdb (which is PITRed from db3) is in sync with db1 and db2?


Because drdb (PITR slave from db3) is in sync with db1 and db2 and because
the base backup was taken before the record in case was created, I believe
that the xlogs are fine and I have a data kind of corruption on db3 on the
data file for that table that happened after August 11 @ 2:30 (because the
compare script found the dbs in sync)...

Also the index is correct on db3 as the record in case (with drawid =
*318216*) is retrieved if I filter by drawid = *318220*

Any help is greatly appreciated,

Thank you


On Mon, Aug 8, 2016 at 1:25 PM, Adrian Klaver 
wrote:

> 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.kla...@aklaver.com 
>> > >
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Corrupted Data ?

[Adrian Klaver]

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.kla...@aklaver.com 
>




--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

On Mon, Aug 8, 2016 at 12:55 PM, Adrian Klaver 
wrote:

> On 08/08/2016 09:47 AM, Ioana Danes wrote:
>
>>
>>
>> On Mon, Aug 8, 2016 at 12:37 PM, Adrian Klaver
>> > wrote:
>>
>> On 08/08/2016 09:28 AM, Ioana Danes wrote:
>>
>>
>>
>> On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver
>> 
>> > >> wrote:
>>
>> On 08/08/2016 09:11 AM, Ioana Danes wrote:
>>
>> Hi,
>>
>> I suspect I am having a case of data corruption. Here
>> are the
>> details:
>>
>> I am running postgres 9.4.8:
>>
>> postgresql94-9.4.8-1PGDG.rhel7.x86_64
>> postgresql94-contrib-9.4.8-1PGDG.rhel7.x86_64
>> postgresql94-libs-9.4.8-1PGDG.rhel7.x86_64
>> postgresql94-server-9.4.8-1PGDG.rhel7.x86_64
>>
>> on CentOS Linux release 7.2.1511 (Core)
>>
>> This is happening in a production environment but
>> luckily on the
>> reporting database.
>> I have a cluster of 3 databases, db1 and db2 are masters
>> and
>> replicate
>> between each other and also replicate to db3 (db1 <->
>> db2, db1
>> -> db3,
>> db2 -> db3).
>> For replication I am using Bucardo.
>>
>>
>> I would say this is more a question for the Burcardo list:
>>
>> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
>> 
>> > >
>>
>> I am just not seeing that replicating two masters on to a
>> single
>> database is going to end well.
>>
>>
>> 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.
>>
>> That was my first assumption, that it is a kind of a race
>> condition or a
>> bug on replication but I quickly ruled that out because that
>> does not
>> explain why when I filtered the table by transactionid = 75315815
>> it
>> shows one record with transactionid 75315811...
>>
>> select gameplayid, transactionid, encodedplay from
>> abrazo.matchgameplay
>> where transactionid in (75315815) order by transactionid;;
>>  gameplayid | transactionid | encodedplay
>> +---+--
>>   160019271 |  75315815 | mix:9,0,9
>>   160019269 |  75315815 | mix:9,8,9
>>   160019267 |  75315815 | mix:9,2,2
>>   160019265 |  75315815 | mix:2,2,8
>>   160019263 |  *75315811 *| backup:1,9,1
>>   160019261 |  75315815 | backup:2,0,9
>>
>> So I don't think it is a replication issue...
>>
>>
>> Other that, if I am following correctly, it is on the database(db3)
>> being replicated to. The only way db3 is getting its data is through
>> replication, is that correct?. On the master databases the data is
>> correct.
>>
>> OK, let's assume that what you're saying is correct and the replication
>> has a bug, or corruption or whatever that is and the record gets created
>> with transactionid = 75315811. Bucardo replication is trigger based and
>> it is using a copy command to insert the new records into the replicated
>> database.
>>
>> Then how can I explain that my query select gameplayid, transactionid,
>> encodedplay from abrazo.matchgameplay where transactionid in (75315815)
>> order by transactionid; returns me a record with transactionid 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...


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,
  

Re: [GENERAL] Corrupted Data ?

[Adrian Klaver]

On 08/08/2016 09:47 AM, Ioana Danes wrote:



On Mon, Aug 8, 2016 at 12:37 PM, Adrian Klaver
> wrote:

On 08/08/2016 09:28 AM, Ioana Danes wrote:



On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver

>> wrote:

On 08/08/2016 09:11 AM, Ioana Danes wrote:

Hi,

I suspect I am having a case of data corruption. Here
are the
details:

I am running postgres 9.4.8:

postgresql94-9.4.8-1PGDG.rhel7.x86_64
postgresql94-contrib-9.4.8-1PGDG.rhel7.x86_64
postgresql94-libs-9.4.8-1PGDG.rhel7.x86_64
postgresql94-server-9.4.8-1PGDG.rhel7.x86_64

on CentOS Linux release 7.2.1511 (Core)

This is happening in a production environment but
luckily on the
reporting database.
I have a cluster of 3 databases, db1 and db2 are masters and
replicate
between each other and also replicate to db3 (db1 <->
db2, db1
-> db3,
db2 -> db3).
For replication I am using Bucardo.


I would say this is more a question for the Burcardo list:

https://mail.endcrypt.com/mailman/listinfo/bucardo-general

>

I am just not seeing that replicating two masters on to a single
database is going to end well.


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.

That was my first assumption, that it is a kind of a race
condition or a
bug on replication but I quickly ruled that out because that
does not
explain why when I filtered the table by transactionid = 75315815 it
shows one record with transactionid 75315811...

select gameplayid, transactionid, encodedplay from
abrazo.matchgameplay
where transactionid in (75315815) order by transactionid;;
 gameplayid | transactionid | encodedplay
+---+--
  160019271 |  75315815 | mix:9,0,9
  160019269 |  75315815 | mix:9,8,9
  160019267 |  75315815 | mix:9,2,2
  160019265 |  75315815 | mix:2,2,8
  160019263 |  *75315811 *| backup:1,9,1
  160019261 |  75315815 | backup:2,0,9

So I don't think it is a replication issue...


Other that, if I am following correctly, it is on the database(db3)
being replicated to. The only way db3 is getting its data is through
replication, is that correct?. On the master databases the data is
correct.

OK, let's assume that what you're saying is correct and the replication
has a bug, or corruption or whatever that is and the record gets created
with transactionid = 75315811. Bucardo replication is trigger based and
it is using a copy command to insert the new records into the replicated
database.

Then how can I explain that my query select gameplayid, transactionid,
encodedplay from abrazo.matchgameplay where transactionid in (75315815)
order by transactionid; returns me a record with transactionid 75315811???


Corrupted index on db3?

Might want to look in the db3 logs to see if anything pops out.

I just do not know enough about Burcardo to be of much help beyond that.



Thanks,
ioana



--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Melvin Davidson]

On Mon, Aug 8, 2016 at 12:47 PM, Ioana Danes  wrote:

>
>
> On Mon, Aug 8, 2016 at 12:37 PM, Adrian Klaver 
> wrote:
>
>> On 08/08/2016 09:28 AM, Ioana Danes wrote:
>>
>>>
>>>
>>> On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver
>>> > wrote:
>>>
>>> On 08/08/2016 09:11 AM, Ioana Danes wrote:
>>>
>>> Hi,
>>>
>>> I suspect I am having a case of data corruption. Here are the
>>> details:
>>>
>>> I am running postgres 9.4.8:
>>>
>>> postgresql94-9.4.8-1PGDG.rhel7.x86_64
>>> postgresql94-contrib-9.4.8-1PGDG.rhel7.x86_64
>>> postgresql94-libs-9.4.8-1PGDG.rhel7.x86_64
>>> postgresql94-server-9.4.8-1PGDG.rhel7.x86_64
>>>
>>> on CentOS Linux release 7.2.1511 (Core)
>>>
>>> This is happening in a production environment but luckily on the
>>> reporting database.
>>> I have a cluster of 3 databases, db1 and db2 are masters and
>>> replicate
>>> between each other and also replicate to db3 (db1 <-> db2, db1
>>> -> db3,
>>> db2 -> db3).
>>> For replication I am using Bucardo.
>>>
>>>
>>> I would say this is more a question for the Burcardo list:
>>>
>>> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
>>> 
>>>
>>> I am just not seeing that replicating two masters on to a single
>>> database is going to end well.
>>>
>>>
>>> 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.
>>>
>>> That was my first assumption, that it is a kind of a race condition or a
>>> bug on replication but I quickly ruled that out because that does not
>>> explain why when I filtered the table by transactionid = 75315815 it
>>> shows one record with transactionid 75315811...
>>>
>>> select gameplayid, transactionid, encodedplay from abrazo.matchgameplay
>>> where transactionid in (75315815) order by transactionid;;
>>>  gameplayid | transactionid | encodedplay
>>> +---+--
>>>   160019271 |  75315815 | mix:9,0,9
>>>   160019269 |  75315815 | mix:9,8,9
>>>   160019267 |  75315815 | mix:9,2,2
>>>   160019265 |  75315815 | mix:2,2,8
>>>   160019263 |  *75315811 *| backup:1,9,1
>>>   160019261 |  75315815 | backup:2,0,9
>>>
>>> So I don't think it is a replication issue...
>>>
>>>
>> Other that, if I am following correctly, it is on the database(db3) being
>> replicated to. The only way db3 is getting its data is through replication,
>> is that correct?. On the master databases the data is correct.
>>
>> OK, let's assume that what you're saying is correct and the replication
> has a bug, or corruption or whatever that is and the record gets created
> with transactionid = 75315811. Bucardo replication is trigger based and it
> is using a copy command to insert the new records into the replicated
> database.
>
> Then how can I explain that my query select gameplayid, transactionid,
> encodedplay from abrazo.matchgameplay where transactionid in (75315815)
> order by transactionid; returns me a record with transactionid 75315811???
>
> Thanks,
> ioana
>
>
>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>
You did provide the full table structure, but this might possibly be a case
of index corruption.
Try executing the following query and if any show up as invalid, just drop
and rebuild.

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;

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

On Mon, Aug 8, 2016 at 12:37 PM, Adrian Klaver 
wrote:

> On 08/08/2016 09:28 AM, Ioana Danes wrote:
>
>>
>>
>> On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver
>> > wrote:
>>
>> On 08/08/2016 09:11 AM, Ioana Danes wrote:
>>
>> Hi,
>>
>> I suspect I am having a case of data corruption. Here are the
>> details:
>>
>> I am running postgres 9.4.8:
>>
>> postgresql94-9.4.8-1PGDG.rhel7.x86_64
>> postgresql94-contrib-9.4.8-1PGDG.rhel7.x86_64
>> postgresql94-libs-9.4.8-1PGDG.rhel7.x86_64
>> postgresql94-server-9.4.8-1PGDG.rhel7.x86_64
>>
>> on CentOS Linux release 7.2.1511 (Core)
>>
>> This is happening in a production environment but luckily on the
>> reporting database.
>> I have a cluster of 3 databases, db1 and db2 are masters and
>> replicate
>> between each other and also replicate to db3 (db1 <-> db2, db1
>> -> db3,
>> db2 -> db3).
>> For replication I am using Bucardo.
>>
>>
>> I would say this is more a question for the Burcardo list:
>>
>> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
>> 
>>
>> I am just not seeing that replicating two masters on to a single
>> database is going to end well.
>>
>>
>> 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.
>>
>> That was my first assumption, that it is a kind of a race condition or a
>> bug on replication but I quickly ruled that out because that does not
>> explain why when I filtered the table by transactionid = 75315815 it
>> shows one record with transactionid 75315811...
>>
>> select gameplayid, transactionid, encodedplay from abrazo.matchgameplay
>> where transactionid in (75315815) order by transactionid;;
>>  gameplayid | transactionid | encodedplay
>> +---+--
>>   160019271 |  75315815 | mix:9,0,9
>>   160019269 |  75315815 | mix:9,8,9
>>   160019267 |  75315815 | mix:9,2,2
>>   160019265 |  75315815 | mix:2,2,8
>>   160019263 |  *75315811 *| backup:1,9,1
>>   160019261 |  75315815 | backup:2,0,9
>>
>> So I don't think it is a replication issue...
>>
>>
> Other that, if I am following correctly, it is on the database(db3) being
> replicated to. The only way db3 is getting its data is through replication,
> is that correct?. On the master databases the data is correct.
>
> OK, let's assume that what you're saying is correct and the replication
has a bug, or corruption or whatever that is and the record gets created
with transactionid = 75315811. Bucardo replication is trigger based and it
is using a copy command to insert the new records into the replicated
database.

Then how can I explain that my query select gameplayid, transactionid,
encodedplay from abrazo.matchgameplay where transactionid in (75315815)
order by transactionid; returns me a record with transactionid 75315811???

Thanks,
ioana



> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Corrupted Data ?

[Adrian Klaver]

On 08/08/2016 09:28 AM, Ioana Danes wrote:



On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver
> wrote:

On 08/08/2016 09:11 AM, Ioana Danes wrote:

Hi,

I suspect I am having a case of data corruption. Here are the
details:

I am running postgres 9.4.8:

postgresql94-9.4.8-1PGDG.rhel7.x86_64
postgresql94-contrib-9.4.8-1PGDG.rhel7.x86_64
postgresql94-libs-9.4.8-1PGDG.rhel7.x86_64
postgresql94-server-9.4.8-1PGDG.rhel7.x86_64

on CentOS Linux release 7.2.1511 (Core)

This is happening in a production environment but luckily on the
reporting database.
I have a cluster of 3 databases, db1 and db2 are masters and
replicate
between each other and also replicate to db3 (db1 <-> db2, db1
-> db3,
db2 -> db3).
For replication I am using Bucardo.


I would say this is more a question for the Burcardo list:

https://mail.endcrypt.com/mailman/listinfo/bucardo-general


I am just not seeing that replicating two masters on to a single
database is going to end well.


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.

That was my first assumption, that it is a kind of a race condition or a
bug on replication but I quickly ruled that out because that does not
explain why when I filtered the table by transactionid = 75315815 it
shows one record with transactionid 75315811...

select gameplayid, transactionid, encodedplay from abrazo.matchgameplay
where transactionid in (75315815) order by transactionid;;
 gameplayid | transactionid | encodedplay
+---+--
  160019271 |  75315815 | mix:9,0,9
  160019269 |  75315815 | mix:9,8,9
  160019267 |  75315815 | mix:9,2,2
  160019265 |  75315815 | mix:2,2,8
  160019263 |  *75315811 *| backup:1,9,1
  160019261 |  75315815 | backup:2,0,9

So I don't think it is a replication issue...



Other that, if I am following correctly, it is on the database(db3) 
being replicated to. The only way db3 is getting its data is through 
replication, is that correct?. On the master databases the data is correct.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Corrupted Data ?

[Ioana Danes]

On Mon, Aug 8, 2016 at 12:19 PM, Adrian Klaver 
wrote:

> On 08/08/2016 09:11 AM, Ioana Danes wrote:
>
>> Hi,
>>
>> I suspect I am having a case of data corruption. Here are the details:
>>
>> I am running postgres 9.4.8:
>>
>> postgresql94-9.4.8-1PGDG.rhel7.x86_64
>> postgresql94-contrib-9.4.8-1PGDG.rhel7.x86_64
>> postgresql94-libs-9.4.8-1PGDG.rhel7.x86_64
>> postgresql94-server-9.4.8-1PGDG.rhel7.x86_64
>>
>> on CentOS Linux release 7.2.1511 (Core)
>>
>> This is happening in a production environment but luckily on the
>> reporting database.
>> I have a cluster of 3 databases, db1 and db2 are masters and replicate
>> between each other and also replicate to db3 (db1 <-> db2, db1 -> db3,
>> db2 -> db3).
>> For replication I am using Bucardo.
>>
>
> I would say this is more a question for the Burcardo list:
>
> https://mail.endcrypt.com/mailman/listinfo/bucardo-general
>
> I am just not seeing that replicating two masters on to a single database
> is going to end well.
>

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.

That was my first assumption, that it is a kind of a race condition or a
bug on replication but I quickly ruled that out because that does not
explain why when I filtered the table by transactionid = 75315815 it shows
one record with transactionid 75315811...

select gameplayid, transactionid, encodedplay from abrazo.matchgameplay
where transactionid in (75315815) order by transactionid;;
 gameplayid | transactionid | encodedplay
+---+--
  160019271 |  75315815 | mix:9,0,9
  160019269 |  75315815 | mix:9,8,9
  160019267 |  75315815 | mix:9,2,2
  160019265 |  75315815 | mix:2,2,8
  160019263 |  *75315811 *| backup:1,9,1
  160019261 |  75315815 | backup:2,0,9

So I don't think it is a replication issue...



>
>
>> The problem I am having is that one record in a table it shows a wrong
>> value for one single field:
>>
>> select gameplayid, transactionid, encodedplay from mytable where
>> transactionid in (75315811, 75315815) order by transactionid;
>>
>>  gameplayid | transactionid | encodedplay
>> +---+--
>>   160019239 |  75315811 | mix:5,2,7
>>   160019237 |  75315811 | mix:5,4,8
>>   160019235 |  75315811 | mix:6,2,9
>>   160019233 |  75315811 | mix:1,9,8
>>   160019271 |  75315815 | mix:9,0,9
>>   160019269 |  75315815 | mix:9,8,9
>>   160019267 |  75315815 | mix:9,2,2
>>   160019265 |  75315815 | mix:2,2,8
>>   160019263 |  *75315811* | backup:1,9,1
>>   160019261 |  75315815 | backup:2,0,9
>>
>> select gameplayid, transactionid, encodedplay from mytable where
>> transactionid in (75315815) order by transactionid;
>>
>>  gameplayid | transactionid | encodedplay
>> +---+--
>>   160019271 |  75315815 | mix:9,0,9
>>   160019269 |  75315815 | mix:9,8,9
>>   160019267 |  75315815 | mix:9,2,2
>>   160019265 |  75315815 | mix:2,2,8
>>   160019263 |  *75315811* | backup:1,9,1
>>   160019261 |  75315815 | backup:2,0,9
>>
>> select gameplayid, transactionid, encodedplay from mytable where
>> transactionid in (75315811) order by transactionid;
>>
>>  gameplayid | transactionid | encodedplay
>> +---+--
>>   160019239 |  75315811 | mix:5,2,7
>>   160019237 |  75315811 | mix:5,4,8
>>   160019235 |  75315811 | mix:6,2,9
>>   160019233 |  75315811 | mix:1,9,8
>>
>> So the record with gameplayid = 160019263 have a wrong transactionid,
>> 75315811 instead of 75315815.
>> The correct value is 75315815 and that I know because of the following
>> facts:
>> - on db1 and db2 transactionid = 75315815 for gameplayid = 160019263,
>> - this table gets mostly inserts, very rare updates and only on other 2
>> fields not this one.
>> - there is another parent table that shows the number of records in this
>> table which is 4 for transactionid =75315811  and 6 for transactionid =
>> 7531581.
>>
>> This table has an index by transactionid and that index seem correct
>> because the filtering and the ordering are fine (like the filed has the
>> correct value)...
>>
>> What puzzles me is that the value that shows in this field is a real
>> value from another record...
>>
>> I only caught this issue because I have a script that runs in the night
>> that compares the databases ...
>>
>> By now I updated the field with the correct value and everything seem
>> stable.
>>
>> Postgres logs don't have any information about file corruption or any
>> other kind of error. I also checked other logs on the system and I could
>> not find any traces of corruption.
>>
>> select name, setting from pg_settings order by 1;
>> name
>> | setting
>> -+--
>> 

Re: [GENERAL] Corrupted Data ?

[Adrian Klaver]

On 08/08/2016 09:11 AM, Ioana Danes wrote:

Hi,

I suspect I am having a case of data corruption. Here are the details:

I am running postgres 9.4.8:

postgresql94-9.4.8-1PGDG.rhel7.x86_64
postgresql94-contrib-9.4.8-1PGDG.rhel7.x86_64
postgresql94-libs-9.4.8-1PGDG.rhel7.x86_64
postgresql94-server-9.4.8-1PGDG.rhel7.x86_64

on CentOS Linux release 7.2.1511 (Core)

This is happening in a production environment but luckily on the
reporting database.
I have a cluster of 3 databases, db1 and db2 are masters and replicate
between each other and also replicate to db3 (db1 <-> db2, db1 -> db3,
db2 -> db3).
For replication I am using Bucardo.


I would say this is more a question for the Burcardo list:

https://mail.endcrypt.com/mailman/listinfo/bucardo-general

I am just not seeing that replicating two masters on to a single 
database is going to end well.




The problem I am having is that one record in a table it shows a wrong
value for one single field:

select gameplayid, transactionid, encodedplay from mytable where
transactionid in (75315811, 75315815) order by transactionid;

 gameplayid | transactionid | encodedplay
+---+--
  160019239 |  75315811 | mix:5,2,7
  160019237 |  75315811 | mix:5,4,8
  160019235 |  75315811 | mix:6,2,9
  160019233 |  75315811 | mix:1,9,8
  160019271 |  75315815 | mix:9,0,9
  160019269 |  75315815 | mix:9,8,9
  160019267 |  75315815 | mix:9,2,2
  160019265 |  75315815 | mix:2,2,8
  160019263 |  *75315811* | backup:1,9,1
  160019261 |  75315815 | backup:2,0,9

select gameplayid, transactionid, encodedplay from mytable where
transactionid in (75315815) order by transactionid;

 gameplayid | transactionid | encodedplay
+---+--
  160019271 |  75315815 | mix:9,0,9
  160019269 |  75315815 | mix:9,8,9
  160019267 |  75315815 | mix:9,2,2
  160019265 |  75315815 | mix:2,2,8
  160019263 |  *75315811* | backup:1,9,1
  160019261 |  75315815 | backup:2,0,9

select gameplayid, transactionid, encodedplay from mytable where
transactionid in (75315811) order by transactionid;

 gameplayid | transactionid | encodedplay
+---+--
  160019239 |  75315811 | mix:5,2,7
  160019237 |  75315811 | mix:5,4,8
  160019235 |  75315811 | mix:6,2,9
  160019233 |  75315811 | mix:1,9,8

So the record with gameplayid = 160019263 have a wrong transactionid,
75315811 instead of 75315815.
The correct value is 75315815 and that I know because of the following
facts:
- on db1 and db2 transactionid = 75315815 for gameplayid = 160019263,
- this table gets mostly inserts, very rare updates and only on other 2
fields not this one.
- there is another parent table that shows the number of records in this
table which is 4 for transactionid =75315811  and 6 for transactionid =
7531581.

This table has an index by transactionid and that index seem correct
because the filtering and the ordering are fine (like the filed has the
correct value)...

What puzzles me is that the value that shows in this field is a real
value from another record...

I only caught this issue because I have a script that runs in the night
that compares the databases ...

By now I updated the field with the correct value and everything seem
stable.

Postgres logs don't have any information about file corruption or any
other kind of error. I also checked other logs on the system and I could
not find any traces of corruption.

select name, setting from pg_settings order by 1;
name
| setting
-+-
 allow_system_table_mods | off
 application_name| psql
 archive_command | test -f
/cbnDBscripts/tmp/PITR_primarydb_stop_backup || rsync --timeout=60 -atz
%p stldrdb:/data01/wal_files/%f
 archive_mode| on
 archive_timeout | 60
 array_nulls | on
 authentication_timeout  | 60
 autovacuum  | on
 autovacuum_analyze_scale_factor | 0.1
 autovacuum_analyze_threshold| 50
 autovacuum_freeze_max_age   | 2
 autovacuum_max_workers  | 3
 autovacuum_multixact_freeze_max_age | 4
 autovacuum_naptime  | 60
 autovacuum_vacuum_cost_delay| 20
 autovacuum_vacuum_cost_limit| -1
 autovacuum_vacuum_scale_factor  | 0.2
 autovacuum_vacuum_threshold | 50
 autovacuum_work_mem | -1
 backslash_quote | safe_encoding
 bgwriter_delay  | 200
 bgwriter_lru_maxpages   | 100
 bgwriter_lru_multiplier | 2
 block_size  | 8192
 bonjour | 

[GENERAL] Corrupted Data ?

[Ioana Danes]

Hi,

I suspect I am having a case of data corruption. Here are the details:

I am running postgres 9.4.8:

postgresql94-9.4.8-1PGDG.rhel7.x86_64
postgresql94-contrib-9.4.8-1PGDG.rhel7.x86_64
postgresql94-libs-9.4.8-1PGDG.rhel7.x86_64
postgresql94-server-9.4.8-1PGDG.rhel7.x86_64

on CentOS Linux release 7.2.1511 (Core)

This is happening in a production environment but luckily on the reporting
database.
I have a cluster of 3 databases, db1 and db2 are masters and replicate
between each other and also replicate to db3 (db1 <-> db2, db1 -> db3, db2
-> db3).
For replication I am using Bucardo.

The problem I am having is that one record in a table it shows a wrong
value for one single field:

select gameplayid, transactionid, encodedplay from mytable where
transactionid in (75315811, 75315815) order by transactionid;

 gameplayid | transactionid | encodedplay
+---+--
  160019239 |  75315811 | mix:5,2,7
  160019237 |  75315811 | mix:5,4,8
  160019235 |  75315811 | mix:6,2,9
  160019233 |  75315811 | mix:1,9,8
  160019271 |  75315815 | mix:9,0,9
  160019269 |  75315815 | mix:9,8,9
  160019267 |  75315815 | mix:9,2,2
  160019265 |  75315815 | mix:2,2,8
  160019263 |  *75315811* | backup:1,9,1
  160019261 |  75315815 | backup:2,0,9

select gameplayid, transactionid, encodedplay from mytable where
transactionid in (75315815) order by transactionid;

 gameplayid | transactionid | encodedplay
+---+--
  160019271 |  75315815 | mix:9,0,9
  160019269 |  75315815 | mix:9,8,9
  160019267 |  75315815 | mix:9,2,2
  160019265 |  75315815 | mix:2,2,8
  160019263 |  *75315811* | backup:1,9,1
  160019261 |  75315815 | backup:2,0,9

select gameplayid, transactionid, encodedplay from mytable where
transactionid in (75315811) order by transactionid;

 gameplayid | transactionid | encodedplay
+---+--
  160019239 |  75315811 | mix:5,2,7
  160019237 |  75315811 | mix:5,4,8
  160019235 |  75315811 | mix:6,2,9
  160019233 |  75315811 | mix:1,9,8

So the record with gameplayid = 160019263 have a wrong transactionid,
75315811 instead of 75315815.
The correct value is 75315815 and that I know because of the following
facts:
- on db1 and db2 transactionid = 75315815 for gameplayid = 160019263,
- this table gets mostly inserts, very rare updates and only on other 2
fields not this one.
- there is another parent table that shows the number of records in this
table which is 4 for transactionid =75315811  and 6 for transactionid =
7531581.

This table has an index by transactionid and that index seem correct
because the filtering and the ordering are fine (like the filed has the
correct value)...

What puzzles me is that the value that shows in this field is a real value
from another record...

I only caught this issue because I have a script that runs in the night
that compares the databases ...

By now I updated the field with the correct value and everything seem
stable.

Postgres logs don't have any information about file corruption or any other
kind of error. I also checked other logs on the system and I could not find
any traces of corruption.

select name, setting from pg_settings order by 1;
name
| setting
-+-
 allow_system_table_mods | off
 application_name| psql
 archive_command | test -f
/cbnDBscripts/tmp/PITR_primarydb_stop_backup || rsync --timeout=60 -atz %p
stldrdb:/data01/wal_files/%f
 archive_mode| on
 archive_timeout | 60
 array_nulls | on
 authentication_timeout  | 60
 autovacuum  | on
 autovacuum_analyze_scale_factor | 0.1
 autovacuum_analyze_threshold| 50
 autovacuum_freeze_max_age   | 2
 autovacuum_max_workers  | 3
 autovacuum_multixact_freeze_max_age | 4
 autovacuum_naptime  | 60
 autovacuum_vacuum_cost_delay| 20
 autovacuum_vacuum_cost_limit| -1
 autovacuum_vacuum_scale_factor  | 0.2
 autovacuum_vacuum_threshold | 50
 autovacuum_work_mem | -1
 backslash_quote | safe_encoding
 bgwriter_delay  | 200
 bgwriter_lru_maxpages   | 100
 bgwriter_lru_multiplier | 2
 block_size  | 8192
 bonjour | off
 bonjour_name|
 bytea_output| hex
 check_function_bodies   | on
 checkpoint_completion_target| 0.5
 checkpoint_segments | 16
 checkpoint_timeout  | 300
 

Re: [GENERAL] Corrupted Data?

[Alvaro Herrera]

On Tue, Sep 14, 2004 at 08:13:24AM -0400, Matthew T. O'Connor wrote:
 On Tue, 2004-09-14 at 00:46, Chester Kustarz wrote:
  On Mon, 13 Sep 2004, Matthew T. O'Connor wrote:
   I backed up the entire data directory, and did a pg_resetxlog, but that
   didn't help.  I found the specific row that seems to be the problem, but
   I can't delete it.
  
  I have used TRUNCATE on the table in this situation to recover. Another
  option might be to DROP the table. Or perhaps restore from backups.
 
 I would really prefer not to do that as pg_dump has apparently been
 failing for a while so I would lose a fair amount of data. 

You can create a pg_clog file (the one it's complaining about) filled
with zeros, using
dd if=/dev/zero bs=1k count=8 of=/path/to/data/pg_clog/0E3D

and then you should be able to pg_dump the table (or at least find out
if there is another corrupted tuple.)  Beware that the corrupted tuple
may be in there if it's supposed not to be, or it may not be if it's
supposed to be.

After you get your data back, I'd suggest running the usual hardware
checking tools, and restore from the backup.

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
Use it up, wear it out, make it do, or do without


---(end of broadcast)---
TIP 8: explain analyze is your friend