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 1pHgxv-00045E-Rx for pgsql-general@arkaria.postgresql.org; Tue, 17 Jan 2023 08:05:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pHgxu-00012q-MX for pgsql-general@arkaria.postgresql.org; Tue, 17 Jan 2023 08:05:06 +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 1pHgxu-00011z-AS for pgsql-general@lists.postgresql.org; Tue, 17 Jan 2023 08:05:06 +0000 Received: from mail-ed1-x533.google.com ([2a00:1450:4864:20::533]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pHgxr-0001Kc-UY for pgsql-general@postgresql.org; Tue, 17 Jan 2023 08:05:05 +0000 Received: by mail-ed1-x533.google.com with SMTP id 18so43875887edw.7 for ; Tue, 17 Jan 2023 00:05:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=tuNCWnkMufFdis+1T9aJenBOKJtocMISUkO//fQiUVk=; b=UfdeCpmfB5TulA3BcvG9YbVXN9HDQRqCnOz6BAmgg5DwrxL2UXkiot6V9UVr5EG1tH 0m5NiXYga7hJ0P+XWv2zZLkXVVU3W2NQloPxTWCCwumYwczH5LWYzqt3scQqs9eB5DDS Mz+VBitJ2h7HHq5JzlH+Zsv5xwtlluNtlKh4wwVV37OpKtI5Cmv37+jgrdXWwbXwrq+3 IqyvqN+SNEw7SSxAZPsmqlIfBMfaYQwb9kyP1B2COFF7Ltzsd7BdoPexTjWV6nK/U00C n2fooS/Hk71fGiv1L5P7lJlRD03729bMfeP7Y8ZLrDUEX4CxDMok8WVch3Z7Ic9kl44n YllQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=tuNCWnkMufFdis+1T9aJenBOKJtocMISUkO//fQiUVk=; b=SY3vcga+wC0YYVWstNuElco/kHNzralTrYEQUiLwf+IlGrjNl4w88Z5rbDOhK7OFa1 FtQYyG4dg21iaImrgqStVcQ1xoDnjuuYlsNUkY/WPwD/XqvsC5cuW5GTYocRYV+ZfHVY tmPHl/FDim3v9KD8LRsv/e+P0E7h2TKjiSGTGHMKBzSMYKH6CSiPEO3f6OiURlDl7D9H D8kK4EYQoxb2MDXnm6c3LxVocct44QrpbgMP2Ld9GNQon873jAMNOfbJQfa6wy6cYuz9 UpP1+soezxuiS4k/MurTJkEygYuKpK5BsOAhJ0KWaCEe8BCTz4hhqtEiW7lk+6+VdFDd k6Iw== X-Gm-Message-State: AFqh2koGKuWs9A2S+gmytdSFrFGIWKpfOX/I7zbqEUfNSIAstKjmSl73 KBLVPEeQXwDwq8KtpdVPL8k5o0ydXMk= X-Google-Smtp-Source: AMrXdXtn2oEXHSZiR/CINEJj1GI1cOl0HzTqumoGLE/oQCYGoM5ZIV4NpOvY4srEp23cH0vuGCvLyQ== X-Received: by 2002:a05:6402:1002:b0:49b:63ea:b5d8 with SMTP id c2-20020a056402100200b0049b63eab5d8mr2189849edu.4.1673942702662; Tue, 17 Jan 2023 00:05:02 -0800 (PST) Received: from hollewijn.internal (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id g11-20020a056402428b00b004722d7e8c7csm12315851edc.14.2023.01.17.00.05.01 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 17 Jan 2023 00:05:02 -0800 (PST) Content-Type: text/plain; charset=us-ascii Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\)) Subject: Re: Are ZFS snapshots unsafe when PGSQL is spreading through multiple zpools? From: Alban Hertroys In-Reply-To: Date: Tue, 17 Jan 2023 09:05:00 +0100 Cc: Laurenz Albe , Magnus Hagander , "pgsql-general@postgresql.org " Content-Transfer-Encoding: quoted-printable Message-Id: <9CCDF037-BB5F-4369-AE12-37B25C20B0EF@gmail.com> References: To: HECTOR INGERTO X-Mailer: Apple Mail (2.3608.120.23.2.7) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 16 Jan 2023, at 15:37, HECTOR INGERTO = wrote: >=20 > > The database relies on the data being consistent when it performs = crash recovery. > > Imagine that a checkpoint is running while you take your snapshot. = The checkpoint > > 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 different > > file system, and your snapshot captures the WAL and the control = file, but not > > the new row (it was still sitting in the kernel page cache when the = snapshot was taken). > > You end up with a lost row. > >=20 > > That is only one scenario. Many other ways of corruption can = happen. > =20 > Can we say then that the risk comes only from the possibility of a = checkpoint running inside the time gap between the non-simultaneous = snapshots? I recently followed a course on distributed algorithms and recognised = one of the patterns here. The problem boils down to a distributed snapshotting algorithm, where = both ZFS filesystem processes each initiate their own snapshot = independently. Without communicating with each other and with the database which = messages (in this case traffic to and from the database to each FS) are = part of their snapshots (sent or received), there are chances of lost = messages, where either none of the process snapshots know that a = 'message' was sent or none received it. Algorithms like Tarry, Lai-Yang or the Echo algorithm solve this by = adding communication between those processes about messages in transit. Alban Hertroys -- There is always an exception to always.