System Architecture Overview
Relevant source files
The following files were used as context for generating this wiki page:
The meltano-state-backend-postgresql project is a specialized extension for Meltano that implements a PostgreSQL-based state storage backend. Its primary purpose is to provide a robust, relational database alternative for persisting Meltano state data, moving away from default local filesystem storage or standard system database configurations. It leverages the psycopg library for high-performance interaction with PostgreSQL instances.
Sources: README.md:1-10, pyproject.toml:23-27, src/meltano_state_backend_postgresql/backend.py:108-112
Core Components and Backend Logic
The system is centered around the PostgreSQLStateStoreManager class, which extends Meltano's base StateStoreManager. This component handles the lifecycle of state data, including connection management, table initialization, and CRUD operations.
Class Hierarchy and State Management
The architecture follows a standard inheritance pattern where the PostgreSQL backend implements the abstract interface defined by Meltano's core state store.
The PostgreSQLStateStoreManager uses a psycopg.Connection with autocommit=True to handle transactions and ensure state persistence immediately upon execution.
Sources: src/meltano_state_backend_postgresql/backend.py:108-165, tests/test_integration.py:44-60
Data Model and Schema
State information is stored in a single table, defaulting to the name state. The schema is designed to hold both partial and completed state as serialized JSON strings, allowing for compatibility with various Meltano extractor and loader formats.
Table Schema Definition
| Column | Data Type | Constraints | Description |
|---|---|---|---|
state_id | VARCHAR(900) | PRIMARY KEY | Unique identifier for the Meltano job/state. |
partial_state | TEXT | Serialized JSON containing incremental progress. | |
completed_state | TEXT | Serialized JSON containing fully committed state. | |
updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last modification timestamp. |
Sources: src/meltano_state_backend_postgresql/backend.py:167-178, README.md:36-39
Table Initialization Flow
Upon initialization of the manager, the system automatically checks for the existence of the state table and creates it if missing.
Connection and Configuration
The backend is configured via a PostgreSQL connection URI or individual settings. These settings are registered as Meltano entry points.
Configuration Parameters
| Setting | Type | Default | Description |
|---|---|---|---|
host | String | localhost | Server hostname. |
port | Integer | 5432 | Server port. |
user | String | N/A | Authentication username (Required). |
password | String | N/A | Authentication password (Required). |
database | String | N/A | Target database name (Required). |
schema | String | Search Path | Target schema for the table. |
sslmode | String | prefer | SSL connection mode. |
table | String | state | Name of the storage table. |
Sources: src/meltano_state_backend_postgresql/backend.py:43-105, pyproject.toml:34-41
URI Parsing and Overrides
The function conninfo_from_params handles the merging of a base URI with explicit setting overrides. It prioritizes explicit keyword arguments over parameters found within the URI string.
Sources: src/meltano_state_backend_postgresql/backend.py:84-105, tests/test_conninfo.py:15-30
Concurrency Control (Locking Mechanism)
To prevent race conditions during concurrent Meltano runs, the backend implements a locking mechanism using PostgreSQL Advisory Locks.
Lock Logic
- Key Generation: The
state_idis hashed using MD5, and the first 8 bytes are converted into a signed 64-bit integer. - Acquisition: The system calls
pg_try_advisory_lock(key). If it fails, it retries for up to 30 seconds. - Release: The lock is released via
pg_advisory_unlock(key)once the operation (or context) finishes.
Implementation Details
State Retrieval and Serialization
When retrieving state (get), the backend fetches the TEXT columns and uses json.loads() to convert them back into Python dictionaries. If a column is NULL in the database, the backend returns an empty dictionary {} to maintain compatibility with Meltano's expected MeltanoState object structure.
Sources: src/meltano_state_backend_postgresql/backend.py:214-232, tests/test_state.py:46-75
Glob Pattern Matching
The get_state_ids method supports filtering using glob-style patterns (e.g., job_*). The backend converts these into SQL LIKE syntax:
*is converted to%?is converted to_
Sources: src/meltano_state_backend_postgresql/backend.py:270-292, tests/test_state.py:100-111
Summary
The meltano-state-backend-postgresql provides a production-ready state management solution for Meltano. By utilizing PostgreSQL features like JSON storage (via TEXT columns) and advisory locks, it ensures data integrity and supports high-concurrency environments while offering flexible configuration through standard URIs and Meltano settings.