Configuration Overview

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
• tests/test_meltano.py

The meltano-state-backend-postgresql extension allows Meltano to store job state information within a PostgreSQL database. This provides a robust, centralized alternative to local file-based state storage, facilitating state management across distributed environments.

The configuration architecture relies on a primary connection URI or individual connection parameters defined within the Meltano environment. The extension handles connection string parsing, schema isolation via search paths, and automated table creation.

Sources: README.md:1-12, src/meltano_state_backend_postgresql/backend.py:100-110

Connection Methods

The backend supports two primary methods for establishing a connection: a consolidated URI or discrete configuration settings. Both methods eventually resolve into a standard PostgreSQL connection info string used by the psycopg driver.

1. Connection URI

The most concise way to configure the backend is by setting state_backend.uri. This URI can include authentication details, host information, and specific PostgreSQL options like the search_path.

state_backend:
  uri: postgresql://<user>:<password>@<host>:<port>/<database>?options=-csearch_path%3D<schema>

Sources: README.md:32-37, src/meltano_state_backend_postgresql/backend.py:112-140

2. Individual Settings

Alternatively, developers can specify parameters individually. This is useful when sensitive values like passwords are provided via environment variables.

state_backend:
  uri: postgresql://localhost/my_database
  postgresql:
    host: localhost
    port: 5432
    user: my_user
    password: my_password
    database: my_database
    schema: my_schema
    sslmode: prefer

Sources: README.md:46-57, src/meltano_state_backend_postgresql/backend.py:44-93

Configuration Parameters

The following table details the available settings managed by the PostgreSQLStateStoreManager.

Setting Name	Env Var Equivalent	Kind	Description	Default
host	MELTANO_STATE_BACKEND_POSTGRESQL_HOST	String	Server hostname	localhost
port	MELTANO_STATE_BACKEND_POSTGRESQL_PORT	Integer	Server port	5432
database	MELTANO_STATE_BACKEND_POSTGRESQL_DATABASE	String	Target database	-
user	MELTANO_STATE_BACKEND_POSTGRESQL_USER	String	Authentication user	-
password	MELTANO_STATE_BACKEND_POSTGRESQL_PASSWORD	String	Authentication password	-
schema	MELTANO_STATE_BACKEND_POSTGRESQL_SCHEMA	String	Target schema	search_path
sslmode	MELTANO_STATE_BACKEND_POSTGRESQL_SSLMODE	String	SSL mode (prefer, require, etc.)	prefer
table	MELTANO_STATE_BACKEND_POSTGRESQL_TABLE	String	State storage table name	state

Sources: src/meltano_state_backend_postgresql/backend.py:44-98, pyproject.toml:31-38, tests/test_meltano.py:38-66

Logic and Data Flow

The PostgreSQLStateStoreManager initializes by merging the provided URI with any explicit overrides. It ensures that required parameters (user, password, dbname) are present before attempting a connection.

Initialization Flow

The following diagram illustrates how configuration is resolved during manager initialization:

The logic prioritizes explicit keyword arguments over values found in the URI string. For example, if a schema is provided as a kwarg, it overrides any search_path found in the URI options.

Sources: src/meltano_state_backend_postgresql/backend.py:112-140, src/meltano_state_backend_postgresql/backend.py:157-195, tests/test_conninfo.py:65-75

Schema and Table Identification

The manager uses a table_identifier to route SQL queries. If an explicit schema is provided, the identifier is qualified (e.g., "myschema"."state"). If omitted, the identifier remains unqualified (e.g., "state"), relying on the PostgreSQL connection's default search_path.

Sources: src/meltano_state_backend_postgresql/backend.py:204-210, tests/test_conninfo.py:34-45

Security Configuration

Security is managed primarily through sslmode and environment variable injection for sensitive credentials.

• SSL Modes: Supported values include disable, allow, prefer, require, verify-ca, and verify-full.
• Environment Variables: All settings are mapped to environment variables prefixed with MELTANO_STATE_BACKEND_POSTGRESQL_.
• Permissions: The database user requires CREATE TABLE, INSERT, UPDATE, DELETE, and SELECT privileges on the target schema.

Sources: README.md:65-88, src/meltano_state_backend_postgresql/backend.py:86-93, tests/test_meltano.py:38-66

Data Schema

The backend automatically manages a state table (default name: state) with the following structure:

Column	Type	Description
state_id	VARCHAR(900)	Primary Key. The unique ID for the Meltano job.
partial_state	TEXT	JSON-encoded partial state data.
completed_state	TEXT	JSON-encoded completed state data.
updated_at	TIMESTAMP	Timestamp of the last update.

Sources: src/meltano_state_backend_postgresql/backend.py:230-238, README.md:40-42

Summary

The configuration of the PostgreSQL state backend is designed for flexibility, supporting both quick-start URI strings and production-ready environment variable configurations. By delegating connection management to psycopg and providing automated table provisioning, the extension integrates seamlessly into standard Meltano workflows.

Sources: README.md:28-60, src/meltano_state_backend_postgresql/backend.py:36-40