WIP: Using pg_ls_waldir instead of pg_ls_dir for dump_xlog_stat

[ng-pe]

Hello,

The pg_monitor role allows to use the pg_ls_waldir function introduced in PostgreSQL 10. The pg_monitor role is available on many PostgreSQL PaaS services (like RDS).

1 - Modification of the dump_xlog_stat query to use pg_ls_waldir instead of pg_ls_dir for postgresql 10 and above to be compatible with a user belonging to pg_monitor.

Tested with :

    SELECT
        date_trunc('seconds', now()),
        count(*) AS num_file,
        pg_walfile_name (pg_current_wal_lsn ()) AS current,
        sum(is_recycled::int) AS is_recycled,
        sum((NOT is_recycled)::int) AS written,
        max_wal
    FROM (
        SELECT
            file.name > first_value(file.name) OVER w AS is_recycled, (
                    SELECT
                        setting
                    FROM
                        pg_settings
                    WHERE
                        name = 'max_wal_size')::float4 AS max_wal
                FROM
                    (select * from pg_ls_waldir()) AS file
                WHERE
                    file.name ~ '^[0-9A-F]{24}\$'
    WINDOW w AS (ORDER BY file.modification DESC)) AS t
    GROUP BY
        6;

2 - Add an additional control in "if (!&is_superuser($DBUSER)) {" to control if the user belongs to the role pg_monitor to ensure the recovery of wal info is not removed from metrics.

I am preparing a patch in this direction, if you have any comments ...

[darold]

I think I have never received this PR, however commit 7d83483 fixes point 1 and commit 32abd8d fixes point 2.

[ng-pe]

Thanks for integrating my comments:)

I didn't have time to integrate them :D

It's perfect!

Thanks Gilles!