Documentation

postgresql_idx – Create or drop indexes from a PostgreSQL database

New in version 2.8.

Requirements

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

  • psycopg2

Parameters

Parameter Choices/Defaults Comments
ca_cert
string
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
boolean
    Choices:
  • no ←
  • yes
Automatically drop objects that depend on the index, and in turn all objects that depend on those objects https://www.postgresql.org/docs/current/sql-dropindex.html.
It used only with state=absent.
Mutually exclusive with concurrent=yes
columns
list
List of index columns that need to be covered by index.
Mutually exclusive with state=absent.

aliases: column
concurrent
boolean
    Choices:
  • no
  • yes ←
Enable or disable concurrent mode (CREATE / DROP INDEX CONCURRENTLY).
Pay attention, if concurrent=no, the table will be locked (ACCESS EXCLUSIVE) during the building process. For more information about the lock levels see https://www.postgresql.org/docs/current/explicit-locking.html.
If the building process was interrupted for any reason when cuncurrent=yes, the index becomes invalid. In this case it should be dropped and created again.
Mutually exclusive with cascade=yes.
cond
string
Index conditions.
Mutually exclusive with state=absent.
db
string
Name of database to connect to and where the index will be created/dropped.

aliases: login_db
idxname
string / required
Name of the index to create or drop.

aliases: name
idxtype
string
Index type (like btree, gist, gin, etc.).
Mutually exclusive with state=absent.

aliases: type
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.
port
integer
Default:
5432
Database port to connect to.

aliases: login_port
schema
string
Name of a database schema where the index will be created.
session_role
string
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
    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 ←
Index state.
state=present implies the index will be created if it does not exist.
state=absent implies the index will be dropped if it exists.
storage_params
list
Storage parameters like fillfactor, vacuum_cleanup_index_scale_factor, etc.
Mutually exclusive with state=absent.
table
string / required
Table to create index on it.
Mutually exclusive with state=absent.
tablespace
string
Set a tablespace for the index.
Mutually exclusive with state=absent.

Notes

Note

  • The index building process can affect database performance.
  • To avoid table locks on production databases, use concurrent=yes (default behavior).
  • 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 btree index if not exists test_idx concurrently covering columns id and name of table products
  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
  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
  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
  postgresql_idx:
    idxname: gin0_idx
    table: test
    columns: comment gin_trgm_ops
    concurrent: no
    idxtype: gin

- name: Drop btree test_idx concurrently
  postgresql_idx:
    db: mydb
    idxname: test_idx
    state: absent

- name: Drop test_idx cascade
  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
  postgresql_idx:
    db: mydb
    table: test
    columns: id,comment
    idxname: test_idx
    cond: id > 1

Return Values

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

Key Returned Description
name
string
always
Index name.

Sample:
foo_idx
query
string
always
Query that was tried to be executed.

Sample:
CREATE INDEX CONCURRENTLY foo_idx ON test_table USING BTREE (id)
schema
string
always
Schema where index exists.

Sample:
public
state
string
always
Index state.

Sample:
present
storage_params
list
always
Index storage parameters.

Sample:
['fillfactor=90']
tablespace
string
always
Tablespace where index exists.

Sample:
ssd
valid
boolean
always
Index validity.

Sample:
True


Status

Authors

  • Andrew Klychkov (@Andersson007)

Hint

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