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.
  • upgrade: perform a operator API upgrade of the cluster
runAt string

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

If not specified or if the date it’s in the past, it will be interpreted ASAP.
timeout string

An ISO 8601 duration in the format PnDTnHnMn.nS, that specifies a timeout after which the operation execution will be canceled.

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

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.

A value of 0 (zero) means no retries are made. Can not be greater than 10. Defaults to: 0.
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 minor version upgrade
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 has completed successfully
  • Failed: to indicate when the operation has 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 benchmarks are:

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

Specify the service where the benchmark will connect to:

  • 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 in the format PnDTnHnMn.nS, that specifies how long the benchmark will run.
usePreparedStatements boolean false
Use extended query protocol with prepared statements. Defaults to: false.
concurrentClients integer 1
Number of clients simulated, that is, number of concurrent database sessions. Defaults to: 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. Defaults to: 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. Defaults to: false.
analyze boolean
If true, updates statistics used by the planner to determine the most efficient way to execute a query. Defaults to: 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. Defaults to: false.
databases array
List of databases 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. Defaults to: 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. Defaults to: false.
analyze boolean
If true, updates statistics used by the planner to determine the most efficient way to execute a query. Defaults to: 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. Defaults to: false.

Repack

Property Required Updatable Type Default Description
noOrder boolean
If true do vacuum full instead of cluster. Defaults to: 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. Defaults to: false.
noAnalyze boolean
If true don’t analyze at end. Defaults to: false.
excludeExtension boolean
If true don’t repack tables which belong to specific extension. Defaults to: 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. Defaults to: 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. Defaults to: false.
noAnalyze boolean
If true don’t analyze at end. Defaults to: false.
excludeExtension boolean
If true don’t repack tables which belong to specific extension. Defaults to: 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. Defaults to: 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. Defaults to: 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. Defaults to: 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 or Unknown.
reason string
The reason for the condition 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 used to run pgbench (--scale).
transactionsProcessed integer
The number of transactions processed.
latency object
The latency results of the pgbench benchmark
transactionsPerSecond object
All the transactions per second results of the pgbench benchmark

Pgbench Status Latency

Property Required Updatable Type Default Description
average object
Average latency of transactions
standartDeviation object
The latency standard deviation of transactions.

Pgbench Status Latency Average

Property Required Updatable Type Default Description
value number 0.00
The latency average value
unit string ms
The latency measure unit represented in milliseconds

Pgbench Status Latency Standard Deviation

Property Required Updatable Type Default Description
value number 0.00
The latency standard deviation value
unit string ms
The latency measure unit represented in milliseconds

Pgbench Status TPS

Property Required Updatable Type Default Description
includingConnectionsEstablishing object
Number of Transaction Per Second (tps) including connection establishing.
excludingConnectionsEstablishing object
Number of Transaction Per Second (tps) excluding connection establishing.

Pgbench Status TPS Including Connections Establishing

Property Required Updatable Type Default Description
value number
The Transaction Per Second (tps) including connections establishing value
unit string
Transaction Per Second (tps) measure

Pgbench Status TPS Excluding Connections Establishing

Property Required Updatable Type Default Description
value number
The Transaction Per Second (tps) excluding connections establishing value
unit string
Transaction Per Second (tps) measure

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 string
An ISO 8601 date indicating if and when the switchover initiated
switchoverFinalized string
An ISO 8601 date indicating if and when the switchover finalized
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 string
An ISO 8601 date indicating if and when the switchover initiated
switchoverFinalized string
An ISO 8601 date indicating if and when the switchover finalized
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 string
An ISO 8601 date indicating if and when the switchover initiated
switchoverFinalized string
An ISO 8601 date indicating if and when the switchover finalized
failure string
A failure message (when available)

Example:

apiVersion: stackgres.io/v1
kind: SGDbOps
metadata:
  name: benchmark
spec:
 sgCluster: my-cluster
 op: benchmark
 maxRetries: 1
 benchmark:
   type: pgbench
   pgbench:
     databaseSize: 1Gi
     duration: P0DT0H10M0S
     concurrentClients: 10
     threads: 10
   connectionType: primary-service