Skip to content

postgres Operations

The PostgreSQL modules manage PostgreSQL databases, users and privileges.

Requires the psql CLI executable on the target host(s).

All operations in this module take four optional arguments: + psql_user: the username to connect to postgresql to + psql_password: the password for the connecting user + psql_host: the hostname of the server to connect to + psql_port: the port of the server to connect to + psql_database: the database on the server to connect to

See example/postgresql.py for detailed example

Facts used in these operations: postgres.PostgresDatabases, postgres.PostgresRoles.

postgres.database

Add/remove PostgreSQL databases.

postgres.database(
         database: 'str',
         present=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,
         psql_user: 'str | None' = None,
         psql_password: 'str | None' = None,
         psql_host: 'str | None' = None,
         psql_port: 'int | None' = None,
         psql_database: 'str | None' = None,
         **kwargs,
    )
  • name: name of the database
  • present: whether the database should exist or not
  • owner: the PostgreSQL role that owns the database
  • template: name of the PostgreSQL template to use
  • encoding: encoding of the database
  • lc_collate: lc_collate of the database
  • lc_ctype: lc_ctype of the database
  • tablespace: the tablespace to use for the template
  • connection_limit: the connection limit to apply to the database
  • psql_*: global module arguments, see above

Updates: pyinfra will change existing databases - but some parameters are not changeable (template, encoding, lc_collate and lc_ctype).

Example:

postgresql.database(
    name="Create the pyinfra_stuff database",
    database="pyinfra_stuff",
    owner="pyinfra",
    encoding="UTF8",
    _sudo_user="postgres",
)

Global arguments

This operation also inherits all global arguments.

postgres.dump

Stateless operation

This operation will always execute commands and is not idempotent.

Dump a PostgreSQL database into a .sql file. Requires pg_dump.

postgres.dump(
         dest: 'str',
         psql_user: 'str | None' = None,
         psql_password: 'str | None' = None,
         psql_host: 'str | None' = None,
         psql_port: 'int | None' = None,
         psql_database: 'str | None' = None,
         **kwargs,
    )
  • dest: name of the file to dump the SQL to
  • psql_*: global module arguments, see above

Example:

postgresql.dump(
    name="Dump the pyinfra_stuff database",
    dest="/tmp/pyinfra_stuff.dump",
    sudo_user="postgres",
)

Global arguments

This operation also inherits all global arguments.

postgres.load

Stateless operation

This operation will always execute commands and is not idempotent.

Load .sql file into a database.

postgres.load(
         src: 'str',
         psql_user: 'str | None' = None,
         psql_password: 'str | None' = None,
         psql_host: 'str | None' = None,
         psql_port: 'int | None' = None,
         psql_database: 'str | None' = None,
         **kwargs,
    )
  • src: the filename to read from
  • psql_*: global module arguments, see above

Example:

postgresql.load(
    name="Import the pyinfra_stuff dump into pyinfra_stuff_copy",
    src="/tmp/pyinfra_stuff.dump",
    sudo_user="postgres",
)

Global arguments

This operation also inherits all global arguments.

postgres.role

Add/remove PostgreSQL roles.

postgres.role(
         role: 'str',
         present: 'bool' = True,
         password: 'str | None' = None,
         login: 'bool' = True,
         superuser: 'bool' = False,
         inherit: 'bool' = False,
         createdb: 'bool' = False,
         createrole: 'bool' = False,
         replication: 'bool' = False,
         connection_limit: 'int | None' = None,
         psql_user: 'str | None' = None,
         psql_password: 'str | None' = None,
         psql_host: 'str | None' = None,
         psql_port: 'int | None' = None,
         psql_database: 'str | None' = None,
         **kwargs,
    )
  • role: name of the role
  • present: whether the role should be present or absent
  • password: the password for the role
  • login: whether the role can login
  • superuser: whether role will be a superuser
  • inherit: whether the role inherits from other roles
  • createdb: whether the role is allowed to create databases
  • createrole: whether the role is allowed to create new roles
  • replication: whether this role is allowed to replicate
  • connection_limit: the connection limit for the role
  • psql_*: global module arguments, see above

Updates: pyinfra will not attempt to change existing roles - it will either create or drop roles, but not alter them (if the role exists this operation will make no changes).

Example:

from pyinfra.operations import postgresql
postgresql.role(
    name="Create the pyinfra PostgreSQL role",
    role="pyinfra",
    password="somepassword",
    superuser=True,
    login=True,
    _sudo_user="postgres",
)

Global arguments

This operation also inherits all global arguments.

postgres.sql

Stateless operation

This operation will always execute commands and is not idempotent.

Execute arbitrary SQL against PostgreSQL.

postgres.sql(
         sql: 'str',
         psql_user: 'str | None' = None,
         psql_password: 'str | None' = None,
         psql_host: 'str | None' = None,
         psql_port: 'int | None' = None,
         psql_database: 'str | None' = None,
         **kwargs,
    )
  • sql: SQL command(s) to execute
  • psql_*: global module arguments, see above

Global arguments

This operation also inherits all global arguments.