pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex corruption after pg_basebackup
4+ messages / 2 participants
[nested] [flat]
* Index corruption after pg_basebackup
@ 2018-08-28 16:08 Jorge Torralba <jorge.torralba@gmail.com>
2018-08-28 16:42 ` Re: Index corruption after pg_basebackup Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 4+ messages in thread
From: Jorge Torralba @ 2018-08-28 16:08 UTC (permalink / raw)
To: pgsql-admin
I have been struggling with this particular scenario now for a few days and
very frustrated with the consistent results. maybe someone can help.
We are migrating a service to new docker container from a bare metal box.
This is one of about 150 which we have done already successfully.
basically setup a replica, promote it, and point the f5 to the new master.
However with this particular migration we always get index corruption on
the new server once we are up and running.
In a nut shell, here is our flow.
1. Use pg_basebackup initiated from new server against the old one to
establish a replica of the old server.
2. Recovery.conf file has a restore command to point to the archive
directory because we cycle through wal files very frequently.
3. Start new replica, let it do it's magic and catch up.
At this point all seems normal. However certain queries against the replica
return drastically different result sets when compared to the primary.
During our investigation, we promote the replica and do a few things
including a vacuumdb -za. This is where we see error messages regarding
indexes such as ...
ERROR: failed to re-find parent key in index "myindex" for deletion target
page 851753
We could just reindex and be done. But, the db is very large and hot, we
cannot have inconsistent data while we reindex.
When we run the same vacuum on the source server, all is good so the source
data is good.
We can repeat this scenario in both staging and production so I am thinking
it is something that is incompatible between the two.
So a little background just for your reference.
- Source database and target database are the same versions of postgres
( 9.5 ) However, the minor version is different. 9.5.5 vs 9.5.7
- fsync = on
- full_page_write = on
Any tips or help would be appreciated.
Don't have the luxury of doing a pg_dump :(
Thanks
JT
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index corruption after pg_basebackup
2018-08-28 16:08 Index corruption after pg_basebackup Jorge Torralba <jorge.torralba@gmail.com>
@ 2018-08-28 16:42 ` Christoph Berg <myon@debian.org>
2018-08-28 17:03 ` Re: Index corruption after pg_basebackup Jorge Torralba <jorge.torralba@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Christoph Berg @ 2018-08-28 16:42 UTC (permalink / raw)
To: Jorge Torralba <jorge.torralba@gmail.com>; +Cc: pgsql-admin
Re: Jorge Torralba 2018-08-28 <CACut7uRx72_R-nGOdOdnTSYCGGdKZ_TfsSghCb35+deF3s-4nw@mail.gmail.com>
> ERROR: failed to re-find parent key in index "myindex" for deletion target
> page 851753
Which data type is that, and which LC_COLLATE setting is used?
If it's text (or similar), there's a known incompatibility with
de_DE.UTF-8 in different glibc versions. Possibly other locales and
libcs are affected as well.
Christoph
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index corruption after pg_basebackup
2018-08-28 16:08 Index corruption after pg_basebackup Jorge Torralba <jorge.torralba@gmail.com>
2018-08-28 16:42 ` Re: Index corruption after pg_basebackup Christoph Berg <myon@debian.org>
@ 2018-08-28 17:03 ` Jorge Torralba <jorge.torralba@gmail.com>
2018-08-28 17:27 ` Re: Index corruption after pg_basebackup Christoph Berg <myon@debian.org>
0 siblings, 1 reply; 4+ messages in thread
From: Jorge Torralba @ 2018-08-28 17:03 UTC (permalink / raw)
To: myon@debian.org; pgsql-admin
Same on both systems.
lc_collate
-------------
en_US.UTF-8
(1 row)
One of the failed index is
btree (account_id, lower(t1_type), t_id, "timestamp" DESC)
datatypes of: UUID, text, text, timestamp
On Tue, Aug 28, 2018 at 9:42 AM Christoph Berg <myon@debian.org> wrote:
> Re: Jorge Torralba 2018-08-28 <
> CACut7uRx72_R-nGOdOdnTSYCGGdKZ_TfsSghCb35+deF3s-4nw@mail.gmail.com>
> > ERROR: failed to re-find parent key in index "myindex" for deletion
> target
> > page 851753
>
> Which data type is that, and which LC_COLLATE setting is used?
>
> If it's text (or similar), there's a known incompatibility with
> de_DE.UTF-8 in different glibc versions. Possibly other locales and
> libcs are affected as well.
>
> Christoph
>
--
Thanks,
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential
information. If you are not the intended recipient, please do not print,
copy, retransmit, disseminate or otherwise use the information. Please
indicate to the sender that you have received this email in error and
delete the copy you received. Thank You.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index corruption after pg_basebackup
2018-08-28 16:08 Index corruption after pg_basebackup Jorge Torralba <jorge.torralba@gmail.com>
2018-08-28 16:42 ` Re: Index corruption after pg_basebackup Christoph Berg <myon@debian.org>
2018-08-28 17:03 ` Re: Index corruption after pg_basebackup Jorge Torralba <jorge.torralba@gmail.com>
@ 2018-08-28 17:27 ` Christoph Berg <myon@debian.org>
0 siblings, 0 replies; 4+ messages in thread
From: Christoph Berg @ 2018-08-28 17:27 UTC (permalink / raw)
To: Jorge Torralba <jorge.torralba@gmail.com>; +Cc: pgsql-admin
Re: Jorge Torralba 2018-08-28 <CACut7uTwWhXKHg+Fd3sy6hFnxWn58sLZPOr4frgrg23+NEgq-A@mail.gmail.com>
> Same on both systems.
>
> en_US.UTF-8
The problem is that you are moving from one OS (full installation, one
libc vendor, one libc version) to another OS (docker, possibly
different libc vendor, likely different libc version). If any
combination of strings sort differently in these locale incarnations,
you need to REINDEX.
I'm not aware of any specific problem with en_US.UTF-8, but I wouldn't
be surprised if there's differences.
Christoph
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-08-28 17:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-08-28 16:08 Index corruption after pg_basebackup Jorge Torralba <jorge.torralba@gmail.com>
2018-08-28 16:42 ` Christoph Berg <myon@debian.org>
2018-08-28 17:03 ` Jorge Torralba <jorge.torralba@gmail.com>
2018-08-28 17:27 ` Christoph Berg <myon@debian.org>
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