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 1pCjkp-0004bq-Qw for pgsql-general@arkaria.postgresql.org; Tue, 03 Jan 2023 16:03: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 1pCjko-0005JQ-N2 for pgsql-general@arkaria.postgresql.org; Tue, 03 Jan 2023 16:03: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 1pCjko-0005JF-Bd for pgsql-general@lists.postgresql.org; Tue, 03 Jan 2023 16:03:06 +0000 Received: from mx3.stadt-zuerich.ch ([194.56.33.12]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pCjkl-0005QH-Nk for pgsql-general@lists.postgresql.org; Tue, 03 Jan 2023 16:03:05 +0000 IronPort-SDR: GVr7WOALNGuNZSlc5khnu7vQCsA//e/Ur4Yf5Ce0fSkSSD0rkM8LJ+ChW8K3AXD8av9EiqhT3a eJTcFhCdq0nbHKzw++dyu/mS5es+dLO2c2ySzFAV9uRjKAJ7DiMrAfuAqK20guoi0UZQgHdIvC W/oRu8b46nh7Gby5EJFWlrPZIQfKUOMSvwIL9x5c4RlrAs1UF+3uAF60Sm5LLsGVx/k7jzR4Zf mz/kUKh92/Vr5t1VQaP8d4VOHAG9RkAzkGpGvdzsY47yb7FPflER245XmMsPBm9n091Ta4/zfO Luk= X-IronPort-AV: E=Sophos;i="5.96,297,1665439200"; d="scan'208,217";a="132206326" Received: from unknown (HELO SZHM27372.global.szh.loc) ([10.7.50.60]) by mx3.prm.szh.zone with ESMTP/TLS/AES256-GCM-SHA384; 03 Jan 2023 17:03:02 +0100 Received: from SZHM27370.global.szh.loc (10.7.50.50) by SZHM27372.global.szh.loc (10.7.50.63) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2375.18; Tue, 3 Jan 2023 17:03:01 +0100 Received: from SZHM27370.global.szh.loc ([fe80::25ce:879f:5795:3325]) by SZHM27370.global.szh.loc ([fe80::25ce:879f:5795:3325%19]) with mapi id 15.01.2375.018; Tue, 3 Jan 2023 17:03:01 +0100 From: "Zwettler Markus (OIZ)" To: "pgsql-general@lists.postgresql.org" Subject: postgres restore & needed history files Thread-Topic: postgres restore & needed history files Thread-Index: AdkfcUECBYzXAF5nQG+9MHuEV4xR1Q== Date: Tue, 3 Jan 2023 16:03:01 +0000 Message-ID: <0ce7906d55174fa6902c011c0aa0e884@zuerich.ch> Accept-Language: de-CH, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.7.48.249] Content-Type: multipart/alternative; boundary="_000_0ce7906d55174fa6902c011c0aa0e884zuerichch_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_0ce7906d55174fa6902c011c0aa0e884zuerichch_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 directory. After some time, we had to perform a point-in-time recovery of 1 of the 5 d= atabases to a point in time 7 days in the past. We tried to perform a point-in-time restore on another host, which did not = work until we copied the contents of the current pg_wal directory. The current pg_wal directory included 8 history files: 00000002.history to = 00000009.history. The point-in-time restore worked smoodly after it had all these history fil= es. Afaik, all necessary history files should also be restored by the restore_c= ommand. 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 rete= ntion of 30 days. Question: Is it necessary to retain all history files? --_000_0ce7906d55174fa6902c011c0aa0e884zuerichch_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

We a= re using a DIY Postgres backup:

---<= o:p>

psql= -c "select pg_start_backup ('Full');"

save= -s "${NSR_SERVER}" -g "${NSR_POOL}" "${PGDATA}&qu= ot;

psql= -c "select pg_stop_backup();"

---<= o:p>

The = pg_wal directory is not saved with it because it is a linked directory.

 

 

Afte= r some time, we had to perform a point-in-time recovery of 1 of the 5 datab= ases to a point in time 7 days in the past.

We t= ried to perform a point-in-time restore on another host, which did not work= until we copied the contents of the current pg_wal directory.

The = current pg_wal directory included 8 history files: 00000002.history to 0000= 0009.history.

The = point-in-time restore worked smoodly after it had all these history files.<= o:p>

 

 

Afai= k, all necessary history files should also be restored by the restore_comma= nd.

I ha= d a look at our archived wal backups and found that 00000002.history to 000= 00008.history files already had been deleted due to our NSR backup retentio= n of 30 days.

 

 

Ques= tion: Is it necessary to retain all history files?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

--_000_0ce7906d55174fa6902c011c0aa0e884zuerichch_--