Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nmLRv-0007gN-Jf for pgsql-admin@arkaria.postgresql.org; Wed, 04 May 2022 20:18:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nmLRu-0001IB-18 for pgsql-admin@arkaria.postgresql.org; Wed, 04 May 2022 20:18:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nmLRt-0001I1-7y for pgsql-admin@lists.postgresql.org; Wed, 04 May 2022 20:18:13 +0000 Received: from mail-oi1-x234.google.com ([2607:f8b0:4864:20::234]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nmLRq-0007FW-OX for pgsql-admin@lists.postgresql.org; Wed, 04 May 2022 20:18:12 +0000 Received: by mail-oi1-x234.google.com with SMTP id v66so2333171oib.3 for ; Wed, 04 May 2022 13:18:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=+BDfW0JHAoHpZkX/gRVGs/Z0prWC8H8cq351tQHqnBE=; b=TbIQaU13tCZqgrn7LtxtzBuS1IfoT5XdVt0du0JwVQO6UtW4wJzVsJL0Tfr/t2XmEt KV+Q9E6TZU0s+S0Hh6z4F2xgNQtrsqDZ2aKwVYxHz7NUQZmJEOs4OEwxkymfTraHfpYw MA3wK8Tg7Z1bIOrm8MI/lEmiwczX94LXsiRQrrana2KIM+z+wzJC2d7K0MomQq+l49YN VTMZGDC2GNOQ1XbeX4T9HuZ2hOSP0W1wjYbxJzOSvwLAta4Pmql9Q5XnpIklaj3hUIYK ySzwwUz5R0wsBuhlptec1vPk/AH6Hgeww/gE6cWPURvEeoomRrczlXAuRRNWLVmjArp6 cFTg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=+BDfW0JHAoHpZkX/gRVGs/Z0prWC8H8cq351tQHqnBE=; b=U+OQb8KQvBn4sa7vzxcfdrw8Sq3nmMYWrfONUOsDbZYufFDhp5VPD3RF9l2KT5AVm4 M6AOxxz/scZlFEVQ99iJTKFETnKUYE6n0XAW9g1MURNGlljBe1GcADLPkZEKBWo3qtp+ 1M0Cy6zGn02QVHTuaQdv30Frog8lsRwFs4YsGBRwvNCeEgjl4LA1nPFE95MUhGfHK5bI 605FsWqef/lYXwduGFGXeJ6Zwztrq96Ou72hmRV1xGP+OdJQZzlyxm/+B391ZLh4rqzZ yLjJShRGbMMeAp8fBGlJpTBn4QdPts4NKcmfkisY0mrKu2eHTO+97nIbVbLgBbCFuUO8 Yi8Q== X-Gm-Message-State: AOAM531CIPgzJHolF4BR+AT7gflHUc9rtdtZPhV8FTF71BJ55qYcSXC0 2rzmyqDXqFwXE8pHLgM3FZgrXO/jB18= X-Google-Smtp-Source: ABdhPJzCGQ3MeuNfghGikTvikMipKGdca6NxP+Gbjz4TpOwSBRQYOQ4tyay//LgdogEjSEBb5gWXaA== X-Received: by 2002:a05:6808:1455:b0:326:70d0:b194 with SMTP id x21-20020a056808145500b0032670d0b194mr128933oiv.275.1651695489859; Wed, 04 May 2022 13:18:09 -0700 (PDT) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id p203-20020acaf1d4000000b00325cda1ffacsm4702749oih.43.2022.05.04.13.18.09 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 04 May 2022 13:18:09 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------003ACDvXDfDpzKMlvsWefD4O" Message-ID: <397f5b28-0f5c-de1f-c825-06e2ea7b6a1f@gmail.com> Date: Wed, 4 May 2022 15:18:08 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.8.1 Subject: Re: Losing records in PostgreSQL 9.6 Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------003ACDvXDfDpzKMlvsWefD4O Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------003ACDvXDfDpzKMlvsWefD4O Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------003ACDvXDfDpzKMlvsWefD4O--