pg-converter

pg-converter 2.0 beta runs SQL and Python packets against one or more PostgreSQL databases. It retains the original packet execution model under the pgc_* tracker and PGC_PL_* placeholder namespaces. The threaded runtime has been replaced with asyncio and asyncpg; bounded retry, reconnection, SSL/mTLS, and a programmatic API for a future UI have been added.

The repository contains every original packet: 46 directories and 167 files. The Python-step example uses the asynchronous API, and hook metadata uses the pg_converter username. SQL, generator, tracker, and action behavior is retained with PG Converter 2.0 names.

The distribution and installed command are pg-converter; the import package and GitHub repository are named pg_converter.

Project status

The core runtime and original packet contract are tested on PostgreSQL 10-18. The wheel and source distribution include the complete bundled packet tree, the configuration example, package metadata, and license. Remaining stabilization work includes:

• the general RunRequest, event, and full PGCResult programmatic contracts are not yet versioned; the bounded pg_play machine plan/result contracts are versioned independently;
• concurrent database tasks do not yet have a configurable upper bound;
• several logging settings are loaded but are not yet connected to the CLI renderer;
• production fixtures for non-standard deployed tracker revisions are still being collected.

The remaining work and stable-release criteria are recorded in the roadmap.

Documentation

• Architecture and asynchronous runtime
• Complete ASCII diagram catalog
• Configuration
• Packet, step, action, and generator format
• CLI operations and recovery
• Security and trust boundaries
• Programmatic API and events
• pg_play machine and MCP integration
• Export and Mattermost/Slack hooks
• Bundled packet catalog
• Use cases
• Tests and the PostgreSQL 10-18 matrix
• Stabilization roadmap

Features

• parallel asynchronous database processing and sequential --seq mode;
• default, read_only, no_commit, maintenance, and export_data packet types;
• run_once.sql, object/schema generators, and maintenance commands;
• GEN_OBJ_FLD_N, GEN_NSP_FLD_N, and user-defined PGC_PL_* placeholders;
• SQL steps and synchronous/asynchronous Python steps;
• persistent pgc_packets, pgc_steps, pgc_actions, and pgc_locks;
• a session advisory lock combined with the durable pgc_locks row;
• bounded retry and reconnection with session-setting and generator recovery;
• a lock observer for worker-as-blocker and worker-as-waiter cases;
• typed failures and safe outcome_unknown handling;
• tab-separated output in .csv files, ZIP, and AES ZIP;
• asynchronous Mattermost and Slack hooks;
• SSL/TLS, CA and hostname verification, CRL, and client certificates;
• a cancellable UI-neutral API with events and a JSON-safe result;
• a reviewed pg_play/component/v1 machine contract with deterministic packet plans and compact results;
• PostgreSQL 10-18 container tests and a separate mTLS test.

Requirements

No PostgreSQL extension is required. The database role must be able to perform the target changes, create or use the tracker schema, and inspect its own backend sessions. Stopping backends owned by another role may require pg_signal_backend or superuser; see the security guide.

Installation

Install a built release:

python -m pip install 'pg-converter[export,webhooks]'
pg-converter --version

For development from a source checkout:

git clone https://github.com/O2eg/pg_converter.git
cd pg_converter

python3 -m venv .venv
. .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install -e '.[dev,export,webhooks]'

cp conf/pg_converter.conf.example conf/pg_converter.conf
pg-converter --version
pg-converter --help

Custom packets/, conf/, logs/, and export paths are resolved from the current working directory. A custom $PWD/packets/PACKET_NAME overrides a bundled packet with the same name; otherwise the installed packet is used. A relative --config-file NAME resolves to $PWD/conf/NAME; an absolute path is recommended for external configuration.

Minimal configuration

[databases]
development = postgresql://user:password@localhost:5432/development

[main]
schema_location = pgc
db_name_all_confirmation = true

The complete parameter list, defaults, precedence, and current limitations are documented in docs/CONFIGURATION.md.

Quick start

# One database.
pg-converter --packet-name=dba_get_version --db-name=development

# Every alias in [databases]; confirmation requires YES when enabled.
pg-converter --packet-name=dba_get_version --db-name=ALL

# Masks and exclusions.
pg-converter --packet-name=dba_get_version --db-name='prod_*'
pg-converter --packet-name=dba_get_version \
  --db-name='ALL,exclude:prod_old,dev_*'

# Process selected databases sequentially.
pg-converter --packet-name=test_common --db-name=ALL --seq

# Only list selected aliases.
pg-converter --packet-name=test_common --db-name='prod_*' --list

Administrative operations:

pg-converter --packet-name=test_common --db-name=development --status
pg-converter --packet-name=test_common --db-name=development --wipe
pg-converter --packet-name=test_common --db-name=development --unlock
pg-converter --packet-name=test_common --db-name=development --stop

--status, --wipe, --unlock, and --stop connect to the database and call the non-destructive init_tables; if the tracker is absent, its schema and tables are created. --wipe deletes packet history but does not reverse changes to application objects. --stop terminates matching backend sessions.

The full CLI contract and recovery runbook are in docs/OPERATIONS.md.

Packet format at a glance

packets/my_packet/
|-- meta_data.json
|-- run_once.sql
|-- 01_step.sql
|-- 02_gen_nsp.sql
|-- 02_gen_obj.sql
|-- 02_step.sql
|-- 03_step.py
`-- data/

Terminology retained from the original project:

• packet: a directory representing one logical operation;
• step: a root .sql or .py file;
• action: one concrete transaction or command produced from a step;
• generator: SQL that supplies values for one step;
• conversion/deployment: applying a packet to selected databases.

Steps run in lexical order. A generator-backed step creates multiple actions. Generator column zero is reserved for a maintenance command; placeholders start at index 1.

run_once.sql runs before generators are loaded. TEMP and pg_temp objects work only while the same physical session survives. After a reconnection they are gone, so the runtime fails with SessionLocalRunOnceLost instead of replaying potentially destructive run_once.sql.

The normative format, checksum behavior, and transactional guarantees are in docs/PACKET_FORMAT.md.

Tracker and resume behavior

The following objects are created in main.schema_location:

• pgc_packets: metadata, packet checksum, and final status;
• pgc_steps: status and latest step error;
• pgc_actions: checksums of successfully applied actions;
• pgc_locks: durable representation of the runtime lock.

For a default packet, a transactional action and its pgc_actions row commit atomically. After reconnection, already confirmed action hashes are not run again.

PG Converter 2.0 does not auto-discover tracker objects created by pre-2.0 builds. Before reusing an existing migration history, migrate its rows and database objects to the documented pgc_* names or start with a new tracking schema only after reviewing which packet actions have already been applied. Never run two tracker namespaces for the same packet concurrently.

If a connection is lost while a non-transactional or maintenance action is in flight, the server may have applied the command even though the client did not receive confirmation. The runtime reports outcome_unknown, does not retry it, and blocks the next normal run. The operator must inspect the database and tracker state; --force is appropriate only after that review.

SSL/TLS

PostgreSQL URI, pq://, and libpq key=value formats are supported:

[databases]
verified = postgresql://pgc@db.example.com/app?sslmode=verify-full&sslrootcert=/etc/pg_converter/ca.crt

mutual_tls = postgresql://pgc@db.example.com/app?sslmode=verify-full&sslrootcert=/etc/pg_converter/ca.crt&sslcert=/etc/pg_converter/client.crt&sslkey=/etc/pg_converter/client.key

libpq_style = host=db.example.com port=5432 dbname=app user=pgc sslmode=verify-ca sslrootcert=/etc/pg_converter/ca.crt sslcert=/etc/pg_converter/client.crt sslkey=/etc/pg_converter/client.key

Read docs/SECURITY.md before production use: SQL packets, Python packets, placeholders, hooks, logs, and exports are separate trust boundaries.

Programmatic API

from pathlib import Path

from pg_converter.api import PGConverterService, RunRequest

service = PGConverterService(Path("/etc/pg_converter/pg_converter.conf"))
handle = service.start(
    RunRequest(
        packet_name="test_common",
        db_name="development",
        placeholders={"SCHEMA": "public"},
    ),
    event_handler=on_execution_event,
    confirmation_handler=confirm_databases,
)

# If needed:
# cancellation = await handle.cancel()

result = await handle.wait()
payload = result.to_dict()

The API contains no UI components. It exposes RunRequest, PGConverterService, RunHandle, ExecutionEvent, and PGCResult. The result is JSON-safe, but it does not yet carry a schema version; integrations should pin the pg-converter version they use. See docs/PROGRAMMATIC_API.md.

Exit status and results

The CLI returns:

The programmatic result distinguishes success, fail, locked, nothing_todo, and terminate. Packet status is one of done, started, exception, new, or unknown.

Tests

# Unit tests.
pytest -m 'not integration'

# One PostgreSQL version.
PGC_DOCKER_INTEGRATION=1 PGC_DOCKER_VERSIONS=18 \
  pytest --run-integration tests/integration

# PostgreSQL 10-18 and TLS/mTLS.
PGC_DOCKER_INTEGRATION=1 \
  pytest --run-integration tests/integration

The complete matrix in GitHub Actions is currently triggered manually with workflow_dispatch. The unit suite runs on Python 3.10 and 3.12 for pushes and pull requests. Test layout and regression-test guidance are in tests/README.md.

Known export security limitations

Files are published atomically through .part plus rename, but their permissions currently depend on the process umask. In addition, the "password": "random" mode puts the AES ZIP password in the filename and that archive path is logged. Do not use random for sensitive exports until this is fixed; use a controlled password and a protected working directory. See docs/EXPORT_AND_HOOKS.md.

Preserved packet and tracker contracts

Retained:

• the complete original packet set and the dba_, alert_, maint_, test_, and YYYYMMDD_TASK_* naming convention;
• arbitrary step names and run_once.sql;
• the generator and placeholder model;
• all five packet types and meta_data.json;
• Mattermost/Slack metadata;
• MD5 packet and action checksums;
• the pgc_* schema and migration history;
• list/status/wipe/stop/unlock/template and skip-cancel modes.

The packet inventory is complete. The asynchronous Python example and bundled hook usernames use the PG Converter 2.0 beta runtime contract.

Intentional changes:

• threads are replaced by asynchronous tasks;
• py-postgresql is replaced by asyncpg;
• unbounded retry is replaced by a bounded policy;
• lock ownership is strengthened with an advisory lock;
• ambiguous non-transactional outcomes are not replayed;
• SSL/mTLS and a UI-neutral API are added.

License

The project is distributed under the MIT License. See LICENSE.