ansible / ansible.builtin / v2.8.11 / module / postgresql_idx Create or drop indexes from a PostgreSQL database | "added in version" 2.8 of ansible.builtin" Authors: Andrew Klychkov (@Andersson007) preview | supported by communityansible.builtin.postgresql_idx (v2.8.11) — module
pip
Install with pip install ansible==2.8.11
Create or drop indexes from a PostgreSQL database.
For more information see U(https://www.postgresql.org/docs/current/sql-createindex.html), U(https://www.postgresql.org/docs/current/sql-dropindex.html).
- 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
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. - I(state=present) implies the index will be created if it does not exist. - I(state=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). required: true type: str schema: description: - Name of a database schema where the index will be created. type: str 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 U(https://www.postgresql.org/docs/current/sql-dropindex.html). - 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). 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: 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: - Set a tablespace for the index. - Mutually exclusive with I(state=absent). required: false type: str 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: default: '' description: - The password this module should use to establish its PostgreSQL session. type: str storage_params: description: - Storage parameters like fillfactor, vacuum_cleanup_index_scale_factor, etc. - Mutually exclusive with I(state=absent). type: list login_unix_socket: default: '' 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