Re: Losing records in PostgreSQL 9.6

Thanks for your input!

We checked the application that has access to the database, but it would
never delete rows from that table. The missing rows in the database were
stored at some point through committed transactions and had a lower
sequential primary key. We don't think the transactions were rolled back
since they were part of an older backup.

We believe that there was probably a manual access through the customer or
a service partner, but wanted to make sure that there is no other way that
Postgres would lose rows during a pg_dump because of something like a
hardware failure, for instance.

Best regards,
Andreas

On Sat, May 7, 2022 at 4:03 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

> On 5/4/22 09:55, A G wrote:
>
> Hi,
> thanks for your help.
>
> My team is using Postgres 9.6.10 for an on-premise application (we are
> planing on upgrading to a newer Postgres version). Our application comes
> with Postgres running in a docker container with its data stored in a
> docker volume. Our software uses pg_dump / pg_restore to backup and restore
> the database.
>
> Now we got a ticket from a customer where their database is missing rows
> from a table. There are 971 consecutive rows missing from the beginning of
> the table. The missing rows were inserted first. We find it also strange,
> that all the other tables don’t seem to be affected at all. It appears that
> there is only data loss in this single table.
> Unfortunately, we don’t have access to the original database anymore and
> need to find out what happened through the backups the customer provides.
> We have one backup right after they installed and initially configured the
> application, which seems complete. Then there is another backup 10 months
> later where the first 971 rows are already missing in this one table.
>
> If we exclude a manual deletion, which the customer denies,
>
>
> There's more to PEBKAC than manual deletion.
>
> we are wondering if it’s possible that Postgres 9.6 could lose some of its
> data through a storage or memory error and would create a “successful”
> pg_dump with only partial data? Is such a behaviour even thinkable with
> Postgres?
>
> Do you have an idea what else could cause this issue?
>
>
> Uncommitted transactions?
> * Purge job with a bug in it?
> * Two different date columns (for example "transaction_date" and
> "posted_date") which are *expected to be* the same apparently not
> always. Since the errors apparently happen at the beginning of the month,
> the purge job might have seen them as the previous month's records.
>
> These are our dump and restore commands:
> pg_dump -Fc --no-acl --no-owner -U acme -h 127.0.0.1 acme > acme.dump
> pg_restore -d acme -n public -U acme -h 127.0.0.1 --jobs=4 acme.dump
>
> We use just a single db user to access the database and we don’t use RLS.
>
> Thank you.
>
> Best regards,
> Andreas
>
>
> --
> Angular momentum makes the world go 'round.
>