Bootstrap¶

This section describes the options you have to create a newPostgreSQL cluster and the design rationale behind them.There are primarily two ways to bootstrap a new cluster:

Important

Bootstrapping from an existing cluster opens up the possibility to create a replica cluster, that is an independent PostgreSQL cluster which is in continuous recovery, synchronized with the source and that accepts read-only connections.

Warning

CloudNativePG requires both the postgres user and database to always exists. Using the local Unix Domain Socket, it needs to connect as postgres user to the postgres database via peer authentication in order to perform administrative tasks on the cluster. DO NOT DELETE the postgres user or the postgres database!!!

The `bootstrap` section¶

The bootstrap method can be defined in the bootstrap section of the clusterspecification.CloudNativePG currently supports the following bootstrap methods:

Differently from the initdb method, both recovery and pg_basebackup create a new cluster based on another one (either offline or online) and can beused to spin up replica clusters. They both rely on the definition of externalclusters.

The `externalClusters` section¶

The externalClusters section allows you to define one or more PostgreSQLclusters that are somehow related to the current one. While in the futurethis section will enable more complex scenarios, it is currently intendedto define a cross-region PostgreSQL cluster based on physical replication,and spanning over different Kubernetes clusters or even traditional VM/bare-metalenvironments.

As far as bootstrapping is concerned, externalClusters can be usedto define the source PostgreSQL cluster for either the pg_basebackup method or the recovery one. An external cluster needs to have:

information about streaming connection - information about the recoveryobjectstore , which is a Barman Cloud compatible object store that contains the backup files of the source cluster - that is, base backups and WAL archives.

Note

A recovery object store is normally an AWS S3, or an Azure Blob Storage, or a Google Cloud Storage source that is managed by Barman Cloud.

When only the streaming connection is defined, the source can be used for the pg_basebackup method. When only the recovery object store is defined, thesource can be used for the recovery method. When both are defined, any of thetwo bootstrap methods can be chosen.

Furthermore, in case of pg_basebackup or full recovery point in time), thecluster is eligible for replica cluster mode. This means that the cluster iscontinuously fed from the source, either via streaming, via WAL shippingthrough the PostgreSQL’s restore_command , or any of the two.

Bootstrap an empty cluster ( `initdb` )¶

The initdb bootstrap method is used to create a new PostgreSQL cluster fromscratch. It is the default one unless specified differently.

The following example contains the full structure of the initdb configuration:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example-initdb
spec:
  instances: 3

  superuserSecret:
    name: superuser-secret

  bootstrap:
    initdb:
      database: app
      owner: app
      secret:
        name: app-secret

  storage:
    size: 1Gi

The above example of bootstrap will:

create a new PGDATA folder using PostgreSQL’s native initdb command2. set a password for the postgres superuser from the secret named superuser-secret 3. create an unprivileged user named app 4. set the password of the latter ( app ) using the one in the app-secret secret (make sure that username matches the same name of the owner )5. create a database called app owned by the app user.

Thanks to the convention over configuration paradigm, you can let theoperator choose a default database name ( app ) and a default applicationuser name (same as the database name), as well as randomly generate asecure password for both the superuser and the application user inPostgreSQL.

Alternatively, you can generate your passwords, store them as secrets,and use them in the PostgreSQL cluster - as described in the above example.

The supplied secrets must comply with the specifications of the: kubernetes.io/basic-auth .As a result, the username in the secret must match

the one of the owner (for the application secret) and postgres for the superuser one.

The following is an example of a basic-auth secret:

apiVersion: v1
data:
  username: YXBw
  password: cGFzc3dvcmQ=
kind: Secret
metadata:
  name: app-secret
type: kubernetes.io/basic-auth

The application database is the one that should be used to store applicationdata. Applications should connect to the cluster with the user that ownsthe application database.

Important

Future implementations of the operator might allow you to create additional users in a declarative configuration fashion.

The postgres superuser and the postgres database are supposed to be usedonly by the operator to configure the cluster.

In case you don’t supply any database name, the operator will proceedby convention and create the app database, and adds it to the clusterdefinition using a defaulting webhook.The user that owns the database defaults to the database name instead.

The application user is not used internally by the operator, which insteadrelies on the superuser to reconcile the cluster with the desired status.

Important

For now, changes to the name of the superuser secret are not applied to the cluster.

The actual PostgreSQL data directory is created via an invocation of the initdb PostgreSQL command. If you need to add custom options to that command(i.e., to change the locale used for the template databases or to add datachecksums), you can use the following parameters:

dataChecksums: When dataChecksums is set to true , CNPG invokes the -k option in initdb to enable checksums on data pages and help detect corruption by the I/O system - that would otherwise be silent (default: false ).

encoding: When encoding set to a value, CNPG passes it to the --encoding option in initdb , which selects the encoding of the template database (default: UTF8 ).

localeCollate: When localeCollate is set to a value, CNPG passes it to the --lc-collate option in initdb . This option controls the collation order ( LC_COLLATE subcategory), as defined in

Locale Support from the PostgreSQL documentation (default: C ).

localeCType: When localeCType is set to a value, CNPG passes it to the --lc-ctype option in initdb . This option controls the collation order ( LC_CTYPE subcategory), as defined in Locale Support from the PostgreSQL documentation (default: C ).

walSegmentSize: When walSegmentSize is set to a value, CNPG passes it to the --wal-segsize option in initdb (default: not set - defined by PostgreSQL as 16 megabytes).

Note

The only two locale options that CloudNativePG implements during the initdb bootstrap refer to the LC_COLLATE and LC_TYPE subcategories. The remaining locale subcategories can be configured directly in the PostgreSQL configuration, using the lc_messages , lc_monetary , lc_numeric , and lc_time parameters.

The following example enables data checksums and sets the default encoding to LATIN1 :

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example-initdb
spec:
  instances: 3

  bootstrap:
    initdb:
      database: app
      owner: app
      dataChecksums: true
      encoding: LATIN1
  storage:
    size: 1Gi

CloudNativePG supports another way to customize the behaviour of the initdb invocation, using the options subsection. However, given that thereare options that can break the behaviour of the operator (such as --auth or -d ), this technique is deprecated and will be removed from future versions ofthe API.

You can also specify a custom list of queries that will be executedonce, just after the database is created and configured. These queries willbe executed as the superuser ( postgres ), connected to the postgres database:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example-initdb
spec:
  instances: 3

  bootstrap:
    initdb:
      database: app
      owner: app
      dataChecksums: true
      localeCollate: en_US
      localeCType: en_US
      postInitSQL:
        - CREATE ROLE angus
        - CREATE ROLE malcolm
  storage:
    size: 1Gi

Warning

Please use the postInitSQL , postInitApplicationSQL and postInitTemplateSQL options with extreme care, as queries are run as a superuser and can disrupt the entire cluster. An error in any of those queries interrupts the bootstrap phase, leaving the cluster incomplete.

Bootstrap from another cluster¶

CloudNativePG enables the bootstrap of a cluster starting fromanother one of the same major version.This operation can happen by connecting directly to the source cluster viastreaming replication ( pg_basebackup ), or indirectly via a recovery objectstore ( recovery ).

The source cluster must be defined in the externalClusters section, identifiedby name (our recommendation is to use the same name of the origin cluster).

Important

By default the recovery method strictly uses the name of the cluster in the externalClusters section to locate the main folder of the backup data within the object store, which is normally reserved for the name of the server. You can specify a different one with the backupObjectStore.serverName property (by default assigned to the value of name in the external cluster definition).

Bootstrap from a backup ( `recovery` )¶

The recovery bootstrap mode lets you create a new cluster froman existing backup, namely a recovery object store.

There are two ways to achieve this result in CloudNativePG:

Both recovery methods enable either full recovery (up to the lastavailable WAL) or up to a Point in time recovery (PITR) .When performing a full recovery, the cluster can also be startedin replica mode.

Note

You can find more information about backup and recovery of a running cluster in the Backup and Recovery .

Recovery from an object store¶

You can recover from a backup created by Barman Cloud and stored on a supportedobject storage. Once you have defined the external cluster, including all therequired configuration in the barmanObjectStore section, you need toreference it in the .spec.recovery.source option. The following exampledefines a recovery object store in a blob container in Azure:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-restore
spec:
  [...]

  superuserSecret:
    name: superuser-secret

  bootstrap:
    recovery:
      source: clusterBackup

  externalClusters:
    - name: clusterBackup
      barmanObjectStore:
        destinationPath: https://STORAGEACCOUNTNAME.blob.core.windows.net/CONTAINERNAME/
        azureCredentials:
          storageAccount:
            name: recovery-object-store-secret
            key: storage_account_name
          storageKey:
            name: recovery-object-store-secret
            key: storage_account_key
        wal:
          maxParallel: 8

Important

By default the recovery method strictly uses the name of the cluster in the externalClusters section to locate the main folder of the backup data within the object store, which is normally reserved for the name of the server. You can specify a different one with the backupObjectStore.serverName property (by default assigned to the value of name in the external clusters definition).

Note

In the above example we are taking advantage of the parallel WAL restore feature, dedicating up to 8 jobs to concurrently fetch the required WAL files from the archive. This feature can sensibly reduce the recovery time. Make sure that you plan ahead for this scenario and correctly tune the value of this parameter for your environment. It will certainly make a difference when (not if) you’ll need it.

Recovery from a `Backup` object¶

In case a Backup resource is already available in the namespace in which thecluster should be created, you can specify its name through .spec.bootstrap.recovery.backup.name , as in the following example:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-example-initdb
spec:
  instances: 3

  superuserSecret:
    name: superuser-secret

  bootstrap:
    recovery:
      backup:
        name: backup-example

  storage:
    size: 1Gi

This bootstrap method allows you to specify just a reference to thebackup that needs to be restored.

Additional considerations¶

Whether you recover from a recovery object store or an existing Backup resource, the following considerations apply:

The application database name and the application database user are preserved from the backup that is being restored. The operator does not currently attempt to back up the underlying secrets, as this is part of the usual maintenance activity of the Kubernetes cluster itself.
In case you don’t supply any superuserSecret , a new one is automatically generated with a secure and random password. The secret is then used to reset the password for the postgres user of the cluster.
By default, the recovery will continue up to the latest available WAL on the default target timeline ( current for PostgreSQL up to 11, latest for version 12 and above). You can optionally specify a recoveryTarget to perform a point in time recovery (see the

Point in time recovery (PITR) ).

Important

Consider using the barmanObjectStore.wal.maxParallel option to speed up WAL fetching from the archive by concurrently download the transaction logs from the recovery object store.

Point in time recovery (PITR)¶

Instead of replaying all the WALs up to the latest one, we can ask PostgreSQLto stop replaying WALs at any given point in time, after having extracted abase backup. PostgreSQL uses this technique to achieve point-in-time recovery(PITR).

Note

PITR is available from recovery object stores as well as Backup objects.

The operator will generate the configuration parameters required for thisfeature to work in case a recovery target is specified, like in the followingexample that uses a recovery object stored in Azure:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-restore-pitr
spec:
  instances: 3

  storage:
    size: 5Gi

  bootstrap:
    recovery:
      source: clusterBackup
      recoveryTarget:
        targetTime: "2020-11-26 15:22:00.00000+00"

  externalClusters:
    - name: clusterBackup
      barmanObjectStore:
        destinationPath: https://STORAGEACCOUNTNAME.blob.core.windows.net/CONTAINERNAME/
        azureCredentials:
          storageAccount:
            name: recovery-object-store-secret
            key: storage_account_name
          storageKey:
            name: recovery-object-store-secret
            key: storage_account_key
        wal:
          maxParallel: 8

Besides targetTime , you can use the following criteria to stop the recovery:

targetXID specify a transaction ID up to which recovery will proceed

You can choose only a single one among the targets above in each recoveryTarget configuration.

Additionally, you can specify targetTLI force recovery to a specifictimeline.

By default, the previous parameters are considered to be exclusive, stoppingjust before the recovery target. You can request inclusive behavior,stopping right after the recovery target, setting the exclusive parameter to false like in the following example relying on a blob container in Azure:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-restore-pitr
spec:
  instances: 3

  storage:
    size: 5Gi

  bootstrap:
    recovery:
      source: clusterBackup
      recoveryTarget:
        targetName: "maintenance-activity"
        exclusive: false

  externalClusters:
    - name: clusterBackup
      barmanObjectStore:
        destinationPath: https://STORAGEACCOUNTNAME.blob.core.windows.net/CONTAINERNAME/
        azureCredentials:
          storageAccount:
            name: recovery-object-store-secret
            key: storage_account_name
          storageKey:
            name: recovery-object-store-secret
            key: storage_account_key
        wal:
          maxParallel: 8

Bootstrap from a live cluster ( `pg_basebackup` )¶

The pg_basebackup bootstrap mode lets you create a new cluster (target) asan exact physical copy of an existing and binarycompatible PostgreSQLinstance (source), through a valid streaming replication connection.The source instance can be either a primary or a standby PostgreSQL server.

The primary use case for this method is represented by migrations to CloudNativePG,either from outside Kubernetes or within Kubernetes (e.g., from another operator).

Warning

The current implementation creates a snapshot of the origin PostgreSQL instance when the cloning process terminates and immediately starts the created cluster. See Current limitations below for details.

Similar to the case of the recovery bootstrap method, once the clone operationcompletes, the operator will take ownership of the target cluster, starting fromthe first instance. This includes overriding some configuration parameters, asrequired by CloudNativePG, resetting the superuser password, creatingthe streaming_replica user, managing the replicas, and so on. The resultingcluster will be completely independent of the source instance.

Important

Configuring the network between the target instance and the source instance goes beyond the scope of CloudNativePG documentation, as it depends on the actual context and environment.

The streaming replication client on the target instance, which will betransparently managed by pg_basebackup , can authenticate itself on the sourceinstance in any of the following ways:

via Username/Password authentication 2. via TLS client certificate

The latter is the recommended one if you connect to a source managedby CloudNativePG or configured for TLS authentication.The first option is, however, the most common form of authentication to aPostgreSQL server in general, and might be the easiest way if the sourceinstance is on a traditional environment outside Kubernetes.Both cases are explained below.

Requirements¶

The following requirements apply to the pg_basebackup bootstrap method:

About the replication user¶

As explained in the requirements section, you need to have a userwith either the SUPERUSER or, preferably, just the REPLICATION privilege in the source instance.

If the source database is created with CloudNativePG, youcan reuse the streaming_replica user and take advantage of clientTLS certificates authentication (which, by default, is the only allowedconnection method for streaming_replica ).

For all other cases, including outside Kubernetes, please verify thatyou already have a user with the REPLICATION privilege, or createa new one by following the instructions below.

As postgres user on the source system, please run:

createuser -P --replication streaming_replica

Enter the password at the prompt and save it for later, as youwill need to add it to a secret in the target instance.

Note

Although the name is not important, we will use streaming_replica for the sake of simplicity. Feel free to change it as you like, provided you adapt the instructions in the following sections.

Username/Password authentication¶

The first authentication method supported by CloudNativePGwith the pg_basebackup bootstrap is based on username and password matching.

Make sure you have the following information before you start the procedure:

You might need to add a line similar to the following to the pg_hba.conf file on the source PostgreSQL instance:

#  A more restrictive rule for TLS and IP of origin is recommended
host replication streaming_replica all md5

The following manifest creates a new PostgreSQL 14.2 cluster,called target-db , using the pg_basebackup bootstrap methodto clone an external PostgreSQL cluster defined as source-db (in the externalClusters array). As you can see, the source-db definition points to the source-db.foo.com host and connects asthe streaming_replica user, whose password is stored in the password key of the source-db-replica-user secret.

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: target-db
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:14.2

  bootstrap:
    pg_basebackup:
      source: source-db

  storage:
    size: 1Gi

  externalClusters:
  - name: source-db
    connectionParameters:
      host: source-db.foo.com
      user: streaming_replica
    password:
      name: source-db-replica-user
      key: password

All the requirements must be met for the clone operation to work, includingthe same PostgreSQL version (in our case 14.2).

TLS certificate authentication¶

The second authentication method supported by CloudNativePGwith the pg_basebackup bootstrap is based on TLS client certificates.This is the recommended approach from a security standpoint.

The following example clones an existing PostgreSQL cluster ( cluster-example )in the same Kubernetes cluster.

Note

This example can be easily adapted to cover an instance that resides outside the Kubernetes cluster.

The manifest defines a new PostgreSQL 14.2 cluster called cluster-clone-tls ,which is bootstrapped using the pg_basebackup method from the cluster-example external cluster. The host is identified by the read/write servicein the same cluster, while the streaming_replica user is authenticatedthanks to the provided keys, certificate, and certification authorityinformation (respectively in the cluster-example-replication and cluster-example-ca secrets).

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: cluster-clone-tls
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:14.2

  bootstrap:
    pg_basebackup:
      source: cluster-example

  storage:
    size: 1Gi

  externalClusters:
  - name: cluster-example
    connectionParameters:
      host: cluster-example-rw.default.svc
      user: streaming_replica
      sslmode: verify-full
    sslKey:
      name: cluster-example-replication
      key: tls.key
    sslCert:
      name: cluster-example-replication
      key: tls.crt
    sslRootCert:
      name: cluster-example-ca
      key: ca.crt

Current limitations¶

Missing tablespace support¶

CloudNativePG does not currently include full declarative managementof PostgreSQL global objects, namely roles, databases, and tablespaces.While roles and databases are copied from the source instance to the targetcluster, tablespaces require a capability that this version ofCloudNativePG is missing: definition and management of additionalpersistent volumes. When dealing with base backup and tablespaces, PostgreSQLitself requires that the exact mount points in the source instancemust also exist in the target instance, in our case, the pods in Kubernetesthat CloudNativePG manages. For this reason, you cannot directlymigrate in CloudNativePG a PostgreSQL instance that takes advantageof tablespaces (you first need to remove them from the source or, if yourorganization requires this feature, contact EDB to prioritize it).

Snapshot copy¶

The pg_basebackup method takes a snapshot of the source instance in the form ofa PostgreSQL base backup. All transactions written from the start ofthe backup to the correct termination of the backup will be streamed to the targetinstance using a second connection (see the --wal-method=stream option for pg_basebackup ).

Once the backup is completed, the new instance will be started on a new timelineand diverge from the source.For this reason, it is advised to stop all write operations to the source databasebefore migrating to the target database in Kubernetes.

Important

Before you attempt a migration, you must test both the procedure and the applications. In particular, it is fundamental that you run the migration procedure as many times as needed to systematically measure the downtime of your applications in production. Feel free to contact EDB for assistance.

Future versions of CloudNativePG will enable users to controlPostgreSQL’s continuous recovery mechanism via Write-Ahead Log (WAL) shippingby creating a new cluster that is a replica of another PostgreSQL instance.This will open up two main use cases:

Bootstrap¶

The `bootstrap` section¶

The `externalClusters` section¶

Bootstrap an empty cluster ( `initdb` )¶

Bootstrap from another cluster¶

Bootstrap from a backup ( `recovery` )¶

Recovery from an object store¶

Recovery from a `Backup` object¶

Additional considerations¶

Point in time recovery (PITR)¶

Bootstrap from a live cluster ( `pg_basebackup` )¶

Requirements¶

About the replication user¶

Username/Password authentication¶

TLS certificate authentication¶

Current limitations¶

Missing tablespace support¶

Snapshot copy¶

Previous topic

Next topic

Table of Contents

Bootstrap¶

The bootstrap section¶

The externalClusters section¶

Bootstrap an empty cluster ( initdb )¶

Bootstrap from another cluster¶

Bootstrap from a backup ( recovery )¶

Recovery from an object store¶

Recovery from a Backup object¶

Additional considerations¶

Point in time recovery (PITR)¶

Bootstrap from a live cluster ( pg_basebackup )¶

Requirements¶

About the replication user¶

Username/Password authentication¶

TLS certificate authentication¶

Current limitations¶

Missing tablespace support¶

Snapshot copy¶

The `bootstrap` section¶

The `externalClusters` section¶

Bootstrap an empty cluster ( `initdb` )¶

Bootstrap from a backup ( `recovery` )¶

Recovery from a `Backup` object¶

Bootstrap from a live cluster ( `pg_basebackup` )¶