Re: Losing records in PostgreSQL 9.6

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.