pg-materialize 0.2.5

Description

pg_materialize is a utility for generating PostgreSQL creation and refresh scripts from a folder of Materialized View SQL definitions. It uses psqlparse to transform the SQL into parse trees, identifies which Materialized Views have a dependency on other views by generating a DAG, and produces the correct order for constructing and refreshing these views. Cross-schema views are handled correctly, and extraneous transaction blocks are filtered out from the final output. The files from each subsequent run are timestamped with the suffix YYYYMMDD-HHMMSS.sql.

Options

pg_materialize accepts the following command line arguments.

• Analyzes dependencies without actually generating the output files.

• The directory for the PostgreSQL scripts to analyze.

• Regex pattern to match when ignoring refresh on Materialized Views (i.e. “hist” for “users_mv_hist”).

• The directory for the output creation and refresh scripts.

• Materialized View regex pattern to match (i.e. “_mv” for “users_mv”),

• Enables verbose logging.

Example

Command:

pg_materialize -v \
    -i ~/Projects/my_project/src \
    -I hist \
    -o ~/Projects/my_project/output

Output:

Found 97 Scripts in /Users/ali/Projects/my_project/src
Identified 169 Materialized Views, Containing 90 View Dependencies

Materialized View Dependencies:
'public.users_mv': ['public.user_addresses_mv',
                    'public.user_invites_mv'],
'public.orders_mv': ['public.payment_methods_mv']

Selecting 97 Materialized Views for Refresh
Successfully Saved Creation Script to ~/Projects/my_project/output/create-20170824-120626.sql
Successfully Saved Refresh Script to ~/Projects/my_project/output/refresh-20170824-120626.sql