Try SpotterCreate, drop, or modify a PostgreSQL table
Authors: Andrei Klychkov (@Andersson007)
Install with ansible-galaxy collection install community.general:==1.3.14
collections:
- name: community.general
version: 1.3.14Allows to create, drop, rename, truncate a table, or change some table attributes.
- name: Create tbl2 in the acme database with the DDL like tbl1 with testuser as an owner
community.general.postgresql_table:
db: acme
name: tbl2
like: tbl1
owner: testuser
- name: Create tbl2 in the acme database and tablespace ssd with the DDL like tbl1 including comments and indexes
community.general.postgresql_table:
db: acme
table: tbl2
like: tbl1
including: comments, indexes
tablespace: ssd
- name: Create test_table with several columns in ssd tablespace with fillfactor=10 and autovacuum_analyze_threshold=1
community.general.postgresql_table:
name: test_table
columns:
- id bigserial primary key
- num bigint
- stories text
tablespace: ssd
storage_params:
- fillfactor=10
- autovacuum_analyze_threshold=1
- name: Create an unlogged table in schema acme
community.general.postgresql_table:
name: acme.useless_data
columns: waste_id int
unlogged: true
- name: Rename table foo to bar
community.general.postgresql_table:
table: foo
rename: bar
- name: Rename table foo from schema acme to bar
community.general.postgresql_table:
name: acme.foo
rename: bar
- name: Set owner to someuser
community.general.postgresql_table:
name: foo
owner: someuser
- name: Change tablespace of foo table to new_tablespace and set owner to new_user
community.general.postgresql_table:
name: foo
tablespace: new_tablespace
owner: new_user
- name: Truncate table foo
community.general.postgresql_table:
name: foo
truncate: yes
- name: Drop table foo from schema acme
community.general.postgresql_table:
name: acme.foo
state: absent
- name: Drop table bar cascade
community.general.postgresql_table:
name: bar
state: absent
cascade: yes
db:
aliases:
- login_db
description:
- Name of database to connect and where the table will be created.
type: str
like:
description:
- Create a table like another table (with similar DDL). Mutually exclusive with I(columns),
I(rename), and I(truncate).
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
owner:
description:
- Set a table owner.
type: str
state:
choices:
- absent
- present
default: present
description:
- The table state. I(state=absent) is mutually exclusive with I(tablespace), I(owner),
I(unlogged), I(like), I(including), I(columns), I(truncate), I(storage_params) and,
I(rename).
type: str
table:
aliases:
- name
description:
- Table name.
required: true
type: str
rename:
description:
- New table name. Mutually exclusive with I(tablespace), I(owner), I(unlogged), I(like),
I(including), I(columns), I(truncate), and I(storage_params).
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 table (such as views). Used with I(state=absent)
only.
type: bool
columns:
description:
- Columns that are needed.
elements: str
type: list
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
truncate:
default: false
description:
- Truncate a table. Mutually exclusive with I(tablespace), I(owner), I(unlogged),
I(like), I(including), I(columns), I(rename), and I(storage_params).
type: bool
unlogged:
default: false
description:
- Create an unlogged table.
type: bool
including:
description:
- Keywords that are used with like parameter, may be DEFAULTS, CONSTRAINTS, INDEXES,
STORAGE, COMMENTS or ALL. Needs I(like) specified. Mutually exclusive with I(columns),
I(rename), and I(truncate).
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:
- Set a tablespace for the table.
required: false
type: str
trust_input:
default: true
description:
- If C(no), check whether values of parameters are potentially dangerous.
- It makes sense to use C(no) only when SQL injections 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, autovacuum_vacuum_treshold, etc. Mutually exclusive
with I(rename) and I(truncate).
elements: str
type: list
login_unix_socket:
description:
- Path to a Unix domain socket for local connections.
type: str
owner: description: Table owner. returned: always sample: postgres type: str queries: description: List of executed queries. returned: always sample: - CREATE TABLE "test_table" (id bigint) type: str state: description: Table state. returned: always sample: present type: str storage_params: description: Storage parameters. returned: always sample: - fillfactor=100 - autovacuum_analyze_threshold=1 type: list table: description: Name of a table. returned: always sample: foo type: str tablespace: description: Tablespace. returned: always sample: ssd_tablespace type: str