Try SpotterCopy data between a file/program and a PostgreSQL table
Authors: Andrew Klychkov (@Andersson007)
Install with ansible-galaxy collection install community.general:==1.3.14
collections:
- name: community.general
version: 1.3.14Copy data between a file/program and a PostgreSQL table.
- name: Copy text TAB-separated data from file /tmp/data.txt to acme table
community.general.postgresql_copy:
copy_from: /tmp/data.txt
dst: acme
- name: Copy CSV (comma-separated) data from file /tmp/data.csv to columns id, name of table acme
community.general.postgresql_copy:
copy_from: /tmp/data.csv
dst: acme
columns: id,name
options:
format: csv
- name: >
Copy text vertical-bar-separated data from file /tmp/data.txt to bar table.
The NULL values are specified as N
community.general.postgresql_copy:
copy_from: /tmp/data.csv
dst: bar
options:
delimiter: '|'
null: 'N'
- name: Copy data from acme table to file /tmp/data.txt in text format, TAB-separated
community.general.postgresql_copy:
src: acme
copy_to: /tmp/data.txt
- name: Copy data from SELECT query to/tmp/data.csv in CSV format
community.general.postgresql_copy:
src: 'SELECT * FROM acme'
copy_to: /tmp/data.csv
options:
format: csv
- name: Copy CSV data from my_table to gzip
community.general.postgresql_copy:
src: my_table
copy_to: 'gzip > /tmp/data.csv.gz'
program: yes
options:
format: csv
- name: >
Copy data from columns id, name of table bar to /tmp/data.txt.
Output format is text, vertical-bar-separated, NULL as N
community.general.postgresql_copy:
src: bar
columns:
- id
- name
copy_to: /tmp/data.csv
options:
delimiter: '|'
null: 'N'
db:
aliases:
- login_db
description:
- Name of database to connect to.
type: str
dst:
aliases:
- destination
description:
- Copy data to I(dst=tablename) from I(copy_from=/path/to/data.file).
- Used with I(copy_from) only.
type: str
src:
aliases:
- source
description:
- Copy data from I(copy_from) to I(src=tablename).
- Used with I(copy_to) only.
type: str
port:
aliases:
- login_port
default: 5432
description:
- Database port to connect to.
type: int
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
columns:
aliases:
- column
description:
- List of column names for the src/dst table to COPY FROM/TO.
elements: str
type: list
copy_to:
aliases:
- to
description:
- Copy the contents of a table to a file.
- Can also copy the results of a SELECT query.
- Mutually exclusive with I(copy_from) and I(dst).
type: path
options:
description:
- Options of COPY command.
- See the full list of available options U(https://www.postgresql.org/docs/current/sql-copy.html).
type: dict
program:
default: false
description:
- Mark I(src)/I(dst) as a program. Data will be copied to/from a program.
- See block Examples and PROGRAM arg description U(https://www.postgresql.org/docs/current/sql-copy.html).
type: bool
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
copy_from:
aliases:
- from
description:
- Copy data from a file to a table (appending the data to whatever is in the table
already).
- Mutually exclusive with I(copy_to) and I(src).
type: path
login_host:
description:
- Host running the database.
type: str
login_user:
default: postgres
description:
- The username used to authenticate with.
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
login_unix_socket:
description:
- Path to a Unix domain socket for local connections.
type: str
dst: description: Data destination. returned: always sample: /tmp/data.csv type: str queries: description: List of executed queries. returned: always sample: - COPY test_table FROM '/tmp/data_file.txt' (FORMAT csv, DELIMITER ',', NULL 'NULL') type: str src: description: Data source. returned: always sample: mytable type: str