TimescaleDB Streaming Replication

This guide will lead you through setting up your first TimescaleDB streaming replication cluster (primary-replica) with PgBouncer as a load balancer using ClusterControl. By following this tutorial, you'll have a fully functional database cluster that you can effortlessly monitor, manage, and scale.

TimescaleDB Streaming Replication is a built-in feature that allows data from a primary TimescaleDB server to be streamed in real-time to one or more replica servers. This architecture improves read scalability, provides data redundancy, and enhances high availability when combined with a failover mechanism.

Prerequisites

Ensure the following requirements are met before proceeding:

ClusterControl is installed and running. If not, follow the instructions in Quickstart or use the Installer Script.
At least four hosts (bare-metal or virtual machines) with a supported OS:
- One host for the TimescaleDB primary server
- Two hosts for the TimescaleDB replica servers
- One host for the PgBouncer load balancer
SSH access to all servers involved.
All nodes must have internet access during the deployment stage.
NTP is configured and running on all hosts to keep their clocks synchronized.

Architecture

Below is a simplified diagram of the final architecture:

flowchart TB
   a{{**Users/<br>clients/<br>apps**}} -->|RW - 6432 - PgBouncer<br>RW - 5432 - Direct| d[(**db1**<br>Primary + PgBouncer<br>192.168.99.101)] 
   a -->|RO| e[(**db2**<br>Replica<br>192.168.99.102)] & f[(**db3**<br>Replica<br>192.168.99.103)]
   d -.-> |streaming replication| e & f
   d & e & f -.-|manages| y[/**ClusterControl server**<br>192.168.99.5/]
    subgraph Streaming Replication
        d
        e
        f
    end

Step 1: Set up SSH key-based authentication

On the ClusterControl server, generate a new SSH key as the root user:
```
ssh-keygen -t rsa
```

Copy the SSH public key to all target nodes:

ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # lb1
ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # db1
ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # db2
ssh-copy-id -i /root/.ssh/id_rsa [email protected]  # db3

Test connectivity from the ClusterControl server to each host:

ssh [email protected] "ls /root"
ssh [email protected] "ls /root"
ssh [email protected] "ls /root"
ssh [email protected] "ls /root"

Step 2: Deploy a new cluster

Open your browser and navigate to your ClusterControl server’s IP or domain name.
From the ClusterControl dashboard, click on Deploy a cluster in the top-right corner and choose Create a database cluster.
Choose "TimescaleDB" from the Database dropdown and the appropriate Vendor and Version (e.g., "TimescaleDB" and "16") from the wizard. Click Continue.
In the Deploy TimescaleDB wizard, configure the cluster as follows:
(1) Cluster details(2) SSH configuration(3) Node configuration(4) Add nodes(5) Extensions(6) Preview
Name: Enter a name for your cluster (e.g., My First TimescaleDB Cluster).

Tags: Enter one or more tags, separated by pressing "Enter" (e.g., production, replication, dc1).
SSH user: root

SSH user key path: /root/.ssh/id_rsa

SSH port: 22

SSH sudo password: (leave blank if using key-based auth)

Install software: On (default)

Disable firewall: Checked (default)

Disable SELinux/AppArmor: Checked (default)
Server data directory: /var/lib/pgsql (default varies by distro)

Server port: 5432 (default)

User: postgres

Password: Enter a secure password for the TimescaleDB postgres superuser

Repository: Use vendor repositories (default)

Enable SSL encryption: On (default)
Primary node:

Specify "192.168.99.101" as the primary node and press Enter.

Replica node:

Specify "192.168.99.102" as replica and press Enter.

Specify "192.168.99.103" as another replica and press Enter.

Wait until everything is green. If a red warning appears, inspect the error and fix it.

Turn "On" Synchronous Replication if required.

Proceed only if all nodes are reachable (shown in green).
Leave the toggle button "Off" if you don't require the extensions, else turn "On" "pgvector" or "PostGIS" if you want to enable them.
Review the summary. You can go back to modify any configuration. The settings persist until you exit the wizard.
Click Finish to start the deployment.
ClusterControl will provision your TimescaleDB streaming replication cluster. Monitor progress from Activity center. This process can take a few minutes. Once complete, the cluster appears on the Home page.

Step 3: Monitor your cluster

After deployment, the Home page provides an overview of your cluster's health, node status, alarms, automatic recovery status, and topology visualization.

Step 4: Deploy PgBouncer

Integrating PgBouncer with TimescaleDB improves connection pooling and scalability. Follow these steps to deploy PgBouncer with ClusterControl:

Navigate to ClusterControl → Clusters → locate the cluster → Actions → Add new → Load balancer → Create balancer → PgBouncer → Continue.
In the Create a load balancer wizard, configure PgBouncer:
(1) Authentication(2) Add nodes(4) Preview
PgBouncer admin user: Enter a PgBouncer admin user. For example: pgbouncer.

PgBouncer admin password: : Enter the password.
PgBouncer node: Select the TimescaleDB node from the dropdown menu and wait until the status turned green.

Listen port: 6432 (default).
Review the summary. You can go back to any previous section to modify configurations if needed.
Click Finish to deploy PgBouncer.
ClusterControl provisions the PgBouncer instance. Monitor the progress from Activity center. Once done, the load balancer appears under Nodes.

Step 5: Creating connection pool for PgBouncer

Now that the PgBouncer is deployed, the next step is to create the connection pool. To create a connection pool:

Navigate to ClusterControl → Clusters → Nodes, click on the node that is showing PgBouncer under Type category.
In the Nodes details page, click on Create new pool:
In the Create new pool wizard, configure connection pool:
Create new pool
PgBouncer host name: Pick the PgBouncer node.

Pool Name: : Enter connection pool name.

Username: select the database user from the dropdown menu.

Pool Mode: Select one of the following modes:

session: Releases server after client disconnects.

transaction: Releases server after transaction completes.

statement: Releases server after each query. Multi-statement transactions are not allowed in this mode.

Pool Size: Leave empty to use the default.

Max Connections: Enter the maximum number of connections.
Click Add to create the connection pool.

Once created, PgBouncer will begin managing connections based on your configured pool parameters.

Step 6: Connecting to the database cluster

For read-write workloads, connect to the primary TimescaleDB serrver, for example:

psql -U postgres -h 192.168.99.101 -p 5432

Step 7: Connecting to the database cluster via PgBouncer

After creating a connection pool in ClusterControl, you can connect to that specific pool using the format:

psql -U <username> -h <pgbouncer_host> -p <pgbouncer_port> -d <pool_name>

Example: If you created a pool named app_pool for user appuser, and PgBouncer runs on 192.168.99.101:

psql -U appuser -h 192.168.99.101 -p 6432 -d app_pool

Important

The -d parameter must match the pool name you defined during pool creation (not necessarily the actual database name).

Parameters:

U: The database user assigned to the pool
h: PgBouncer host/IP
p: PgBouncer listening port (default: 5432)
d: The pool name (acts like the database name to PgBouncer)

Step 8: Enable automatic backups

To protect your data, set up automatic backups. ClusterControl supports both logical (pg_dump) and physical (pg_basebackup, pgBackRest) backups for TimescaleDB.

Go to ClusterControl → Clusters → choose the cluster → Backups to open the backup overview.
Go to Create Backup → Schedule a Backup. The backup wizard appears.
In the Create a backup schedule wizard:
(1) Configuration(2) Advanced settings(3) Storage(4) Schedule(5) Preview
Schedule name: e.g., Daily Backup

Cluster: (auto-selected)

Backup host: db3 (select one of the replicas to offload backup load)

Backup method: pg_dump or pg_basebackup

PITR enabled: On (default for other than pgbasebackup)

Upload backup to cloud: Off (default, can be enabled if needed)
Compression: On

Compression level: 6

Enable encryption: Off or On (optional)

Verify backup: Off (default)

Retention: On, configure how many backups to keep

Failover backup if node is down: Off (default)
Storage location: Store on controller (default)

Storage directory: /root/backups (default)

Backup subdirectory: BACKUP-%I (default)
Set backup schedule: Simple

Every: day at 2:00

Timezone: Your preferred timezone
Review the summary. You can always go back to any previous section to modify settings.
Click Create to set the backup schedule.

ClusterControl will handle the scheduled backups automatically. Advanced backup features like partial backups, encryption, verification, throttling, and point-in-time recovery can also be configured.

Step 9: Configure alerts

To keep track of any issues or incidents in your cluster, it's important to set up alerting. ClusterControl supports sending alarms and alerts to email, web hooks and third-party notification services like Slack or Telegram. In this example, we are going to use email.

Firstly, configure the mail server. Go to ClusterControl GUI → Settings → You don't have a mail server configured. Configure now → SMTP Server. Fill up all necessary information about the SMTP server. You can also opt for Sendmail, however a mail transfer agent (sendmail, postfix or exim) must be installed on the ClusterControl server.

Once configured, we can configure the alert and recipients as below:

Go to ClusterControl GUI → choose the cluster → Settings → Email Notifications.
Choose a user group where you are belong to from the User group dropdown.
Choose your email address to from the Users in the selected group dropdown.
Click Enable.
Set the Digest delivery time when you want a digested (summarized events) to be sent to you every day.
Set all Critical events to "Deliver" (default), all Warning events to "Digest" and you may ignore the Info events.

This ensures you are always notified when critical issues arise and can act quickly to resolve them. You will also get an email with summarized warning events every day at the configured delivery time.

Tip

You can also configure alarms to be sent to third-party notification systems (Slack, Telegram), incident management systems (PagerDuty, ServiceNow, OpsGenie) or web hooks. See Integration → Notification Services.

Step 10: Manage your cluster

ClusterControl offers many features for ongoing management:

PostgreSQL upgrades: Perform rolling version upgrade to keep the cluster up-to-date. This feature is available at ClusterControl GUI → choose cluster → Actions → Upgrades.
Node management: Start/stop/restart a node, promote a replica to primary, reboot host, etc. These features are available at ClusterControl GUI → choose cluster → Nodes → Actions.
Connection pool management: Manage and configure PgBouncer connection pools and databases. These features are available at ClusterControl GUI → choose cluster → Nodes → Actions (for PgBouncer nodes).
Configuration management: Adjust TimescaleDB settings globally or on individual nodes. This feature is available at ClusterControl GUI → choose cluster → Manage → Configuration.
User management: Manage TimescaleDB roles and privileges. These features are available at ClusterControl GUI → choose cluster → Manage → DB Users.
Backup management: Create, schedule, restore, perform point-in-time recovery, upload backups to cloud, verify backups, and set retention. These features are available at ClusterControl GUI → choose cluster → Backups.
Maintenance mode: Activate or schedule maintenance for nodes. These features are available at ClusterControl GUI → choose cluster → Nodes → Actions → Schedule maintenance.
SSH console: Directly access nodes via a web-based SSH console. This feature is available at ClusterControl GUI → choose cluster → Nodes → Actions → SSH Console.

Conclusion

Congratulations! You have successfully deployed a TimescaleDB streaming replication with PgBouncer as a load balancer using ClusterControl. Explore additional features such as automatic failover, monitoring, and backup strategies to enhance your database's reliability and performance.