Обсуждение: 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
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
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 <aqoyyuum@cardaccess.com.au>
Sent: Tuesday, July 19, 2022 3:40 PM
To: Meera Nair <mnair@commvault.com>
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
On Tue, Jul 19, 2022 at 4:33 PM Meera Nair <mnair@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
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 <aqoyyuum@cardaccess.com.au>
Sent: Tuesday, July 19, 2022 3:40 PM
To: Meera Nair <mnair@commvault.com>
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
On Tue, Jul 19, 2022 at 4:33 PM Meera Nair <mnair@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
Hi Thomas,
This worked for me, thanks a lot.
Regards,
Meera
From: Thomas Boussekey <thomas.boussekey@gmail.com>
Sent: Tuesday, July 19, 2022 5:33 PM
To: Meera Nair <mnair@commvault.com>
Cc: Abdul Qoyyuum <aqoyyuum@cardaccess.com.au>; 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 <mnair@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
Regards,
Meera
From: Abdul Qoyyuum <aqoyyuum@cardaccess.com.au>
Sent: Tuesday, July 19, 2022 3:40 PM
To: Meera Nair <mnair@commvault.com>
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
On Tue, Jul 19, 2022 at 4:33 PM Meera Nair <mnair@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
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/60000D8
(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/60000D8 (file 000000010000000000000006)+
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
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/60000D8
(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/60000D8 (file 000000010000000000000006)+
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)
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/60000D8
(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/60000D8 (file 000000010000000000000006)+
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
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/F9000060
(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 <guillaume@lelarge.info>
Sent: Thursday, July 21, 2022 6:01 PM
To: Meera Nair <mnair@commvault.com>
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 <mnair@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/60000D8
(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/60000D8 (file 000000010000000000000006)+
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.
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/F9000060
(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 <subtitle.indo@gmail.com>
Sent: Friday, September 2, 2022 12:36 AM
To: Meera Nair <mnair@commvault.com>
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 <mnair@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/F9000060
(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 <guillaume@lelarge.info>
Sent: Thursday, July 21, 2022 6:01 PM
To: Meera Nair <mnair@commvault.com>
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 <mnair@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/60000D8
(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/60000D8 (file 000000010000000000000006)+
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.
> On Sep 1, 2022, at 21:41, Meera Nair <mnair@commvault.com> wrote: > Archival hangs. Is this expected? > postgres=# select pg_start_backup('test', true, false); > pg_start_backup > ----------------- > 1/F9000060 > (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 backupwill 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 backupwill 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 CSVlogs, check the *.log file rather than the *.csv file).
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 <xof@thebuild.com> Sent: Friday, September 2, 2022 7:43 PM To: Meera Nair <mnair@commvault.com> Cc: pgsql-general@lists.postgresql.org; Punit Pranesh Koujalgi <pkoujalgi@commvault.com> Subject: Re: Unable to archive logs in standby server External email. Inspect before opening. > On Sep 1, 2022, at 21:41, Meera Nair <mnair@commvault.com> wrote: > Archival hangs. Is this expected? > postgres=# select pg_start_backup('test', true, false); > pg_start_backup > ----------------- > 1/F9000060 > (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 backupwill 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 backupwill 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 CSVlogs, check the *.log file rather than the *.csv file).
At Tue, 30 Aug 2022 05:22:56 +0000, Meera Nair <mnair@commvault.com> 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://www.postgresql.org/docs/12/continuous-archiving.html > 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
Hi Kyotaro, This helped, thanks. Regards, Meera -----Original Message----- From: Kyotaro Horiguchi <horikyota.ntt@gmail.com> Sent: Monday, September 5, 2022 7:31 AM To: Meera Nair <mnair@commvault.com> Cc: guillaume@lelarge.info; pgsql-general@lists.postgresql.org; Punit Pranesh Koujalgi <pkoujalgi@commvault.com> Subject: Re: Unable to archive logs in standby server External email. Inspect before opening. At Tue, 30 Aug 2022 05:22:56 +0000, Meera Nair <mnair@commvault.com> 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