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 1nmKwX-0006jK-UA for pgsql-admin@arkaria.postgresql.org; Wed, 04 May 2022 19:45:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nmKwW-0004h4-R1 for pgsql-admin@arkaria.postgresql.org; Wed, 04 May 2022 19:45:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nmGPb-0004No-Fp for pgsql-admin@lists.postgresql.org; Wed, 04 May 2022 14:55:31 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nmGPY-0000wA-VZ for pgsql-admin@lists.postgresql.org; Wed, 04 May 2022 14:55:30 +0000 Received: by mail-ej1-x629.google.com with SMTP id n10so3393519ejk.5 for ; Wed, 04 May 2022 07:55:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:from:date:message-id:subject:to; bh=JbAvlXg7gpB4ZDYZLjxXC8gE0yvIDwtlOVzQwcRWYkA=; b=jQeisLN6njoK6h0oXJ8rewXuobKTB8dN6j0pS+Yq0u856xayxzM43CWPLClqRTHdFc rrO2crr5ey+8av0HoSNeX1yKL0GL/+2oO3LvNJblsRNBMddVIfwfSt+XulckVjuhcwwy wr8ieT/iLTpLbtpMbR+RKKgu3xh6R65QoN9guYouqeCn0D4vquQsZm8lWyxmim+kV6gv A5NfvlNjkwjLN0pm2yOYscXozky0KIQPk8icfXpd3i1uKUC6EdjF34eBlmuwyLrP44vA TVLpmmnNkoUssx4CZXrHZy7tQo+O+SN18ut7wnDyqilKKsiw6sclDaoKqQZjbR49L9L2 THwA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=JbAvlXg7gpB4ZDYZLjxXC8gE0yvIDwtlOVzQwcRWYkA=; b=maqR+aWoFCRoVI0mks5vmGnN+2+csywFgB5F7y8jlYQbF+0RKM4rdFpEwdLKrUP9z3 3K+Ov/e/Z01qVvCGtI/AdHliDopu1rySeFIJFglFNIJ4toxM917hYxWhRvkpXYWcnIRn WPAJlRthRzCcSrnGjDoJ+aHjoGcMWQnjHpE5JOZqXgHX0YAe+vG0FZDH6z/94JnPUffY VMnk1T+3dDPwW8Ua2neafQ4p+GQWzI9+u8QdmApWesBUey6cqeBmuL33taEzynrImBXw 1DcbSNgdPiCw2yOqPosSFfaLEN9y/NWbsfTP7HVzpTM1bSttZgkUuaWOT9z+fh2aDosp nV3A== X-Gm-Message-State: AOAM530PSjaMWjGbbCu1ZG4qAgTOirb/I3NXriPJPsqIoqnBB4BtU/bP HwAlgRC1OyHqAQ57jBCTRRervv1hBZAbVk5EEWTCoOlsZaA= X-Google-Smtp-Source: ABdhPJy6U/V+0j9Xrswn76CRNodpE0LLE8aiF/qstpnLSVjDxtc3kqp3FMUr09rhsHZysz5SOpbm4yo8/QE705AKTA0= X-Received: by 2002:a17:907:1c82:b0:6f4:d2ee:2f8e with SMTP id nb2-20020a1709071c8200b006f4d2ee2f8emr1761674ejc.714.1651676127997; Wed, 04 May 2022 07:55:27 -0700 (PDT) MIME-Version: 1.0 From: A G Date: Wed, 4 May 2022 16:55:17 +0200 Message-ID: Subject: Losing records in PostgreSQL 9.6 To: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000e4be4505de30d0a0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e4be4505de30d0a0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=99t seem to be affected at all. It appe= ars that there is only data loss in this single table. Unfortunately, we don=E2=80=99t have access to the original database anymor= e 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, we are wondering if it=E2=80=99s possible that Postgres 9.6 could lose some of its= data through a storage or memory error and would create a =E2=80=9Csuccessful=E2= =80=9D 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? 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=3D4 acme.dump We use just a single db user to access the database and we don=E2=80=99t us= e RLS. Thank you. Best regards, Andreas --000000000000e4be4505de30d0a0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,=C2=A0
thanks f= or your help.

My team is using Postgres 9.6.10 for an on-premise application (we are pla= ning on upgrading to a newer Postgres version). Our application comes with = Postgres running in a docker container with its data stored in a docker vol= ume. Our software uses pg_dump / pg_restore to backup and restore the datab= ase.

Now we g= ot a ticket from a customer where their database is missing rows from a tab= le. 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 th= e other tables don=E2=80=99t seem to be affected at all. It appears that th= ere is only data loss in this single table.
Unfortunately, we don=E2=80=99t 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 t= hey installed and initially configured the application, which seems complet= e. Then there is another backup 10 months later where the first 971 rows ar= e already missing in this one table.

If we exclude a manual deletion, which the customer= denies, we are wondering if it=E2=80=99s possible that Postgres 9.6 could = lose some of its data through a storage or memory error and would create a = =E2=80=9Csuccessful=E2=80=9D pg_dump with only partial data? Is such a beha= viour even thinkable with Postgres?

Do you have an idea what else could cause this issue= ?


These are our du= mp and restore commands:
pg_dump -Fc --no-acl --no-owner -U acme -h 127.0.0.1 acm= e > acme.dump
pg_restore -d acme -n public -U acme -h 127.0.0.1 --jobs=3D4 acm= e.dump

<= span style=3D"color:rgb(0,0,0);font-family:Helvetica;font-size:12px">We use= just a single db user to access the database and we don=E2=80=99t use RLS.=
=
Thank you.
Best regards,<= /span>
<= span style=3D"color:rgb(0,0,0);font-family:Helvetica;font-size:12px">Andrea= s
--000000000000e4be4505de30d0a0--