autobase Documentation

File: README.md Copy Chapter

Autobase for PostgreSQL® :elephant: :sparkling_heart:

Autobase is an Internal Database Platform for PostgreSQL, bringing the managed database experience of DBaaS into your own infrastructure.

Create and manage production-ready, highly available PostgreSQL clusters. Autobase simplifies deployment, reduces operational costs, and makes database management accessible, even for teams without specialized expertise.

Automate deployment, failover, backups, restore, upgrades, scaling, and more with ease.

Say goodbye to manual database management 👋

Documentation Copy Chapter

Autobase documentation can be found here.

Architecture Copy Chapter

For a detailed description of the cluster components, visit the Architecture page.

Getting Started Copy Chapter

You can deploy PostgreSQL clusters using the Console (UI), command line (Ansible), or GitOps.

Console (UI) Copy Section

• Community Edition - Free license for individual developers and hobby projects; includes lightweight cluster deployment.
• Enterprise Edition - Commercial license for production environments; includes extended cluster management features and support.

Ansible Collection Copy Section

• Ansible Collection - The automation layer for those who prefer Ansible playbooks instead of the database platform.

GitOps Copy Section

• GitOps (CI/CD) - Manage cluster configuration in Git and apply changes through CI/CD pipelines.

[!TIP]
📩 Contact us at [email protected], and our team will help you implement Autobase into your infrastructure.

Compatibility Copy Chapter

Red Hat and Debian-based distributions.

Supported Linux Distributions:

• Debian: 11, 12, 13
• Ubuntu: 22.04, 24.04, 26.04
• CentOS Stream: 9, 10
• Oracle Linux: 8, 9, 10
• Rocky Linux: 8, 9, 10
• AlmaLinux: 8, 9, 10

Architecture: x86_64 (amd64), aarch64 (arm64).

PostgreSQL versions:

All supported PostgreSQL versions.

:white_check_mark: tested, works fine: PostgreSQL 10, 11, 12, 13, 14, 15, 16, 17, 18

Table of results of daily automated testing of cluster deployment:

Project Status Copy Chapter

Autobase has been actively developed for over 7 years (since 2019) and is trusted by companies worldwide, including in production environments with high loads and demanding reliability requirements. Our mission is to provide an open-source DBaaS that delivers reliability, flexibility, and cost-efficiency.

The project will remain open-source forever, but to ensure its continuous growth and development, we rely on sponsorship and support packages. Support packages provide access to personalized support from the project authors and PostgreSQL experts.

Star us Copy Chapter

If you find our project helpful, consider giving it a star on GitHub! Your support helps us grow and motivates us to keep improving. Starring the project is a simple yet effective way to show your appreciation and help others discover it.

License Copy Chapter

Licensed under the MIT License. See the LICENSE file for details.

Author Copy Chapter

Vitaliy Kukharik (PostgreSQL Expert, Founder Autobase.tech)
[email protected]

File: automation/roles/wal_g/README.md Copy Chapter

Ansible Role: wal_g

This role installs and configures WAL-G, an archival and restoration tool for PostgreSQL. WAL-G is designed to perform continuous archiving of PostgreSQL WAL files and provide point-in-time recovery capabilities with cloud storage support.

Variables Copy Chapter

Notes:

• Binary installation is skipped if the installed version equals wal_g_version.
• On RHEL 8, the role builds from source due to GLIBC requirements for official binaries.
• The role creates ~postgres/.walg.json and the prefetch directory when enabled.
• Cron jobs are created via ansible.builtin.cron from wal_g_cron_jobs.

WAL-G auto conf (cloud_provider) Copy Section

If cloud_provider is set, the role runs tasks/auto_conf.yml to automatically build WAL-G config.

Dependencies Copy Chapter

This role depends on:

• vitabaks.autobase.common - Provides common variables and configurations

File: automation/roles/vip_manager/README.md Copy Chapter

Ansible Role: vip_manager

This role installs and configures vip-manager, a service that manages virtual IP addresses for PostgreSQL high availability clusters. It provides automatic VIP assignment to the PostgreSQL primary server for seamless application connectivity during failover.

Role Variables Copy Chapter

Dependencies Copy Chapter

This role depends on:

• vitabaks.autobase.common - Provides common variables and configurations

File: automation/roles/upgrade/README.md Copy Chapter

PostgreSQL major upgrade Copy Chapter

This role is designed for in-place or blue-green upgrade methods (e.g., from version 17 to 18).

Playbooks

In-place method:

• pg_upgrade - Upgrades the PostgreSQL to a new major version.
• pg_upgrade_rollback - Performs a rollback of a PostgreSQL upgrade.

Blue-green method:

• pg_logical_upgrade - Upgrades the target cluster and converting a physical replica into a logical replica.
• pg_logical_switchover - Redirects PostgreSQL traffic from the source cluster to the target cluster with near-zero downtime.
• pg_logical_switchover_rollback - Switches PostgreSQL traffic back to the source cluster.
• pg_logical_replication_stop - Clean up publications, subscriptions, and replication slots.

How it works

In-place method upgrades the current production cluster directly. It is simpler and does not require additional servers, but it usually requires a short maintenance window (typically about 1 minute, depending on workload and environment).

Schematic:

flowchart TD
    A[Current cluster v17] -->|pg_upgrade using hard-links| B[Current cluster v18]

Blue-green method upgrades a separate target cluster that is a copy of the current one, so upgrade stages on target do not impact production availability. This method is suitable when downtime must be limited to a few seconds: during switchover, the source cluster is briefly set to read-only, traffic is redirected to target (by updating PgBouncer pool backend IPs), and you can observe stability before switching the application to the address of the new cluster or rolling back to the source cluster if needed.

Schematic:

flowchart LR
  S[Source cluster/BLUE] -->|physycal replication| T[Target cluster/GREEN]
  S -->|pg_upgrade on target| T
  S -->|logical replication| T
  S -->|switchover: redirect traffic| T

Rollback is possible without losing changes made after cutover, because reverse logical replication (source <- target) is created during traffic switching.

Database Downtime Considerations

In-place To minimize the downtime, we pause PgBouncer pools. This doesn’t terminate application connections, but may temporarily increase query latency while pools are paused. The pause typically lasts ~30-60 seconds, but may be longer for large databases due to pg_upgrade and rsync. The default maximum wait time during a pause is 2 minutes (controlled by the query_wait_timeout parameter).

Blue-green A short service impact occurs only during switchover: the source cluster is briefly set to read-only, the remaining logical replication lag is drained, and traffic is redirected to target. Only the write traffic degrades (~10-15 seconds), while the read traffic has near-zero downtime.

Compatibility Copy Chapter

The upgrade is supported starting from PostgreSQL 9.3 for in-place upgrade method, and starting from PostgreSQL 12 for blue-green upgrade method.

Requirements Copy Chapter

Specify the current (old) version of PostgreSQL in the pg_old_version variable and target version of PostgreSQL for the upgrade in the pg_new_version variable.

For the blue-green method, deploy the target cluster in standby cluster mode. See patroni_standby_cluster variable.

Recommendations Copy Chapter

1. Before upgrading to a new major version, it's recommended to update PostgreSQL and its extensions. Additionally, consider updating Patroni and the entire system.

   To achieve this, use the update_pgcluster.yml playbook. More details can be found here.

2. Before moving forward, execute preliminary checks to ensure that your database schema is compatible with the upcoming PostgreSQL version and that the cluster is ready for the upgrade.

   To do this, run the playbook using the tags 'pre-checks,upgrade-check'.

   If any errors arise, such as schema object incompatibilities, resolve these issues and repeat the checks.

   Once the playbook completes the pre-checks without any errors, you should see the following messages in the Ansible log:

   • "The database schema is compatible with PostgreSQL <new_version>"
   • "Clusters are compatible"

   Upon seeing these messages, proceed to run the playbook without any tags to initiate the upgrade.

Upgrade Copy Chapter

In-place upgrade method Copy Section

Use pg_upgrade playbook, example:

ansible-playbook pg_upgrade.yml -i inventory -e "pg_old_version=17 pg_new_version=18"

Rollback

It's designed to be used when a PostgreSQL upgrade hasn't been fully completed and the new version hasn't been started.

ansible-playbook pg_upgrade_rollback.yml -i inventory

In some scenarios, if errors occur, the pg_upgrade.yml playbook may automatically initiate a rollback. Alternatively, if the automatic rollback does not occur, you can manually execute the pg_upgrade_rollback playbook to revert the changes.

The rollback operation is performed by starting the Patroni cluster with the old version of PostgreSQL using the same PGDATA. The playbook first checks the health of the current cluster, verifies the version of PostgreSQL, and ensures the new PostgreSQL is not running. If these checks pass, the playbook switches back to the old PostgreSQL paths and restarts the Patroni service.

Blue-green upgrade method Copy Section

Use pg_logical_upgrade playbook, example:

1. Deploy the target (standby cluster)

TIP: During target cluster deployment, it is enough to point the standby cluster to the source cluster by setting either source_cluster_host to an IP address of the source cluster host.

Create inventory.yml file: describe the nodes of the current and target cluster.

---
all:
  vars:
    # upgrade settings
    pg_old_version: 17 # the current (old) version of PostgreSQL
    pg_new_version: 18 # the target (new) version of PostgreSQL for the upgrade
    pg_replication_database: "all" # dbname for replication, 'all' for the entire cluster
    pg_publication_count: 1 # number of publications, replication slots and subscriptions
    # connection settings
    ansible_ssh_user: root
    ansible_ssh_private_key_file: /home/ubuntu/.ssh/id_rsa
  children:
    source_cluster:
      hosts:
        10.142.0.21:
        10.142.0.22:
        10.142.0.23:
    target_cluster:
      hosts:
        10.142.0.24:
        10.142.0.26:
        10.142.0.43:

2. Upgrade the target cluster:

ansible-playbook pg_logical_upgrade.yml -i inventory.yml

3. Switchover to the target cluster:

After pg_logical_upgrade is completed and logical replication is active (source -> target), use pg_logical_switchover playbook to switch production traffic to the target cluster.

ansible-playbook playbooks/pg_logical_switchover.yml -i inventory.yml

WARNING: If PgBouncer is not installed in your cluster, traffic redirection will not happen automatically.
In this case, use the following sequence:

1. Stop your application
2. Run the pg_logical_switchover playbook
3. Start your application and point it to the new (target) cluster address

Optionally:

• Force mode (allow a small lag during cutover):

  bash Copy

  ansible-playbook playbooks/pg_logical_switchover.yml -i inventory.yml \
    -e "pg_switchover_force_mode=true pg_switchover_force_mode_lag_bytes=1048576"

• Rollback after switchover (if you need to move traffic back to the original cluster):

  bash Copy

  ansible-playbook playbooks/pg_logical_switchover_rollback.yml -i inventory.yml

4. Switch application to the target cluster

After observing your services and database on the new cluster, if everything is stable, switch your application to the new cluster by updating the connection string with the new cluster address.

5. Stop reverse replication

To clean up logical replication objects after migration (subscriptions, replication slots, and publications), run:

ansible-playbook playbooks/pg_logical_replication_stop.yml -i inventory.yml

Variables Copy Chapter

Upgrade settings (for both methods):

Blue-green deployment method settings:

Note: For more information, see the upgrade role variables file.

Upgrade Plan: Copy Section

PRE-UPGRADE: Perform Pre-Checks

• Make sure that the required variables are specified
  • Notes: pg_old_version and pg_new_version variables
  • Stop, if one or more required variables have empty values.
• Make sure that the old and new data and config directories do not match
  • Stop, if pg_old_datadir and pg_new_datadir, or pg_old_confdir and pg_new_confdir match.
• Make sure the ansible required Python library is installed
  • Notes: Install 'pexpect' package if missing
• Test PostgreSQL database access using a unix socket
  • if there is an error (no pg_hba.conf entry):
    • Add temporary local hba rule (during the upgrade)
    • Update the PostgreSQL configuration
• Check the current version of PostgreSQL
  • Stop, if the current version does not match pg_old_version
  • Stop, if the current version greater than or equal to pg_new_version. No upgrade is needed.
• Ensure new data directory is different from the current one
  • Note: This check is necessary to avoid the risk of deleting the current data directory
  • Stop, if the current data directory is the same as pg_new_datadir.
  • Stop, if the current WAL directory is the same as pg_new_wal_dir (if a custom wal dir is used).
• Perform pre-checks and preparation for blue-green upgrade method
  • Note: If the pg_logical_upgrade.yml playbook is used
  • Get a list of databases from the source cluster
    • Note: if pg_replication_database == "all" (default: all)
  • Make sure that the wal_level parameter is set to 'logical'
    • Stop, if wal_level != logical
  • Make sure that the max_logical_replication_workers parameter is sufficient
    • Stop, if max_logical_replication_workers is too low for the number of publications and databases
  • Test access from the target cluster to the source database
  • Test access from the source cluster to the target database
  • Make sure there are no tables with replica identity "nothing"
  • Make sure that tables with replica identity "default" have primary key
  • Set REPLICA IDENTITY FULL for tables without primary key
    • Note: if pg_allow_replica_identity_full is true (default: true)
  • Make sure that the 'restore_command' parameter is not specified
    • if 'restore_command' is specified: comment out recovery_conf in patroni.yml
  • Increase the wal_keep_segments/wal_keep_size parameter on the source primary
    • if pg_wal_keep_gigabytes != none (default: 100)
    • Note: To guarantee that the necessary WAL files are available to reach recovery_target_lsn via streaming replication.
• Make sure that physical replication is active
  • Stop, if there are no active replicas
• Make sure there is no high replication lag
  • Stop, if replication lag is high (more than max_replication_lag_bytes)
• Make sure there are no long-running transactions
  • Stop, if long-running transactions detected (more than max_transaction_sec)
• Make sure that SSH key-based authentication is configured between cluster nodes
  • Create and copy ssh keys between database servers (if not configured)
• Perform Rsync Checks
  • Make sure that the rsync package are installed
  • Create 'testrsync' file on primary
  • Test rsync and ssh key access
  • Cleanup 'testrsync' file
• Check if PostgreSQL tablespaces exist
  • Print tablespace location (if exists)
  • Note: If tablespaces are present they will be upgraded (step 5) on replicas using rsync
• Make sure that the 'recovery.signal' file is absent in the data directory
• Test PgBouncer access via unix socket
  • Ensure correct permissions for PgBouncer unix socket directory
  • Test access via unix socket to be able to perform 'PAUSE' command

PRE-UPGRADE: Install new PostgreSQL packages

• Clean yum/dnf cache (for RedHat based) or Update apt cache for (Debian based)
• Install new PostgreSQL packages
• Install TimescaleDB package for new PostgreSQL
  • Note: if 'enable_timescale' is 'true'

PRE-UPGRADE: Initialize new db, schema compatibility check, and pg_upgrade --check

• Initialize new PostgreSQL
  • Make sure new PostgreSQL data directory exists
  • Make sure new PostgreSQL data directory is not initialized
    • If already initialized:
      • Perform pg_dropcluster (for Debian based)
      • Clear the new PostgreSQL data directory
  • Get the current install user (rolname with oid = 10)
  • Get the current encoding and data_checksums settings
  • Initialize new PostgreSQL data directory
    • for Debian based: on all database servers to create default config files
    • for RedHat based: on the primary only
• Copy files specified in the upgrade_copy_files_to_all_server variable [optional]
  • Notes: for example, it may be necessary for Postgres Full-Text Search (FTS) files
• Schema compatibility check
  • Get the current shared_preload_libraries settings
  • Get the current cron.database_name settings
    • Notes: if 'pg_cron' is defined in 'pg_shared_preload_libraries'
  • Start new PostgreSQL to check the schema compatibility
    • Note: on the port specified in the schema_compatibility_check_port variable
    • Wait for PostgreSQL to start
  • Check the compatibility of the database schema with the new PostgreSQL
    • Notes: used pg_dumpall with --schema-only options
    • Wait for the schema compatibility check to complete
  • Checking the result of the schema compatibility
    • Note: Checking for errors in /tmp/pg_schema_compatibility_check.log
    • Stop, if the schema is not compatible (there are errors)
  • Print result of checking the compatibility of the schema
  • Stop new PostgreSQL to re-initdb
  • Drop new PostgreSQL to re-initdb (perform pg_dropcluster for Debian based)
  • Reinitialize the database after checking schema compatibility
• Perform pg_upgrade check
  • Get the current shared_preload_libraries settings
  • Verify the two clusters are compatible (pg_upgrade --check)
  • Print the result of the pg_upgrade check

PRE-UPGRADE: Create a publication/slot and reach recovery_target_lsn

• Note: for blue-green upgrade method (pg_logical_upgrade.yml playbook)
• Stop PostgreSQL on target primary
  • Pause WAL replay (recovery) on the target cluster replicas
  • Pause Patroni on the target cluster before stopping PostgreSQL
  • Execute CHECKPOINT before stopping PostgreSQL
  • Wait for the CHECKPOINT to complete
  • Stop Patroni service on the standby cluster leader before stopping PostgreSQL
  • Wait for Patroni REST API to stop on the standby cluster leader
  • Stop PostgreSQL on the standby cluster leader
• Create a publication and logical replication slot for each database
  • Get a list of tables distributed by groups
    • Note: Split tables (sorted by DML activity) into groups for publications to evenly distribute tables across slots.
    • if pg_publication_count more than 1 (default: 1)
  • Start pg_terminator script
    • Note: Monitor locks and terminate the 'create publication' blockers
  • Create publications for logical replication
  • Create slots for logical replication
  • Stop pg_terminator script
  • Set variable: target_lsn
  • Advance replication slots to target_lsn for each database
• Reach recovery_target_lsn on target primary
  • Specify recovery parameters on the standby cluster leader
  • Start PostgreSQL on standby cluster leader to reach recovery_target_lsn
  • Wait for the PostgreSQL start to complete
  • Wait until the recovery is complete
  • Check the PostgreSQL log file
  • Resume WAL replay (recovery) on target cluster replicas
  • Wait until physical replication becomes active
  • Wait until physical replication lag is 0 bytes

PRE-UPGRADE: Prepare the Patroni configuration

• Backup the patroni.yml configuration file
• Edit the patroni.yml configuration file
  • Update parameters: data_dir, bin_dir, config_dir
  • Prepare the PostgreSQL parameters
    • Notes: Removed or renamed parameters
  • Remove 'standby_cluster' parameter (if exists)
    • Notes: To support upgrades in the Patroni standby cluster
• Copy pg_hba.conf to pg_new_confdir
  • Notes: to save pg_hba rules

UPGRADE: Upgrade PostgreSQL

• Enable maintenance mode for Patroni cluster (pause)
• Enable maintenance mode for HAProxy (if used)
  • Notes: if 'pgbouncer_install' is 'true' and 'pgbouncer_pool_pause' is 'true'
  • Stop confd service
  • Update haproxy conf file
    • Notes: Temporarily disable http-checks in order to keep database connections after stopping the Patroni service
  • Reload haproxy service
• Enable maintenance mode for vip-manager (if used)
  • Notes: if 'pgbouncer_install' is 'true' and 'pgbouncer_pool_pause' is 'true'
  • Update vip-manager service file (comment out 'ExecStopPost')
    • Notes: Temporarily disable vip-manager service to keep database connections after stopping the Patroni service
  • Stop vip-manager service
    • Notes: This prevents the VIP from being removed when the Patroni leader is unavailable during maintenance
  • Make sure that the cluster ip address (VIP) is running
• Stop Patroni service
  • Wait until the Patroni cluster is stopped
• Execute CHECKPOINT before stopping PostgreSQL
  • Wait for the CHECKPOINT to complete
• Wait until replication lag is less than max_replication_lag_bytes
  • Notes: max wait time: 2 minutes
  • Stop, if replication lag is high
  • Perform rollback
    • Print error message: "There's a replication lag in the PostgreSQL cluster. Please try again later"
• Perform PAUSE on all pgbouncers servers
  • Notes: if 'pgbouncer_install' is 'true' and 'pgbouncer_pool_pause' is 'true'
  • Notes: pgbouncer pause script performs the following actions:
    • Waits for active queries on the database servers to complete (with a runtime more than pg_slow_active_query_treshold).
    • If there are no active queries, sends a PAUSE command to each PgBouncer server in parallel (using xargs and ssh connections).
    • If all PgBouncer pools are successfully paused, the script exits with code 0 (successful).
    • If active queries do not complete within 30 seconds (pgbouncer_pool_pause_terminate_after variable), the script terminates slow active queries (longer than pg_slow_active_query_treshold_to_terminate).
    • If after that it is still not possible to pause the pgbouncer servers within 60 seconds (pgbouncer_pool_pause_stop_after variable) from the start of the script, the script exits with an error.
      • Perform rollback
        • Print error message: "PgBouncer pools could not be paused, please try again later."
• Stop PostgreSQL on the leader and replicas
  • Check if old PostgreSQL is stopped
  • Check if new PostgreSQL is stopped
• Get 'Latest checkpoint location' on the leader and replicas
  • Print 'Latest checkpoint location' for the leader and replicas
• Check if all 'Latest checkpoint location' values match
  • if 'Latest checkpoint location' values match
    • Print info message:
      • "'Latest checkpoint location' is the same on the leader and its standbys"
  • if 'Latest checkpoint location' values do not match
    • Perform rollback
      • Print error message: "Latest checkpoint location' doesn't match on leader and its standbys. Please try again later"
• Upgrade the PostgreSQL on the primary (using pg_upgrade --link)
  • Perform rollback, if the upgrade failed
  • Print the result of the pg_upgrade
• Make sure that the new data directory is empty on the replica
• Upgrade the PostgreSQL on the replica (using rsync --hard-links)
  • Wait for the rsync to complete
• Upgrade the PostgreSQL tablespaces on the replica (using rsync --hard-links)
  • Notes: if tablespaces exist
  • Wait for the tablespaces rsync to complete
• Synchronize WAL directory (if pg_new_wal_dir is defined) [optional]
  • Make sure new pg_wal directory is not symlink
  • Make sure the custom WAL directory exists and is empty
  • Synchronize new pg_wal to 'pg_new_wal_dir' path
  • Rename pg_wal to pg_wal_old
  • Create symlink
  • Remove 'pg_wal_old' directory
• Remove existing cluster from DCS
• Start Patroni service on the cluster leader
  • Wait for Patroni port to become open on the host
  • Check Patroni is healthy on the leader
• Perform RESUME PgBouncer pools on the leader
  • Notes: if 'pgbouncer_install' is 'true' and 'pgbouncer_pool_pause' is 'true'
• Start Patroni service on the cluster replica
  • Wait for Patroni port to become open on the host
  • Check Patroni is healthy on the replica
• Perform RESUME PgBouncer pools on the replica
  • Notes: if 'pgbouncer_install' is 'true' and 'pgbouncer_pool_pause' is 'true'
• Check PostgreSQL is started and accepting connections
• Disable maintenance mode for HAProxy (if used)
  • Update haproxy conf file
    • Notes: Enable http-checks
  • Reload haproxy service
  • Start confd service
• Disable maintenance mode for vip-manager (if used)
  • Update vip-manager service file (uncomment 'ExecStopPost')
  • Start vip-manager service
  • Make sure that the cluster ip address (VIP) is running

POST-UPGRADE: Create a subscription for logical replication

• Note: for blue-green upgrade method (pg_logical_upgrade.yml playbook)
• Create a subscription on target primary
  • Create subscription for logical replication in each database
  • Make sure that logical replication is active
  • Check the logical replication lag

POST-UPGRADE: Analyze a PostgreSQL database (update optimizer statistics) and Post-Upgrade tasks

• Run vacuumdb to analyze the PostgreSQL databases
  • Note: Uses parallel processes equal to 50% of CPU cores ('vacuumdb_parallel_jobs' variable)
  • Note: Before collecting statistics, the 'pg_terminator' script is launched to monitor and terminate any 'ANALYZE' blockers. Once statistics collection is complete, the script is stopped.
• Update extensions in each database
  • Get list of installed PostgreSQL extensions
  • Get list of old PostgreSQL extensions
    • Update old PostgreSQL extensions
      • Notes: excluding: 'pg_repack' and 'pg_stat_kcache' (if exists), as they require re-creation to update
    • Recreate old pg_stat_statements and pg_stat_kcache extensions to update
      • Notes: if pg_stat_kcache is installed
    • Recreate old pg_repack extension to update
      • Notes: if pg_repack is installed
    • Notes: if there are no old extensions, print message:
      • "The extension versions are up-to-date for the database. No update is required."
• Perform Post-Checks
  • Make sure that physical replication is active
    • Note: if no active replication connections found, print error message: "No active replication connections found. Please check the replication status and PostgreSQL logs."
    • Create a table "test_replication" with 10000 rows on the primary
    • Wait until the PostgreSQL replica is synchronized (max wait time: 2 minutes)
    • Drop a table "test_replication"
    • Print the result of checking the number of records
    • if the number of rows match, print info message: "The PostgreSQL replication is OK. The number of records in the 'test_replication' table is the same as on the primary."
    • if the number of rows does not match, print error message: "The number of records in the 'test_replication' table does not match the primary. Please check the replication status and PostgreSQL logs."
• Perform Post-Upgrade tasks
  • Perform tasks for blue-green upgrade method
    • Note: If the pg_logical_upgrade.yml playbook is used
    • Reset the wal_keep_segments/wal_keep_size parameter to original state on the source primary
  • Ensure the current data directory is the new data directory
    • Notes: to prevent deletion the old directory if it is used
  • Delete the old PostgreSQL data directory
    • Notes: perform pg_dropcluster for Debian based
  • Delete the old PostgreSQL WAL directory
    • Notes: if 'pg_new_wal_dir' is defined
  • Remove old PostgreSQL packages
    • Notes: if 'pg_old_packages_remove' is 'true'
  • Remove temporary local hba rule from pg_hba.conf
    • Notes: if it has been changed
    • Update the PostgreSQL configuration
  • pgBackRest (if 'pgbackrest_install' is 'true')
    • Check pg-path option
    • Update pg-path in pgbackrest.conf
    • Upgrade stanza
  • WAL-G (if 'wal_g_install' is 'true')
    • Update PostgreSQL data directory path in .walg.json
    • Update PostgreSQL data directory path in cron jobs
  • Wait for the analyze to complete.
    • Notes: max wait time: 1 hour ('vacuumdb_analyze_timeout' variable)
  • Check the Patroni cluster state
  • Check the current PostgreSQL version
  • Print info messages
    • List the Patroni cluster members
    • Upgrade completed

Switchover plan Copy Chapter

Note: for blue-green upgrade method (pg_logical_switchover playbook)

• Perform pre-checks
  • Ensure logical replication lag is no more than max_replication_lag_bytes (default: 10485760)
  • Print logical replication lag
• Prepare pgbouncer configuration
  • Note: if pgbouncer_install is true
  • Prepare PgBouncer configuration to redirect primary traffic
    • Note: replace the 'host=' option value with the target primary host address
  • Prepare PgBouncer configuration to redirect replica traffic
    • Note: replace the 'host=' option value with target hosts addresses using deterministic mapping by replica index; if target has no replicas, use target primary
  • Temporarily disable TLS from PgBouncer to Postgres during redirect
    • Note: if self-signed certificates are used (tls_cert_generate = true), target cluster uses a different CA, so PgBouncer on source may fail to verify TLS when connecting to Postgres on target
• Increase all sequence values
  • Get sequence values for each database
  • Increase sequence values for each database
  • Note: Add + pg_sequences_increase_value to current value (default: 1000000)
• Wait for a window with low replication lag
  • Ensure logical replication lag is no more than pg_switchover_max_replication_lag_bytes (default: 16777216)
• Enable read-only mode on the source cluster
  • Set default_transaction_read_only = 'on'
  • Reload PostgreSQL configuration
• Wait until there is no replication lag
  • Wait until the lag is 0 bytes
  • or no more than pg_switchover_force_mode_lag_bytes (default: 1048576) if force mode is enabled (pg_switchover_force_mode)
• Delete the previous subscription
  • Drop subscription in each database
• Create publication and slot for reverse replication
  • Create publication and slot for each database
• Redirect database traffic to the target cluster
  • Note: if pgbouncer_install is true
  • Restart pgbouncer service to apply changes
• Disable read-only mode on the source cluster
  • Reset default_transaction_read_only option
  • Reload PostgreSQL configuration
• Start reverse logical replication
  • Create subscription in each database
  • Make sure that logical replication is active
  • Check the logical replication lag
• Print info messages
  • Switchover completed
  • Final step: switch application services to the target cluster

Dependencies Copy Chapter

This role depends on:

• vitabaks.autobase.common - Provides common variables and configurations

File: automation/roles/update/README.md Copy Chapter

Update the PostgreSQL Cluster Copy Chapter

This role is designed to update the PostgreSQL Cluster to a new minor version (for example, 17.1->17.2, and etc).

By default, only PostgreSQL packages defined in the postgresql_packages variable are updated. In addition, you can update Patroni or the entire system.

Usage

Update PostgreSQL:

ansible-playbook update_pgcluster.yml

Update Patroni:

ansible-playbook update_pgcluster.yml -e target=patroni

Update all system packages:

ansible-playbook update_pgcluster.yml -e target=system

Variables

• target
  • Defines the target for the update.
  • Available values: 'postgres', 'patroni', 'system'
  • Default value: postgres
• max_replication_lag_bytes
  • Determines the size of the replication lag above which the update will not be performed.
  • Note: If the lag is high, you will be prompted to try again later.
  • Default value: 10485760 (10 MiB)
• max_transaction_sec
  • Determines the maximum transaction time, in the presence of which the update will not be performed.
  • Note: If long-running transactions are present, you will be prompted to try again later.
  • Default value: 15 (seconds)
• update_extensions
  • Attempt to automatically update all PostgreSQL extensions in all databases.
  • Note: Specify 'false', to avoid updating extensions.
  • Default value: true
• reboot_host_after_update
  • Restart the server if it is required after the update.
  • Default value: true
• reboot_host_timeout
  • Maximum seconds to wait for machine to reboot and respond to a test command.
  • Default value: 1800 (30 minutes)
• reboot_host_post_delay
  • The waiting time (in minutes) for the caches to warm up after restarting the server before updating the next server.
  • Note: Applicable when there are multiple replicas.
  • Default value: 5 (minutes).

Plan: Copy Chapter

Note: About the expected downtime of the database during the update:

When using load balancing for read-only traffic (the "Type A" and "Type C" schemes), zero downtime is expected (for read traffic), provided there is more than one replica in the cluster. For write traffic (to the Primary), the expected downtime is ~5-10 seconds.

1. PRE-UPDATE: Perform pre-update tasks

• Test PostgreSQL DB Access
• Make sure that physical replication is active
  • Stop, if there are no active replicas
• Make sure there is no high replication lag
  • Note: no more than max_replication_lag_bytes
  • Stop, if replication lag is high
• Make sure there are no long-running transactions
  • no more than max_transaction_sec
  • Stop, if long-running transactions detected
• Update the pgBackRest package on the backup server (Dedicated Repository Host).
  • Note: This task runs only if the backup host is specified in the 'pgbackrest' group in the inventory file, and the variable target is set to 'system'.

2. UPDATE: Secondary (one by one)

• Stop read-only traffic
  • Enable noloadbalance, nosync, nofailover parameters in the patroni.yml
  • Reload patroni service
  • Make sure replica endpoint is unavailable
  • Wait for active transactions to complete
• Stop Services
  • Execute CHECKPOINT before stopping PostgreSQL
  • Stop Patroni service on the replica
• Update PostgreSQL
  • if target variable is not defined or target=postgres
  • Install the latest version of PostgreSQL packages
• Update Patroni
  • if target=patroni (or system)
  • Install the latest version of Patroni package
• Update all system packages (includes PostgreSQL and Patroni)
  • if target=system
  • Update all system packages
• Start Services
  • Start Patroni service
  • Wait for Patroni port to become open on the host
  • Check that the Patroni is healthy
  • Check PostgreSQL is started and accepting connections
• Start read-only traffic
  • Disable noloadbalance, nosync, nofailover parameters in the patroni.yml
  • Reload patroni service
  • Make sure replica endpoint is available
  • Wait N minutes for caches to warm up after reboot
    • Note: variable reboot_host_post_delay
• Perform the same steps for the next replica server.

3. UPDATE: Primary

• Switchover Patroni leader role
  • Perform switchover of the leader for the Patroni cluster
  • Make sure that the Patroni is healthy and is a replica
    • Notes:
      • At this stage, the leader becomes a replica
      • the database downtime is ~5 seconds (write traffic)
• Stop read-only traffic
  • Enable noloadbalance, nosync, nofailover parameters in the patroni.yml
  • Reload patroni service
  • Make sure replica endpoint is unavailable
  • Wait for active transactions to complete
• Stop Services
  • Execute CHECKPOINT before stopping PostgreSQL
  • Stop Patroni service on the leader
• Update PostgreSQL
  • if target variable is not defined or target=postgres
  • Install the latest version of PostgreSQL packages
• Update Patroni
  • if target=patroni (or system)
  • Install the latest version of Patroni package
• Update all system packages (includes PostgreSQL and Patroni)
  • if target=system
  • Update all system packages
• Start Services
  • Start Patroni service
  • Wait for Patroni port to become open on the host
  • Check that the Patroni is healthy
  • Check PostgreSQL is started and accepting connections
• Start read-only traffic
  • Disable noloadbalance, nosync, nofailover parameters in the patroni.yml
  • Reload patroni service
  • Make sure replica endpoint is available

4. POST-UPDATE: Update extensions

• Update extensions
  • Get the current Patroni Cluster Leader Node
  • Get a list of databases
  • Update extensions in each database
    • Get a list of old PostgreSQL extensions
    • Update old PostgreSQL extensions (if an update is required)
• Check the Patroni cluster state
• Check the current PostgreSQL version
• List the Patroni cluster members
• Update completed.

Dependencies Copy Chapter

This role depends on:

• vitabaks.autobase.common - Provides common variables and configurations
• vitabaks.autobase.patroni - Uses the switchover task

File: automation/roles/transparent_huge_pages/README.md Copy Chapter

Ansible Role: transparent_huge_pages

Disables Linux Transparent Huge Pages (THP) for better PostgreSQL performance. The role creates and enables a systemd service that sets THP and defrag to "never".

Role Variables Copy Chapter

Notes Copy Chapter

• Idempotent; updates the unit at /etc/systemd/system/disable-transparent-huge-pages.service
• Not executed on virtualization types: container, docker, lxc, podman.

Dependencies Copy Chapter

This role depends on:

• vitabaks.autobase.common - Provides common variables and configurations

File: automation/roles/tls_certificate/README.md Copy Chapter

Ansible Role: tls_certificate

Generates and manages TLS certificates for the cluster. Creates a self-signed CA and server certificate/key (with SANs), and optionally distributes them to all nodes.

How it works Copy Section

• Generation (tasks/main.yml):
  • Creates CA (ca.crt/ca.key) and server cert/key (server.crt/server.key) under tls_dir.
  • SANs are auto-built from hostnames/FQDNs/IPs of hosts in tls_group_name unless tls_subject_alt_name variable is provided.
  • Skips if files exist; override with tls_cert_regenerate/tls_ca_regenerate.
• Distribution (tasks/copy.yml):
  • Reads files from the first host in tls_group_name (or master by default) and copies to all nodes with desired owner.

Main Role Variables Copy Chapter

Additional Variables Copy Chapter

Notes:

• SAN auto-detection uses one of: etcd_bind_address, consul_bind_address, patroni_bind_address, or bind_address, depending on tls_group_name.
• Generation runs on groups[tls_group_name][0]; copy slurps from that host (default group “master” in copy.yml if tls_group_name is unset).

Dependencies Copy Chapter

This role depends on:

• vitabaks.autobase.common - Provides common variables and configurations

File: automation/roles/timezone/README.md Copy Chapter

Ansible Role: timezone

Sets the system timezone on target hosts. Installs tzdata when using package-based installations and applies the timezone via community.general.timezone module.

Role Variables Copy Chapter

Note: The role runs only when timezone variable is non-empty.

Dependencies Copy Chapter

This role depends on:

• vitabaks.autobase.common - Provides common variables and configurations

File: automation/roles/sysctl/README.md Copy Chapter

Ansible Role: sysctl

Configures Linux kernel parameters via sysctl for PostgreSQL and related components. The role composes parameters dynamically from group-based mappings and applies them persistently.

Description Copy Chapter

• Collects sysctl entries from sysctl_conf for all groups the host belongs to.
• Flattens and de-duplicates entries, then applies them via ansible.posix.sysctl.
• Persists settings under sysctl_file and reloads sysctl.

Role Variables Copy Chapter

Notes on sysctl_conf:

• Example structure (see roles/common/defaults/main.yml for the full list and defaults):
  • postgres_cluster: includes memory, networking, and scheduler-tuning (e.g., vm.swappiness, net.core.somaxconn, etc.).
  • balancers: network tuning for HAProxy hosts.
  • etcd_cluster/consul_instances: I/O and network tuning for DCS nodes.
• You may add your own group keys; the role automatically picks them up if the host is a member of those groups.

Example:

sysctl_set: true
sysctl_file: /etc/sysctl.d/autobase.sysctl.conf
sysctl_conf:
  etcd_cluster: []
  master: []
  replica: []
  postgres_cluster:
    - { name: "vm.overcommit_memory", value: "2" }
    - { name: "vm.swappiness", value: "1" }
    - { name: "vm.min_free_kbytes", value: "102400" }
    - { name: "vm.dirty_expire_centisecs", value: "1000" }
    - { name: "vm.dirty_background_bytes", value: "67108864" }
    - { name: "vm.dirty_bytes", value: "536870912" }
    - { name: "vm.nr_hugepages", value: "9510" }  # ~18GB
    - { name: "vm.zone_reclaim_mode", value: "0" }
    - { name: "kernel.numa_balancing", value: "0" }
    - { name: "kernel.sched_autogroup_enabled", value: "0" }
    - { name: "net.ipv4.ip_nonlocal_bind", value: "1" }
    - { name: "net.ipv4.ip_forward", value: "1" }
    - { name: "net.ipv4.ip_local_port_range", value: "10000 65535" }
    - { name: "net.core.netdev_max_backlog", value: "10000" }
    - { name: "net.ipv4.tcp_max_syn_backlog", value: "8192" }
    - { name: "net.core.somaxconn", value: "65535" }
    - { name: "net.ipv4.tcp_tw_reuse", value: "1" }
  balancers:
    - { name: "net.ipv4.ip_nonlocal_bind", value: "1" }
    - { name: "net.ipv4.ip_forward", value: "1" }
    - { name: "net.ipv4.ip_local_port_range", value: "10000 65535" }
    - { name: "net.core.netdev_max_backlog", value: "10000" }
    - { name: "net.ipv4.tcp_max_syn_backlog", value: "8192" }
    - { name: "net.core.somaxconn", value: "65535" }
    - { name: "net.ipv4.tcp_tw_reuse", value: "1" }

Dependencies Copy Chapter

This role depends on:

• vitabaks.autobase.common - Provides common variables and configurations

File: automation/roles/swap/README.md Copy Chapter

Ansible Role: swap

Manages system swap via a swap file. Creates, resizes, or removes the swap file when needed, adds it to /etc/fstab, and enables it with swapon.

Role Variables Copy Chapter

Notes:

• Skips on virtualization types: container, docker, lxc, podman.

Dependencies Copy Chapter

This role depends on:

• vitabaks.autobase.common - Provides common variables and configurations