Importing Postgres databases v1.27.0

This section describes how to import one or more existing PostgreSQL databases inside a brand new EDB Postgres® AI for CloudNativePG™ Cluster cluster.

The import operation is based on the concept of online logical backups in PostgreSQL, and relies on pg_dump via a network connection to the origin host, and pg_restore. Thanks to native Multi-Version Concurrency Control (MVCC) and snapshots, PostgreSQL enables taking consistent backups over the network, in a concurrent manner, without stopping any write activity.

Logical backups are also the most common, flexible and reliable technique to perform major upgrades of PostgreSQL versions.

As a result, the instructions in this section are suitable for both:

• importing one or more databases from an existing PostgreSQL instance, even outside Kubernetes
• importing the database from any PostgreSQL version to one that is either the same or newer, enabling major upgrades of PostgreSQL (e.g. from version 13.x to version 17.x)

In both cases, the operation is performed on a consistent snapshot of the origin database.

How it works

Conceptually, the import requires you to create a new cluster from scratch (destination cluster), using the initdb bootstrap method, and then complete the initdb.import subsection to import objects from an existing Postgres cluster (source cluster). As per PostgreSQL recommendation, we suggest that the PostgreSQL major version of the destination cluster is greater or equal than the one of the source cluster.

EDB Postgres® AI for CloudNativePG™ Cluster provides two main ways to import objects from the source cluster into the destination cluster:

• microservice approach: the destination cluster is designed to host a single application database owned by the specified application user, as recommended by the EDB Postgres® AI for CloudNativePG™ Cluster project

• monolith approach: the destination cluster is designed to host multiple databases and different users, imported from the source cluster

The first import method is available via the microservice type, the second via the monolith type.

The microservice type

With the microservice approach, you can specify a single database you want to import from the source cluster into the destination cluster. The operation is performed in 4 steps:

• initdb bootstrap of the new cluster
• export of the selected database (in initdb.import.databases) using pg_dump -Fd
• import of the database using pg_restore --no-acl --no-owner into the initdb.database (application database) owned by the initdb.owner user
• cleanup of the database dump file
• optional execution of the user defined SQL queries in the application database via the postImportApplicationSQL parameter
• execution of ANALYZE VERBOSE on the imported database

In the figure below, a single PostgreSQL cluster containing N databases is imported into separate EDB Postgres® AI for CloudNativePG™ Cluster clusters, with each cluster using a microservice import for one of the N source databases.

For example, the YAML below creates a new 3 instance PostgreSQL cluster (latest available major version at the time the operator was released) called cluster-microservice that imports the angus database from the cluster-pg96 cluster (with the unsupported PostgreSQL 9.6), by connecting to the postgres database using the postgres user, via the password stored in the cluster-pg96-superuser secret.

apiVersion: postgresql.k8s.enterprisedb.io/v1
kind: Cluster
metadata:
  name: cluster-microservice
spec:
  instances: 3

  bootstrap:
    initdb:
      import:
        type: microservice
        databases:
          - angus
        source:
          externalCluster: cluster-pg96
        #postImportApplicationSQL:
        #- |
        #  INSERT YOUR SQL QUERIES HERE
  storage:
    size: 1Gi
  externalClusters:
    - name: cluster-pg96
      connectionParameters:
        # Use the correct IP or host name for the source database
        host: pg96.local
        user: postgres
        dbname: postgres
      password:
        name: cluster-pg96-superuser
        key: password

There are a few things you need to be aware of when using the microservice type:

• It requires an externalCluster that points to an existing PostgreSQL instance containing the data to import (for more information, please refer to "The externalClusters section")
• Traffic must be allowed between the Kubernetes cluster and the externalCluster during the operation
• Connection to the source database must be granted with the specified user that needs to run pg_dump and read roles information (superuser is OK)
• Currently, the pg_dump -Fd result is stored temporarily inside the dumps folder in the PGDATA volume, so there should be enough available space to temporarily contain the dump result on the assigned node, as well as the restored data and indexes. Once the import operation is completed, this folder is automatically deleted by the operator.
• Only one database can be specified inside the initdb.import.databases array
• Roles are not imported - and as such they cannot be specified inside initdb.import.roles

The monolith type

With the monolith approach, you can specify a set of roles and databases you want to import from the source cluster into the destination cluster. The operation is performed in the following steps:

• initdb bootstrap of the new cluster
• export and import of the selected roles
• export of the selected databases (in initdb.import.databases), one at a time, using pg_dump -Fd
• create each of the selected databases and import data using pg_restore
• run ANALYZE on each imported database
• cleanup of the database dump files

For example, the YAML below creates a new 3 instance PostgreSQL cluster (latest available major version at the time the operator was released) called cluster-monolith that imports the accountant and the bank_user roles, as well as the accounting, banking, resort databases from the cluster-pg96 cluster (with the unsupported PostgreSQL 9.6), by connecting to the postgres database using the postgres user, via the password stored in the cluster-pg96-superuser secret.

apiVersion: postgresql.k8s.enterprisedb.io/v1
kind: Cluster
metadata:
  name: cluster-monolith
spec:
  instances: 3
  bootstrap:
    initdb:
      import:
        type: monolith
        databases:
          - accounting
          - banking
          - resort
        roles:
          - accountant
          - bank_user
        source:
          externalCluster: cluster-pg96
  storage:
    size: 1Gi
  externalClusters:
    - name: cluster-pg96
      connectionParameters:
        # Use the correct IP or host name for the source database
        host: pg96.local
        user: postgres
        dbname: postgres
        sslmode: require
      password:
        name: cluster-pg96-superuser
        key: password

There are a few things you need to be aware of when using the monolith type:

• It requires an externalCluster that points to an existing PostgreSQL instance containing the data to import (for more information, please refer to "The externalClusters section")
• Traffic must be allowed between the Kubernetes cluster and the externalCluster during the operation
• Connection to the source database must be granted with the specified user that needs to run pg_dump and retrieve roles information (superuser is OK)
• Currently, the pg_dump -Fd result is stored temporarily inside the dumps folder in the PGDATA volume of the destination cluster's instances, so there should be enough available space to temporarily contain the dump result on the assigned node, as well as the restored data and indexes. Once the import operation is completed, this folder is automatically deleted by the operator.
• At least one database to be specified in the initdb.import.databases array
• Any role that is required by the imported databases must be specified inside initdb.import.roles, with the limitations below:
  • The following roles, if present, are not imported: postgres, streaming_replica, cnp_pooler_pgbouncer
  • The SUPERUSER option is removed from any imported role
• Wildcard "*" can be used as the only element in the databases and/or roles arrays to import every object of the kind; When matching databases the wildcard will ignore the postgres database, template databases, and those databases not allowing connections
• After the clone procedure is done, ANALYZE VERBOSE is executed for every database.
• The postImportApplicationSQL field is not supported

A practical example

There is nothing to stop you from using the monolith approach to import a single database. It is interesting to see how the results of doing so would differ from using the microservice approach.

Given a source cluster, for example the following, with a database named mydb owned by role me:

apiVersion: postgresql.k8s.enterprisedb.io/v1
kind: Cluster
metadata:
  name: cluster-example
spec:
  instances: 1

  postgresql:
    pg_hba:
      - host all all all trust

  storage:
    size: 1Gi

  bootstrap:
    initdb:
      database: mydb
      owner: me

We can import it via microservice:

apiVersion: postgresql.k8s.enterprisedb.io/v1
kind: Cluster
metadata:
  name: cluster-example-microservice
spec:
  instances: 1
  
  storage:
    size: 1Gi

  bootstrap:
    initdb:
      import:
        type: microservice
        databases:
          - mydb
        source:
          externalCluster: cluster-example

  externalClusters:
    - name: cluster-example
      connectionParameters:
        host: cluster-example-rw
        dbname: postgres

as well as via monolith:

apiVersion: postgresql.k8s.enterprisedb.io/v1
kind: Cluster
metadata:
  name: cluster-example-monolith
spec:
  instances: 1
  
  storage:
    size: 1Gi

  bootstrap:
    initdb:
      import:
        type: monolith
        databases:
          - mydb
        roles:
          - me
        source:
          externalCluster: cluster-example

  externalClusters:
    - name: cluster-example
      connectionParameters:
        host: cluster-example-rw
        dbname: postgres

In both cases, the database's contents will be imported, but:

• In the microservice case, the imported database's name and owner both become app, or whichever configuration for the fields database and owner are set in the bootstrap.initdb stanza.
• In the monolith case, the database and owner are kept exactly as in the source cluster, i.e. mydb and me respectively. No app database nor user will be created. If there are custom settings for database and owner in the bootstrap.initdb stanza that don't match the source databases/owners to import, the instance manager will create a new empty application database and owner role, but will leave the imported databases/owners intact.

Import optimizations

During the logical import of a database, EDB Postgres® AI for CloudNativePG™ Cluster optimizes the configuration of PostgreSQL in order to prioritize speed versus data durability, by forcing:

• archive_mode to off
• fsync to off
• full_page_writes to off
• max_wal_senders to 0
• wal_level to minimal

Before completing the import job, EDB Postgres® AI for CloudNativePG™ Cluster restores the expected configuration, then runs initdb --sync-only to ensure that data is permanently written on disk.

There are other optimizations you can do during the import phase. Although this topic is beyond the scope of EDB Postgres® AI for CloudNativePG™ Cluster, we recommend that you reduce unnecessary writes in the checkpoint area by tuning Postgres GUCs like shared_buffers, max_wal_size, checkpoint_timeout directly in the Cluster configuration.

Customizing pg_dump and pg_restore Behavior

You can customize the behavior of pg_dump and pg_restore by specifying additional options using the pgDumpExtraOptions and pgRestoreExtraOptions parameters. For instance, you can enable parallel jobs to speed up data import/export processes, as shown in the following example:

  # <snip>
  bootstrap:
    initdb:
      import:
        type: microservice
        databases:
        - app
        source:
          externalCluster: cluster-example
        pgDumpExtraOptions:
        - '--jobs=2'
        pgRestoreExtraOptions:
        - '--jobs=2'
  # <snip>

Online Import and Upgrades

Logical replication offers a powerful way to import any PostgreSQL database accessible over the network using the following approach:

• Import Bootstrap with Schema-Only Option: Initialize the schema in the target database before replication begins.
• Subscription Resource: Set up continuous replication to synchronize data changes.

This technique can also be leveraged for performing major PostgreSQL upgrades with minimal downtime, making it ideal for seamless migrations and system upgrades.

For more details, including limitations and best practices, refer to the Logical Replication section in the documentation.