pg_converter

KO | EN

gitlite — search

#javascript #python #hacktoberfest #react #ai #typescript #llm #go #golang #android #machine-learning #rust #deep-learning #linux

★ 16 Open GitHub ↗

Tool for Database Migrations

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# 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](https://github.com/O2eg/pg_converter/blob/master/docs/ROADMAP.md).

## Documentation

- [Architecture and asynchronous runtime](https://github.com/O2eg/pg_converter/blob/master/docs/ARCHITECTURE.md)
- [Complete ASCII diagram catalog](https://github.com/O2eg/pg_converter/blob/master/docs/DIAGRAMS.md)
- [Configuration](https://github.com/O2eg/pg_converter/blob/master/docs/CONFIGURATION.md)
- [Packet, step, action, and generator format](https://github.com/O2eg/pg_converter/blob/master/docs/PACKET_FORMAT.md)
- [CLI operations and recovery](https://github.com/O2eg/pg_converter/blob/master/docs/OPERATIONS.md)
- [Security and trust boundaries](https://github.com/O2eg/pg_converter/blob/master/docs/SECURITY.md)
- [Programmatic API and events](https://github.com/O2eg/pg_converter/blob/master/docs/PROGRAMMATIC_API.md)
- [pg_play machine and MCP integration](https://github.com/O2eg/pg_converter/blob/master/docs/pg_play-integration.md)
- [Export and Mattermost/Slack hooks](https://github.com/O2eg/pg_converter/blob/master/docs/EXPORT_AND_HOOKS.md)
- [Bundled packet catalog](https://github.com/O2eg/pg_converter/blob/master/docs/BUNDLED_PACKETS.md)
- [Use cases](https://github.com/O2eg/pg_converter/blob/master/docs/USE_CASES.md)
- [Tests and the PostgreSQL 10-18 matrix](https://github.com/O2eg/pg_converter/blob/master/tests/README.md)
- [Stabilization roadmap](https://github.com/O2eg/pg_converter/blob/master/docs/ROADMAP.md)

## 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

| Component | Supported contract |
|---|---|
| Python | 3.10 or newer |
| PostgreSQL | Major versions 10-18 |
| Operating system | Linux is the primary CI-tested platform |
| Runtime dependencies | `asyncpg`, `sqlparse` |
| AES export | Optional `export` extra (`pyzipper`) |
| Webhooks | Optional `webhooks` extra (`aiohttp`) |
| Tests and development | Optional `dev` extra |

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](https://github.com/O2eg/pg_converter/blob/master/docs/SECURITY.md).

## Installation

Install a built release:

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

For development from a source checkout:

```bash
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

```ini
[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](https://github.com/O2eg/pg_converter/blob/master/docs/CONFIGURATION.md).

## Quick start

```bash
# 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:

```bash
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](https://github.com/O2eg/pg_converter/blob/master/docs/OPERATIONS.md).

## Packet format at a glance

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](https://github.com/O2eg/pg_converter/blob/master/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:

```ini
[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](https://github.com/O2eg/pg_converter/blob/master/docs/SECURITY.md)
before production use: SQL packets, Python packets, placeholders, hooks, logs,
and exports are separate trust boundaries.

## Programmatic API

```python
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](https://github.com/O2eg/pg_converter/blob/master/docs/PROGRAMMATIC_API.md).

## Exit status and results

The CLI returns:

| Code | Meaning |
|---:|---|
| `0` | Every selected database has `success` or `nothing_todo` |
| `1` | Argument/runtime error, `fail`, `locked`, `terminate`, or cancellation |

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

## Tests

```bash
# 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](https://github.com/O2eg/pg_converter/blob/master/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](https://github.com/O2eg/pg_converter/blob/master/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](https://github.com/O2eg/pg_converter/blob/master/LICENSE).

pg_converter

오늘의 추천 프로젝트

Was this content helpful?

Recent Feedback

pg_converter

오늘의 추천 프로젝트

Was this content helpful?

Recent Feedback

Download README