cancel
Showing results for 
Search instead for 
Did you mean: 

keeping track of wal archives

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? 

Tags (1)
7 REPLIES
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
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. 

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!
################################

Level 2 Adventurer

Re: keeping track of wal archives

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

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).

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.

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