SGDbOps

The database operations CR represent an operation that is performed on a cluster.


Kind: SGDbOps

listKind: SGDbOpsList

plural: sgdbops

singular: sgdbops


Spec

Property Required Updatable Default Type Description
sgCluster string
The name of SGCluster on which the operation will be performed
op string

The kind of operation that will be performed on the SGCluster. Available operations are:

  • benchmark: run a benchmark on the specified SGCluster and report the results in the status.
  • vacuum: perform a vacuum operation on the specified SGCluster.
  • repack: run pg_repack command on the specified SGCluster.
  • majorVersionUpgrade: perform a major version upgrade of PostgreSQL using pg_upgrade command.
  • restart: perform a restart of the cluster.
  • minorVersionUpgrade: perform a minor version upgrade of PostgreSQL.
  • securityUpgrade: perform a security upgrade of the cluster.
runAt string

An ISO 8601 date, that holds UTC scheduled date of the operation execution.

If not specified or date it’s in the past it will be interpreted as ASAP.

timeout string

An ISO 8601 duration format PnDTnHnMn.nS, that specify a timeout after which the operation execution will not be performed.

If the operation can not be performed due to timeout expiration the condition Failed will have a status of True and and the reason will be TimedOut.

If not specified the operation will never fail for timeout expiration.

maxRetries integer

The maximum number of retries the operation is allowed to do after a failure.

If not specified this default to 1. Can not be greater than 10.

benchmark object
Configuration of the benchmark
vacuum object
Configuration of vacuum operation
repack object
Configuration of pg_repack command
majorVersionUpgrade object
Configuration of major version upgrade (see also pg_upgrade command)
restart object
Configuration of restart
minorVersionUpgrade object
Configuration of restart
securityUpgrade object
Configuration of security upgrade

Status

Property Required Updatable Default Type Description
conditions array

Possible conditions are:

  • Running: to indicate when the operation is actually running
  • Completed: to indicate when the operation is completed successfully
  • Failed: to indicate when the operation had failed
opRetries integer
The number of retries performed by the operation
opStarted string
The ISO 8601 timestamp of when the operation started running
benchmark object
The results of the benchmark
majorVersionUpgrade object
The results of a major version upgrade
restart object
The results of a restart
minorVersionUpgrade object
The results of a minor version upgrade
securityUpgrade object
The results of a security upgrade

Benchmark

Property Required Updatable Type Default Description
type string

The type of benchmark that will be performed on the SGCluster. Available benchamrks are:

  • pgbench: run pgnech on the specified SGCluster and report the results in the status.
pgbench object
Configuration of pgbench benchmark
connectionType string primary-service

Specify where the benchmark connection will be directed:

  • primary-service: Connect to the primary service
  • replicas-service: Connect to the replicas service

Pgbench

Property Required Updatable Type Default Description
databaseSize string
Size of the database to generate. This size is specified either in Mebibytes, Gibibytes or Tebibytes (multiples of 2^20, 2^30 or 2^40, respectively).
duration string
An ISO 8601 duration format PnDTnHnMn.nS, that specify the duration of the benchmark.
usePreparedStatements boolean false
Use extended query protocol with prepared statements. If not specified is considered false.
concurrentClients integer 1
Number of clients simulated, that is, number of concurrent database sessions. Default is 1.
threads integer 1
Number of worker threads within pgbench. Using more than one thread can be helpful on multi-CPU machines. Clients are distributed as evenly as possible among available threads. Default is 1.

Vacuum

Property Required Updatable Type Default Description
full boolean
If true selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn’t release the old copy until the operation is complete. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table. By default false
freeze boolean
If true selects aggressive “freezing” of tuples. Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to zero. Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified. By default false
analyze boolean
If true, updates statistics used by the planner to determine the most efficient way to execute a query. By default true
disablePageSkipping boolean
Normally, VACUUM will skip pages based on the visibility map. Pages where all tuples are known to be frozen can always be skipped, and those where all tuples are known to be visible to all transactions may be skipped except when performing an aggressive vacuum. Furthermore, except when performing an aggressive vacuum, some pages may be skipped in order to avoid waiting for other sessions to finish using them. This option disables all page-skipping behavior, and is intended to be used only when the contents of the visibility map are suspect, which should happen only if there is a hardware or software issue causing database corruption. By default false
databases array
List of database to vacuum or repack, don’t specify to select all databases

Vacuum database

Property Required Updatable Type Default Description
name string
the name of the database
full boolean
If true selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn’t release the old copy until the operation is complete. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table. By default false
freeze boolean
If true selects aggressive “freezing” of tuples. Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to zero. Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified. By default false
analyze boolean
If true, updates statistics used by the planner to determine the most efficient way to execute a query. By default true
disablePageSkipping boolean
Normally, VACUUM will skip pages based on the visibility map. Pages where all tuples are known to be frozen can always be skipped, and those where all tuples are known to be visible to all transactions may be skipped except when performing an aggressive vacuum. Furthermore, except when performing an aggressive vacuum, some pages may be skipped in order to avoid waiting for other sessions to finish using them. This option disables all page-skipping behavior, and is intended to be used only when the contents of the visibility map are suspect, which should happen only if there is a hardware or software issue causing database corruption. By default false

Repack

Property Required Updatable Type Default Description
noOrder boolean
If true do vacuum full instead of cluster. By default false.
waitTimeout string
If specified, an ISO 8601 duration format PnDTnHnMn.nS to set a timeout to cancel other backends on conflict.
noKillBackend boolean
If true don’t kill other backends when timed out. By default false.
noAnalyze boolean
If true don’t analyze at end. By default false.
excludeExtension boolean
If true don’t repack tables which belong to specific extension. By default false.
databases array
List of database to vacuum or repack, don’t specify to select all databases

Repack database

Property Required Updatable Type Default Description
name string
the name of the database
noOrder boolean
If true do vacuum full instead of cluster. By default false.
waitTimeout string
If specified, an ISO 8601 duration format PnDTnHnMn.nS to set a timeout to cancel other backends on conflict.
noKillBackend boolean
If true don’t kill other backends when timed out. By default false.
noAnalyze boolean
If true don’t analyze at end. By default false.
excludeExtension boolean
If true don’t repack tables which belong to specific extension. By default false.

Major Version Upgrade

Property Required Updatable Type Default Description
link boolean
If true use hard links instead of copying files to the new cluster. This option is mutually exclusive with clone. By default false.
clone boolean

If true use efficient file cloning (also known as “reflinks” on some systems) instead of copying files to the new cluster. This can result in near-instantaneous copying of the data files, giving the speed advantages of link while leaving the old cluster untouched. This option is mutually exclusive with link. By default false.

File cloning is only supported on some operating systems and file systems. If it is selected but not supported, the pg_upgrade run will error. At present, it is supported on Linux (kernel 4.5 or later) with Btrfs and XFS (on file systems created with reflink support), and on macOS with APFS.

check boolean
If true check clusters before changing any data. By default false.

Restart

Property Required Updatable Type Default Description
method string

The method used to perform the restart operation. Available methods are:

  • InPlace: the in-place method does not require more resources than those that are available. In case only an instance of the StackGres cluster is present this mean the service disruption will last longer so we encourage use the reduced impact restart and especially for a production environment.
  • ReducedImpact: this procedure is the same as the in-place method but require additional resources in order to spawn a new updated replica that will be removed when the procedure completes.

Minor Version Upgrade

Property Required Updatable Type Default Description
method string

The method used to perform the minor version upgrade operation. Available methods are:

  • InPlace: the in-place method does not require more resources than those that are available. In case only an instance of the StackGres cluster is present this mean the service disruption will last longer so we encourage use the reduced impact restart and especially for a production environment.
  • ReducedImpact: this procedure is the same as the in-place method but require additional resources in order to spawn a new updated replica that will be removed when the procedure completes.

Security Upgrade

Property Required Updatable Type Default Description
method string

The method used to perform the security upgrade operation. Available methods are:

  • InPlace: the in-place method does not require more resources than those that are available. In case only an instance of the StackGres cluster is present this mean the service disruption will last longer so we encourage use the reduced impact restart and especially for a production environment.
  • ReducedImpact: this procedure is the same as the in-place method but require additional resources in order to spawn a new updated replica that will be removed when the procedure completes.

Conditions

Property Required Updatable Type Default Description
type string
Type of deployment condition.
status string
Status of the condition, one of True, False, Unknown.
reason string
The reason for the condition’s last transition.
lastTransitionTime string
Last time the condition transitioned from one status to another.
message string
A human readable message indicating details about the transition.

Benchmark Status

Property Required Updatable Type Default Description
pgbench object
The results of the pgbench benchmark

Pgbench Status

Property Required Updatable Type Default Description
scaleFactor numeric
The scale factor user to run pgbench (--scale).
transactionsProcessed integer
The number of transaction processed.
latencyAverage numeric
Average latency of transactions.
tpsIncludingConnectionsEstablishing numeric
Number of transaction per second including connection establishing.
tpsExcludingConnectionsEstablishing numeric
Number of transaction per second excluding connection establishing.

Major Version Upgrade Status

Property Required Updatable Type Default Description
primaryInstance string
The primary instance when the operation started
initialInstances array
The instances present when the operation started
pendingToRestartInstances array
The instances that are pending to be restarted
restartedInstances array
The instances that have been restarted
failure string
A failure message (when available)

Restart Status

Property Required Updatable Type Default Description
primaryInstance string
The primary instance when the operation started
initialInstances array
The instances present when the operation started
pendingToRestartInstances array
The instances that are pending to be restarted
restartedInstances array
The instances that have been restarted
switchoverInitiated boolean
An ISO 8601 date indicating if and when the switchover took place
failure string
A failure message (when available)

Minor Version Upgrade Status

Property Required Updatable Type Default Description
primaryInstance string
The primary instance when the operation started
initialInstances array
The instances present when the operation started
pendingToRestartInstances array
The instances that are pending to be restarted
restartedInstances array
The instances that have been restarted
switchoverInitiated boolean
An ISO 8601 date indicating if and when the switchover took place
failure string
A failure message (when available)

Security Upgrade Status

Property Required Updatable Type Default Description
primaryInstance string
The primary instance when the operation started
initialInstances array
The instances present when the operation started
pendingToRestartInstances array
The instances that are pending to be restarted
restartedInstances array
The instances that have been restarted
switchoverInitiated boolean
An ISO 8601 date indicating if and when the switchover took place
failure string
A failure message (when available)

Example:

apiVersion: stackgres.io/v1
kind: SGDbOps
metadata:
  name: benchmark
spec:
 sgCluster: my-cluster
 op: benchmark
 benchmark:
   type: pgbench
   pgbench:
     databaseSize: 1Gi
     duration: P5M
     concurrentClients: 10
     threads: 10
   connectionType: primary-service
status:
 opStatus: completed
 opRetries: 0
 benchmark:
   pgbench:
     scaleFactor: 4
     transactionsProcessed: 3000000
     latencyAverage: 0.054
     tpsIncludingConnectionsEstablishing: 29300
     tpsExcludingConnectionsEstablishing: 30500