keeping track of wal archives

Highlighted
Level 2 Adventurer

keeping track of wal archives

Is there at table or a switch in the postgresql.conf that will allow me to see a history of the wal archives and when the wal archive command was run? 

Archived Discussions


Effective March 31st, we will no longer engage on PostgresRocks.


How to engage with us further?


  • Thought Leadership: EDB Blogs

  • Tips and Tricks: Postgres Tutorials

  • Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket

  • Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.

7 REPLIES 7
Highlighted
EDB Team Member

Re: keeping track of wal archives

Hi,

 

Their is no table or a switch in postgresql.conf file that will allow you to see a history of the wal archives and when the wal archive command was run, However their is a view called "pg_stat_archiver", The pg_stat_archiver view will always have a single row, containing statistics/data about the WAL archiver process's activity of the cluster.

 

For example:

postgres=# SELECT * FROM pg_stat_archiver;

-[ RECORD 1 ]------+------------------------------

archived_count     | 28824

last_archived_wal  | 00000001000000700000007C

last_archived_time | 2018-07-03 08:07:23.722138-05

failed_count       | 0

last_failed_wal    |

last_failed_time   |

stats_reset        | 2018-01-16 07:25:17.745589-06

 

Description of cloumns used in pg_stat_archiver view:

 

Column Type Description
archived_count bigint Number of WAL files that have been successfully archived
last_archived_wal text Name of the last WAL file successfully archived
last_archived_time timestamp with time zone Time of the last successful archive operation
failed_count bigint Number of failed attempts for archiving WAL files
last_failed_wal text Name of the WAL file of the last failed archival operation
last_failed_time timestamp with time zone Time of the last failed archival operation
stats_reset timestamp with time zone Time at which these statistics were last reset
Highlighted
Level 2 Adventurer

Re: keeping track of wal archives

I found this table it is not what I'm looking for.

 

I'm looking for a record of each and every time a wal file has been archived. That way you can determine the system usage over a period of time. 

Highlighted
EDB Team Member

Re: keeping track of wal archives

Hi Mediis,

 

Their is no such table in postgres where you can track record of each and every time a wal file has been archived. You need to write a shell script for archive_command to track record of each and every time a wal file has been archived.

 

Below example output can be achieved with the shell script for archive_command.

 

For example output:

#####################
Sun Sep 18 18:06:56 IST 2017: Start archiving WAL files to local disk into /data02/pg_xlog_archive
Sun Sep 18 18:06:56 IST 2017: Archiving of WAL file /data01/data/pg_xlog/00000009000184ED0000000B locally succeeded!
################################
Sun Sep 18 18:06:57 IST 2017: Start archiving WAL files to local disk into /data02/pg_xlog_archive
Sun Sep 18 18:06:57 IST 2017: Archiving of WAL file /data01/data/pg_xlog/00000009000184ED0000000C locally succeeded!
################################
Sun Sep 18 18:06:57 IST 2017: Start archiving WAL files to local disk into /data02/pg_xlog_archive
Sun Sep 18 18:06:57 IST 2017: Archiving of WAL file /data01/data/pg_xlog/00000009000184ED0000000D locally succeeded!
################################
Sun Sep 18 18:06:57 IST 2017: Start archiving WAL files to local disk into /data02/pg_xlog_archive
Sun Sep 18 18:06:57 IST 2017: Archiving of WAL file /data01/data/pg_xlog/00000009000184ED0000000E locally succeeded!
################################
Sun Sep 18 18:06:57 IST 2017: Start archiving WAL files to local disk into /data02/pg_xlog_archive
Sun Sep 18 18:06:58 IST 2017: Archiving of WAL file /data01/data/pg_xlog/00000009000184ED0000000F locally succeeded!
################################

Highlighted
Level 2 Adventurer

Re: keeping track of wal archives

Yah, the 1990's called, they want their database back.

Highlighted
Adventurer

Re: keeping track of wal archives

Hey, you can use okmeter.io (disclaimer - I work there) to track (with historical view) when archiver worked and if it succeeded or failed (we'll send  you an alert).

Highlighted
EDB Team Member

Re: keeping track of wal archives

Hi Mediis,

 

There is no such view/table or switch available in the Postgres whichwill allow you to see a history of the wal archives and when the wal archive command was run.

However, there is one workaround for it.

Kindly refer below for the example:

 

 

Preview Table :

postgres=# create table archive_details(date timestamp with time zone, archivename varchar);
CREATE TABLE



Archive Command :
postgres=# show archive_command ;
archive_command 
------------------------------------------
/opt/edb/as9.6/data/archive_wal.sh %p %f
(1 row)



Archive script :

postgres=# \! cat /opt/edb/as9.6/data/archive_wal.sh
cp -a $1 /opt/edb/as9.6/data/wal_archive/$2 && /opt/edb/as9.6/bin/psql -p 5445 -U enterprisedb -d postgres -c "insert into archive_details(date, archivename) values(now(),'$1')"



Preview table data stored post archive command execution:

postgres=# select * from archive_details ;
date | archivename 
----------------------------------+----------------------------------
09-AUG-18 04:02:46.824502 +05:30 | pg_xlog/000000010000000000000003
09-AUG-18 04:02:46.855435 +05:30 | pg_xlog/000000010000000000000004
09-AUG-18 04:02:46.910291 +05:30 | pg_xlog/000000010000000000000005
09-AUG-18 04:02:47.058112 +05:30 | pg_xlog/000000010000000000000006
(4 rows)



Archive location data:

postgres=# \! ls -ltrh /opt/edb/as9.6/data/wal_archive/
total 64M
-rw------- 1 enterprisedb enterprisedb 16M Aug 9 03:31 000000010000000000000003
-rw------- 1 enterprisedb enterprisedb 16M Aug 9 03:31 000000010000000000000004
-rw------- 1 enterprisedb enterprisedb 16M Aug 9 03:31 000000010000000000000005
-rw------- 1 enterprisedb enterprisedb 16M Aug 9 03:56 000000010000000000000006



Archive status:

postgres=# \! ls -ltrh /opt/edb/as9.6/data/pg_xlog/archive_status/
total 0

Hope this workaround helps you.

Disclaimer: Kindly do all kind of testing on it. Before moving this to your production server.

Regards,
Dhananjay
Highlighted
EDB Team Member

Re: keeping track of wal archives

Hi,

 

Thank you for the information. okmeter.io is a third party tool which is not supported by enterprisedb 

© 2019 EnterpriseDB Corporation. All rights reserved.   |   Privacy Policy   |  Terms of Use   |   Trademarks