Postgresql 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 topsql_password
: the password for the connecting userpsql_host
: the hostname of the server to connect topsql_port
: the port of the server to connect to
See example/postgresql.py for detailed example
Facts used in these operations: postgresql.PostgresqlDatabases, postgresql.PostgresqlRoles.
postgresql.database
¶
Add/remove PostgreSQL databases.
postgresql.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,
)
- 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:
postgresql.database(
name="Create the pyinfra_stuff database",
database="pyinfra_stuff",
owner="pyinfra",
encoding="UTF8",
sudo_user="postgres",
)
postgresql.dump
¶
Dump a PostgreSQL database into a .sql
file. Requires pg_dump
.
postgresql.dump(dest, database=None, psql_user=None, psql_password=None, psql_host=None, psql_port=None)
- dest: name of the file to dump the SQL to
- database: name of the database to dump
- psql_*: global module arguments, see above
Example:
postgresql.dump(
name="Dump the pyinfra_stuff database",
dest="/tmp/pyinfra_stuff.dump",
database="pyinfra_stuff",
sudo_user="postgres",
)
postgresql.load
¶
Load .sql
file into a database.
postgresql.load(src, database=None, psql_user=None, psql_password=None, psql_host=None, psql_port=None)
- src: the filename to read from
- database: name of the database to import into
- psql_*: global module arguments, see above
Example:
postgresql.load(
name="Import the pyinfra_stuff dump into pyinfra_stuff_copy",
src="/tmp/pyinfra_stuff.dump",
database="pyinfra_stuff_copy",
sudo_user="postgres",
)
postgresql.role
¶
Add/remove PostgreSQL roles.
postgresql.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,
)
- 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:
postgresql.role(
name="Create the pyinfra PostgreSQL role",
role="pyinfra",
password="somepassword",
superuser=True,
login=True,
sudo_user="postgres",
)
postgresql.sql
¶
Execute arbitrary SQL against PostgreSQL.
postgresql.sql(sql, database=None, psql_user=None, psql_password=None, psql_host=None, psql_port=None)
- sql: SQL command(s) to execute
- database: optional database to execute against
- psql_*: global module arguments, see above