Project Overview
Relevant source files
The following files were used as context for generating this wiki page:
The meltano-state-backend-postgresql project is a Meltano extension that provides a dedicated PostgreSQL state backend. Its primary purpose is to allow Meltano to store and manage incremental replication state (Singer state) within a PostgreSQL database rather than using the default system database or local filesystem. Sources: README.md:1-10, pyproject.toml:44-45
The core of the project is the PostgreSQLStateStoreManager class, which implements the Meltano StateStoreManager interface using the psycopg library. It handles the creation of state tables, persistence of state data, state retrieval, and concurrency control through PostgreSQL advisory locks. Sources: src/meltano_state_backend_postgresql/backend.py:126-150, src/meltano_state_backend_postgresql/backend.py:382-390
Architecture and Core Components
The system is designed as a plugin for Meltano, registered via entry points in the project configuration. It utilizes psycopg for high-performance, asynchronous-capable database interaction. Sources: pyproject.toml:44-45, src/meltano_state_backend_postgresql/backend.py:126
Class Structure
The primary logic resides in the PostgreSQLStateStoreManager, which inherits from Meltano's base StateStoreManager.
The diagram above shows the inheritance relationship between the Meltano base class and the PostgreSQL implementation.
Sources: src/meltano_state_backend_postgresql/backend.py:126-150
Connection Management
The backend handles connection string construction by merging a base URI with specific configuration overrides. It supports standard PostgreSQL URI formats and explicit parameters like sslmode. Sources: src/meltano_state_backend_postgresql/backend.py:91-123, README.md:30-45
Data Model and Storage
State is stored in a single table, which is automatically created if it does not exist. By default, this table is named state. Sources: src/meltano_state_backend_postgresql/backend.py:27, src/meltano_state_backend_postgresql/backend.py:186-196
Table Schema
The schema is designed to hold JSON-serialized state data for different job identifiers.
| Field | Type | Description |
|---|---|---|
state_id | VARCHAR(900) | Primary Key. The unique identifier for the Meltano job state. |
partial_state | TEXT | JSON string containing the current partial state. |
completed_state | TEXT | JSON string containing the last fully successful state. |
updated_at | TIMESTAMP | Timestamp of the last update, defaults to CURRENT_TIMESTAMP. |
Sources: src/meltano_state_backend_postgresql/backend.py:188-193
Operational Logic
State Operations Flow
The backend manages the lifecycle of state data through standard CRUD-like operations tailored for Meltano's state management needs.
This flow illustrates the internal steps for common state operations.
Sources: src/meltano_state_backend_postgresql/backend.py:202-230, src/meltano_state_backend_postgresql/backend.py:240-272, src/meltano_state_backend_postgresql/backend.py:382-410
Concurrency and Locking
To prevent race conditions when multiple processes attempt to update the same state ID, the backend uses PostgreSQL Advisory Locks.
- The
state_idstring is converted to a deterministic 64-bit integer using an MD5 hash. - The system calls
pg_try_advisory_lockto attempt acquisition. - It implements a retry mechanism that polls for the lock for up to 30 seconds.
Sources: src/meltano_state_backend_postgresql/backend.py:382-431, tests/test_state.py:157-190
Configuration Options
The backend is configured via Meltano settings or environment variables.
| Setting Name | Env Var | Description | Default |
|---|---|---|---|
state_backend.uri | MELTANO_STATE_BACKEND_URI | Full Postgres connection URI | N/A |
state_backend.postgresql.host | MELTANO_STATE_BACKEND_POSTGRESQL_HOST | Server hostname | localhost |
state_backend.postgresql.port | MELTANO_STATE_BACKEND_POSTGRESQL_PORT | Server port | 5432 |
state_backend.postgresql.user | MELTANO_STATE_BACKEND_POSTGRESQL_USER | Auth username | N/A |
state_backend.postgresql.password | MELTANO_STATE_BACKEND_POSTGRESQL_PASSWORD | Auth password | N/A |
state_backend.postgresql.schema | MELTANO_STATE_BACKEND_POSTGRESQL_SCHEMA | Target schema | Server search_path |
state_backend.postgresql.table | MELTANO_STATE_BACKEND_POSTGRESQL_TABLE | Target table name | state |
state_backend.postgresql.sslmode | MELTANO_STATE_BACKEND_POSTGRESQL_SSLMODE | SSL connection mode | prefer |
Sources: src/meltano_state_backend_postgresql/backend.py:35-89, README.md:30-65
Backwards Compatibility
The backend includes specific logic to support migration from the older systemdb schema. It can detect and interact with states previously managed by Meltano's internal SQLAlchemy-based migration service when the search_path is configured correctly. Sources: tests/test_backwards_compat.py:48-96
Summary
The meltano-state-backend-postgresql provides a robust, PostgreSQL-native implementation for Meltano state storage. By using psycopg and advisory locks, it ensures reliable state management for complex ELT pipelines while allowing users to leverage their existing PostgreSQL infrastructure for metadata persistence. Sources: README.md:1-20, src/meltano_state_backend_postgresql/backend.py:126-140