community / community.general / 1.3.11 / module / postgresql_idx Create or drop indexes from a PostgreSQL database Authors: Andrew Klychkov (@Andersson007), Thomas O'Donnell (@andytom)community.general.postgresql_idx (1.3.11) — module
Install with ansible-galaxy collection install community.general:==1.3.11
collections: - name: community.general version: 1.3.11
Create 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