Try SpotterAdd or remove PostgreSQL databases from a remote host.
| "added in version" 0.6 of ansible.builtin"
Authors: Ansible Core Team
stableinterface | supported by community
pipInstall with pip install ansible==2.9.24
Add or remove PostgreSQL databases from a remote host.
- name: Create a new database with name "acme"
postgresql_db:
name: acme
# Note: If a template different from "template0" is specified, encoding and locale settings must match those of the template.
- name: Create a new database with name "acme" and specific encoding and locale # settings.
postgresql_db:
name: acme
encoding: UTF-8
lc_collate: de_DE.UTF-8
lc_ctype: de_DE.UTF-8
template: template0
# Note: Default limit for the number of concurrent connections to a specific database is "-1", which means "unlimited"
- name: Create a new database with name "acme" which has a limit of 100 concurrent connections
postgresql_db:
name: acme
conn_limit: "100"
- name: Dump an existing database to a file
postgresql_db:
name: acme
state: dump
target: /tmp/acme.sql
- name: Dump an existing database to a file (with compression)
postgresql_db:
name: acme
state: dump
target: /tmp/acme.sql.gz
- name: Dump a single schema for an existing database
postgresql_db:
name: acme
state: dump
target: /tmp/acme.sql
target_opts: "-n public"
# Note: In the example below, if database foo exists and has another tablespace
# the tablespace will be changed to foo. Access to the database will be locked
# until the copying of database files is finished.
- name: Create a new database called foo in tablespace bar
postgresql_db:
name: foo
tablespace: bar
name:
aliases:
- db
description:
- Name of the database to add or remove
required: true
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect (if needed)
type: int
owner:
description:
- Name of the role to set as owner of the database
type: str
state:
choices:
- absent
- dump
- present
- restore
default: present
description:
- The database state.
- C(present) implies that the database should be created if necessary.
- C(absent) implies that the database should be removed if present.
- C(dump) requires a target definition to which the database will be backed up. (Added
in Ansible 2.4) Note that in some PostgreSQL versions of pg_dump, which is an embedded
PostgreSQL utility and is used by the module, returns rc 0 even when errors occurred
(e.g. the connection is forbidden by pg_hba.conf, etc.), so the module returns changed=True
but the dump has not actually been done. Please, be sure that your version of pg_dump
returns rc 1 in this case.
- C(restore) also requires a target definition from which the database will be restored.
(Added in Ansible 2.4)
- The format of the backup will be detected based on the target name.
- Supported compression formats for dump and restore include C(.bz2), C(.gz) and C(.xz)
- Supported formats for dump and restore include C(.sql) and C(.tar)
type: str
target:
description:
- File to back up or restore from.
- Used when I(state) is C(dump) or C(restore).
type: path
version_added: '2.4'
version_added_collection: ansible.builtin
ca_cert:
aliases:
- ssl_rootcert
description:
- 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.
type: str
encoding:
description:
- Encoding of the database
type: str
lc_ctype:
description:
- Character classification (LC_CTYPE) to use in the database (e.g. lower, upper, ...)
Must match LC_CTYPE of template database unless C(template0) is used as template.
type: str
ssl_mode:
choices:
- allow
- disable
- prefer
- require
- verify-ca
- verify-full
default: prefer
description:
- Determines whether or with what priority a secure SSL TCP/IP connection will be
negotiated with the server.
- See U(https://www.postgresql.org/docs/current/static/libpq-ssl.html) for more information
on the modes.
- Default of C(prefer) matches libpq default.
type: str
template:
description:
- Template used to create the database
type: str
conn_limit:
description:
- Specifies the database connection limit.
type: str
version_added: '2.8'
version_added_collection: ansible.builtin
lc_collate:
description:
- Collation order (LC_COLLATE) to use in the database. Must match collation order
of template database unless C(template0) is used as template.
type: str
login_host:
default: ''
description:
- Host running the database.
- If you have connection issues when using C(localhost), try to use C(127.0.0.1) instead.
type: str
login_user:
default: postgres
description:
- The username this module should use to establish its PostgreSQL session.
type: str
tablespace:
description:
- The tablespace to set for the database U(https://www.postgresql.org/docs/current/sql-alterdatabase.html).
- If you want to move the database back to the default tablespace, explicitly set
this to pg_default.
type: path
version_added: '2.9'
version_added_collection: ansible.builtin
target_opts:
description:
- Further arguments for pg_dump or pg_restore.
- Used when I(state) is C(dump) or C(restore).
type: str
version_added: '2.4'
version_added_collection: ansible.builtin
session_role:
description:
- 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.
type: str
version_added: '2.8'
version_added_collection: ansible.builtin
login_password:
default: ''
description:
- The password this module should use to establish its PostgreSQL session.
type: str
maintenance_db:
default: postgres
description:
- The value specifies the initial database (which is also called as maintenance DB)
that Ansible connects to.
type: str
version_added: '2.5'
version_added_collection: ansible.builtin
login_unix_socket:
default: ''
description:
- Path to a Unix domain socket for local connections.
type: str