Documentation

postgresql_schema – Add or remove PostgreSQL schema

New in version 2.3.

Synopsis

  • Add or remove PostgreSQL schema.

Requirements

The below requirements are needed on the host that executes this module.

  • psycopg2

Parameters

Parameter Choices/Defaults Comments
ca_cert
string
added in 2.8
Specifies the name of a file containing SSL certificate authority (CA) certificate(s).
If the file exists, the server's certificate will be verified to be signed by one of these authorities.

aliases: ssl_rootcert
cascade_drop
boolean
added in 2.8
    Choices:
  • no ←
  • yes
Drop schema with CASCADE to remove child objects.
database
string
Default:
"postgres"
Name of the database to connect to and add or remove the schema.

aliases: db, login_db
login_host
string
Host running the database.
login_password
string
The password used to authenticate with.
login_unix_socket
string
Path to a Unix domain socket for local connections.
login_user
string
Default:
"postgres"
The username used to authenticate with.
name
string / required
Name of the schema to add or remove.

aliases: schema
owner
string
Name of the role to set as owner of the schema.
port
integer
Default:
5432
Database port to connect to.

aliases: login_port
session_role
string
added in 2.8
Switch to session_role after connecting.
The specified session_role must be a role that the current login_user is a member of.
Permissions checking for SQL commands is carried out as though the session_role were the one that had logged in originally.
ssl_mode
string
added in 2.8
    Choices:
  • allow
  • disable
  • prefer ←
  • require
  • verify-ca
  • verify-full
Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server.
See https://www.postgresql.org/docs/current/static/libpq-ssl.html for more information on the modes.
Default of prefer matches libpq default.
state
string
    Choices:
  • absent
  • present ←
The schema state.

Notes

Note

  • The default authentication assumes that you are either logging in as or sudo’ing to the postgres account on the host.
  • To avoid “Peer authentication failed for user postgres” error, use postgres user as a become_user.
  • This module uses psycopg2, a Python PostgreSQL database adapter. You must ensure that psycopg2 is installed on the host before using this module.
  • If the remote host is the PostgreSQL server (which is the default case), then PostgreSQL must also be installed on the remote host.
  • For Ubuntu-based systems, install the postgresql, libpq-dev, and python-psycopg2 packages on the remote host before using this module.
  • The ca_cert parameter requires at least Postgres version 8.4 and psycopg2 version 2.4.3.

Examples

- name: Create a new schema with name acme in test database
  postgresql_schema:
    db: test
    name: acme

- name: Create a new schema acme with a user bob who will own it
  postgresql_schema:
    name: acme
    owner: bob

- name: Drop schema "acme" with cascade
  postgresql_schema:
    name: acme
    ensure: absent
    cascade_drop: yes

Return Values

Common return values are documented here, the following are the fields unique to this module:

Key Returned Description
queries
list
always
List of executed queries.

Sample:
['CREATE SCHEMA "acme"']
schema
string
success, changed
Name of the schema.

Sample:
acme


Status

Authors

Hint

If you notice any issues in this documentation you can edit this document to improve it.