pg_dump is filling C: drive up to 100 percent

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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

[Meera Nair]

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)

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

[Meera Nair]

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

[Meera Nair]

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