postgresql_sequence – Create, drop, or alter a PostgreSQL sequence
New in version 2.9.
Synopsis
- Allows to create, drop or change the definition of a sequence generator.
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 |
|
cache
integer
|
Cache specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default.
|
|
cascade
boolean
|
|
Automatically drop objects that depend on the sequence, and in turn all objects that depend on those objects.
Ignored if
state=present.
Only used with
state=absent.
|
cycle
boolean
|
|
The cycle option allows the sequence to wrap around when the
maxvalue or
minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively.
If
false (NO CYCLE) is specified, any calls to nextval after the sequence has reached its maximum value will return an error. False (NO CYCLE) is the default.
|
data_type
string
|
|
Specifies the data type of the sequence. Valid types are bigint, integer, and smallint. bigint is the default. The data type determines the default minimum and maximum values of the sequence. For more info see the documentation
https://www.postgresql.org/docs/current/sql-createsequence.html.
Supported from PostgreSQL 10.
|
db
string
|
Name of database to connect to and run queries against.
aliases: database, login_db |
|
increment
integer
|
Increment specifies which value is added to the current sequence value to create a new value.
A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1.
|
|
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.
|
maxvalue
integer
|
Maxvalue determines the maximum value for the sequence. The default for an ascending sequence is the maximum value of the data type. The default for a descending sequence is -1.
aliases: max |
|
minvalue
integer
|
Minvalue determines the minimum value a sequence can generate. The default for an ascending sequence is 1. The default for a descending sequence is the minimum value of the data type.
aliases: min |
|
newschema
string
|
The new schema for the
sequence. Will be used for moving a
sequence to another
schema.
Works only for existing sequences.
|
|
owner
string
|
Set the owner for the
sequence.
|
|
port
integer
|
Default:
5432
|
Database port to connect to.
aliases: login_port |
rename_to
string
|
The new name for the
sequence.
Works only for existing sequences.
|
|
schema
string
|
Default:
"public"
|
The schema of the
sequence. This is be used to create and relocate a
sequence in the given schema.
|
sequence
string /
required
|
The name of the sequence.
aliases: name |
|
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
|
|
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.
|
start
integer
|
Start allows the sequence to begin anywhere. The default starting value is
minvalue for ascending sequences and
maxvalue for descending ones.
|
|
state
string
|
|
The sequence state.
If
state=absent other options will be ignored except of
name and
schema.
|
Notes
Note
- If you do not pass db parameter, sequence will be created in the database named postgres.
- 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.
See Also
See also
- postgresql_table – Create, drop, or modify a PostgreSQL table
- The official documentation on the postgresql_table module.
- postgresql_owner – Change an owner of PostgreSQL database object
- The official documentation on the postgresql_owner module.
- postgresql_privs – Grant or revoke privileges on PostgreSQL database objects
- The official documentation on the postgresql_privs module.
- postgresql_tablespace – Add or remove PostgreSQL tablespaces from remote hosts
- The official documentation on the postgresql_tablespace module.
- CREATE SEQUENCE reference
- Complete reference of the CREATE SEQUENCE command documentation.
- ALTER SEQUENCE reference
- Complete reference of the ALTER SEQUENCE command documentation.
- DROP SEQUENCE reference
- Complete reference of the DROP SEQUENCE command documentation.
Examples
- name: Create an ascending bigint sequence called foobar in the default database postgresql_sequence: name: foobar - name: Create an ascending integer sequence called foobar, starting at 101 postgresql_sequence: name: foobar data_type: integer start: 101 - name: Create an descending sequence called foobar, starting at 101 and preallocated 10 sequence numbers in cache postgresql_sequence: name: foobar increment: -1 cache: 10 start: 101 - name: Create an ascending sequence called foobar, which cycle between 1 to 10 postgresql_sequence: name: foobar cycle: yes min: 1 max: 10 - name: Create an ascending bigint sequence called foobar in the default database with owner foobar postgresql_sequence: name: foobar owner: foobar - name: Rename an existing sequence named foo to bar postgresql_sequence: name: foo rename_to: bar - name: Change the schema of an existing sequence to foobar postgresql_sequence: name: foobar newschema: foobar - name: Change the owner of an existing sequence to foobar postgresql_sequence: name: foobar owner: foobar - name: Drop a sequence called foobar postgresql_sequence: name: foobar state: absent - name: Drop a sequence called foobar with cascade postgresql_sequence: name: foobar cascade: yes state: absent
Return Values
Common return values are documented here, the following are the fields unique to this module:
Key | Returned | Description |
---|---|---|
cycle
string
|
always |
Shows if the sequence cycle or not.
Sample:
NO
|
data_type
string
|
always |
Shows the current data type of the sequence.
Sample:
bigint
|
increment
integer
|
always |
The value of increment of the sequence. A positive value will make an ascending sequence, a negative one a descending sequence.
Sample:
-1
|
maxvalue
integer
|
always |
The value of maxvalue of the sequence.
Sample:
9223372036854775807
|
minvalue
integer
|
always |
The value of minvalue of the sequence.
Sample:
1
|
newname
string
|
on success |
Shows the new sequence name after rename.
Sample:
barfoo
|
newschema
string
|
on success |
Shows the new schema of the sequence after schema change.
Sample:
foobar
|
owner
string
|
always |
Shows the current owner of the sequence after the successful run of the task.
Sample:
postgres
|
queries
string
|
always |
List of queries that was tried to be executed.
Sample:
['CREATE SEQUENCE "foo"']
|
schema
string
|
always |
Name of the schema of the sequence
Sample:
foo
|
sequence
string
|
always |
Sequence name.
Sample:
foobar
|
start
integer
|
always |
The value of start of the sequence.
Sample:
12
|
state
string
|
always |
Sequence state at the end of execution.
Sample:
present
|
Status
- This module is not guaranteed to have a backwards compatible interface. [preview]
- This module is maintained by the Ansible Community. [community]
Authors
- Tobias Birkefeld (@tcraxs)
Hint
If you notice any issues in this documentation, you can edit this document to improve it.
© 2012–2018 Michael DeHaan
© 2018–2019 Red Hat, Inc.
Licensed under the GNU General Public License version 3.
https://docs.ansible.com/ansible/2.9/modules/postgresql_sequence_module.html