Specific options when creating or updating a PostgreSQL service

You can find all the specific options for PostgreSQL by using the CLI help as such:

exo dbaas create --help-pg
  --pg-admin-password         custom password for admin user
  --pg-admin-username         custom username for admin user
  --pg-backup-schedule        automated backup schedule (format: HH:MM)
  --pg-bouncer-settings       PgBouncer configuration settings (JSON format)
  --pg-fork-from              name of a Database Service to fork from
  --pg-ip-filter              allow incoming connections from CIDR address block
  --pg-lookout-settings       pglookout configuration settings (JSON format)
  --pg-recovery-backup-time   the timestamp of the backup to restore when forking from a Database Service
  --pg-settings               PostgreSQL configuration settings (JSON format)
  --pg-version                PostgreSQL major version

Restricting connections from the Internet

By default, Exoscale DBaaS are accessible from 0.0.0.0/0 which is the whole Internet. Data does not transmit in clear over the network as it is SSL encrypted by default and authenticated.

To restrict the incoming connection to your database service, you can add a filter allowing:

  • just one IP address
  • a network range
  • a combination of the above

To do this update your service or create it with the ip filter which is a comma separated list of CIDRs exo dbaas update -z de-fra-1 test-pg --pg-ip-filter=a.b.c.d/32,w,x,y,z/24

Managing JSON configuration settings

Here we will show the example with pg-settings but the same process is valid with pg-bouncer-settings or pg-lookout-settings by respectively using --settings pgbouncer or --settings pglookout.

You can list all settings available for the database service by using the command:

exo dbaas type show pg --settings pg
┼─────────────────────────────────────┼──────────┼─────────────────────────────────────────────────────────┼
│                 KEY                 │   TYPE   │                       DESCRIPTION                       │
┼─────────────────────────────────────┼──────────┼─────────────────────────────────────────────────────────┼
│ max_wal_senders                     │ integer  │ PostgreSQL maximum WAL senders                          │
│                                     │          │   * Minimum: 8 / Maximum: 64                            │
│ autovacuum_analyze_scale_factor     │ number   │ 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)                                             │
│                                     │          │   * Minimum: 0 / Maximum: 1                             │
│ temp_file_limit                     │ integer  │ PostgreSQL temporary file limit in KiB, -1 for          │
│                                     │          │ unlimited                                               │
│                                     │          │   * Minimum: -1 / Maximum: 2.147483647e+09              │
│                                     │          │   * Example: 5e+06                                      │
│ bgwriter_lru_maxpages               │ integer  │ 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.                                                    │
│                                     │          │   * Minimum: 0 / Maximum: 1.073741823e+09               │
│                                     │          │   * Example: 100                                        │
│ jit                                 │ boolean  │ Controls system-wide use of Just-in-Time                │
│                                     │          │ Compilation (JIT).                                      │
│                                     │          │   * Example: true                                       │
│ track_io_timing                     │ string   │ 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.                                      │
│                                     │          │   * Supported values:                                   │
│                                     │          │     - off                                               │
│                                     │          │     - on                                                │
│                                     │          │   * Example: off                                        │
│ autovacuum_vacuum_threshold         │ integer  │ Specifies the minimum number of updated or deleted      │
│                                     │          │ tuples needed to trigger a VACUUM in any one            │
│                                     │          │ table. The default is 50 tuples                         │
│                                     │          │   * Minimum: 0 / Maximum: 2.147483647e+09               │
│ track_functions                     │ string   │ Enables tracking of function call counts and time       │
│                                     │          │ used.                                                   │
│                                     │          │   * Supported values:                                   │
│                                     │          │     - all                                               │
│                                     │          │     - pl                                                │
│                                     │          │     - none                                              │
│ autovacuum_freeze_max_age           │ integer  │ Specifies the maximum age (in transactions) that a      │
│                                     │          │ table's pg_class.relfrozenxid field can attain          │
│                                     │          │ before a VACUUM operation is forced to prevent          │
│                                     │          │ transaction ID wraparound within the table. Note        │
│                                     │          │ that the system will launch autovacuum processes        │
│                                     │          │ to prevent wraparound even when autovacuum is           │
│                                     │          │ otherwise disabled. This parameter will cause the       │
│                                     │          │ server to be restarted.                                 │
│                                     │          │   * Minimum: 2e+08 / Maximum: 1.5e+09                   │
│                                     │          │   * Example: 2e+08                                      │
│ autovacuum_max_workers              │ integer  │ 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.                                                  │
│                                     │          │   * Minimum: 1 / Maximum: 20                            │
│ autovacuum_vacuum_cost_delay        │ integer  │ 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      │
│                                     │          │   * Minimum: -1 / Maximum: 100                          │
│ max_logical_replication_workers     │ integer  │ PostgreSQL maximum logical replication workers          │
│                                     │          │ (taken from the pool of max_parallel_workers)           │
│                                     │          │   * Minimum: 4 / Maximum: 64                            │
│ max_replication_slots               │ integer  │ PostgreSQL maximum replication slots                    │
│                                     │          │   * Minimum: 8 / Maximum: 64                            │
│ max_stack_depth                     │ integer  │ Maximum depth of the stack in bytes                     │
│                                     │          │   * Minimum: 2.097152e+06 / Maximum: 6.291456e+06       │
│ max_standby_archive_delay           │ integer  │ Max standby archive delay in milliseconds               │
│                                     │          │   * Minimum: 1 / Maximum: 4.32e+07                      │
│ autovacuum_analyze_threshold        │ integer  │ Specifies the minimum number of inserted, updated       │
│                                     │          │ or deleted tuples needed to trigger an  ANALYZE in      │
│                                     │          │ any one table. The default is 50 tuples.                │
│                                     │          │   * Minimum: 0 / Maximum: 2.147483647e+09               │
│ pg_partman_bgw.interval             │ integer  │ Sets the time interval to run pg_partman's              │
│                                     │          │ scheduled tasks                                         │
│                                     │          │   * Minimum: 3600 / Maximum: 604800                     │
│                                     │          │   * Example: 3600                                       │
│ wal_writer_delay                    │ integer  │ WAL flush interval in milliseconds. Note that           │
│                                     │          │ setting this value to lower than the default 200ms      │
│                                     │          │ may negatively impact performance                       │
│                                     │          │   * Minimum: 10 / Maximum: 200                          │
│                                     │          │   * Example: 50                                         │
│ max_parallel_workers                │ integer  │ Sets the maximum number of workers that the system      │
│                                     │          │ can support for parallel queries                        │
│                                     │          │   * Minimum: 0 / Maximum: 96                            │
│ max_pred_locks_per_transaction      │ integer  │ PostgreSQL maximum predicate locks per transaction      │
│                                     │          │   * Minimum: 64 / Maximum: 640                          │
│ bgwriter_lru_multiplier             │ number   │ 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.        │
│                                     │          │   * Minimum: 0 / Maximum: 10                            │
│                                     │          │   * Example: 2                                          │
│ autovacuum_vacuum_scale_factor      │ number   │ 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)                                             │
│                                     │          │   * Minimum: 0 / Maximum: 1                             │
│ track_commit_timestamp              │ string   │ Record commit time of transactions.                     │
│                                     │          │   * Supported values:                                   │
│                                     │          │     - off                                               │
│                                     │          │     - on                                                │
│                                     │          │   * Example: off                                        │
│ max_worker_processes                │ integer  │ Sets the maximum number of background processes         │
│                                     │          │ that the system can support                             │
│                                     │          │   * Minimum: 8 / Maximum: 96                            │
│ bgwriter_delay                      │ integer  │ Specifies the delay between activity rounds for         │
│                                     │          │ the background writer in milliseconds. Default is       │
│                                     │          │ 200.                                                    │
│                                     │          │   * Minimum: 10 / Maximum: 10000                        │
│                                     │          │   * Example: 200                                        │
│ bgwriter_flush_after                │ integer  │ 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.                                              │
│                                     │          │   * Minimum: 0 / Maximum: 2048                          │
│                                     │          │   * Example: 512                                        │
│ deadlock_timeout                    │ integer  │ This is the amount of time, in milliseconds, to         │
│                                     │          │ wait on a lock before checking to see if there is       │
│                                     │          │ a deadlock condition.                                   │
│                                     │          │   * Minimum: 500 / Maximum: 1.8e+06                     │
│                                     │          │   * Example: 1000                                       │
│ max_locks_per_transaction           │ integer  │ PostgreSQL maximum locks per transaction                │
│                                     │          │   * Minimum: 64 / Maximum: 6400                         │
│ wal_sender_timeout                  │ integer  │ Terminate replication connections that are              │
│                                     │          │ inactive for longer than this amount of time, in        │
│                                     │          │ milliseconds. Setting this value to zero disables       │
│                                     │          │ the timeout.                                            │
│                                     │          │   * Example: 60000                                      │
│ max_standby_streaming_delay         │ integer  │ Max standby streaming delay in milliseconds             │
│                                     │          │   * Minimum: 1 / Maximum: 4.32e+07                      │
│ track_activity_query_size           │ integer  │ Specifies the number of bytes reserved to track         │
│                                     │          │ the currently executing command for each active         │
│                                     │          │ session.                                                │
│                                     │          │   * Minimum: 1024 / Maximum: 10240                      │
│                                     │          │   * Example: 1024                                       │
│ autovacuum_vacuum_cost_limit        │ integer  │ 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.                   │
│                                     │          │   * Minimum: -1 / Maximum: 10000                        │
│ max_parallel_workers_per_gather     │ integer  │ Sets the maximum number of workers that can be          │
│                                     │          │ started by a single Gather or Gather Merge node         │
│                                     │          │   * Minimum: 0 / Maximum: 96                            │
│ idle_in_transaction_session_timeout │ integer  │ Time out sessions with open transactions after          │
│                                     │          │ this number of milliseconds                             │
│                                     │          │   * Minimum: 0 / Maximum: 6.048e+08                     │
│ log_line_prefix                     │ string   │ Choose from one of the available log-formats.           │
│                                     │          │ These can support popular log analyzers like            │
│                                     │          │ pgbadger, pganalyze etc.                                │
│                                     │          │   * Supported values:                                   │
│                                     │          │     - '%m [%p] %q[user=%u,db=%d,app=%a] '               │
│                                     │          │     - 'pid=%p,user=%u,db=%d,app=%a,client=%h '          │
│                                     │          │     - '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' │
│ log_error_verbosity                 │ string   │ Controls the amount of detail written in the            │
│                                     │          │ server log for each message that is logged.             │
│                                     │          │   * Supported values:                                   │
│                                     │          │     - TERSE                                             │
│                                     │          │     - DEFAULT                                           │
│                                     │          │     - VERBOSE                                           │
│ log_min_duration_statement          │ integer  │ Log statements that take more than this number of       │
│                                     │          │ milliseconds to run, -1 disables                        │
│                                     │          │   * Minimum: -1 / Maximum: 8.64e+07                     │
│ log_autovacuum_min_duration         │ integer  │ Causes each action executed by autovacuum to be         │
│                                     │          │ logged if it ran for at least the specified number      │
│                                     │          │ of milliseconds. Setting this to zero logs all          │
│                                     │          │ autovacuum actions. Minus-one (the default)             │
│                                     │          │ disables logging autovacuum actions.                    │
│                                     │          │   * Minimum: -1 / Maximum: 2.147483647e+09              │
│ timezone                            │ string   │ PostgreSQL service timezone                             │
│                                     │          │   * Example: Europe/Helsinki                            │
│ max_files_per_process               │ integer  │ PostgreSQL maximum number of files that can be          │
│                                     │          │ open per process                                        │
│                                     │          │   * Minimum: 1000 / Maximum: 4096                       │
│ pg_stat_statements.track            │ [string] │ 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.        │
│                                     │          │   * Supported values:                                   │
│                                     │          │     - all                                               │
│                                     │          │     - top                                               │
│                                     │          │     - none                                              │
│ autovacuum_naptime                  │ integer  │ Specifies the minimum delay between autovacuum          │
│                                     │          │ runs on any given database. The delay is measured       │
│                                     │          │ in seconds, and the default is one minute               │
│                                     │          │   * Minimum: 0 / Maximum: 86400                         │
│ pg_partman_bgw.role                 │ string   │ Controls which role to use for pg_partman's             │
│                                     │          │ scheduled background tasks.                             │
│                                     │          │   * Example: myrolename                                 │
│ max_prepared_transactions           │ integer  │ PostgreSQL maximum prepared transactions                │
│                                     │          │   * Minimum: 0 / Maximum: 10000                         │
┼─────────────────────────────────────┼──────────┼─────────────────────────────────────────────────────────┼

Update and display settings

To set a setting to another value than default you can update it using the syntax below:

exo dbaas update  test-pg -z de-fra-1 --pg-settings='{"deadlock_timeout": 1002}'
Updating Database Service "test-pg"...
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼
│   DATABASE SERVICE    │                                                                                                                                │
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼
│ Zone                  │ de-fra-1                                                                                                                       │
│ Name                  │ test-pg                                                                                                                        │
│ Type                  │ pg                                                                                                                             │
│ Plan                  │ startup-4                                                                                                                      │
│ Disk Size             │ 80 GiB                                                                                                                         │
│ State                 │ running                                                                                                                        │
│ Creation Date         │ 2021-10-21 08:42:40 +0000 UTC                                                                                                  │
│ Update Date           │ 2021-11-01 14:17:01 +0000 UTC                                                                                                  │
│ Nodes                 │ 1                                                                                                                              │
│ Node CPUs             │ 2                                                                                                                              │
│ Node Memory           │ 4.0 GiB                                                                                                                        │
│ Termination Protected │ true                                                                                                                           │
│ Maintenance           │ tuesday (10:10:10)                                                                                                             │
│ Version               │ 13.4                                                                                                                           │
│ Backup Schedule       │ 20:28                                                                                                                          │
│ URI                   │ postgres://avnadmin:xxxxx@test-pg-exoscale-08b0165e-ef03-47ec-926f-f01163d557ed.aivencloud.com:21699/defaultdb?sslmode=require │
│ IP Filter             │ 0.0.0.0/0                                                                                                                      │
│ Components            │                                                                                                                                │
│                       │   pg          test-pg-exoscale-08b0165e-ef03-47ec-926f-f01163d557ed.aivencloud.com:21699   route:dynamic   usage:primary       │
│                       │   pgbouncer   test-pg-exoscale-08b0165e-ef03-47ec-926f-f01163d557ed.aivencloud.com:21700   route:dynamic   usage:primary       │
│                       │                                                                                                                                │
│ Users                 │ avnadmin (primary)                                                                                                             │
┼───────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼

You can check the setting has been configured with the show command and the right setting namespace.

Here our deadlock timeout is a general pg namespace setting

exo dbaas show  test-pg -z de-fra-1 --settings=pg
{
  "deadlock_timeout": 1002
}

Using a JSON file and Bash

If you are using Bash as your shell you can also craft your settings as a separate JSON file and pass it as a parameter using the structure below where config.json is the name of your configuration file

exo dbaas update test-pg -z de-fra-1 --pg-settings "$(<config.json)"