postgresql_db – Add or remove PostgreSQL databases from a remote host.

From Get docs
Ansible/docs/2.7/modules/postgresql db module


postgresql_db – Add or remove PostgreSQL databases from a remote host.

Synopsis

  • Add or remove PostgreSQL databases from a remote host.

Requirements

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

  • psycopg2

Parameters

Parameter Choices/Defaults Comments

encoding

-

Encoding of the database

lc_collate

-

Collation order (LC_COLLATE) to use in the database. Must match collation order of template database unless template0 is used as template.

lc_ctype

-

Character classification (LC_CTYPE) to use in the database (e.g. lower, upper, ...) Must match LC_CTYPE of template database unless template0 is used as template.

login_host

-

Host running the database

login_password

-

The password used to authenticate with

login_unix_socket

-

Path to a Unix domain socket for local connections

login_user

-

Default:

"postgres"

The username used to authenticate with

maintenance_db

-

added in 2.5

Default:

"postgres"

The value specifies the initial database (which is also called as maintenance DB) that Ansible connects to.

name

- / required

name of the database to add or remove


aliases: db

owner

-

Name of the role to set as owner of the database

port

-

Default:

5432

Database port to connect to.

ssl_mode

-

added in 2.3

  • disable
  • allow
  • 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.

ssl_rootcert

-

added in 2.3

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.

state

-

  • present

  • absent
  • dump
  • restore

The database state. present implies that the database should be created if necessary. absent implies that the database should be removed if present. dump requires a target definition to which the database will be backed up. (Added in 2.4) restore also requires a target definition from which the database will be restored. (Added in 2.4) The format of the backup will be detected based on the target name. Supported compression formats for dump and restore are: .bz2, .gz, and .xz Supported formats for dump and restore are: .sql and .tar

target

-

added in 2.4

File to back up or restore from. Used when state is "dump" or "restore"

target_opts

-

added in 2.4

Further arguments for pg_dump or pg_restore. Used when state is "dump" or "restore"

template

-

Template used to create the database



Notes

Note

  • The default authentication assumes that you are either logging in as or sudo’ing to the postgres account on the host.
  • 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 ssl_rootcert parameter requires at least Postgres version 8.4 and psycopg2 version 2.4.3.


Examples

# Create a new database with name "acme"
- postgresql_db:
    name: acme

# Create a new database with name "acme" and specific encoding and locale
# settings. If a template different from "template0" is specified, encoding
# and locale settings must match those of the template.
- postgresql_db:
    name: acme
    encoding: UTF-8
    lc_collate: de_DE.UTF-8
    lc_ctype: de_DE.UTF-8
    template: template0

# Dump an existing database to a file
- postgresql_db:
    name: acme
    state: dump
    target: /tmp/acme.sql

# Dump an existing database to a file (with compression)
- postgresql_db:
    name: acme
    state: dump
    target: /tmp/acme.sql.gz

# Dump a single schema for an existing database
- postgresql_db:
    name: acme
    state: dump
    target: /tmp/acme.sql
    target_opts: "-n public"

Status

Authors

  • Ansible Core Team

Hint

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


© 2012–2018 Michael DeHaan
© 2018–2019 Red Hat, Inc.
Licensed under the GNU General Public License version 3.
https://docs.ansible.com/ansible/2.7/modules/postgresql_db_module.html