Try SpotterAdd or remove PostgreSQL databases from a remote host.
Authors: Ansible Core Team
Install with ansible-galaxy collection install community.general:==1.3.14
collections:
- name: community.general
version: 1.3.14Add or remove PostgreSQL databases from a remote host.
- name: Create a new database with name "acme"
community.general.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.
community.general.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
community.general.postgresql_db:
name: acme
conn_limit: "100"
- name: Dump an existing database to a file
community.general.postgresql_db:
name: acme
state: dump
target: /tmp/acme.sql
- name: Dump an existing database to a file excluding the test table
community.general.postgresql_db:
name: acme
state: dump
target: /tmp/acme.sql
dump_extra_args: --exclude-table=test
- name: Dump an existing database to a file (with compression)
community.general.postgresql_db:
name: acme
state: dump
target: /tmp/acme.sql.gz
- name: Dump a single schema for an existing database
community.general.postgresql_db:
name: acme
state: dump
target: /tmp/acme.sql
target_opts: "-n public"
- name: Dump only table1 and table2 from the acme database
community.general.postgresql_db:
name: acme
state: dump
target: /tmp/table1_table2.sql
target_opts: "-t table1 -t table2"
# 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
community.general.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(.pgc), C(.bz2), C(.gz)
and C(.xz)
- Supported formats for dump and restore include C(.sql) and C(.tar)
- 'Restore program is selected by target file format: C(.tar) and C(.pgc) are handled
by pg_restore, other with pgsql.'
type: str
target:
description:
- File to back up or restore from.
- Used when I(state) is C(dump) or C(restore).
type: path
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
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:
description:
- Host running the database.
type: str
login_user:
default: postgres
description:
- The username used to authenticate with.
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
target_opts:
description:
- Additional arguments for pg_dump or restore program (pg_restore or psql, depending
on target's format).
- Used when I(state) is C(dump) or C(restore).
type: str
trust_input:
default: true
description:
- If C(no), check whether values of parameters I(owner), I(conn_limit), I(encoding),
I(db), I(template), I(tablespace), I(session_role) are potentially dangerous.
- It makes sense to use C(no) only when SQL injections via the parameters are possible.
type: bool
version_added: 0.2.0
version_added_collection: community.general
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
login_password:
description:
- The password used to authenticate with.
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
dump_extra_args:
description:
- Provides additional arguments when I(state) is C(dump).
- Cannot be used with dump-file-format-related arguments like ``--format=d``.
type: str
version_added: 0.2.0
version_added_collection: community.general
login_unix_socket:
description:
- Path to a Unix domain socket for local connections.
type: str
executed_commands: description: List of commands which tried to run. returned: always sample: - CREATE DATABASE acme type: list version_added: 0.2.0 version_added_collection: community.general