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 1pDfH9-0004Cb-NA for pgsql-general@arkaria.postgresql.org; Fri, 06 Jan 2023 05:28:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pDfH8-0008I8-Cm for pgsql-general@arkaria.postgresql.org; Fri, 06 Jan 2023 05:28:18 +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 1pDfH7-0008Hy-Sa for pgsql-general@lists.postgresql.org; Fri, 06 Jan 2023 05:28:18 +0000 Received: from mail-ej1-x634.google.com ([2a00:1450:4864:20::634]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pDfH4-00009q-SX for pgsql-general@lists.postgresql.org; Fri, 06 Jan 2023 05:28:17 +0000 Received: by mail-ej1-x634.google.com with SMTP id x22so1301151ejs.11 for ; Thu, 05 Jan 2023 21:28:14 -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:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=eMf2HB+Thwsl+s0vUngGA0zS89DO37cS5RBGr9rr9Nk=; b=LCJPVwb2jNce4otR5+mxvXxEgAItJ3MdHZC8epkjkYwTfkjoryWjtiLfhjbct3TE1n a1nF2Xv8bZty5ze0jUCraZKk0X0c3buZbxyfA1Dr+g5BwNS1SQ5f5QqEHZUBtsLp1KjH DbjafqI0itf99MlSu2/UNMsROaFCpSeJlxue9cacW2uQIVFUSbGkH4x32nc7rkwgIZ5g hoICUbiqEhOiNTBatMVLGILFxjMv5LtanBDyH6N5C5XSjK98RGQc7iOE74BTR0147qMT pVCn8wfgEtxyNSFQe59nFZNuRsBUwrFJ/3HAJpx+aJLMscI7/ivZJ+fuDcaB8BbyjN9f sV3w== 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:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=eMf2HB+Thwsl+s0vUngGA0zS89DO37cS5RBGr9rr9Nk=; b=HsD7pKZTD5P2b4/5U4o7cw2oGlzSt1l1n6EPiztjmOWzxkMIPH28OLC1AAD5iqIQOw ADgKH+6b8PKG42vYNO+AhBLQypLtc85wNr88e7x19Fllt/doX3DMQlgbXb6CtvztWLaT z5vYk8qxWA6lNyyiqPs/7fiiQFJiCFy+ztJ0VVIdtCC9tBakdCVWGOBJk0AuDaJ8ifvV qcPAPLjv/lUC3QtXgirb1syvVSKagVqVx1VG5Mgt3jI5ZhaAe7vmioFFIEj2VbdRCkFl 0BqyYXQP75b3gUMUMAD4pjYMauIXWs1J/Fpdy0psURcttekdgaxFCyFDT4Xtw4im8KON eAVQ== X-Gm-Message-State: AFqh2kqGRK3WPp7V+WHjVDUXhN63WNnG8QS50/YihgtskAjnFWZYGy70 L42rtI2oxMGtMDOi0PgNdmnDNA== X-Google-Smtp-Source: AMrXdXtRT2v/ncPM0h8M9W1r+0BM/PCqCRH2t4CpUIdr7DhngTd6+w5tOuwdGBWcP5CLbtqDspdNBw== X-Received: by 2002:a17:906:17d2:b0:846:fafa:5c77 with SMTP id u18-20020a17090617d200b00846fafa5c77mr38683558eje.48.1672982893131; Thu, 05 Jan 2023 21:28:13 -0800 (PST) Received: from localhost.localdomain (62-46-248-108.adsl.highway.telekom.at. [62.46.248.108]) by smtp.gmail.com with ESMTPSA id w22-20020a1709061f1600b0081bfc79beaesm58842ejj.75.2023.01.05.21.28.12 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 05 Jan 2023 21:28:12 -0800 (PST) Message-ID: Subject: Re: postgres restore & needed history files From: Laurenz Albe To: "Zwettler Markus (OIZ)" , "pgsql-general@lists.postgresql.org" Date: Fri, 06 Jan 2023 06:28:12 +0100 In-Reply-To: <0ce7906d55174fa6902c011c0aa0e884@zuerich.ch> References: <0ce7906d55174fa6902c011c0aa0e884@zuerich.ch> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.2 (3.46.2-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 2023-01-03 at 16:03 +0000, Zwettler Markus (OIZ) wrote: > We are using a DIY Postgres backup: > --- > psql -c "select pg_start_backup ('Full');" > save -s "${NSR_SERVER}" -g "${NSR_POOL}" "${PGDATA}" > psql -c "select pg_stop_backup();" > --- > The pg_wal directory is not saved with it because it is a linked director= y. > =C2=A0 > =C2=A0 > After some time, we had to perform a point-in-time recovery of 1 of the 5= databases to a point in time 7 days in the past. > We tried to perform a point-in-time restore on another host, which did no= t work until we copied the contents of the current pg_wal directory. > The current pg_wal directory included 8 history files: 00000002.history t= o 00000009.history. > The point-in-time restore worked smoodly after it had all these history f= iles. > =C2=A0 > =C2=A0 > Afaik, all necessary history files should also be restored by the restore= _command. > I had a look at our archived wal backups and found that 00000002.history = to 00000008.history files already had been deleted due to our NSR backup re= tention of 30 days. > =C2=A0 > Question: Is it necessary to retain all history files? Yes, the history files are an integral part of the database. You must not delete them from your WAL archive. Yours, Laurenz Albe --=20 Cybertec | https://www.cybertec-postgresql.com