Skip to content

Latest commit

 

History

History
282 lines (246 loc) · 20.4 KB

database_postgresql_v2.md

File metadata and controls

282 lines (246 loc) · 20.4 KB

database_postgresql_v2

Create, read, and update a Linode PostgreSQL database.

Minimum Required Fields

Field Type Required Description
api_token str Required The Linode account personal access token. It is necessary to run the module.
It can be exposed by the environment variable LINODE_API_TOKEN instead.
See details in Usage.

Examples

- name: Create a basic PostgreSQL database
  linode.cloud.database_postgresql_v2:
    label: my-db
    region: us-mia
    engine: postgresql/16
    type: g6-nanode-1
    allow_list:
      - 0.0.0.0/0
    state: present
- name: Create a PostgreSQL database with three nodes
  linode.cloud.database_postgresql_v2:
    label: my-db
    region: us-mia
    engine: postgresql/16
    type: g6-standard-1
    cluster_size: 3
    allow_list:
      - 0.0.0.0/0
    state: present
- name: Create a PostgreSQL database with an explicit maintenance schedule and engine configuration
  linode.cloud.database_postgresql_v2:
    label: my-db
    region: us-mia
    engine: postgresql/16
    type: g6-nanode-1
    engine_config:
        work_mem: 1023
        pg:
            autovacuum_analyze_scale_factor: 0.2
    updates:
        duration: 4
        frequency: weekly
        hour_of_day: 16
        day_of_week: 4
    state: present
- name: Create a PostgreSQL database forked from another database
  linode.cloud.database_postgresql_v2:
    label: my-db
    region: us-mia
    engine: postgresql/16
    type: g6-nanode-1
    fork:
        source: 12345
    state: present
- name: Create a PostgreSQL database attached to a VPC
  linode.cloud.database_postgresql_v2:
    label: my-db
    region: us-mia
    engine: postgresql/16
    type: g6-nanode-1
    private_network:
        vpc_id: 123
        subnet_id: 456
        public_access: true
    state: present
- name: Delete a PostgreSQL database
  linode.cloud.database_postgresql_v2:
    label: my-db
    state: absent

Parameters

Field Type Required Description
state str Required The desired state of the Managed Database. (Choices: resume, suspend, present, absent)
allow_list list Optional A list of IP addresses and CIDR ranges that can access the Managed Database. (Updatable)
cluster_size int Optional The number of Linode instance nodes deployed to the Managed Database. (Updatable)
engine str Optional The Managed Database engine in engine/version format. (Updatable)
engine_config (sub-options) dict Optional Various parameters used to configure this database's underlying engine. NOTE: If a configuration parameter is not current accepted by this field, configure using the linode.cloud.api_request module. (Updatable)
label str Optional The label of the Managed Database.
detach_private_network bool Optional If true, the Managed Database will be detached from its current private network when private_network is null. If the Managed Database is not currently attached to a private network or the private_network field is specified, this option has no effect. This is not necessary when switching between VPC subnets. (Default: False)
private_network (sub-options) dict Optional Restricts access to this database using a virtual private cloud (VPC) that you've configured in the region where the database will live. (Updatable)
region str Optional The region of the Managed Database.
type str Optional The Linode Instance type used by the Managed Database for its nodes. (Updatable)
fork (sub-options) dict Optional Information about a database to fork from.
updates (sub-options) dict Optional Configuration settings for automated patch update maintenance for the Managed Database. (Updatable)
wait_timeout int Optional The maximum number of seconds a poll operation can take before raising an error. (Default: 2700)

engine_config

Field Type Required Description
pg (sub-options) dict Optional The configuration for PostgreSQL. Contains settings and controls for database behavior.
pglookout (sub-options) dict Optional The configuration for pglookout. Contains controls for failover and replication settings.
pg_stat_monitor_enable bool Optional Enable the pg_stat_monitor extension. Enabling this extension will cause the cluster to be restarted. When this extension is enabled, pg_stat_statements results for utility commands are unreliable.
shared_buffers_percentage float Optional Percentage of total RAM that the database server uses for shared memory buffers. Valid range is 20-60 (float), which corresponds to 20% - 60%. This setting adjusts the shared_buffers configuration value.
work_mem int Optional Sets the maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files, in MB. Default is 1MB + 0.075% of total RAM (up to 32MB).

pg

Field Type Required Description
autovacuum_analyze_scale_factor float Optional Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE. The default is 0.2 (20% of table size).
autovacuum_analyze_threshold int Optional Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples.
autovacuum_max_workers int Optional Specifies the maximum number of autovacuum processes (other than the autovacuum launcher) that may be running at any one time. The default is three. This parameter can only be set at server start.
autovacuum_naptime int Optional Specifies the minimum delay between autovacuum runs on any given database. The delay is measured in seconds, and the default is one minute.
autovacuum_vacuum_cost_delay int Optional Specifies the cost delay value that will be used in automatic VACUUM operations. If -1 is specified, the regular vacuum_cost_delay value will be used. The default value is 20 milliseconds.
autovacuum_vacuum_cost_limit int Optional Specifies the cost limit value that will be used in automatic VACUUM operations. If -1 is specified (which is the default), the regular vacuum_cost_limit value will be used.
autovacuum_vacuum_scale_factor float Optional Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size).
autovacuum_vacuum_threshold int Optional Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples.
bgwriter_delay int Optional Specifies the delay between activity rounds for the background writer in milliseconds. Default is 200.
bgwriter_flush_after int Optional Whenever more than bgwriter_flush_after bytes have been written by the background writer, attempt to force the OS to issue these writes to the underlying storage. Specified in kilobytes, default is 512. Setting of 0 disables forced writeback.
bgwriter_lru_maxpages int Optional In each round, no more than this many buffers will be written by the background writer. Setting this to zero disables background writing. Default is 100.
bgwriter_lru_multiplier float Optional The average recent need for new buffers is multiplied by bgwriter_lru_multiplier to arrive at an estimate of the number that will be needed during the next round, (up to bgwriter_lru_maxpages). 1.0 represents a “just in time” policy of writing exactly the number of buffers predicted to be needed. Larger values provide some cushion against spikes in demand, while smaller values intentionally leave writes to be done by server processes. The default is 2.0.
deadlock_timeout int Optional This is the amount of time, in milliseconds, to wait on a lock before checking to see if there is a deadlock condition.
default_toast_compression str Optional Specifies the default TOAST compression method for values of compressible columns (the default is lz4). (Choices: pglz, lz4)
idle_in_transaction_session_timeout int Optional Time out sessions with open transactions after this number of milliseconds.
jit bool Optional Controls system-wide use of Just-in-Time Compilation (JIT).
max_files_per_process int Optional PostgreSQL maximum number of files that can be open per process.
max_locks_per_transaction int Optional PostgreSQL maximum locks per transaction.
max_logical_replication_workers int Optional PostgreSQL maximum logical replication workers (taken from the pool of max_parallel_workers).
max_parallel_workers int Optional Sets the maximum number of workers that the system can support for parallel queries.
max_parallel_workers_per_gather int Optional Sets the maximum number of workers that can be started by a single Gather or Gather Merge node.
max_pred_locks_per_transaction int Optional PostgreSQL maximum predicate locks per transaction.
max_replication_slots int Optional PostgreSQL maximum replication slots.
max_slot_wal_keep_size int Optional PostgreSQL maximum WAL size (MB) reserved for replication slots. Default is -1 (unlimited). wal_keep_size minimum WAL size setting takes precedence over this.
max_stack_depth int Optional Maximum depth of the stack in bytes.
max_standby_archive_delay int Optional Max standby archive delay in milliseconds.
max_standby_streaming_delay int Optional Max standby streaming delay in milliseconds.
max_wal_senders int Optional PostgreSQL maximum WAL senders.
max_worker_processes int Optional Sets the maximum number of background processes that the system can support.
password_encryption str Optional Chooses the algorithm for encrypting passwords. (Choices: md5, scram-sha-256)
pg_partman_bgw.interval int Optional Sets the time interval to run pg_partman's scheduled tasks.
pg_partman_bgw.role str Optional Controls which role to use for pg_partman's scheduled background tasks.
pg_stat_monitor.pgsm_enable_query_plan bool Optional Enables or disables query plan monitoring.
pg_stat_monitor.pgsm_max_buckets int Optional Sets the maximum number of buckets.
pg_stat_statements.track str Optional Controls which statements are counted. Specify 'top' to track top-level statements (those issued directly by clients), 'all' to also track nested statements (such as statements invoked within functions), or 'none' to disable statement statistics collection. The default value is 'top'. (Choices: top, all, none)
temp_file_limit int Optional PostgreSQL temporary file limit in KiB, -1 for unlimited.
timezone str Optional PostgreSQL service timezone.
track_activity_query_size int Optional Specifies the number of bytes reserved to track the currently executing command for each active session.
track_commit_timestamp str Optional Record commit time of transactions. (Choices: on, off)
track_functions str Optional Enables tracking of function call counts and time used. (Choices: none, pl, all)
track_io_timing str Optional Enables timing of database I/O calls. This parameter is off by default, because it will repeatedly query the operating system for the current time, which may cause significant overhead on some platforms.
wal_sender_timeout int Optional Terminate replication connections that are inactive for longer than this amount of time, in milliseconds. Setting this value to zero disables the timeout.
wal_writer_delay int Optional WAL flush interval in milliseconds. Note that setting this value to lower than the default 200ms may negatively impact performance.

pglookout

Field Type Required Description
max_failover_replication_time_lag int Optional Number of seconds of master unavailability before triggering database failover to standby.

private_network

Field Type Required Description
vpc_id int Required The ID of the virtual private cloud (VPC) to restrict access to this database using
subnet_id int Required The ID of the VPC subnet to restrict access to this database using.
public_access bool Optional Set to true to allow clients outside of the VPC to connect to the database using a public IP address. (Default: False)

fork

Field Type Required Description
restore_time str Optional The database timestamp from which it was restored.
source int Optional The instance id of the database that was forked from.

updates

Field Type Required Description
day_of_week int Required The day to perform maintenance. 1=Monday, 2=Tuesday, etc. (Choices: 1, 2, 3, 4, 5, 6, 7)
duration int Required The maximum maintenance window time in hours.
hour_of_day int Required The hour to begin maintenance based in UTC time.
frequency str Optional The frequency at which maintenance occurs. (Choices: weekly; Default: weekly)

Return Values

  • database - The database in JSON serialized form.

    • Sample Response: 
      {
  "allow_list": [
    "10.0.0.3/32"
  ],
  "cluster_size": 3,
  "created": "2025-02-10T20:10:20",
  "encrypted": true,
  "engine": "postgresql",
  "engine_config": {
    "pg": {
      "autovacuum_analyze_scale_factor": 0.2
    },
    "work_mem": 1023
  },
  "hosts": {
    "primary": "a225891-akamai-prod-1798333-default.g2a.akamaidb.net",
    "standby": "replica-a225891-akamai-prod-1798333-default.g2a.akamaidb.net"
  },
  "id": 12345,
  "label": "my-db",
  "members": {
    "172.104.207.136": "primary",
    "194.195.112.177": "failover",
    "45.79.126.72": "failover"
  },
  "oldest_restore_time": "2025-02-10T20:15:07",
  "platform": "rdbms-default",
  "port": 11876,
  "private_network": {
    "public_access": true,
    "subnet_id": 456,
    "vpc_id": 123
  },
  "region": "ap-west",
  "ssl_connection": true,
  "status": "active",
  "total_disk_size_gb": 30,
  "type": "g6-standard-1",
  "updated": "2025-02-10T20:25:55",
  "updates": {
    "day_of_week": 4,
    "duration": 4,
    "frequency": "weekly",
    "hour_of_day": 16,
    "pending": []
  },
  "used_disk_size_gb": 0,
  "version": "8.0.35"
}
    • See the Linode API response documentation for a list of returned fields

  • ssl_cert - The SSL CA certificate for an accessible Managed PostgreSQL Database.

  • credentials - The root username and password for an accessible Managed PostgreSQL Database.