Overview

pgmetrics is an open-source tool to collect and report various statistics and information from a running PostgreSQL server to aid in troubleshooting, monitoring and automation.

  • Server: version, system identifier, timeline, transaction id wraparound, checkpoint lag
  • Replication: primary-side, standby-side, physical and logical replication slots
  • WAL Archiving: archive rate, wal and ready file counts, last success and fail time
  • BG Writer: checkpoint rate, total checkpoints (sched+req), buffers
  • Vacuum-related: ongoing auto/manual vacuum progress, last analyze/vacuum, settings
  • Tablespaces: location, size, disk and inode usage of filesystem
  • Database: size, bloat, disabled triggers, installed extensions, temp files, transaction id wraparound, deadlocks, conflicts
  • Roles: users, groups, membership
  • Active backends: transaction running too long, idling in transaction, waiting for locks
  • Tables: vacuum, analyze, row estimates, idx and seq scans, cache hit ratio, HOT update ratio, size, bloat
  • Indexes: cache hit ratio, scans, rows read/scan, rows fetched/scan
  • Sequences: cache hit ratio
  • System metrics: cores, load average, memory and disk usage
  • Settings: current values, and default ones where different
  • Slow queries: from pg_stat_statements, if available
  • Locks: granted and waiting locks, from pg_locks
  • Blocked queries: blocked queries, along with the queries that they are waiting for

It can also collect and report information about PgBouncer instances when connected to PgBouncer’s admin database.

pgmetrics is a standalone, dependency-free tool: there are no PostgreSQL extensions to be installed, and no package or installation dependencies for the tool itself.

Output

pgmetrics can report the collected statistics in human-readable text format, or export it to JSON or CSV formats. The JSON and CSV format outputs are intended to be used in scripts for monitoring and automation. pgmetrics can also redisplay the contents of a previously saved JSON file in the text format.

PostgreSQL versions

pgmetrics can work with PostgreSQL versions 9.3 to 11. It also works with managed PostgreSQL instances provided by:

  • Amazon AWS Aurora
  • Amazon AWS RDS
  • Google GCP SQL
  • Heroku Postgres
Availability

pgmetrics is written in Go, and available for download as a zero-dependency statically linked command-line tool for many platforms. It is open source and licensed under Apache License 2.0.

Example

Here is an example of the text output that pgmetrics generates:

$ pgmetrics -h 127.0.0.1 bench test

pgmetrics run at: 7 Aug 2018 2:15:16 AM (1 second ago)

PostgreSQL Cluster:
    Name:                develpg10
    Server Version:      10.3
    Server Started:      7 Aug 2018 1:37:33 AM (37 minutes ago)
    System Identifier:   6540060726486386353
    Timeline:            1
    Last Checkpoint:     7 Aug 2018 2:14:20 AM (57 seconds ago)
    Prior LSN:           3/20000060
    REDO LSN:            3/22000028 (32 MiB since Prior)
    Checkpoint LSN:      3/22000060 (56 B since REDO)
    Transaction IDs:     548 to 789754 (diff = 789206)
    Last Transaction:    7 Aug 2018 2:06:37 AM (8 minutes ago)
    Notification Queue:  0.0% used
    Active Backends:     1 (max 100)
    Recovery Mode?       no

System Information:
    Hostname:            deb9
    CPU Cores:           2 x Intel(R) Core(TM) i5-3450 CPU @ 3.10GHz
    Load Average:        0.40
    Memory:              used=630 MiB, free=66 MiB, buff=20 MiB, cache=1.3 GiB
    Swap:                used=40 KiB, free=2.0 GiB
    +---------------------------------+------------------+
    |                         Setting |            Value |
    +---------------------------------+------------------+
    |                  shared_buffers |  32768 (256 MiB) |
    |                        work_mem |   8192 (8.0 MiB) |
    |            maintenance_work_mem | 131072 (128 MiB) |
    |                    temp_buffers |   1024 (8.0 MiB) |
    |             autovacuum_work_mem |               -1 |
    |                 temp_file_limit |               -1 |
    |            max_worker_processes |                8 |
    |          autovacuum_max_workers |                3 |
    | max_parallel_workers_per_gather |                2 |
    |        effective_io_concurrency |                1 |
    +---------------------------------+------------------+

Outgoing Replication Stats:
    Destination #1:
      User:              mdevan
      Application:       walreceiver
      Client Address:    
      State:             streaming
      Started At:        7 Aug 2018 2:15:07 AM (10 seconds ago)
      Sent LSN:          3/22000108
      Written Until:     3/22000108 (no write lag)
      Flushed Until:     3/22000108 (no flush lag)
      Replayed Until:    3/22000108 (no replay lag)
      Sync Priority:     0
      Sync State:        async

Physical Replication Slots:
    +-------------+--------+---------------+-------------+-----------+
    |        Name | Active | Oldest Txn ID | Restart LSN | Temporary |
    +-------------+--------+---------------+-------------+-----------+
    |   backup_g1 |    yes |               |  3/22000108 |        no |
    | walarchslot |     no |               |  3/13000000 |        no |
    +-------------+--------+---------------+-------------+-----------+

Logical Replication Slots:
    +----------+---------------+----------+--------+---------------+-------------+---------------+-----------+
    |     Name |        Plugin | Database | Active | Oldest Txn ID | Restart LSN | Flushed Until | Temporary |
    +----------+---------------+----------+--------+---------------+-------------+---------------+-----------+
    | logslot1 | test_decoding |    bench |     no |               |  3/12002CF8 |    3/13001B20 |        no |
    +----------+---------------+----------+--------+---------------+-------------+---------------+-----------+

WAL Files:
    WAL Archiving?       yes
    WAL Files:           18
    Ready Files:         0
    Archive Rate:        0.00 per min
    Last Archived:       7 Aug 2018 2:14:31 AM (46 seconds ago)
    Last Failure:        
    Totals:              11 succeeded, 0 failed
    Totals Since:        2 May 2018 4:30:41 AM (3 months ago)
    +--------------------+---------------+
    |            Setting |         Value |
    +--------------------+---------------+
    |          wal_level |       logical |
    |    archive_timeout |           120 |
    |    wal_compression |            on |
    |       max_wal_size | 1024 (16 GiB) |
    |       min_wal_size | 100 (1.6 GiB) |
    | checkpoint_timeout |            30 |
    |   full_page_writes |            on |
    |  wal_keep_segments |             2 |
    +--------------------+---------------+

BG Writer:
    Checkpoint Rate:     0.00 per min
    Average Write:       68 KiB per checkpoint
    Total Checkpoints:   79 sched (96.3%) + 3 req (3.7%) = 82
    Total Write:         5.6 MiB, @ 0 B per sec
    Buffers Allocated:   2931 (23 MiB)
    Buffers Written:     692 chkpt (95.7%) + 0 bgw (0.0%) + 31 be (4.3%)
    Clean Scan Stops:    0
    BE fsyncs:           0
    Counts Since:        2 May 2018 4:30:41 AM (3 months ago)
    +------------------------------+--------------+
    |                      Setting |        Value |
    +------------------------------+--------------+
    |               bgwriter_delay |     200 msec |
    |         bgwriter_flush_after | 64 (512 KiB) |
    |        bgwriter_lru_maxpages |          100 |
    |      bgwriter_lru_multiplier |            2 |
    |                   block_size |         8192 |
    |           checkpoint_timeout |       30 sec |
    | checkpoint_completion_target |          0.5 |
    +------------------------------+--------------+

Backends:
    Total Backends:      1 (1.0% of max 100)
    Problematic:         0 waiting on locks, 0 waiting on other, 0 xact too long, 0 idle in xact

Vacuum Progress:
    No manual or auto vacuum jobs in progress.
    +------------------------------+------------------+
    |                      Setting |            Value |
    +------------------------------+------------------+
    |         maintenance_work_mem | 131072 (128 MiB) |
    |                   autovacuum |               on |
    | autovacuum_analyze_threshold |               50 |
    |  autovacuum_vacuum_threshold |               50 |
    |    autovacuum_freeze_max_age |        200000000 |
    |       autovacuum_max_workers |                3 |
    |           autovacuum_naptime |           60 sec |
    |        vacuum_freeze_min_age |         50000000 |
    |      vacuum_freeze_table_age |        150000000 |
    +------------------------------+------------------+

Roles:
    +----------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
    |                 Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires |                                                    Member Of |
    +----------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
    |               mdevan |   yes |  yes |   yes |       yes |      yes |        yes |     yes |         |                                                              |
    |           pg_monitor |       |      |       |           |          |            |     yes |         | pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables |
    | pg_read_all_settings |       |      |       |           |          |            |     yes |         |                                                              |
    |    pg_read_all_stats |       |      |       |           |          |            |     yes |         |                                                              |
    |  pg_stat_scan_tables |       |      |       |           |          |            |     yes |         |                                                              |
    |    pg_signal_backend |       |      |       |           |          |            |     yes |         |                                                              |
    |                staff |   yes |      |       |           |      yes |            |     yes |         |                                                              |
    |                alice |   yes |      |   yes |           |      yes |            |     yes |         |                                                        staff |
    |                  bob |   yes |      |       |       yes |          |            |     yes |         |                                                              |
    |              charlie |       |      |       |           |          |            |     yes |         |                                                        staff |
    |                frank |   yes |      |       |           |          |            |     yes |         |                                                              |
    +----------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+

Tablespaces:
    +------------+--------+------------------------+---------+----------------------------+-------------------------+
    |       Name |  Owner |               Location |    Size |                  Disk Used |              Inode Used |
    +------------+--------+------------------------+---------+----------------------------+-------------------------+
    | pg_default | mdevan | $PGDATA = /opt/db/main |  44 MiB |  7.8 GiB (44.4%) of 18 GiB | 87665 (7.4%) of 1179648 |
    |  pg_global | mdevan | $PGDATA = /opt/db/main | 621 KiB |  7.8 GiB (44.4%) of 18 GiB | 87665 (7.4%) of 1179648 |
    |    datazfs | mdevan |       /data/pg-main-ts | 1.6 GiB | 4.6 GiB (59.2%) of 7.7 GiB |   945 (0.0%) of 6576361 |
    +------------+--------+------------------------+---------+----------------------------+-------------------------+

Database #1:
    Name:                postgres
    Owner:               mdevan
    Tablespace:          pg_default
    Connections:         0 (no max limit)
    Frozen Xid Age:      789207
    Transactions:        162 (100.0%) commits, 0 (0.0%) rollbacks
    Cache Hits:          97.5%
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    Total Temp:          0 B in 0 files
    Problems:            0 deadlocks, 0 conflicts
    Totals Since:        2 May 2018 4:32:28 AM (3 months ago)
    Size:                7.2 MiB
    Slow Queries:
      +-------+----------+------------+-----------+----------------------------------------------------+
      | Calls | Avg Time | Total Time | Rows/Call |                                              Query |
      +-------+----------+------------+-----------+----------------------------------------------------+
      |     1 |     63ms |       63ms |         1 |     select pg_create_physical_replication_slot($1) |
      |     2 |     19ms |       38ms |         6 | SELECT   current_database() AS db, schemaname, tab |
      |     2 |     10ms |       20ms |         1 | SELECT archived_count,     COALESCE(last_archived_ |
      |     2 |      8ms |       16ms |         1 | SELECT name, current_database(), COALESCE(default_ |
      |     2 |      5ms |       11ms |       273 | SELECT name, setting, COALESCE(boot_val,$1), sourc |
      |     6 |       0s |        5ms |         1 |                      SELECT pg_tablespace_size($1) |
      |     6 |       0s |        5ms |         1 |                        SELECT pg_database_size($1) |
      |     2 |      1ms |        2ms |         0 | SELECT funcid, schemaname, funcname, current_datab |
      |     2 |       0s |         0s |         3 | SELECT oid, spcname, pg_get_userbyid(spcowner),    |
      |     2 |       0s |         0s |        11 | SELECT R.oid, R.rolname, R.rolsuper, R.rolinherit, |
      |     2 |       0s |         0s |         1 | SELECT EXTRACT($1 FROM pg_postmaster_start_time()) |
      |     4 |       0s |         0s |         1 | SELECT COUNT(*) FROM pg_ls_dir($1) WHERE pg_ls_dir |
      |     2 |       0s |         0s |         3 | SELECT D.oid, D.datname, D.datdba, D.dattablespace |
      |     2 |       0s |         0s |         0 | SELECT S.relid, S.schemaname, S.relname, current_d |
      |     2 |       0s |         0s |         0 | SELECT usename, application_name,    COALESCE(clie |
      |     2 |       0s |         0s |         0 | SELECT S.relid, S.indexrelid, S.schemaname, S.reln |
      |     2 |       0s |         0s |         1 | SELECT checkpoints_timed, checkpoints_req, checkpo |
      |     2 |       0s |         0s |         2 | SELECT slot_name, COALESCE(plugin, $1), slot_type, |
      |     2 |       0s |         0s |         1 | SELECT COALESCE(datname, $1), COALESCE(usename, $2 |
      |     2 |       0s |         0s |         0 | SELECT datname, relid, phase, heap_blks_total, hea |
      |     2 |       0s |         0s |         0 | SELECT relid, schemaname, relname, current_databas |
      |     2 |       0s |         0s |         1 |  SELECT system_identifier FROM pg_control_system() |
      |     2 |       0s |         0s |         1 | SELECT checkpoint_lsn, prior_lsn, redo_lsn, timeli |
      |     2 |       0s |         0s |         1 | SELECT xid, COALESCE(EXTRACT($1 FROM timestamp)::b |
      |     2 |       0s |         0s |         1 |                          SELECT current_database() |
      |     2 |       0s |         0s |         1 | SELECT pg_is_in_recovery(),    COALESCE(pg_last_wa |
      +-------+----------+------------+-----------+----------------------------------------------------+

Database #2:
    Name:                bench
    Owner:               mdevan
    Tablespace:          datazfs
    Connections:         1 (no max limit)
    Frozen Xid Age:      789207
    Transactions:        797 (100.0%) commits, 0 (0.0%) rollbacks
    Cache Hits:          94.3%
    Rows Changed:        ins 24.9%, upd 75.1%, del 0.0%
    Total Temp:          0 B in 0 files
    Problems:            0 deadlocks, 0 conflicts
    Totals Since:        7 Aug 2018 1:38:03 AM (37 minutes ago)
    Size:                1.6 GiB
    Tracked Functions:
      +--------------------+-------+-------------+----------------------+
      |           Function | Calls | Time (self) | Time (self+children) |
      +--------------------+-------+-------------+----------------------+
      |          substring |  2850 |    25.874ms |             25.874ms |
      | pg_stat_statements |     5 |       591µs |                591µs |
      +--------------------+-------+-------------+----------------------+

    Installed Extensions:
      +--------------------+---------+-----------------------------------------------------------+
      |               Name | Version |                                                   Comment |
      +--------------------+---------+-----------------------------------------------------------+
      | pg_stat_statements |     1.5 | track execution statistics of all SQL statements executed |
      |            plpgsql |     1.0 |                              PL/pgSQL procedural language |
      +--------------------+---------+-----------------------------------------------------------+

    Slow Queries:
      +-------+----------+------------+-----------+----------------------------------------------------+
      | Calls | Avg Time | Total Time | Rows/Call |                                              Query |
      +-------+----------+------------+-----------+----------------------------------------------------+
      |   399 |     22ms |     9.096s |         1 | UPDATE pgbench_accounts SET abalance = abalance +  |
      |   399 |       0s |      185ms |         1 | UPDATE pgbench_tellers SET tbalance = tbalance + $ |
      |     5 |     18ms |       91ms |        12 | SELECT   current_database() AS db, schemaname, tab |
      |     6 |     13ms |       81ms |         2 | SELECT name, current_database(), COALESCE(default_ |
      |     6 |     11ms |       67ms |         1 | SELECT archived_count,     COALESCE(last_archived_ |
      |   399 |       0s |       53ms |         1 | UPDATE pgbench_branches SET bbalance = bbalance +  |
      |    18 |      2ms |       40ms |         1 |                        SELECT pg_database_size($1) |
      |     1 |     31ms |       31ms |         1 |              select count(*) from pgbench_branches |
      |    18 |      1ms |       26ms |         1 |                      SELECT pg_tablespace_size($1) |
      |   399 |       0s |       26ms |         1 | INSERT INTO pgbench_history (tid, bid, aid, delta, |
      |     6 |      3ms |       22ms |       273 | SELECT name, setting, COALESCE(boot_val,$1), sourc |
      |   399 |       0s |       10ms |         1 | SELECT abalance FROM pgbench_accounts WHERE aid =  |
      |    12 |       0s |        9ms |         1 | SELECT COUNT(*) FROM pg_ls_dir($1) WHERE pg_ls_dir |
      |     6 |       0s |        5ms |         1 | SELECT COALESCE(datname, $2), COALESCE(usename, $3 |
      |     6 |       0s |        4ms |         4 | SELECT S.relid, S.schemaname, S.relname, current_d |
      |     5 |       0s |        4ms |        83 | SELECT userid, dbid, queryid, LEFT(query, $1), cal |
      |     6 |       0s |        2ms |         1 | SELECT funcid, schemaname, funcname, current_datab |
      |     6 |       0s |        2ms |         3 | SELECT S.relid, S.indexrelid, S.schemaname, S.reln |
      |     6 |       0s |        1ms |        11 | SELECT R.oid, R.rolname, R.rolsuper, R.rolinherit, |
      |    24 |       0s |        1ms |         1 |                           SELECT pg_table_size($1) |
      |     6 |       0s |        1ms |         3 | SELECT D.oid, D.datname, D.datdba, D.dattablespace |
      |     6 |       0s |        1ms |         1 | SELECT EXTRACT($1 FROM pg_postmaster_start_time()) |
      |    18 |       0s |        1ms |         1 |                  SELECT pg_total_relation_size($1) |
      |     6 |       0s |         0s |         3 | SELECT oid, spcname, pg_get_userbyid(spcowner),    |
      |   399 |       0s |         0s |         0 |                                                END |
      |     6 |       0s |         0s |         0 | SELECT usename, application_name,    COALESCE(clie |
      |   399 |       0s |         0s |         0 |                                              BEGIN |
      |     6 |       0s |         0s |         1 | SELECT checkpoints_timed, checkpoints_req, checkpo |
      |     6 |       0s |         0s |         2 | SELECT slot_name, COALESCE(plugin, $1), slot_type, |
      |     6 |       0s |         0s |         0 | SELECT c.oid, inhparent::regclass, pg_get_expr(c.r |
      |     6 |       0s |         0s |         0 | SELECT relid, schemaname, relname, current_databas |
      |     6 |       0s |         0s |         1 | SELECT checkpoint_lsn, prior_lsn, redo_lsn, timeli |
      |     6 |       0s |         0s |         1 |  SELECT system_identifier FROM pg_control_system() |
      |     6 |       0s |         0s |         0 | SELECT datname, relid, phase, heap_blks_total, hea |
      |     6 |       0s |         0s |         1 | SELECT xid, COALESCE(EXTRACT($1 FROM timestamp)::b |
      |     5 |       0s |         0s |         0 | WITH    sc AS (SELECT srsubid, COUNT(*) AS c FROM  |
      |     6 |       0s |         0s |         1 | SELECT COALESCE(inet_client_addr() = inet_server_a |
      |     6 |       0s |         0s |         1 | SELECT pg_current_wal_flush_lsn(),     pg_current_ |
      |     6 |       0s |         0s |         0 | SELECT status, receive_start_lsn, receive_start_tl |
      |     6 |       0s |         0s |         1 | SELECT pg_is_in_recovery(),    COALESCE(pg_last_wa |
      |     6 |       0s |         0s |         1 |               SELECT pg_notification_queue_usage() |
      |     6 |       0s |         0s |         0 | SELECT T.oid, T.tgrelid, T.tgname, P.proname     F |
      |     5 |       0s |         0s |         0 | WITH pc AS (SELECT pubname, COUNT(*) AS c FROM pg_ |
      |     6 |       0s |         0s |         1 |                          SELECT current_database() |
      |     6 |       0s |         0s |         0 | SELECT c.oid, i.inhparent::regclass    FROM pg_cla |
      +-------+----------+------------+-----------+----------------------------------------------------+

Database #3:
    Name:                test
    Owner:               mdevan
    Tablespace:          pg_default
    Connections:         1 (no max limit)
    Frozen Xid Age:      789207
    Transactions:        290 (97.6%) commits, 7 (2.4%) rollbacks
    Cache Hits:          98.9%
    Rows Changed:        ins 93.4%, upd 6.6%, del 0.0%
    Total Temp:          0 B in 0 files
    Problems:            0 deadlocks, 0 conflicts
    Totals Since:        7 Aug 2018 1:38:04 AM (37 minutes ago)
    Size:                23 MiB
    Sequences:
      +---------------+------------+
      |      Sequence | Cache Hits |
      +---------------+------------+
      | seqtest_a_seq |            |
      +---------------+------------+

    Tracked Functions:
      +-----------+-------+-------------+----------------------+
      |  Function | Calls | Time (self) | Time (self+children) |
      +-----------+-------+-------------+----------------------+
      | substring |  2841 |    23.041ms |             23.041ms |
      +-----------+-------+-------------+----------------------+

    Installed Extensions:
      +---------+---------+------------------------------+
      |    Name | Version |                      Comment |
      +---------+---------+------------------------------+
      | plpgsql |     1.0 | PL/pgSQL procedural language |
      +---------+---------+------------------------------+

    Disabled Triggers:
      +------+---------------+-----------+
      | Name |         Table | Procedure |
      +------+---------------+-----------+
      |  tr1 | public.trtest |    trigfn |
      +------+---------------+-----------+

    Slow Queries:
      +-------+----------+------------+-----------+----------------------------------------------------+
      | Calls | Avg Time | Total Time | Rows/Call |                                              Query |
      +-------+----------+------------+-----------+----------------------------------------------------+
      |     1 |    125ms |      125ms |         0 | CREATE TABLE measurement (     city_id         int |
      |     5 |     17ms |       89ms |         9 | SELECT   current_database() AS db, schemaname, tab |
      |     1 |     80ms |       80ms |         0 | create subscription sub_invoices connection 'port= |
      |     5 |     11ms |       57ms |         8 | SELECT S.relid, S.schemaname, S.relname, current_d |
      |     1 |     41ms |       41ms |         0 | CREATE TABLE invoices (     invoice_number   int   |
      |     1 |     39ms |       39ms |         0 | create publication pub1 for table logrep_table1, l |
      |    43 |       0s |       38ms |         1 |                           SELECT pg_table_size($1) |
      |     1 |     36ms |       36ms |         0 |           create table repl_table1 (a int, b text) |
      |     1 |     18ms |       18ms |         0 |           create table repl_table3 (a int, b text) |
      |     1 |     11ms |       11ms |         0 |         create table logrep_table1 (a int, b text) |
      |     5 |      1ms |        9ms |         0 | SELECT funcid, schemaname, funcname, current_datab |
      |     1 |      7ms |        7ms |         0 | CREATE TABLE government_invoices (     department_ |
      |     5 |      1ms |        6ms |         0 | WITH pc AS (SELECT pubname, COUNT(*) AS c FROM pg_ |
      |     1 |      5ms |        5ms |         0 |           create table repl_table2 (a int, b text) |
      |     1 |      5ms |        5ms |         0 |         create table logrep_table2 (c int, d text) |
      |     5 |       0s |        4ms |         1 | SELECT name, current_database(), COALESCE(default_ |
      |     5 |       0s |        1ms |         1 | SELECT S.relid, S.indexrelid, S.schemaname, S.reln |
      |     1 |      1ms |        1ms |         0 | CREATE TABLE measurement_y2006m02 PARTITION OF mea |
      |     1 |      1ms |        1ms |         0 | create publication pub1_onlyins1tbl for table logr |
      |     1 |       0s |         0s |         0 | CREATE TABLE measurement_y2006m03 PARTITION OF mea |
      |     5 |       0s |         0s |         1 | SELECT relid, schemaname, relname, current_databas |
      |     5 |       0s |         0s |         0 | SELECT c.oid, inhparent::regclass, pg_get_expr(c.r |
      |     5 |       0s |         0s |         0 | WITH    sc AS (SELECT srsubid, COUNT(*) AS c FROM  |
      |     7 |       0s |         0s |         1 |                  SELECT pg_total_relation_size($1) |
      |     5 |       0s |         0s |         0 | SELECT c.oid, i.inhparent::regclass    FROM pg_cla |
      |     5 |       0s |         0s |         1 | SELECT T.oid, T.tgrelid, T.tgname, P.proname     F |
      |     1 |       0s |         0s |         1 | SELECT subname AS "Name" ,  pg_catalog.pg_get_user |
      |     5 |       0s |         0s |         1 |                          SELECT current_database() |
      |     1 |       0s |         0s |         2 | SELECT pubname AS "Name",   pg_catalog.pg_get_user |
      +-------+----------+------------+-----------+----------------------------------------------------+

    Logical Replication Publications:
      +------------------+-------------+---------------------------+--------+
      |             Name | All Tables? |                 Propagate | Tables |
      +------------------+-------------+---------------------------+--------+
      |             pub1 |          no | inserts, updates, deletes |      2 |
      | pub1_onlyins1tbl |          no |                   inserts |      1 |
      +------------------+-------------+---------------------------+--------+

    Logical Replication Subscriptions:
      Subscription #1:
        Name:              sub_invoices
        Enabled?           yes
        Publications:      1
        Tables:            3
        Workers:           1
        Received Until:    0/15F19D0
        Latency:           74us

Table #1 in "bench":
    Name:                bench.public.pgbench_history
    Columns:             6
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        100.0% est. rows modified
    Row Estimate:        100.0% live of total 399
    Rows Changed:        ins 100.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          98.5% (idx=0.0%)
    Size:                7.0 MiB

Table #2 in "bench":
    Name:                bench.public.pgbench_tellers
    Columns:             4
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        1, last 8 minutes ago
    Auto Analyze:        1, last 8 minutes ago
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        100.0% live of total 1000
    Rows Changed:        ins 0.0%, upd 100.0%, del 0.0%
    HOT Updates:         100.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           399, 1.0 rows/scan
    Cache Hits:          53.7% (idx=76.9%)
    Size:                5.9 MiB
    Bloat:               9.1 MiB (152.4%)
    +----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+
    |                Index |  Type |    Size |            Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
    +----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+
    | pgbench_tellers_pkey | btree | 3.2 MiB | 9.1 MiB (279.5%) |      76.9% |   399 |            1.0 |               1.0 |
    +----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+

Table #3 in "bench":
    Name:                bench.public.pgbench_accounts
    Columns:             4
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 96.7%, del 0.0%
    HOT Updates:         96.7% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           798, 1.0 rows/scan
    Cache Hits:          66.5% (idx=79.8%)
    Size:                1.3 GiB
    Bloat:               24 MiB (1.8%)
    +-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+
    |                 Index |  Type |    Size |          Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
    +-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+
    | pgbench_accounts_pkey | btree | 214 MiB | 24 MiB (11.3%) |      79.8% |   798 |            1.0 |               1.0 |
    +-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+

Table #4 in "bench":
    Name:                bench.public.pgbench_branches
    Columns:             3
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        1, last 8 minutes ago
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        60.6% live of total 165
    Rows Changed:        ins 0.0%, upd 100.0%, del 0.0%
    HOT Updates:         100.0% of all updates
    Seq Scans:           1, 100.0 rows/scan
    Idx Scans:           399, 1.0 rows/scan
    Cache Hits:          81.1% (idx=91.5%)
    Size:                1.9 MiB
    Bloat:               2.7 MiB (139.0%)
    +-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+
    |                 Index |  Type |    Size |            Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
    +-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+
    | pgbench_branches_pkey | btree | 816 KiB | 2.7 MiB (335.3%) |      91.5% |   399 |            1.0 |               1.0 |
    +-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+

Table #1 in "test":
    Name:                test.public.vactest
    Columns:             1
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                9.0 MiB
    Bloat:               744 KiB (8.1%)

Table #2 in "test":
    Name:                test.public.seqtest
    Columns:             1
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                0 B

Table #3 in "test":
    Name:                test.public.trtest
    Columns:             1
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                0 B

Table #4 in "test":
    Name:                test.public.locktest
    Columns:             1
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                8.0 KiB

Table #5 in "test":
    Name:                test.public.bloattest
    Columns:             2
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                3.8 MiB
    Bloat:               3.0 MiB (78.6%)
    +--------+-------+---------+------------------+------------+-------+----------------+-------------------+
    |  Index |  Type |    Size |            Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
    +--------+-------+---------+------------------+------------+-------+----------------+-------------------+
    | bt_idx | btree | 2.6 MiB | 3.0 MiB (117.0%) |            |     0 |            0.0 |               0.0 |
    +--------+-------+---------+------------------+------------+-------+----------------+-------------------+

Table #6 in "test":
    Name:                test.public.measurement_y2006m02
    Attributes:          partition
    Partition of:        measurement, FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
    Columns:             4
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                0 B

Table #7 in "test":
    Name:                test.public.measurement_y2006m03
    Attributes:          partition
    Partition of:        measurement, FOR VALUES FROM ('2006-03-01') TO ('2006-04-01')
    Columns:             4
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                0 B

Table #8 in "test":
    Name:                test.public.invoices
    Columns:             2
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           1, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                0 B
    +---------------+-------+---------+-------+------------+-------+----------------+-------------------+
    |         Index |  Type |    Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan |
    +---------------+-------+---------+-------+------------+-------+----------------+-------------------+
    | invoices_pkey | btree | 8.0 KiB |       |            |     0 |            0.0 |               0.0 |
    +---------------+-------+---------+-------+------------+-------+----------------+-------------------+

Table #9 in "test":
    Name:                test.public.government_invoices
    Inherits from:       invoices
    Columns:             3
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                8.0 KiB

Table #10 in "test":
    Name:                test.public.logrep_table1
    Columns:             2
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                8.0 KiB

Table #11 in "test":
    Name:                test.public.logrep_table2
    Columns:             2
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                8.0 KiB

Table #12 in "test":
    Name:                test.public.repl_table1
    Columns:             2
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        100.0% est. rows modified
    Row Estimate:        100.0% live of total 3
    Rows Changed:        ins 100.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                16 KiB

Table #13 in "test":
    Name:                test.public.repl_table2
    Columns:             2
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                8.0 KiB

Table #14 in "test":
    Name:                test.public.repl_table3
    Columns:             2
    Manual Vacuums:      never
    Manual Analyze:      never
    Auto Vacuums:        never
    Auto Analyze:        never
    Post-Analyze:        0.0% est. rows modified
    Row Estimate:        0.0% live of total 0
    Rows Changed:        ins 0.0%, upd 0.0%, del 0.0%
    HOT Updates:         0.0% of all updates
    Seq Scans:           0, 0.0 rows/scan
    Idx Scans:           0, 0.0 rows/scan
    Cache Hits:          0.0% (idx=0.0%)
    Size:                8.0 KiB