Production Deployment

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• src/meltano_state_backend_postgresql/backend.py
• pyproject.toml
• fixtures/project/meltano.yml
• tests/test_conninfo.py

The meltano-state-backend-postgresql extension enables Meltano to use a PostgreSQL database as a centralized state store. In production environments, this allows for persistent, shared state across multiple execution environments, moving away from local filesystem state storage.

Deployment involves installing the package within the Meltano environment and configuring the connection parameters to a production-grade PostgreSQL instance. This includes managing authentication, schema isolation, and security settings like SSL/TLS.

Sources: README.md:1-12, src/meltano_state_backend_postgresql/backend.py:118-124

Installation and Environment Setup

In production, the package must be injected into the same Python environment where Meltano is running. The project supports Python 3.10 through 3.14.

Method	Command
uv	uv tool install --with git+https://github.com/meltano/meltano-state-backend-postgresql.git meltano
pipx	`pipx inject meltano git+https://github.com/meltano/meltano-state-backend-postgresql.git%60

Sources: README.md:14-30, pyproject.toml:13-21

Configuration Architecture

The backend can be configured using a unified connection URI or individual settings. The PostgreSQLStateStoreManager processes these inputs to generate a psycopg connection string.

Connection Parameters

The following settings are registered as Meltano settings and can be configured via meltano.yml or environment variables:

Setting	Environment Variable	Default	Description
host	MELTANO_STATE_BACKEND_POSTGRESQL_HOST	localhost	Server hostname
port	MELTANO_STATE_BACKEND_POSTGRESQL_PORT	5432	Server port
database	MELTANO_STATE_BACKEND_POSTGRESQL_DATABASE	-	Target database name
user	MELTANO_STATE_BACKEND_POSTGRESQL_USER	-	Authentication username
password	MELTANO_STATE_BACKEND_POSTGRESQL_PASSWORD	-	Authentication password
schema	MELTANO_STATE_BACKEND_POSTGRESQL_SCHEMA	search_path	Schema for state table
sslmode	MELTANO_STATE_BACKEND_POSTGRESQL_SSLMODE	prefer	SSL connection mode
table	MELTANO_STATE_BACKEND_POSTGRESQL_TABLE	state	Table name for storage

Sources: src/meltano_state_backend_postgresql/backend.py:38-100, README.md:46-72

Configuration Flow

The diagram below illustrates how configuration is resolved from the URI and explicit settings.

The system prioritizes explicit keyword arguments over parameters found within the URI string.

Sources: src/meltano_state_backend_postgresql/backend.py:103-146, tests/test_conninfo.py:73-82

Data Schema and Storage

Upon initialization, the backend automatically ensures the existence of the state table. The default table name is state.

Database Schema

The state table is defined with the following structure:

Column	Type	Description
state_id	VARCHAR(900)	Primary Key; unique identifier for the job state
partial_state	TEXT	JSON-encoded string of the partial job state
completed_state	TEXT	JSON-encoded string of the completed job state
updated_at	TIMESTAMP	Timestamp of the last state update (Default: CURRENT_TIMESTAMP)

Sources: src/meltano_state_backend_postgresql/backend.py:168-177, README.md:40-44

Security Considerations

For production deployments, security should be prioritized through the following mechanisms:

1. Credential Management: Sensitive values like password should be supplied via environment variables (MELTANO_STATE_BACKEND_POSTGRESQL_PASSWORD) rather than hardcoded in meltano.yml.
2. SSL/TLS: The sslmode should be set to require, verify-ca, or verify-full to ensure encrypted transit.
3. Permissions: The database user requires specific privileges on the target schema: CREATE TABLE, INSERT, UPDATE, DELETE, and SELECT.

Sources: README.md:74-88, src/meltano_state_backend_postgresql/backend.py:84-91

Concurrency and Locking

To prevent state corruption during concurrent executions of the same state_id, the backend implements a locking mechanism using PostgreSQL Advisory Locks.

The lock key is a deterministic 64-bit signed integer derived from an MD5 hash of the state_id.

Sources: src/meltano_state_backend_postgresql/backend.py:301-344

Summary

Production deployment of the PostgreSQL state backend provides a robust, thread-safe, and scalable way to manage Meltano state. By utilizing standard PostgreSQL features like advisory locks and schema-level isolation, it ensures state integrity across distributed execution environments.

Sources: README.md:1-12, src/meltano_state_backend_postgresql/backend.py:11-20