Reference

$ flyway -configFiles=conf/flyway.toml migrate
Flyway Community Edition 10.12.0 by Redgate

See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5432/postgres (PostgreSQL 15.5)
ERROR: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 001
-> Applied to database : -332813992
-> Resolved locally    : 422692347
Either revert the changes to the migration, or run repair to update the schema history.
Need more flexibility with validation rules? Learn more: https://rd.gt/3AbJUZE

select version, description, script, success from flyway_schema_history;

version |                      description                      |                             script                              | success
---------+-------------------------------------------------------+-----------------------------------------------------------------+---------
001     | Create initial schema                                 | V001__Create_initial_schema.sql                                 | t
002     | Make initializecontractimplements function idempotent | V002__Make_initializecontractimplements_function_idempotent.sql | t
003     | Fix create index for contract                         | V003__Fix_create_index_for_contract.sql                         | t
004     | Add tmp to tx references cascade constraint           | V004__Add_tmp_to_tx_references_cascade_constraint.sql           | t
005     | Make watermark progression safer                      | V005__Make_watermark_progression_safer.sql                      | t
006     | Make checkpoint functions stable                      | V006__Make_checkpoint_functions_stable.sql                      | t
007     | Expose effective at                                   | V007__Expose_effective_at.sql                                   | t
(7 rows)

$ flyway -configFiles=conf/flyway.toml migrate -table=myapp_version -baselineOnMigrate=true -baselineVersion=0
Flyway Community Edition 10.12.0 by Redgate

See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5432/postgres (PostgreSQL 15.5)
Schema history table "public"."myapp_version" does not exist yet
Successfully validated 1 migration (execution time 00:00.029s)
Creating Schema History table "public"."myapp_version" with baseline ...
Successfully baselined schema with version: 0
Current version of schema "public": 0
Migrating schema "public" to version "1.0 - Add index on Ping sender"
Successfully applied 1 migration to schema "public", now at version v1.0 (execution time 00:00.036s)

select version, description, script, success from myapp_version;

version |       description        |               script               | success
---------+--------------------------+------------------------------------+---------
0       | << Flyway Baseline >>    | << Flyway Baseline >>              | t
1.0     | Add index on Ping sender | V1.0__Add_index_on_Ping_sender.sql | t
(2 rows)

-- new PQS release applies missing migrations
org.flywaydb.core.internal.command.DbValidate: Successfully validated 8 migrations (execution time 00:00.018s)  application=scribe
org.flywaydb.core.internal.command.DbSchemas: Skipping creation of existing schema: "public"  application=scribe
org.flywaydb.core.internal.command.DbMigrate: Current version of schema "public": 007  application=scribe
org.flywaydb.core.internal.parser.Parser: Parsing V008__Add_new_table.sql ...  application=scribe
org.flywaydb.core.internal.sqlscript.ParserSqlScript: Found statement at line 1: create table _foo(value int)  application=scribe
org.flywaydb.core.internal.command.DbMigrate: Starting migration of schema "public" to version "008 - Add new table" ...  application=scribe
org.flywaydb.core.internal.command.DbMigrate: Migrating schema "public" to version "008 - Add new table"  application=scribe
org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor: Executing SQL: create table _foo(value int)  application=scribe
org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor: 0 rows affected  application=scribe
org.flywaydb.core.internal.command.DbMigrate: Successfully completed migration of schema "public" to version "008 - Add new table"  application=scribe
org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory: Schema History table "public"."flyway_schema_history" successfully updated to reflect changes  application=scribe
org.flywaydb.core.internal.command.DbMigrate: Successfully applied 1 migration to schema "public", now at version v008 (execution time 00:00.006s)  application=scribe

### the application applies missing migrations
$ flyway -configFiles=conf/flyway.toml migrate -table=myapp_version
Flyway Community Edition 10.12.0 by Redgate

See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5432/postgres (PostgreSQL 15.5)
Successfully validated 3 migrations (execution time 00:00.060s)
Current version of schema "public": 1.0
Migrating schema "public" to version "1.1 - Add index on Ping receiver"
Successfully applied 1 migration to schema "public", now at version v1.1 (execution time 00:00.020s)

select version, description, script, success from flyway_schema_history ;

version |                      description                      |                             script                              | success
---------+-------------------------------------------------------+-----------------------------------------------------------------+---------
001     | Create initial schema                                 | V001__Create_initial_schema.sql                                 | t
002     | Make initializecontractimplements function idempotent | V002__Make_initializecontractimplements_function_idempotent.sql | t
003     | Fix create index for contract                         | V003__Fix_create_index_for_contract.sql                         | t
004     | Add tmp to tx references cascade constraint           | V004__Add_tmp_to_tx_references_cascade_constraint.sql           | t
005     | Make watermark progression safer                      | V005__Make_watermark_progression_safer.sql                      | t
006     | Make checkpoint functions stable                      | V006__Make_checkpoint_functions_stable.sql                      | t
007     | Expose effective at                                   | V007__Expose_effective_at.sql                                   | t
008     | Add new table                                         | V008__Add_new_table.sql                                         | t
(8 rows)

select version, description, script, success from myapp_version ;

version |        description         |                script                | success
---------+----------------------------+--------------------------------------+---------
0       | << Flyway Baseline >>      | << Flyway Baseline >>                | t
1.0     | Add index on Ping sender   | V1.0__Add_index_on_Ping_sender.sql   | t
1.1     | Add index on Ping receiver | V1.1__Add_index_on_Ping_receiver.sql | t
(3 rows)

postgres=# \di+ __contracts_1_ping_sender_idx
                                                       List of relations
 Schema |             Name              | Type  |  Owner   |     Table     | Persistence | Access method | Size  | Description
--------+-------------------------------+-------+----------+---------------+-------------+---------------+-------+-------------
 public | __contracts_1_ping_sender_idx | index | postgres | __contracts_1 | permanent   | hash          | 32 kB |
(1 row)

postgres=# \di+ __contracts_1_ping_receiver_idx
                                                        List of relations
 Schema |              Name               | Type  |  Owner   |     Table     | Persistence | Access method | Size  | Description
--------+---------------------------------+-------+----------+---------------+-------------+---------------+-------+-------------
 public | __contracts_1_ping_receiver_idx | index | postgres | __contracts_1 | permanent   | hash          | 32 kB |
(1 row)

$ tree .
.
├── conf
│   └── flyway.toml
└── migrations
    ├── V1.0__Add_index_on_Ping_sender.sql
    └── V1.1__Add_index_on_Ping_receiver.sql

$ head migrations/*
==> migrations/V1.0__Add_index_on_Ping_sender.sql <==
call create_index_for_contract('ping_sender', 'PingPong:Ping', '(payload->>''sender'')', 'hash');

==> migrations/V1.1__Add_index_on_Ping_receiver.sql <==
call create_index_for_contract('ping_receiver', 'PingPong:Ping', '(payload->>''receiver'')', 'hash');

$ ./scribe.jar pipeline ledger postgres-document --source-ledger-host 10.0.0.0

Applied configuration:
...
source {
    ledger {
        ...
        host=10.0.0.0
        ...
    }
}

14:42:28.281 I [zio-fiber-600078685] com.digitalasset.zio.daml.Channel:37 Keep-alive (get ledger version) successful

Applied configuration:
...
pipeline {
    filter {
        contracts="*"
        ...
    }
}
...
I [zio-fiber-330812857] com.digitalasset.zio.daml.ledgerapi.logFilterContents:12 Contract filter inclusive of 1 templates and 1 interfaces

14:52:40.201 D [zio-fiber-655477872] com.digitalasset.zio.daml.ledgerapi.logFilterContents:13 Including template 5fe0c850054845d95a855650cba76d9d999c3e985ae549bcca01253b17b08b2c:PingPong:Ping
14:52:40.201 D [zio-fiber-655477872] com.digitalasset.zio.daml.ledgerapi.logFilterContents:13 Including template 5fe0c850054845d95a855650cba76d9d999c3e985ae549bcca01253b17b08b2c:PingPong:PingWithCK
14:52:40.201 D [zio-fiber-655477872] com.digitalasset.zio.daml.ledgerapi.logFilterContents:13 Including template 5fe0c850054845d95a855650cba76d9d999c3e985ae549bcca01253b17b08b2c:PingPong:Pong

$ ./scribe.jar pipeline \
     --logger-level=Debug \
     --logger-mappings-io.netty=Info \
     --logger-mappings-io.grpc.netty=Info

15:01:40.872 I [zio-fiber-950510203] com.digitalasset.zio.daml.ledgerapi.PartiesService:61 2 known parties retrieved
15:01:40.876 I [zio-fiber-950510203] com.digitalasset.scribe.pipeline.pipeline.Impl:39 Starting pipeline on behalf of 'Alice::12209adab9c5e9d672d7b4515e26f4cd296cc2ec99bdb9787be7e804c49ca52f2686,Bob::12209adab9c5e9d672d7b4515e26f4cd296cc2ec99bdb9787be7e804c49ca52f2686'

$ ./scribe.jar pipeline --pipeline-ledger-start=Oldest --pipeline-ledger-stop=Never

15:28:12.778 I [zio-fiber-1984883840] com.digitalasset.zio.daml.ledgerapi.StateService:79 Retrieved ledger end offset: 00000000000000000f
15:28:12.809 I [zio-fiber-1984883840] com.digitalasset.scribe.pipeline.pipeline.Impl:91 Last known checkpoint is at offset '00000000000000000f' and index '9'
15:28:12.811 I [zio-fiber-1984883840] com.digitalasset.scribe.pipeline.pipeline.Impl:100 Continuing from offset '00000000000000000f' and index '9' until offset 'INFINITY'

select stat.relname as relname,
       seq_scan,
       seq_tup_read,
       idx_scan,
       idx_tup_fetch,
       heap_blks_read,
       heap_blks_hit,
       round((100 * heap_blks_hit::float / coalesce(nullif(heap_blks_hit + heap_blks_read, 0), 1))::numeric, 2) as heap_blks_ratio,
       idx_blks_read,
       idx_blks_hit,
       round((100 * idx_blks_hit::float / coalesce(nullif(idx_blks_hit + idx_blks_read, 0), 1))::numeric, 2) as idx_blks_ratio
from pg_stat_user_tables stat
  right join pg_statio_user_tables statio on stat.relid = statio.relid;

select pid,
       datname,
       usename,
       application_name,
       client_hostname,
       client_port,
       backend_start,
       query_start,
       (now() - query_start) as exec_time_so_far,
       query,
       state
from pg_stat_activity
where state = 'active';

select total_exec_time,
       calls,
       query,
       queryid,
       toplevel
from pg_stat_statements
where not (
    query ilike 'create %' or
    query ilike 'alter %' or
    query ilike 'drop %' or
    query ilike 'grant %' or
    query ilike 'revoke %' or
    query ilike 'set %' or
    query ilike 'do %' or
    query like '%pg_stat%' or
    query like '%pg_database%' or
    query like '%pg_replication%' or
    query like '%information_schema%' or
    query like '%prune_contracts%' or
    query like 'with deleted_transactions%' or
    query like 'with deleted_contracts%' or
    query = 'SELECT version()' or
    query = 'SELECT $1' or
    query = 'BEGIN' or
    query = 'COMMIT' or
    query = 'ROLLBACK'
    )
order by total_exec_time desc
limit 10;

select schemaname, relname, n_live_tup as rows_in_table
from pg_stat_user_tables
where n_live_tup > 0
order by rows_in_table desc, relname;

select *
from (select relname,
             heap_blks_read                                                                                           as disk_blocks_read,
             heap_blks_hit                                                                                            as buffer_hits,
             round((100 * heap_blks_hit::float / coalesce(nullif(heap_blks_hit + heap_blks_read, 0), 1))::numeric, 2) as cache_hit_ratio
      from pg_statio_user_tables
      order by cache_hit_ratio desc) as foo
where cache_hit_ratio > 0;

select *
from (select relname,
             idx_blks_read                                                                                         as disk_blocks_read,
             idx_blks_hit                                                                                          as buffer_hits,
             round((100 * idx_blks_hit::float / coalesce(nullif(idx_blks_hit + idx_blks_read, 0), 1))::numeric, 2) as cache_hit_ratio
      from pg_statio_user_tables
      order by cache_hit_ratio desc) as foo
where cache_hit_ratio > 0;

select total_checkpoints,
       seconds_since_start / total_checkpoints / 60                   as minutes_between_checkpoints
from (select extract(epoch from (now() - pg_postmaster_start_time())) as seconds_since_start,
             (checkpoints_timed + checkpoints_req)                    as total_checkpoints
      from pg_stat_bgwriter) as sub;

select n.nspname                                                  as "schema",
       c.relname                                                  as "name",
       case c.relkind
           when 'r' then 'table'
           when 'v' then 'view'
           when 'm' then 'materialized view'
           when 'i' then 'index'
           when 's' then 'sequence'
           when 't' then 'toast table'
           when 'f' then 'foreign table'
           when 'p' then 'partitioned table'
           when 'i' then 'partitioned index' end                  as "type",
       pg_catalog.pg_get_userbyid(c.relowner)                     as "owner",
       case c.relpersistence
           when 'p' then 'permanent'
           when 't' then 'temporary'
           when 'u' then 'unlogged' end                           as "persistence",
       am.amname                                                  as "access method",
       pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "size",
       pg_catalog.obj_description(c.oid, 'pg_class')              as "description"
from pg_catalog.pg_class c
  left join pg_catalog.pg_namespace n on n.oid = c.relnamespace
  left join pg_catalog.pg_am am on am.oid = c.relam
where c.relkind in ('r', 'p', '')
  and n.nspname <> 'pg_catalog'
  and n.nspname !- '-pg_toast'
  and n.nspname <> 'information_schema'
  and pg_catalog.pg_table_is_visible(c.oid)
order by 1, 2;

select n.nspname                                                  as "schema",
       c.relname                                                  as "name",
       case c.relkind
           when 'r' then 'table'
           when 'v' then 'view'
           when 'm' then 'materialized view'
           when 'i' then 'index'
           when 's' then 'sequence'
           when 't' then 'toast table'
           when 'f' then 'foreign table'
           when 'p' then 'partitioned table'
           when 'i' then 'partitioned index' end                  as "type",
       pg_catalog.pg_get_userbyid(c.relowner)                     as "owner",
       c2.relname                                                 as "table",
       case c.relpersistence
           when 'p' then 'permanent'
           when 't' then 'temporary'
           when 'u' then 'unlogged' end                           as "persistence",
       am.amname                                                  as "access method",
       pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "size",
       pg_catalog.obj_description(c.oid, 'pg_class')              as "description"
from pg_catalog.pg_class c
  left join pg_catalog.pg_namespace n on n.oid = c.relnamespace
  left join pg_catalog.pg_am am on am.oid = c.relam
  left join pg_catalog.pg_index i on i.indexrelid = c.oid
  left join pg_catalog.pg_class c2 on i.indrelid = c2.oid
where c.relkind in ('i', 'i', '')
  and n.nspname <> 'pg_catalog'
  and n.nspname !- '-pg_toast'
  and n.nspname <> 'information_schema'
  and pg_catalog.pg_table_is_visible(c.oid)
order by 1, 2;

select template_fqn,
       contract_id,
       octet_length(payload::text)::bigint as size_in_bytes
from active()
order by size_in_bytes desc
limit 10;

select created_at_offset,
       sum(octet_length(payload::text)::bigint) as size_in_bytes
from creates()
group by created_at_offset
order by size_in_bytes desc
limit 10;

select "offset",
       count(*) as events_in_tx
from __events, __transactions
where __events.tx_ix = __transactions.ix
group by "offset"
order by events_in_tx desc
limit 10;