Skip to main content
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 five 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

​
Functions

​
postgres.sql

Execute arbitrary SQL against PostgreSQL. 
postgres.sql(
    sql,
    psql_user=None,
    psql_password=None,
    psql_host=None,
    psql_port=None,
    psql_database=None,
)

​
postgres.role

Add/remove PostgreSQL roles. 
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,
    psql_database=None,
)

​
postgres.database

Add/remove PostgreSQL databases. 
postgres.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,
    psql_database=None,
)

​
postgres.dump

Dump a PostgreSQL database into a .sql file. Requires pg_dump. 
postgres.dump(
    dest,
    psql_user=None,
    psql_password=None,
    psql_host=None,
    psql_port=None,
    psql_database=None,
)

​
postgres.load

Load .sql file into a database. 
postgres.load(
    src,
    psql_user=None,
    psql_password=None,
    psql_host=None,
    psql_port=None,
    psql_database=None,
)

​
Examples

from pyinfra.operations import postgres

# Create a PostgreSQL role
postgres.role(
    name="Create the pyinfra PostgreSQL role",
    role="pyinfra",
    password="somepassword",
    superuser=True,
    login=True,
    _sudo_user="postgres",
)

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

# Dump a database
postgres.dump(
    name="Dump the pyinfra_stuff database",
    dest="/tmp/pyinfra_stuff.dump",
    _sudo_user="postgres",
)

Build docs developers (and LLMs) love

Get started for freeTalk to us