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

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

Output

pgmetrics can report the collected statistics in human-readable text format, or save it into a JSON format file. The JSON format output is 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 10.4. It also works with:

  • Amazon AWS Aurora
  • Amazon AWS RDS
  • Google GCP SQL
  • Postgres 11 (devel)
Availability

pgmetrics is written in Go, and available for download as a zero-dependency statically linked command-line tool for many platforms.

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