Plan for exclusive backup method
Hi team,
https://www.postgresql.org/docs/12/continuous-archiving.html
It is mentioned here that exclusive backup method is deprecated and should
be avoided. it is now recommended that all users upgrade their scripts to
use non-exclusive backups.
Does this mean select pg_start_backup('label', false, *true*) is deprecated?
In a future version, will it not allow the third argument to be true?
--
thanks and regards,
Meera R Nair
Re: Plan for exclusive backup method
Hi Magnus,
Thank you.
On Wed, May 19, 2021 at 3:30 PM Magnus Hagander wrote:
> On Wed, May 19, 2021 at 11:58 AM MEERA wrote:
> >
> > Hi team,
> >
> > https://www.postgresql.org/docs/12/continuous-archiving.html
> >
> > It is mentioned here that exclusive backup method is deprecated and
> should be avoided. it is now recommended that all users upgrade their
> scripts to use non-exclusive backups.
> >
> > Does this mean select pg_start_backup('label', false, true) is
> deprecated?
>
> Yes.
>
> > In a future version, will it not allow the third argument to be true?
>
> The exact future has not been decided, but most likely yes. And as
> most installations using it today are unsafe, it is recommended that
> you don't use it even before then. It basically exists for backwards
> compatibility with PostgreSQL prior to 9.6.
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/
> Work: https://www.redpill-linpro.com/
>
--
thanks and regards,
Meera R Nair
pg_dump is filling C: drive up to 100 percent
Hi team, pg_dump is filling C:\ This is for postgres version 12. Binary directory, data directory are in E:\ I'm redirecting pg_dump output also to E:\, I was taking a tar dump output. But C:\ is getting filled up . Looks like it is used for some sort of temporary staging. Is there a way not to use C:\ for this? Regards, Meera
RE: pg_dump is filling C: drive up to 100 percent
Hi Abdul, We do realize that. With tar format, is there a way to customize the path used for temporary local files? Some way to configure another drive instead of using C:\? Regards, Meera From: Abdul Qoyyuum Sent: Tuesday, July 19, 2022 3:40 PM To: Meera Nair Cc: pgsql-general@lists.postgresql.org Subject: Re: pg_dump is filling C: drive up to 100 percent External email. Inspect before opening. Try dumping without tar format. https://dba.stackexchange.com/a/52730<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdba.stackexchange.com%2Fa%2F52730&data=05%7C01%7Cmnair%40commvault.com%7C3ec9f731977545dc7aba08da696ee8e6%7C40ed1e38a16e46229d7c45161b6969d5%7C0%7C0%7C637938222475670312%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=jsdU7sEJAlpkshOkOQ3X7KQnnALAD43G8jxDfSkOaOI%3D&reserved=0> On Tue, Jul 19, 2022 at 4:33 PM Meera Nair mailto:mn...@commvault.com>> wrote: Hi team, pg_dump is filling C:\ This is for postgres version 12. Binary directory, data directory are in E:\ I'm redirecting pg_dump output also to E:\, I was taking a tar dump output. But C:\ is getting filled up . Looks like it is used for some sort of temporary staging. Is there a way not to use C:\ for this? Regards, Meera -- Abdul Qoyyuum Bin Haji Abdul Kadir HP No: +673 720 8043
RE: pg_dump is filling C: drive up to 100 percent
Hi Thomas, This worked for me, thanks a lot. Regards, Meera From: Thomas Boussekey Sent: Tuesday, July 19, 2022 5:33 PM To: Meera Nair Cc: Abdul Qoyyuum ; pgsql-general@lists.postgresql.org Subject: Re: pg_dump is filling C: drive up to 100 percent External email. Inspect before opening. Hello Meera, Le mar. 19 juil. 2022 à 13:42, Meera Nair mailto:mn...@commvault.com>> a écrit : Hi Abdul, We do realize that. With tar format, is there a way to customize the path used for temporary local files? Some way to configure another drive instead of using C:\? I would try this solution: https://superuser.com/a/1448861/278835<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsuperuser.com%2Fa%2F1448861%2F278835&data=05%7C01%7Cmnair%40commvault.com%7C25d823f8dd8e45dfcbd808da697ebb45%7C40ed1e38a16e46229d7c45161b6969d5%7C0%7C0%7C637938290274062926%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=pbt6xRe2cpru0n%2B7kNFpiGgeAm7BzfYYjIvTWEWUD%2BY%3D&reserved=0> Regards, Meera From: Abdul Qoyyuum mailto:aqoyy...@cardaccess.com.au>> Sent: Tuesday, July 19, 2022 3:40 PM To: Meera Nair mailto:mn...@commvault.com>> Cc: pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org> Subject: Re: pg_dump is filling C: drive up to 100 percent External email. Inspect before opening. Try dumping without tar format. https://dba.stackexchange.com/a/52730<https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdba.stackexchange.com%2Fa%2F52730&data=05%7C01%7Cmnair%40commvault.com%7C25d823f8dd8e45dfcbd808da697ebb45%7C40ed1e38a16e46229d7c45161b6969d5%7C0%7C0%7C637938290274062926%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=wMZcngDmewkZK4uQl04IGkf9SBg4YLViVUQAjLtNN94%3D&reserved=0> On Tue, Jul 19, 2022 at 4:33 PM Meera Nair mailto:mn...@commvault.com>> wrote: Hi team, pg_dump is filling C:\ This is for postgres version 12. Binary directory, data directory are in E:\ I'm redirecting pg_dump output also to E:\, I was taking a tar dump output. But C:\ is getting filled up . Looks like it is used for some sort of temporary staging. Is there a way not to use C:\ for this? Regards, Meera -- Abdul Qoyyuum Bin Haji Abdul Kadir HP No: +673 720 8043 HTH, Thomas
Unable to archive logs in standby server
Hi team,
With non-exclusive backup method, trying backup from standby node.
But pg_stop_backup function returns "WAL archiving is not enabled..." and the
logs are not archived to WAL directory configured.
Please check if I am missing anything in configuring this properly,
Server was restarted after setting the archiving params in postgresql.conf
Below is from version 14:
postgres=# select pg_start_backup('label', false, false);
pg_start_backup
-
0/6D8
(1 row)
postgres=# select pg_stop_backup('false');
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL
segments are copied through other means to complete the backup
pg_stop_backup
---
(0/60001C0,"START WAL LOCATION: 0/6D8 (file 00010006)+
CHECKPOINT LOCATION: 0/6000110 +
BACKUP METHOD: streamed +
BACKUP FROM: standby +
START TIME: 2022-07-21 12:42:11 IST +
LABEL: label +
START TIMELINE: 1+
","")
(1 row)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
---
t
(1 row)
postgres=# show wal_level;
wal_level
---
replica
(1 row)
postgres=# show archive_mode;
archive_mode
--
on
(1 row)
postgres=# show archive_command;
archive_command
copy "%p" "D:\PostgreSQL\14\standby_14\wal\%f"
(1 row)
Regards,
Meera
Unable to archive logs in standby server
Hi team,
With non-exclusive backup method, trying backup from standby node.
But pg_stop_backup function returns "WAL archiving is not enabled..." and the
logs are not archived to WAL directory configured.
Please check if I am missing anything in configuring this properly,
Server was restarted after setting the archiving params in postgresql.conf
Below is from version 14:
postgres=# select pg_start_backup('label', false, false);
pg_start_backup
-
0/6D8
(1 row)
postgres=# select pg_stop_backup('false');
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL
segments are copied through other means to complete the backup
pg_stop_backup
---
(0/60001C0,"START WAL LOCATION: 0/6D8 (file 00010006)+
CHECKPOINT LOCATION: 0/6000110 +
BACKUP METHOD: streamed +
BACKUP FROM: standby +
START TIME: 2022-07-21 12:42:11 IST +
LABEL: label +
START TIMELINE: 1+
","")
(1 row)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
---
t
(1 row)
postgres=# show wal_level;
wal_level
---
replica
(1 row)
postgres=# show archive_mode;
archive_mode
--
on
(1 row)
postgres=# show archive_command;
archive_command
copy "%p" "D:\PostgreSQL\14\standby_14\wal\%f"
(1 row)
Regards,
Meera
RE: Unable to archive logs in standby server
Hi Guillaume/team,
I set archive_mode = always in master and standby.
Archival to standby WAL directory completed when
* standby server was restarted
* pg_stop_backup was executed in master
But archival hangs when pg_stop_backup is executed in standby.
Could someone help to get this working?
postgres=# select pg_start_backup('test', true, false);
pg_start_backup
-
1/F960
(1 row)
postgres=# select pg_stop_backup('f');
NOTICE: base backup done, waiting for required WAL segments to be archived
WARNING: still waiting for all required WAL segments to be archived (60
seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely
cancel this backup, but the database backup will not be usable without all the
WAL segments.
WARNING: still waiting for all required WAL segments to be archived (120
seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely
cancel this backup, but the database backup will not be usable without all the
WAL segments.
……..
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
---
t
(1 row)
postgres=# show wal_level;
wal_level
---
replica
(1 row)
postgres=# show archive_mode;
archive_mode
--
always
(1 row)
postgres=# select version();
version
-
PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
(Red Hat 4.8.5-39), 64-bit
(1 row)
Regards,
Meera
From: Guillaume Lelarge
Sent: Thursday, July 21, 2022 6:01 PM
To: Meera Nair
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Unable to archive logs in standby server
External email. Inspect before opening.
Hi,
Le jeu. 21 juil. 2022 à 13:58, Meera Nair
mailto:mn...@commvault.com>> a écrit :
Hi team,
With non-exclusive backup method, trying backup from standby node.
But pg_stop_backup function returns “WAL archiving is not enabled…” and the
logs are not archived to WAL directory configured.
Please check if I am missing anything in configuring this properly,
Server was restarted after setting the archiving params in postgresql.conf
Below is from version 14:
postgres=# select pg_start_backup('label', false, false);
pg_start_backup
-
0/6D8
(1 row)
postgres=# select pg_stop_backup('false');
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL
segments are copied through other means to complete the backup
pg_stop_backup
---
(0/60001C0,"START WAL LOCATION: 0/6D8 (file 00010006)+
CHECKPOINT LOCATION: 0/6000110 +
BACKUP METHOD: streamed +
BACKUP FROM: standby +
START TIME: 2022-07-21 12:42:11 IST +
LABEL: label +
START TIMELINE: 1+
","")
(1 row)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
---
t
(1 row)
postgres=# show wal_level;
wal_level
---
replica
(1 row)
postgres=# show archive_mode;
archive_mode
--
on
(1 row)
You're doing backups from the standby, and to allow archiving on the backups,
archive_mode should be set to always.
--
Guillaume.
RE: Unable to archive logs in standby server
Hi,
I am still facing issue with executing pg_stop_backup in standby manually.
Copying from previous email -
Archival hangs. Is this expected?
postgres=# select pg_start_backup('test', true, false);
pg_start_backup
-
1/F960
(1 row)
postgres=# select pg_stop_backup('f');
NOTICE: base backup done, waiting for required WAL segments to be archived
WARNING: still waiting for all required WAL segments to be archived (60
seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely
cancel this backup, but the database backup will not be usable without all the
WAL segments.
WARNING: still waiting for all required WAL segments to be archived (120
seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely
cancel this backup, but the database backup will not be usable without all the
WAL segments.
……..
…………
Regards,
Meera
From: subin
Sent: Friday, September 2, 2022 12:36 AM
To: Meera Nair
Subject: Re: Unable to archive logs in standby server
External email. Inspect before opening.
Hope you had a good time.
On Tue, Aug 30, 2022 at 6:00 PM Meera Nair
mailto:mn...@commvault.com>> wrote:
Hi Guillaume/team,
I set archive_mode = always in master and standby.
Archival to standby WAL directory completed when
· standby server was restarted
· pg_stop_backup was executed in master
But archival hangs when pg_stop_backup is executed in standby.
Could someone help to get this working?
postgres=# select pg_start_backup('test', true, false);
pg_start_backup
-
1/F960
(1 row)
postgres=# select pg_stop_backup('f');
NOTICE: base backup done, waiting for required WAL segments to be archived
WARNING: still waiting for all required WAL segments to be archived (60
seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely
cancel this backup, but the database backup will not be usable without all the
WAL segments.
WARNING: still waiting for all required WAL segments to be archived (120
seconds elapsed)
HINT: Check that your archive_command is executing properly. You can safely
cancel this backup, but the database backup will not be usable without all the
WAL segments.
……..
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
---
t
(1 row)
postgres=# show wal_level;
wal_level
---
replica
(1 row)
postgres=# show archive_mode;
archive_mode
--
always
(1 row)
postgres=# select version();
version
-
PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623
(Red Hat 4.8.5-39), 64-bit
(1 row)
Regards,
Meera
From: Guillaume Lelarge mailto:guilla...@lelarge.info>>
Sent: Thursday, July 21, 2022 6:01 PM
To: Meera Nair mailto:mn...@commvault.com>>
Cc:
pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
Subject: Re: Unable to archive logs in standby server
External email. Inspect before opening.
Hi,
Le jeu. 21 juil. 2022 à 13:58, Meera Nair
mailto:mn...@commvault.com>> a écrit :
Hi team,
With non-exclusive backup method, trying backup from standby node.
But pg_stop_backup function returns “WAL archiving is not enabled…” and the
logs are not archived to WAL directory configured.
Please check if I am missing anything in configuring this properly,
Server was restarted after setting the archiving params in postgresql.conf
Below is from version 14:
postgres=# select pg_start_backup('label', false, false);
pg_start_backup
-
0/6D8
(1 row)
postgres=# select pg_stop_backup('false');
NOTICE: WAL archiving is not enabled; you must ensure that all required WAL
segments are copied through other means to complete the backup
pg_stop_backup
---
(0/60001C0,"START WAL LOCATION: 0/6D8 (file 00010006)+
CHECKPOINT LOCATION: 0/6000110 +
BACKUP METHOD: streamed +
BACKUP FROM: standby +
START TIME: 2022-07-21 12:42:11 IST +
LABEL: label +
START TIMELINE: 1+
","")
(1 row)
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
---
t
(1 row)
postgres=# show wal_level;
wal_level
---
replica
(1 row)
postgres=# show archive_mode;
archive_mode
--
on
(1 row)
You're doing backups from the standby, and to allow archiving on the backups,
archive_mode should be set to always.
--
Guillaume.
RE: Unable to archive logs in standby server
Hi Christophe,
If pg_stop_backup is executed in master OR if standby server is restarted, I
can see the archiving completing fine.
Only issue is while manually executing the pg_stop_backup in standby
Regards,
Meera
-Original Message-
From: Christophe Pettus
Sent: Friday, September 2, 2022 7:43 PM
To: Meera Nair
Cc: pgsql-general@lists.postgresql.org; Punit Pranesh Koujalgi
Subject: Re: Unable to archive logs in standby server
External email. Inspect before opening.
> On Sep 1, 2022, at 21:41, Meera Nair wrote:
> Archival hangs. Is this expected?
> postgres=# select pg_start_backup('test', true, false);
> pg_start_backup
> -
> 1/F960
> (1 row)
>
> postgres=# select pg_stop_backup('f');
> NOTICE: base backup done, waiting for required WAL segments to be
> archived
> WARNING: still waiting for all required WAL segments to be archived
> (60 seconds elapsed)
> HINT: Check that your archive_command is executing properly. You can safely
> cancel this backup, but the database backup will not be usable without all
> the WAL segments.
> WARNING: still waiting for all required WAL segments to be archived
> (120 seconds elapsed)
> HINT: Check that your archive_command is executing properly. You can safely
> cancel this backup, but the database backup will not be usable without all
> the WAL segments.
This generally means the command being run by archive_command is failing.
Check the PostgreSQL logs (if you are using CSV logs, check the *.log file
rather than the *.csv file).
RE: Unable to archive logs in standby server
Hi Kyotaro, This helped, thanks. Regards, Meera -Original Message- From: Kyotaro Horiguchi Sent: Monday, September 5, 2022 7:31 AM To: Meera Nair Cc: guilla...@lelarge.info; pgsql-general@lists.postgresql.org; Punit Pranesh Koujalgi Subject: Re: Unable to archive logs in standby server External email. Inspect before opening. At Tue, 30 Aug 2022 05:22:56 +, Meera Nair wrote in > Hi Guillaume/team, > > I set archive_mode = always in master and standby. > Archival to standby WAL directory completed when > > * standby server was restarted > * pg_stop_backup was executed in master > > But archival hangs when pg_stop_backup is executed in standby. > Could someone help to get this working? https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F12%2Fcontinuous-archiving.html&data=05%7C01%7Cmnair%40commvault.com%7C240c3d6f31074e32206c08da8ee277fe%7C40ed1e38a16e46229d7c45161b6969d5%7C0%7C0%7C637979400603662396%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=UHsS8ZG1Vll0z8p3ce6WPfm3IniqVYMtOPfin3Lahbg%3D&reserved=0 > In the same connection as before, issue the command: > > SELECT * FROM pg_stop_backup(false, true); > > This terminates backup mode. On a primary, it also performs an > automatic switch to the next WAL segment. On a standby, it is not > possible to automatically switch WAL segments, so you may wish to run > pg_switch_wal on the primary to perform a manual switch. The reason > for the switch is to arrange for the last WAL segment file written > during the backup interval to be ready to archive. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
PostgreSQL 14.8 - server fails to start even though all transaction logs with base backup are restored
Hi Team, We are following https://www.postgresql.org/docs/14/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP Making An Exclusive Low-Level Backup. After restoring, we see the server fails to start with below error: PostgreSQL server log: 2023-08-04 16:47:47.227 IST [40582] LOG: starting PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2023-08-04 16:47:47.228 IST [40582] LOG: listening on IPv4 address "0.0.0.0", port 5414 2023-08-04 16:47:47.228 IST [40582] LOG: listening on IPv6 address "::", port 5414 2023-08-04 16:47:47.231 IST [40582] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5414" 2023-08-04 16:47:47.236 IST [40582] LOG: listening on Unix socket "/tmp/.s.PGSQL.5414" 2023-08-04 16:47:47.243 IST [40584] LOG: database system was interrupted; last known up at 2023-08-04 14:48:29 IST 2023-08-04 16:47:49.254 IST [40584] LOG: starting archive recovery cp: cannot stat '/var/lib/pgsql/14/wal/0004.history': No such file or directory 2023-08-04 16:47:49.275 IST [40584] LOG: restored log file "000400020041" from archive 2023-08-04 16:47:49.290 IST [40584] LOG: redo starts at 2/4128 2023-08-04 16:47:49.310 IST [40584] LOG: restored log file "000400020042" from archive 2023-08-04 16:47:49.344 IST [40584] LOG: restored log file "000400020043" from archive 2023-08-04 16:47:49.368 IST [40584] LOG: restored log file "000400020044" from archive 2023-08-04 16:47:49.395 IST [40584] LOG: restored log file "000400020045" from archive 2023-08-04 16:47:49.423 IST [40584] LOG: restored log file "000400020046" from archive 2023-08-04 16:47:49.460 IST [40584] LOG: restored log file "000400020047" from archive 2023-08-04 16:47:50.649 IST [40584] LOG: restored log file "000400020048" from archive 2023-08-04 16:47:51.219 IST [40584] LOG: restored log file "000400020049" from archive cp: cannot stat '/var/lib/pgsql/14/wal/00040002004A': No such file or directory 2023-08-04 16:47:51.603 IST [40584] LOG: redo done at 2/49013500 system usage: CPU: user: 0.00 s, system: 0.19 s, elapsed: 2.31 s 2023-08-04 16:47:51.603 IST [40584] LOG: last completed transaction was at log time 2023-08-04 14:58:51.021414+05:30 2023-08-04 16:47:51.628 IST [40584] LOG: restored log file "000400020049" from archive 2023-08-04 16:47:51.973 IST [40584] FATAL: WAL ends before end of online backup 2023-08-04 16:47:51.973 IST [40584] HINT: Online backup started with pg_start_backup() must be ended with pg_stop_backup(), and all WAL up to that point must be available at recovery. 2023-08-04 16:47:51.975 IST [40582] LOG: startup process (PID 40584) exited with exit code 1 2023-08-04 16:47:51.975 IST [40582] LOG: terminating any other active server processes 2023-08-04 16:47:51.975 IST [40582] LOG: shutting down due to startup process failure 2023-08-04 16:47:51.977 IST [40582] LOG: database system is shut down Backup_label from data directory: [root@gkstandby2 data]# cat backup_label START WAL LOCATION: 2/4128 (file 000400020041) CHECKPOINT LOCATION: 2/4160 BACKUP METHOD: pg_start_backup BACKUP FROM: primary START TIME: 2023-08-04 14:48:29 IST LABEL: pgida_backup_5414_108625_1691140709 START TIMELINE: 4 [root@gkstandby2 data]# Archive log directory: [root@gkstandby2 wal]# ls 00020002003B 00020002003E.0028.backup 000400020040.0028.backup 000400020044 000400020048 00020002003C 0002.history 000400020041 000400020045 000400020049 00020002003C.0028.backup 0003000500E4 000400020042 000400020045.5AE0.backup 00020002003D 00040002003F 000400020043 000400020046 00020002003E 000400020040 000400020043.0028.backup 000400020047 [root@gkstandby2 wal]# /usr/pgsql-14/bin/pg_waldump 000400020046 | grep BACKUP rmgr: XLOGlen (rec/tot): 34/34, tx: 0, lsn: 2/46000110, prev 2/4698, desc: BACKUP_END 2/45005AE0 [root@gkstandby2 wal]# We are restoring all the transaction logs required for base backup to be consistent. BACKUP_END is present in the 000400020046 segment. Why did recovery fail to find this? Regards, Meera
RE: PostgreSQL 14.8 - server fails to start even though all transaction logs with base backup are restored
Hi Stephen,
The backups we ran were all exclusive backups.
pg_start_backup('label') returned 000400020041. Then we made a copy
of data directory .
Then pg_stop_backup() was executed and it returned 000400020046.
After this 2 databases were dropped and recreated (using dropdb and createdb).
Then we ran pg_switch_wal()
This returned 000400020049. Then we made a copy of the archive log
directory.
To verify the copied data is restorable, we stopped server. Renamed data and
archive log directories.
Replaced it with data and archive log directories copied earlier. In archive
log directory logs from 41 to 49 is present.
Created recovery.signal and added restore_command to postgresql.conf file. And
tried to start server. this failed with error in previous mail.
[root@gkstandby2 wal]# ls -ltr
total 262172
-rw--- 1 postgres postgres 42 Jun 13 01:08 0002.history
-rw--- 1 postgres postgres 16777216 Jun 22 22:50 00020002003B
-rw--- 1 postgres postgres 357 Jun 22 22:51
00020002003C.0028.backup
-rw--- 1 postgres postgres 16777216 Jun 22 22:51 00020002003C
-rw--- 1 postgres postgres 16777216 Jun 22 22:52 00020002003D
-rw--- 1 postgres postgres 357 Jun 22 22:52
00020002003E.0028.backup
-rw--- 1 postgres postgres 16777216 Jun 22 22:52 00020002003E
-rw--- 1 postgres postgres 16777216 Aug 4 14:47 0003000500E4
-rw--- 1 postgres postgres 16777216 Aug 4 14:48 00040002003F
-rw--- 1 postgres postgres 16777216 Aug 4 14:48 000400020040
-rw--- 1 postgres postgres 16777216 Aug 4 14:49 000400020041
-rw--- 1 postgres postgres 351 Aug 4 14:49
000400020040.0028.backup
-rw--- 1 postgres postgres 16777216 Aug 4 14:49 000400020042
-rw--- 1 postgres postgres 16777216 Aug 4 14:49 000400020043
-rw--- 1 postgres postgres 16777216 Aug 4 14:49 000400020044
-rw--- 1 postgres postgres 16777216 Aug 4 14:50 000400020045
-rw--- 1 postgres postgres 351 Aug 4 14:50
000400020043.0028.backup
-rw--- 1 postgres postgres 16777216 Aug 4 14:50 000400020046
-rw--- 1 postgres postgres 358 Aug 4 14:50
000400020045.5AE0.backup
-rw--- 1 postgres postgres 16777216 Aug 4 14:58 000400020047
-rw--- 1 postgres postgres 16777216 Aug 4 14:58 000400020048
-rw--- 1 postgres postgres 16777216 Aug 4 14:58 000400020049
[root@gkstandby2 wal]#
Regards,
Meera
-Original Message-
From: Stephen Frost mailto:sfr...@snowman.net>>
Sent: Tuesday, August 8, 2023 7:48 PM
To: Meera Nair mailto:mn...@commvault.com>>
Cc:
pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>;
Punit Pranesh Koujalgi mailto:pkouja...@commvault.com>>
Subject: Re: PostgreSQL 14.8 - server fails to start even though all
transaction logs with base backup are restored
Greetings,
* Meera Nair (mn...@commvault.com<mailto:mn...@commvault.com>) wrote:
> We are following
> https://www.postgresql.org/docs/14/continuous-archiving.html#BACKUP-LO
> WLEVEL-BASE-BACKUP
> Making An Exclusive Low-Level Backup.
Exclusive backups have been removed, so you really don't want to be depending
on it.
> After restoring, we see the server fails to start with below error:
Your archive doesn't seem to have all of the WAL which was generated during the
backup, which means that your backup wasn't complete and the system cannot be
restored from this backup.
> We are restoring all the transaction logs required for base backup to be
> consistent.
Evidently not, per the complaint from PG when it starts up.
> BACKUP_END is present in the 000400020046 segment. Why did
> recovery fail to find this?
It's possible to have multiple backups going concurrently. Presumably, this
was from a different backup and that's why it wasn't picked up as being the end
for this backup. You seem to have quite a few .backup files in your archive
directory which would seem to support this.
Not sure exactly what you're doing, but unless your goal is to spend a great
deal of effort developing a PG backup solution, you're really better off using
one of the existing solutions (eg: pg_basebackup or pgBackRest). If you are
developing your own backup solution for PG, you definitely want to be using the
new APIs and not using the exclusive backup method. Please review the current
(15) documentation:
https://www.postgresql.org/docs/current/continuous-archiving.html
Thanks,
Stephen
Query related to pg_dump write to a pipe on a windows client and with compressed format
Hi all, https://www.postgresql.org/message-id/flat/CAKKd065aJ1LuUMw_bhBgmgoM6Ng-cLdBobpzRiU%2BUsdsmW2aOg%40mail.gmail.com#996021734fa788bd1bc737254002ad11 We tried pg_dump write to a pipe with compressed format and faced issue as in thread referred here. It was on windows client and for postgres 9.2 version. So during pg_restore, seek error was seen. With pg_dump writing the dump to a file and then move to another machine for backup, this problem was not there. But now the issue is that we need to have a lot of free space to write the dump file. If we use tar format, temporary files created in C:\ take space. If we use parallel dump format, then also staging the dump output is must. So trying to understand if the compressed format still needs the staging of output file with the latest versions? Or we can write the output to a pipe to move the data to another machine for backup. Regards, Meera
Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir
Hi team, With wal_level = 'logical', backup was taken using non-exclusive backup method. Following procedure here for restore and recovery - PostgreSQL: Documentation: 16: 26.3. Continuous Archiving and Point-in-Time Recovery (PITR)<https://www.postgresql.org/docs/16/continuous-archiving.html#BACKUP-PITR-RECOVERY> While starting the PostgreSQL server, below issue is seen: 2024-06-05 11:41:32.369 IST [54369] LOG: restored log file "00050001006A" from archive 2024-06-05 11:41:33.112 IST [54369] LOG: restored log file "00050001006B" from archive cp: cannot stat '/home/pgsql/wmaster/00050001006C': No such file or directory 2024-06-05 11:41:33.167 IST [54369] LOG: redo done at 1/6B000100 2024-06-05 11:41:33.172 IST [54369] FATAL: archive file "00050001006B" has wrong size: 0 instead of 16777216 2024-06-05 11:41:33.173 IST [54367] LOG: startup process (PID 54369) exited with exit code 1 2024-06-05 11:41:33.173 IST [54367] LOG: terminating any other active server processes 2024-06-05 11:41:33.174 IST [54375] FATAL: archive command was terminated by signal 3: Quit 2024-06-05 11:41:33.174 IST [54375] DETAIL: The failed archive command was: cp pg_wal/00050001006B /home/pgsql/wmaster/00050001006B 2024-06-05 11:41:33.175 IST [54367] LOG: archiver process (PID 54375) exited with exit code 1 2024-06-05 11:41:33.177 IST [54367] LOG: database system is shut down Here '/home/pgsql/wmaster' is my archivedir (the folder where WAL segments are restored from) Before attempting start, size of 00050001006B file was 16 MB. After failing to detect 00050001006C, there is a FATAL error saying wrong size for 00050001006B Now the size of 00050001006B is observed as 2 MB. Size of all other WAL segments remain 16 MB. -rw--- 1 postgres postgres 2359296 Jun 5 11:34 00050001006B Why is it changing the size of WAL segment in archive log directory? All necessary WAL segments are present and 00050001006C was never archived. bash-4.2$ cat /home/pgsql/dmaster/backup_label.old START WAL LOCATION: 1/6928 (file 000500010069) CHECKPOINT LOCATION: 1/6960 BACKUP METHOD: streamed BACKUP FROM: master START TIME: 2024-05-31 17:39:43 IST LABEL: pgida_backup_4321_315606_1717157383 START TIMELINE: 5 bash-4.2$ cat /home/pgsql/wmaster/00050001006B.0028.backup START WAL LOCATION: 1/6B28 (file 00050001006B) STOP WAL LOCATION: 1/6B000100 (file 00050001006B) CHECKPOINT LOCATION: 1/6B60 BACKUP METHOD: streamed BACKUP FROM: master START TIME: 2024-05-31 17:40:28 IST LABEL: pgida_backup_4321_315606_1717157427 START TIMELINE: 5 STOP TIME: 2024-05-31 17:40:28 IST STOP TIMELINE: 5 bash-4.2$ cat /home/pgsql/wmaster/0005.history 1 0/3E00 before 2000-01-01 05:30:00+05:30 2 0/6300 before 2000-01-01 05:30:00+05:30 3 0/E800 no recovery target specified 4 1/6800 before 2000-01-01 05:30:00+05:30 Despite our efforts to troubleshoot, the problem persists. Please help to find a solution. Regards, Meera
RE: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir
Hi Ron, We do have our own solution. We work for Commvault, a data protection company. We support backup of PostgreSQL in different ways - streaming, snapshot, block level, backup from standby server. PostgreSQL (commvault.com)<https://documentation.commvault.com/2024/expert/postgresql.html> Regards, Meera From: Ron Johnson Sent: Thursday, June 6, 2024 9:56 PM To: Laurenz Albe Cc: Meera Nair ; pgsql-general@lists.postgresql.org; Punit Pranesh Koujalgi ; B Ganesh Kishan Subject: Re: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir You don't often get email from ronljohnso...@gmail.com<mailto:ronljohnso...@gmail.com>. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification> External email. Inspect before opening. On Wed, Jun 5, 2024 at 6:26 AM Laurenz Albe mailto:laurenz.a...@cybertec.at>> wrote: On Wed, 2024-06-05 at 06:36 +, Meera Nair wrote: > 2024-06-05 11:41:32.369 IST [54369] LOG: restored log file > "00050001006A" from archive > 2024-06-05 11:41:33.112 IST [54369] LOG: restored log file > "00050001006B" from archive > cp: cannot stat ‘/home/pgsql/wmaster/00050001006C’: No such file > or directory > 2024-06-05 11:41:33.167 IST [54369] LOG: redo done at 1/6B000100 > 2024-06-05 11:41:33.172 IST [54369] FATAL: archive file > "00050001006B" has wrong size: 0 instead of 16777216 > 2024-06-05 11:41:33.173 IST [54367] LOG: startup process (PID 54369) exited > with exit code 1 > 2024-06-05 11:41:33.173 IST [54367] LOG: terminating any other active server > processes > 2024-06-05 11:41:33.174 IST [54375] FATAL: archive command was terminated by > signal 3: Quit > 2024-06-05 11:41:33.174 IST [54375] DETAIL: The failed archive command was: > cp pg_wal/00050001006B > /home/pgsql/wmaster/00050001006B > 2024-06-05 11:41:33.175 IST [54367] LOG: archiver process (PID 54375) exited > with exit code 1 > 2024-06-05 11:41:33.177 IST [54367] LOG: database system is shut down > > Here ‘/home/pgsql/wmaster’ is my archivedir (the folder where WAL segments > are restored from) > > Before attempting start, size of > 00050001006B file was 16 MB. > After failing to detect 00050001006C, there is a FATAL error > saying wrong size for 00050001006B > Now the size of 00050001006B is observed as 2 MB. Size of all > other WAL segments remain 16 MB. > > -rw--- 1 postgres postgres 2359296 Jun 5 11:34 00050001006B That looks like you have "archive_mode = always", and "archive_command" writes back to the archive. Don't do that. In fact, don't write your own PITR backup process. Use something like PgBackRest or BarMan.
RE: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir
Hi Laurenz, Thanks a lot! Regards, Meera -Original Message- From: Laurenz Albe Sent: Wednesday, June 5, 2024 3:56 PM To: Meera Nair ; pgsql-general@lists.postgresql.org Cc: Punit Pranesh Koujalgi ; B Ganesh Kishan Subject: Re: Logical replication type- WAL recovery fails and changes the size of wal segment in archivedir [You don't often get email from laurenz.a...@cybertec.at. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ] External email. Inspect before opening. On Wed, 2024-06-05 at 06:36 +0000, Meera Nair wrote: > 2024-06-05 11:41:32.369 IST [54369] LOG: restored log file > "00050001006A" from archive > 2024-06-05 11:41:33.112 IST [54369] LOG: restored log file > "00050001006B" from archive > cp: cannot stat ‘/home/pgsql/wmaster/00050001006C’: No > such file or directory > 2024-06-05 11:41:33.167 IST [54369] LOG: redo done at 1/6B000100 > 2024-06-05 11:41:33.172 IST [54369] FATAL: archive file > "00050001006B" has wrong size: 0 instead of 16777216 > 2024-06-05 11:41:33.173 IST [54367] LOG: startup process (PID 54369) > exited with exit code 1 > 2024-06-05 11:41:33.173 IST [54367] LOG: terminating any other active > server processes > 2024-06-05 11:41:33.174 IST [54375] FATAL: archive command was > terminated by signal 3: Quit > 2024-06-05 11:41:33.174 IST [54375] DETAIL: The failed archive > command was: cp pg_wal/00050001006B > /home/pgsql/wmaster/00050001006B > 2024-06-05 11:41:33.175 IST [54367] LOG: archiver process (PID 54375) > exited with exit code 1 > 2024-06-05 11:41:33.177 IST [54367] LOG: database system is shut down > > Here ‘/home/pgsql/wmaster’ is my archivedir (the folder where WAL > segments are restored from) > > Before attempting start, size of > 00050001006B file was 16 MB. > After failing to detect 00050001006C, there is a FATAL > error saying wrong size for 00050001006B Now the size of > 00050001006B is observed as 2 MB. Size of all other WAL segments > remain 16 MB. > > -rw--- 1 postgres postgres 2359296 Jun 5 11:34 > 00050001006B That looks like you have "archive_mode = always", and "archive_command" writes back to the archive. Don't do that. Yours, Laurenz Albe
