postgresql module¶

PostgresqlConnection ¶

Attributes:

Name	Type	Description
`pid`		The backend process ID.
`database`		The database the backend is connected to; None for background workers with no database.
`username`		The role the backend authenticated as; None for background workers.
`client_addr`		The client's IP address; None for local (Unix socket) connections.
`state`		The backend state (`active`, `idle`, `idle in transaction`, ...); None for background workers.

PostgresqlConnections ¶

The server's client connections, without the query text.

Commands:

psql -Ac 'SELECT pid, datname, usename, client_addr, state FROM pg_catalog.pg_stat_activity'

Returns:

list[PostgresqlConnection]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_connections.py.

PostgresqlDatabase ¶

Attributes:

Name	Type	Description
`dba`		The OID of the owning role (`pg_database.datdba`).
`encoding`		The character encoding name, e.g. `UTF8` (resolved from the numeric encoding through `pg_encoding_to_char`).
`collate`		The `LC_COLLATE` setting; None when unset.
`ctype`		The `LC_CTYPE` setting; None when unset.
`istemplate`		Whether the database is a template database.
`allowconn`		Whether connections are allowed (`datallowconn`).
`connlimit`		The connection limit; -1 means no limit.
`lastsysoid`		The last system OID (absent on PostgreSQL 12+).
`frozenxid`		The transaction-ID freeze horizon.
`minmxid`		The multixact-ID freeze horizon.
`tablespace`		The OID of the default tablespace (not its name).
`acl`		The raw ACL string; None when default privileges apply.
`owner`		The owning role's name (resolved via `pg_get_userbyid`).
`oid`		The database's OID; present on PostgreSQL 12+ (where `oid` became a regular `pg_database` column), None on older servers.

PostgresqlDatabases ¶

The cluster's databases and their metadata, keyed by database name.

Commands:

psql -Ac 'SELECT pg_catalog.pg_encoding_to_char(encoding), *, pg_catalog.pg_get_userbyid(datdba) AS owner FROM pg_catalog.pg_database'

Returns:

dict[str, PostgresqlDatabase]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_databases.py.

PostgresqlExtension ¶

Attributes:

Name	Type	Description
`name`		The extension name, e.g. `pg_stat_statements`.
`version`		The installed version string, e.g. `1.10`.
`schema`		The schema the extension's objects live in.

PostgresqlExtensions ¶

The extensions installed in a database, with versions and schemas.

Commands:

psql -Ac 'SELECT e.extname, e.extversion, n.nspname FROM pg_catalog.pg_extension e JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace ORDER BY e.extname'

Returns:

list[PostgresqlExtension]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_extensions.py.

PostgresqlIndex ¶

Attributes:

Name	Type	Description
`schema`		The schema the index lives in.
`name`		The index name.
`table`		The name of the table the index covers (unqualified).
`owner`		The owning role's name.
`tablespace`		The index's tablespace name; None when it uses the database default.
`unique`		Whether the index enforces uniqueness.
`method`		The access method, e.g. `btree`, `gin`, `gist`.

PostgresqlIndexes ¶

The user indexes in a database, with table, owner and access method.

Commands:

psql -Ac 'SELECT n.nspname AS schema, c.relname AS name, t.relname AS table_name, pg_catalog.pg_get_userbyid(c.relowner) AS owner, ts.spcname AS tablespace, i.indisunique, am.amname AS method FROM pg_catalog.pg_class c JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid JOIN pg_catalog.pg_class t ON t.oid = i.indrelid JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace JOIN pg_catalog.pg_am am ON am.oid = c.relam LEFT JOIN pg_catalog.pg_tablespace ts ON ts.oid = c.reltablespace WHERE n.nspname NOT LIKE '"'"'pg\_%'"'"' AND n.nspname <> '"'"'information_schema'"'"' ORDER BY n.nspname, c.relname'

Returns:

list[PostgresqlIndex]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_indexes.py.

PostgresqlPrivilege ¶

Attributes:

Name	Type	Description
`object_type`		The granted-on object kind: `database`, `schema`, `table`, `view`, `materialized_view`, `foreign_table`, `sequence` or `function`.
`schema`		The object's schema; None for databases and schemas (which are not schema-qualified).
`name`		The object's name (unqualified).
`grantee`		The role the privilege is granted to; `PUBLIC` for the implicit grant to every role.
`privilege`		The privilege name, e.g. `SELECT`, `USAGE`.
`grantable`		Whether the grant carries WITH GRANT OPTION.

PostgresqlPrivileges ¶

The object-level privilege grants visible from a database.

Commands:

psql -Ac 'SELECT obj.object_type, obj.schema_name, obj.object_name, COALESCE(pg_catalog.pg_get_userbyid(acl.grantee), '"'"'PUBLIC'"'"') AS grantee, acl.privilege_type, acl.is_grantable FROM (...) obj, LATERAL pg_catalog.aclexplode(obj.acl) acl ORDER BY obj.object_type, obj.schema_name, obj.object_name, grantee, acl.privilege_type'

Returns:

list[PostgresqlPrivilege]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_privileges.py.

PostgresqlPublication ¶

Attributes:

Name	Type	Description
`name`		The publication name.
`owner`		The owning role's name.
`all_tables`		Whether the publication covers ALL TABLES.
`tables`		The schema-qualified table names the publication covers, sorted; empty for an ALL TABLES publication (or one created FOR TABLE with no tables yet).

PostgresqlPublications ¶

The logical-replication publications in a database, with their tables.

Commands:

psql -Ac 'SELECT p.pubname, pg_catalog.pg_get_userbyid(p.pubowner) AS owner, p.puballtables, COALESCE(string_agg(t.schemaname || '"'"'.'"'"' || t.tablename, '"'"','"'"' ORDER BY t.schemaname, t.tablename), '"'"''"'"') AS tables FROM pg_catalog.pg_publication p LEFT JOIN pg_catalog.pg_publication_tables t ON t.pubname = p.pubname GROUP BY p.pubname, p.pubowner, p.puballtables ORDER BY p.pubname'

Returns:

list[PostgresqlPublication]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_publications.py.

PostgresqlReplication ¶

The streaming-replication standbys connected to this server.

Commands:

psql -Ac 'SELECT application_name, client_addr, state, sync_state, replay_lag FROM pg_catalog.pg_stat_replication'

Returns:

list[PostgresqlReplicationClient]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_replication.py.

PostgresqlReplicationClient ¶

Attributes:

Name	Type	Description
`application_name`		The standby's application name.
`client_addr`		The standby's IP address; None for a Unix-socket connection.
`state`		The WAL sender state (`streaming`, `catchup`, ...).
`sync_state`		The synchronization mode (`async`, `sync`, `potential`, `quorum`).
`replay_lag`		The interval between the last WAL flush locally and its replay on the standby, as text (e.g. `00:00:00.000512`); None when the standby has not reported a replay position.

PostgresqlRole ¶

Attributes:

Name	Type	Description
`super`		Whether the role is a superuser (`rolsuper`).
`inherit`		Whether the role inherits granted roles' privileges.
`createrole`		Whether the role may create roles.
`createdb`		Whether the role may create databases.
`canlogin`		Whether the role may log in (`rolcanlogin`).
`replication`		Whether the role may initiate replication.
`connlimit`		The role's connection limit; -1 means no limit.
`password`		The stored password hash marker (`********`); None when the role has no password. Never the password itself.
`validuntil`		The password expiry timestamp text; None when unset.
`bypassrls`		Whether the role bypasses row-level security.
`config`		The role's per-role configuration overrides; None when unset.
`oid`		The role's OID.

PostgresqlRoleMembership ¶

Attributes:

Name	Type	Description
`role`		The granted (group) role's name.
`member`		The member role's name.
`admin_option`		Whether the membership carries WITH ADMIN OPTION.

PostgresqlRoleMemberships ¶

The role-to-role membership grants in the cluster.

Commands:

psql -Ac 'SELECT r.rolname AS role, m.rolname AS member, am.admin_option FROM pg_catalog.pg_auth_members am JOIN pg_catalog.pg_roles r ON r.oid = am.roleid JOIN pg_catalog.pg_roles m ON m.oid = am.member ORDER BY r.rolname, m.rolname'

Returns:

list[PostgresqlRoleMembership]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_role_memberships.py.

PostgresqlRoles ¶

The cluster's roles and their attributes, keyed by role name.

Commands:

psql -Ac 'SELECT * FROM pg_catalog.pg_roles'

Returns:

dict[str, PostgresqlRole]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_roles.py.

PostgresqlSchema ¶

Attributes:

Name	Type	Description
`name`		The schema name.
`owner`		The owning role's name.

PostgresqlSchemas ¶

The non-system schemas in a database, with their owners.

Commands:

psql -Ac 'SELECT n.nspname AS name, pg_catalog.pg_get_userbyid(n.nspowner) AS owner FROM pg_catalog.pg_namespace n WHERE n.nspname NOT LIKE '"'"'pg\_%'"'"' AND n.nspname <> '"'"'information_schema'"'"' ORDER BY n.nspname'

Returns:

list[PostgresqlSchema]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_schemas.py.

PostgresqlSetting ¶

Attributes:

Name	Type	Description
`name`		The configuration parameter name, e.g. `max_connections`.
`setting`		The active value as text, in the parameter's `unit` (e.g. `262144` in 8kB blocks for shared_buffers).
`unit`		The value's unit (`8kB`, `ms`, ...); None when unitless.
`source`		Where the active value comes from (`default`, `configuration file`, `override`, ...).
`pending_restart`		Whether a changed value is waiting for a server restart to take effect.

PostgresqlSettings ¶

The server's active configuration parameters (GUCs).

Commands:

psql -Ac 'SELECT name, setting, unit, source, pending_restart FROM pg_catalog.pg_settings ORDER BY name'

Returns:

list[PostgresqlSetting]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_settings.py.

PostgresqlSubscription ¶

Attributes:

Name	Type	Description
`name`		The subscription name.
`owner`		The owning role's name.
`enabled`		Whether the subscription is enabled.
`publications`		The publication names on the publisher, in catalog order; empty when none are set.

PostgresqlSubscriptions ¶

The logical-replication subscriptions in a database.

Commands:

psql -Ac 'SELECT s.subname AS name, pg_catalog.pg_get_userbyid(s.subowner) AS owner, s.subenabled AS enabled, s.subpublications AS publications FROM pg_catalog.pg_subscription s ORDER BY s.subname'

Returns:

list[PostgresqlSubscription]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_subscriptions.py.

PostgresqlTable ¶

Attributes:

Name	Type	Description
`schema`		The schema the table lives in.
`name`		The table name.
`owner`		The owning role's name.
`tablespace`		The table's tablespace name; None when it uses the database default.
`rows`		The planner's row estimate (`reltuples`); -1 when the table has never been vacuumed or analyzed.

PostgresqlTables ¶

The user tables in a database, with owners and row estimates.

Commands:

psql -Ac 'SELECT n.nspname AS schema, c.relname AS name, pg_catalog.pg_get_userbyid(c.relowner) AS owner, ts.spcname AS tablespace, c.reltuples::bigint AS rows FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_catalog.pg_tablespace ts ON ts.oid = c.reltablespace WHERE c.relkind IN ('"'"'r'"'"', '"'"'p'"'"') AND n.nspname NOT LIKE '"'"'pg\_%'"'"' AND n.nspname <> '"'"'information_schema'"'"' ORDER BY n.nspname, c.relname'

Returns:

list[PostgresqlTable]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_tables.py.

PostgresqlTablespace ¶

Attributes:

Name	Type	Description
`oid`		The tablespace's OID (what `pg_database.dattablespace` stores -- the database operation resolves names through it).
`name`		The tablespace name.
`owner`		The owning role's name.
`location`		The filesystem directory; None for the built-in `pg_default`/`pg_global` tablespaces, whose location lives inside the data directory.

PostgresqlTablespaces ¶

The cluster's tablespaces and their filesystem locations.

Commands:

psql -Ac 'SELECT oid, spcname AS name, pg_catalog.pg_get_userbyid(spcowner) AS owner, pg_catalog.pg_tablespace_location(oid) AS location FROM pg_catalog.pg_tablespace ORDER BY spcname'

Returns:

list[PostgresqlTablespace]

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_postgresql_tablespaces.py.

database `async` ¶

database(
    ctx: OpContext,
    database: str,
    present: bool = True,
    owner: str | None = None,
    template: str | None = None,
    encoding: str | None = None,
    lc_collate: str | None = None,
    lc_ctype: str | None = None,
    tablespace: str | None = None,
    connection_limit: int | None = None,
    allow_connections: bool | None = None,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Add, alter or remove a PostgreSQL database.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`database`	`str`	Name of the database to manage.	required
`present`	`bool`	Whether the database should exist. Defaults to True.	`True`
`owner`	`str \| None`	The role that should own the database; altered on an existing database when it differs. Defaults to None.	`None`
`template`	`str \| None`	Template database to create from; creation-only (immutable afterwards). Defaults to None.	`None`
`encoding`	`str \| None`	Character encoding, e.g. UTF8; creation-only. Defaults to None.	`None`
`lc_collate`	`str \| None`	Collation locale; creation-only. Defaults to None.	`None`
`lc_ctype`	`str \| None`	Character-classification locale; creation-only. Defaults to None.	`None`
`tablespace`	`str \| None`	Default tablespace name; moved (SET TABLESPACE) on an existing database when it differs. Defaults to None.	`None`
`connection_limit`	`int \| None`	Connection limit (-1 for unlimited); altered when it differs. Defaults to None.	`None`
`allow_connections`	`bool \| None`	Whether connections are allowed; altered (WITH ALLOW_CONNECTIONS) when it differs. Defaults to None.	`None`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to connect to for issuing the command. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL database commands.

Raises:

CommandNotFoundError, HostConnectionError, OperationError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_database.py.

dump `async` ¶

dump(
    ctx: OpContext,
    dest: str,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Dump a PostgreSQL database into a host-side .sql file.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`dest`	`str`	Path on the target host to write the dump to.	required
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to dump. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	The pg_dump command, always (the operation is stateless).

Raises:

None

Examples:

Examples can be found in examples/modules/postgresql/postgresql_dump.py.

extension `async` ¶

extension(
    ctx: OpContext,
    extension: str,
    schema: str | None = None,
    version: str | None = None,
    present: bool = True,
    cascade: bool = False,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Install, update or remove a PostgreSQL extension in a database.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`extension`	`str`	Name of the extension, e.g. `pg_stat_statements`.	required
`schema`	`str \| None`	Schema to install the extension's objects into; moved (SET SCHEMA) when it differs and the extension is relocatable. Defaults to None.	`None`
`version`	`str \| None`	Extension version to install or update to. Defaults to None.	`None`
`present`	`bool`	Whether the extension should be installed. Defaults to True.	`True`
`cascade`	`bool`	DROP EXTENSION ... CASCADE on removal. Defaults to False.	`False`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to manage the extension in. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL extension commands.

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_extension.py.

index `async` ¶

index(
    ctx: OpContext,
    index: str,
    table: str | None = None,
    columns: list[str] | None = None,
    unique: bool = False,
    method: str = "btree",
    tablespace: str | None = None,
    concurrent: bool = False,
    present: bool = True,
    cascade: bool = False,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Create or drop an index on a PostgreSQL table.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`index`	`str`	Name of the index (may be schema-qualified).	required
`table`	`str \| None`	Table to index; required when present is True. Defaults to None.	`None`
`columns`	`list[str] \| None`	Column names or expressions to index, in order; required when present is True. Defaults to None.	`None`
`unique`	`bool`	Create a UNIQUE index. Defaults to False.	`False`
`method`	`str`	Access method; one of btree, hash, gin, gist, spgist, brin (validated). Defaults to "btree".	`'btree'`
`tablespace`	`str \| None`	Tablespace for the index. Defaults to None.	`None`
`concurrent`	`bool`	CREATE/DROP INDEX CONCURRENTLY (no write lock on the table). Defaults to False.	`False`
`present`	`bool`	Whether the index should exist. Defaults to True.	`True`
`cascade`	`bool`	DROP INDEX ... CASCADE on removal. Defaults to False.	`False`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database holding the table. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL index commands.

Raises:

CommandNotFoundError, HostConnectionError, OperationError, ValueError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_index.py.

load `async` ¶

load(
    ctx: OpContext,
    src: str,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Load a host-side .sql file into a PostgreSQL database.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`src`	`str`	Path on the target host to read the SQL from.	required
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to load into. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	The psql load command, always (the operation is stateless).

Raises:

None

Examples:

Examples can be found in examples/modules/postgresql/postgresql_load.py.

membership `async` ¶

membership(
    ctx: OpContext,
    role: str,
    target_role: str,
    present: bool = True,
    admin_option: bool = False,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Grant or revoke a role's membership in another (group) role.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`role`	`str`	The member role.	required
`target_role`	`str`	The group role the member should belong to.	required
`present`	`bool`	Whether the membership should exist. Defaults to True.	`True`
`admin_option`	`bool`	Whether the membership carries WITH ADMIN OPTION (the member may grant it on); reconciled in both directions on an existing membership. Defaults to False.	`False`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to connect to for issuing the command. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL membership commands.

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_membership.py.

privilege `async` ¶

privilege(
    ctx: OpContext,
    privileges: str | list[str],
    role: str,
    object_type: str = "table",
    objects: list[str] | None = None,
    all_in_schema: str | None = None,
    present: bool = True,
    grant_option: bool = False,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Grant or revoke object privileges for a PostgreSQL role.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`privileges`	`str \| list[str]`	Privilege names (SELECT, INSERT, USAGE, ...) or the string "ALL" for the type's full set.	required
`role`	`str`	The grantee role (or PUBLIC).	required
`object_type`	`str`	One of table, schema, sequence, function, database. Defaults to "table".	`'table'`
`objects`	`list[str] \| None`	Object names, schema- qualified or defaulting to the public schema (databases and schemas are never qualified). Mutually exclusive with all_in_schema. Defaults to None.	`None`
`all_in_schema`	`str \| None`	Apply to ALL objects of the type IN SCHEMA this schema (not valid for databases). Defaults to None.	`None`
`present`	`bool`	Grant when True, revoke when False. Defaults to True.	`True`
`grant_option`	`bool`	WITH GRANT OPTION on a grant; on a revoke, withdraw only the grant option (REVOKE GRANT OPTION FOR), keeping the privilege. Defaults to False.	`False`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database whose objects are addressed. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL GRANT/REVOKE commands.

Raises:

CommandNotFoundError, HostConnectionError, ValueError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_privilege.py.

publication `async` ¶

publication(
    ctx: OpContext,
    publication: str,
    tables: list[str] | None = None,
    all_tables: bool = False,
    owner: str | None = None,
    present: bool = True,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Create, alter or drop a logical-replication publication.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`publication`	`str`	Name of the publication.	required
`tables`	`list[str] \| None`	Tables to publish, schema- qualified or defaulting to public; reconciled (SET TABLE) on an existing publication. Defaults to None.	`None`
`all_tables`	`bool`	Publish ALL TABLES instead of a table list; immutable after creation. Defaults to False.	`False`
`owner`	`str \| None`	The role that should own the publication; altered when it differs. Defaults to None.	`None`
`present`	`bool`	Whether the publication should exist. Defaults to True.	`True`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to publish from. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL publication commands.

Raises:

CommandNotFoundError, HostConnectionError, OperationError, ValueError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_publication.py.

role `async` ¶

role(
    ctx: OpContext,
    role: str,
    present: bool = True,
    login: bool = True,
    superuser: bool = False,
    inherit: bool = True,
    createdb: bool = False,
    createrole: bool = False,
    replication: bool = False,
    connection_limit: int | None = None,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Add, alter or remove a PostgreSQL role.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`role`	`str`	Name of the role to manage.	required
`present`	`bool`	Whether the role should exist. Defaults to True.	`True`
`login`	`bool`	Whether the role may log in; reconciled on an existing role. Defaults to True.	`True`
`superuser`	`bool`	Whether the role is a superuser; reconciled on an existing role. Defaults to False.	`False`
`inherit`	`bool`	Whether the role inherits granted roles' privileges; reconciled on an existing role. Defaults to True (PostgreSQL's own default).	`True`
`createdb`	`bool`	Whether the role may create databases; reconciled on an existing role. Defaults to False.	`False`
`createrole`	`bool`	Whether the role may create roles; reconciled on an existing role. Defaults to False.	`False`
`replication`	`bool`	Whether the role may initiate replication; reconciled on an existing role. Defaults to False.	`False`
`connection_limit`	`int \| None`	Connection limit (-1 for unlimited); altered when it differs, left alone when unset. Defaults to None.	`None`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to connect to for issuing the command. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL role commands.

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_role.py.

schema `async` ¶

schema(
    ctx: OpContext,
    schema: str,
    owner: str | None = None,
    present: bool = True,
    cascade: bool = False,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Create, re-own or drop a schema in a PostgreSQL database.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`schema`	`str`	Name of the schema to manage.	required
`owner`	`str \| None`	The role that should own the schema; altered on an existing schema when it differs. Defaults to None.	`None`
`present`	`bool`	Whether the schema should exist. Defaults to True.	`True`
`cascade`	`bool`	DROP SCHEMA ... CASCADE on removal (drops every object in the schema). Defaults to False.	`False`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to manage the schema in. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL schema commands.

Raises:

CommandNotFoundError, HostConnectionError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_schema.py.

setting `async` ¶

setting(
    ctx: OpContext,
    setting: str,
    value: str | None = None,
    present: bool = True,
    reload: bool = False,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Set or reset a PostgreSQL configuration parameter via ALTER SYSTEM.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`setting`	`str`	The configuration parameter name, e.g. `max_connections`.	required
`value`	`str \| None`	The desired value as text; required when present is True. Defaults to None.	`None`
`present`	`bool`	Set the value when True; RESET the parameter to its default when False. Defaults to True.	`True`
`reload`	`bool`	Follow a change with `SELECT pg_reload_conf()` so reloadable parameters take effect without a restart (restart-required parameters still need a server restart). Defaults to False.	`False`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to connect to for issuing the command. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL configuration commands.

Raises:

CommandNotFoundError, HostConnectionError, ValueError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_setting.py.

sql `async` ¶

sql(
    ctx: OpContext,
    sql: str,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Execute arbitrary SQL against PostgreSQL.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`sql`	`str`	SQL command(s) to execute.	required
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to run against. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	The SQL command, always (the operation is stateless).

Raises:

None

Examples:

Examples can be found in examples/modules/postgresql/postgresql_sql.py.

subscription `async` ¶

subscription(
    ctx: OpContext,
    subscription: str,
    connection: str | None = None,
    publications: list[str] | None = None,
    present: bool = True,
    enabled: bool = True,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Create, alter or drop a logical-replication subscription.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`subscription`	`str`	Name of the subscription.	required
`connection`	`str \| None`	libpq connection string to the publisher; required when creating. Never embed a password -- reference a host-side passfile or service definition instead. Not compared on an existing subscription (the fact deliberately hides conninfo). Defaults to None.	`None`
`publications`	`list[str] \| None`	Publication names on the publisher; required when creating, reconciled (SET PUBLICATION) on an existing subscription. Defaults to None.	`None`
`present`	`bool`	Whether the subscription should exist. Defaults to True.	`True`
`enabled`	`bool`	Whether the subscription should be enabled; reconciled (ENABLE/DISABLE) when it differs. Defaults to True.	`True`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to subscribe in. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL subscription commands.

Raises:

CommandNotFoundError, HostConnectionError, ValueError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_subscription.py.

tablespace `async` ¶

tablespace(
    ctx: OpContext,
    tablespace: str,
    location: str | None = None,
    owner: str | None = None,
    present: bool = True,
    psql_user: str | None = None,
    psql_host: str | None = None,
    psql_port: int | None = None,
    psql_database: str | None = None,
    **kwargs: Any,
) -> AsyncGenerator[Command]

Create, re-own or drop a PostgreSQL tablespace.

Parameters:

Name	Type	Description	Default
`ctx`	`OpContext`	Execution context for the operation.	required
`tablespace`	`str`	Name of the tablespace to manage.	required
`location`	`str \| None`	Absolute filesystem directory for the tablespace (must exist, be empty and be owned by the server user -- prepare it out of band); required when creating, immutable afterwards. Defaults to None.	`None`
`owner`	`str \| None`	The role that should own the tablespace; altered when it differs. Defaults to None.	`None`
`present`	`bool`	Whether the tablespace should exist. Defaults to True.	`True`
`psql_user`	`str \| None`	Role to connect as. Defaults to None.	`None`
`psql_host`	`str \| None`	Server host to connect to. Defaults to None.	`None`
`psql_port`	`int \| None`	Server port to connect to. Defaults to None.	`None`
`psql_database`	`str \| None`	Database to connect to for issuing the command. Defaults to None.	`None`
`**kwargs`	`Any`	Additional keyword arguments.	`{}`

Yields:

Name	Type	Description
`Command`	`AsyncGenerator[Command]`	Generated PostgreSQL tablespace commands.

Raises:

CommandNotFoundError, HostConnectionError, OperationError, ValueError

Examples:

Examples can be found in examples/modules/postgresql/postgresql_tablespace.py.

postgresql module¶

PostgresqlConnection ¶

PostgresqlConnections ¶

PostgresqlDatabase ¶

PostgresqlDatabases ¶

PostgresqlExtension ¶

PostgresqlExtensions ¶

PostgresqlIndex ¶

PostgresqlIndexes ¶

PostgresqlPrivilege ¶

PostgresqlPrivileges ¶

PostgresqlPublication ¶

PostgresqlPublications ¶

PostgresqlReplication ¶

PostgresqlReplicationClient ¶

PostgresqlRole ¶

PostgresqlRoleMembership ¶

PostgresqlRoleMemberships ¶

PostgresqlRoles ¶

PostgresqlSchema ¶

PostgresqlSchemas ¶

PostgresqlSetting ¶

PostgresqlSettings ¶

PostgresqlSubscription ¶

PostgresqlSubscriptions ¶

PostgresqlTable ¶

PostgresqlTables ¶

PostgresqlTablespace ¶

PostgresqlTablespaces ¶

database async ¶

dump async ¶

extension async ¶

index async ¶

load async ¶

membership async ¶

privilege async ¶

publication async ¶

role async ¶

schema async ¶

setting async ¶

sql async ¶

subscription async ¶

tablespace async ¶

database `async` ¶

dump `async` ¶

extension `async` ¶

index `async` ¶

load `async` ¶

membership `async` ¶

privilege `async` ¶

publication `async` ¶

role `async` ¶

schema `async` ¶

setting `async` ¶

sql `async` ¶

subscription `async` ¶

tablespace `async` ¶