Try SpotterCreate or drop indexes from a PostgreSQL database
Authors: Andrew Klychkov (@Andersson007), Thomas O'Donnell (@andytom)
Install with ansible-galaxy collection install community.general:==1.3.14
collections:
- name: community.general
version: 1.3.14Create or drop indexes from a PostgreSQL database.
- name: Create btree index if not exists test_idx concurrently covering columns id and name of table products
community.general.postgresql_idx:
db: acme
table: products
columns: id,name
name: test_idx
- name: Create btree index test_idx concurrently with tablespace called ssd and storage parameter
community.general.postgresql_idx:
db: acme
table: products
columns:
- id
- name
idxname: test_idx
tablespace: ssd
storage_params:
- fillfactor=90
- name: Create gist index test_gist_idx concurrently on column geo_data of table map
community.general.postgresql_idx:
db: somedb
table: map
idxtype: gist
columns: geo_data
idxname: test_gist_idx
# Note: for the example below pg_trgm extension must be installed for gin_trgm_ops
- name: Create gin index gin0_idx not concurrently on column comment of table test
community.general.postgresql_idx:
idxname: gin0_idx
table: test
columns: comment gin_trgm_ops
concurrent: no
idxtype: gin
- name: Drop btree test_idx concurrently
community.general.postgresql_idx:
db: mydb
idxname: test_idx
state: absent
- name: Drop test_idx cascade
community.general.postgresql_idx:
db: mydb
idxname: test_idx
state: absent
cascade: yes
concurrent: no
- name: Create btree index test_idx concurrently on columns id,comment where column id > 1
community.general.postgresql_idx:
db: mydb
table: test
columns: id,comment
idxname: test_idx
cond: id > 1
- name: Create unique btree index if not exists test_unique_idx on column name of table products
community.general.postgresql_idx:
db: acme
table: products
columns: name
name: test_unique_idx
unique: yes
concurrent: no
db:
aliases:
- login_db
description:
- Name of database to connect to and where the index will be created/dropped.
type: str
cond:
description:
- Index conditions.
- Mutually exclusive with I(state=absent).
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
state:
choices:
- absent
- present
default: present
description:
- Index state.
- C(present) implies the index will be created if it does not exist.
- C(absent) implies the index will be dropped if it exists.
type: str
table:
description:
- Table to create index on it.
- Mutually exclusive with I(state=absent).
type: str
schema:
description:
- Name of a database schema where the index will be created.
type: str
unique:
default: false
description:
- Enable unique index.
- Only btree currently supports unique indexes.
type: bool
version_added: 0.2.0
version_added_collection: community.general
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
cascade:
default: false
description:
- Automatically drop objects that depend on the index, and in turn all objects that
depend on those objects.
- It used only with I(state=absent).
- Mutually exclusive with I(concurrent=yes)
type: bool
columns:
aliases:
- column
description:
- List of index columns that need to be covered by index.
- Mutually exclusive with I(state=absent).
elements: str
type: list
idxname:
aliases:
- name
description:
- Name of the index to create or drop.
required: true
type: str
idxtype:
aliases:
- type
description:
- Index type (like btree, gist, gin, etc.).
- Mutually exclusive with I(state=absent).
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
concurrent:
default: true
description:
- Enable or disable concurrent mode (CREATE / DROP INDEX CONCURRENTLY).
- Pay attention, if I(concurrent=no), the table will be locked (ACCESS EXCLUSIVE)
during the building process. For more information about the lock levels see U(https://www.postgresql.org/docs/current/explicit-locking.html).
- If the building process was interrupted for any reason when I(cuncurrent=yes), the
index becomes invalid. In this case it should be dropped and created again.
- Mutually exclusive with I(cascade=yes).
type: bool
login_host:
description:
- Host running the database.
type: str
login_user:
default: postgres
description:
- The username used to authenticate with.
type: str
tablespace:
description:
- Set a tablespace for the index.
- Mutually exclusive with I(state=absent).
required: false
type: str
trust_input:
default: true
description:
- If C(no), check whether values of parameters I(idxname), I(session_role), I(schema),
I(table), I(columns), I(tablespace), I(storage_params), I(cond) 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
storage_params:
description:
- Storage parameters like fillfactor, vacuum_cleanup_index_scale_factor, etc.
- Mutually exclusive with I(state=absent).
elements: str
type: list
login_unix_socket:
description:
- Path to a Unix domain socket for local connections.
type: str
name: description: Index name. returned: always sample: foo_idx type: str query: description: Query that was tried to be executed. returned: always sample: CREATE INDEX CONCURRENTLY foo_idx ON test_table USING BTREE (id) type: str schema: description: Schema where index exists. returned: always sample: public type: str state: description: Index state. returned: always sample: present type: str storage_params: description: Index storage parameters. returned: always sample: - fillfactor=90 type: list tablespace: description: Tablespace where index exists. returned: always sample: ssd type: str valid: description: Index validity. returned: always sample: true type: bool