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 1pHPQl-0004My-IN for pgsql-general@arkaria.postgresql.org; Mon, 16 Jan 2023 13:21:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pHPQk-0001Ya-AY for pgsql-general@arkaria.postgresql.org; Mon, 16 Jan 2023 13:21:42 +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 1pHPQk-0001YP-00 for pgsql-general@lists.postgresql.org; Mon, 16 Jan 2023 13:21:42 +0000 Received: from mail-ej1-x636.google.com ([2a00:1450:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pHPQe-0007mC-NR for pgsql-general@postgresql.org; Mon, 16 Jan 2023 13:21:40 +0000 Received: by mail-ej1-x636.google.com with SMTP id qx13so9683398ejb.13 for ; Mon, 16 Jan 2023 05:21:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:cc:to:from:subject:message-id:from:to:cc:subject :date:message-id:reply-to; bh=gqcPJwlrh1oJ2CCdNkuF+yBdhTjGEDuffEg29OuAfOw=; b=J5pTUxXAlNS4087f5tflEFKctrPKLXOWn2Fwmw9P+iQx5vhA6f8CrnTA5BEkBEFXlP lXRpSAft5tp0WFkiEoZvStne6BBDl/4ZoxqtTEESOKn9t9Q4oUm55FLDfLTRd5SH+Enb 32Sj+Ufvo/a2PcySMMLtfw3U+CNiXQDNZf/d6bN26Y/6vmNsEH2gV4C+PlloHayz4Vij SC3+iVq38S6Rrp4UQ70DzL8q6e29wA13phNz1w7bxkl6dok03rvVEZQShG9LUMiyYKf2 PxzvSNvdqokVmCrIkmjb27bFz9pK7Pw4TrFjXDE+f3athAqZ22YLrVEpUzWY1kbvJukH +2Xg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:cc:to:from:subject:message-id:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=gqcPJwlrh1oJ2CCdNkuF+yBdhTjGEDuffEg29OuAfOw=; b=mH8sZVE6cpBMDdhhjyp3Xxg+FOjTytvJbI2lw3l/KiBD3fic62qssEsoXJ68s2E54t tXJW7VZP1WY22yQ+d2nuU3A3N8gLQAvcUzBE8I6qcjs4fROGnzEhLgHh3GNCQFO2+PH3 g75VzWGHQoBd/hm8Hmi7YD2FkljLL75/GMTPVlEO4tOg074kOth2zHklxo7RggvjPqgw xv6LiIAJX9yAfOsAEZoKz8QqNSJzBVZ7NpYRgRXsoTkXOOIusavTawPEVtjsJY/5GFeq o/xorpu2A0/uqbo+8O6MzdaFS5H4REp6hQxA1gKwjPCAo9etk0/qUnXXwZ7vBJWBa9Aj 0eFg== X-Gm-Message-State: AFqh2krdPhpkilMdv1tomxCjV0UUcpBvW6uDYN+JTovz6U6Oqj2PMHrD ZXDjWGB/KhfMowNTYwObkED3hg== X-Google-Smtp-Source: AMrXdXs0Vhth7dysrX/hMStIRqdkbKxtLR0WzgDtSvuXFHvtsxwvUjnYGlflagOnHpcdQDnj2fvSrg== X-Received: by 2002:a17:907:6e12:b0:86f:4321:4b85 with SMTP id sd18-20020a1709076e1200b0086f43214b85mr7710291ejc.18.1673875294361; Mon, 16 Jan 2023 05:21:34 -0800 (PST) Received: from localhost.localdomain (62-47-151-167.adsl.highway.telekom.at. [62.47.151.167]) by smtp.gmail.com with ESMTPSA id ku5-20020a170907788500b0086ca5a99a15sm3013999ejc.80.2023.01.16.05.21.33 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 16 Jan 2023 05:21:33 -0800 (PST) Message-ID: Subject: Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools? From: Laurenz Albe To: HECTOR INGERTO , Magnus Hagander Cc: "pgsql-general@postgresql.org " Date: Mon, 16 Jan 2023 14:21:32 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.3 (3.46.3-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 2023-01-16 at 08:41 +0000, HECTOR INGERTO wrote: > I have understood I shall not do it, but could the technical details be d= iscussed about > why silent DB corruption can occur with non-atomical snapshots? The database relies on the data being consistent when it performs crash rec= overy. Imagine that a checkpoint is running while you take your snapshot. The che= ckpoint syncs a data file with a new row to disk. Then it writes a WAL record and = updates the control file. Now imagine that the table with the new row is on a diff= erent file system, and your snapshot captures the WAL and the control file, but n= ot the new row (it was still sitting in the kernel page cache when the snapsho= t was taken). You end up with a lost row. That is only one scenario. Many other ways of corruption can happen. Yours, Laurenz Albe --=20 Cybertec | https://www.cybertec-postgresql.com