Skip to content

postgresql

Postgresql Database tasks

This module provides tasks for working with PostgreSQL databases.

database(database, present=True, owner=None, template=None, encoding=None, lc_collate=None, lc_ctype=None, tablespace=None, connection_limit=None, psql_user=None, psql_password=None, psql_host=None, psql_port=None)

Add/remove PostgreSQL databases.

  • 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 not attempt to change existing databases - it will either create or drop databases, but not alter them (if the db exists this operation will make no changes).

Example:

.. code:: python

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

dump(dest, database=None, psql_user=None, psql_password=None, psql_host=None, psql_port=None)

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

  • dest: name of the file to dump the SQL to
  • database: name of the database to dump
  • psql_*: global module arguments, see above

Example:

.. code:: python

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

load(src, database=None, psql_user=None, psql_password=None, psql_host=None, psql_port=None)

Load .sql file into a database.

  • src: the filename to read from
  • database: name of the database to import into
  • psql_*: global module arguments, see above

Example:

.. code:: python

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

role(role, present=True, password=None, login=True, superuser=False, inherit=False, createdb=False, createrole=False, replication=False, connection_limit=None, psql_user=None, psql_password=None, psql_host=None, psql_port=None)

Add/remove PostgreSQL roles.

  • 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:

.. code:: python

postgresql.role(
    name="Create the pyinfra PostgreSQL role",
    role="pyinfra",
    password="somepassword",
    superuser=True,
    login=True,
    sudo_user="postgres",
)

sql(sql, database=None, psql_user=None, psql_password=None, psql_host=None, psql_port=None)

Execute arbitrary SQL against PostgreSQL.

  • sql: SQL command(s) to execute
  • database: optional database to execute against
  • psql_*: global module arguments, see above