Odoo PostgreSQL Replication with Docker

Unlock Powerful Odoo PostgreSQL Replication with Docker: Your Ultimate 5-Step Guide to High Availability!

When running mission-critical applications like Odoo, ensuring data integrity, high availability, and optimal performance isn’t just a best practice—it’s a necessity. A single point of failure in your database can lead to costly downtime, lost data, and frustrated users. This is precisely where Odoo PostgreSQL Replication Docker comes into play, offering a robust solution to these challenges.

This comprehensive guide will walk you through setting up Odoo with PostgreSQL streaming replication using Docker Compose, providing you with a scalable and resilient infrastructure. You’ll learn how to leverage Docker to simplify this complex setup, making your Odoo instance more reliable than ever.

For a live demonstration of some of these concepts, you can refer to the original video that inspired this guide: Odoo 19 w⧸ PostgreSQL Replication Demo

Why Embrace Odoo PostgreSQL Replication Docker? The Unbeatable Advantages

Implementing Odoo PostgreSQL Replication Docker brings a multitude of benefits that directly impact the reliability and performance of your Odoo deployment. Let’s delve into why this setup is indispensable for modern businesses.

1. High Availability (HA): In a replicated setup, if your primary PostgreSQL database server fails, a replica can quickly take over, minimizing downtime. This dramatically reduces the risk of service interruption, ensuring your Odoo instance remains accessible when you need it most. This is crucial for maintaining business continuity and avoiding costly outages.
2. Scalability for Read Operations: Odoo applications, especially those with heavy reporting, e-commerce fronts, or many active users, can generate a significant number of read queries. By offloading these read operations to one or more replica servers, you reduce the load on your primary database. This allows your primary to focus on write-intensive tasks, leading to better overall performance and responsiveness across your Odoo system. This approach allows your system to handle more concurrent users and complex queries without degradation.
3. Data Redundancy and Disaster Recovery: Replication creates multiple copies of your data across different servers. In the event of data corruption or hardware failure on the primary, you have up-to-date backups readily available on your replicas. This forms a critical component of any robust disaster recovery strategy, safeguarding your invaluable business data.
4. Simplified Management with Docker: Docker and Docker Compose streamline the deployment and management of complex multi-service applications. Instead of manually configuring PostgreSQL, Odoo, and their interactions on individual servers, Docker containers encapsulate each component. This ensures consistency across environments (development, staging, production) and simplifies scaling and updates. Managing your entire Odoo PostgreSQL Replication Docker stack becomes a matter of a few commands.
5. Performance Improvement: Beyond read scalability, distributing the database load can lead to a snappier Odoo experience. Queries are processed faster, user interfaces are more responsive, and batch operations complete in less time. This directly contributes to a better user experience and increased operational efficiency.

By combining Odoo, PostgreSQL, and Docker, you’re building a foundation that is not only powerful and efficient but also inherently resilient. This strategy ensures your Odoo system can grow and adapt to the evolving demands of your business without compromising on performance or reliability.

Prerequisites for Your Odoo PostgreSQL Replication Docker Setup

Before diving into the configuration, ensure you have the following essentials in place:

• Docker: The core containerization platform. Make sure it’s installed and running on your system. You can download Docker Desktop from the official Docker website.
• Docker Compose: A tool for defining and running multi-container Docker applications. It typically comes bundled with Docker Desktop.
• Git (Optional but Recommended): To clone repositories or manage your configuration files.
• Basic Understanding of Odoo: Familiarity with Odoo’s server commands and configuration.
• Basic Understanding of PostgreSQL: Awareness of database users, roles, and configuration files.
• A Project Directory: Create an empty directory where you’ll store your docker-compose.yml, odoo.conf, and replication scripts.

Step-by-Step Tutorial: Implementing Odoo PostgreSQL Replication Docker

This tutorial will guide you through setting up your Odoo PostgreSQL Replication Docker environment. We’ll define a primary PostgreSQL server, a streaming replica, and an Odoo instance connecting to the primary for writes and leveraging the replica for reads.

Step 1: Prepare Your Project Structure and Docker Compose File

First, create a project directory and set up the necessary subdirectories.

mkdir odoo-pg-replication
cd odoo-pg-replication
mkdir config scripts addons

Now, create a docker-compose.yml file in your odoo-pg-replication directory. This file defines all the services (Odoo, PG Primary, PG Replica) and their interconnections.

docker-compose.yml:

version: '3.8'

services:
  pgprimary:
    image: postgres:16 # Using a recent, stable PostgreSQL version
    hostname: pgprimary
    environment:
      POSTGRES_DB: odoo_db
      POSTGRES_USER: odoo
      POSTGRES_PASSWORD: odoo_password
      PGDATA: /var/lib/postgresql/data/pgdata
      POSTGRES_HOST_AUTH_METHOD: trust # For simplicity in demo, use password for production
    ports:
      - "5433:5432" # Primary listens on 5432 inside, mapped to 5433 outside for direct access
    volumes:
      - pg_primary_data:/var/lib/postgresql/data/pgdata
      - ./scripts/init-primary.sh:/docker-entrypoint-initdb.d/init-primary.sh:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -h localhost"]
      interval: 5s
      timeout: 5s
      retries: 5
    restart: unless-stopped

  pgreplica:
    image: postgres:16
    hostname: pgreplica
    environment:
      POSTGRES_DB: odoo_db
      POSTGRES_USER: odoo
      POSTGRES_PASSWORD: odoo_password
      PGDATA: /var/lib/postgresql/data/pgdata
      PGPRIMARYHOST: pgprimary # Connect to primary by service name within the Docker network
      PGPRIMARYPORT: 5432
      PGREPLICATIONUSER: repl_user # User created specifically for replication
      PGREPLICATIONPASSWORD: repl_password_secure
      POSTGRES_HOST_AUTH_METHOD: trust
    ports:
      - "5434:5432" # Replica listens on 5432 inside, mapped to 5434 outside for direct access
    volumes:
      - pg_replica_data:/var/lib/postgresql/data/pgdata
      - ./scripts/init-replica.sh:/docker-entrypoint-initdb.d/init-replica.sh:ro
    depends_on:
      pgprimary:
        condition: service_healthy
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -h localhost"]
      interval: 5s
      timeout: 5s
      retries: 5
    restart: unless-stopped

  odoo:
    image: odoo:17 # Using Odoo 17, compatible with PostgreSQL 16
    environment:
      HOST: pgprimary # Odoo connects to the primary database
      PORT: 5432    # Internal PostgreSQL port
      USER: odoo
      PASSWORD: odoo_password
      ADDONS_PATH: /mnt/extra-addons
    depends_on:
      pgprimary:
        condition: service_healthy # Odoo depends on the primary being healthy
    ports:
      - "8069:8069" # Odoo web interface
    volumes:
      - odoo_data:/var/lib/odoo
      - ./config/odoo.conf:/etc/odoo/odoo.conf:ro # Custom Odoo configuration
      - ./addons:/mnt/extra-addons # Where your custom Odoo modules live
    command: ["odoo", "-c", "/etc/odoo/odoo.conf"]
    restart: unless-stopped

volumes:
  pg_primary_data:
  pg_replica_data:
  odoo_data:

Step 2: Configure PostgreSQL Replication Scripts

Next, create the necessary initialization scripts in the scripts directory. These scripts ensure PostgreSQL is correctly configured for streaming replication.

scripts/init-primary.sh:

#!/bin/bash
set -e

echo "Configuring pgprimary for replication..."

# Append replication settings to postgresql.conf if not already present
grep -qxF 'wal_level = replica' /var/lib/postgresql/data/pgdata/postgresql.conf || echo "wal_level = replica" >> /var/lib/postgresql/data/pgdata/postgresql.conf
grep -qxF 'max_wal_senders = 10' /var/lib/postgresql/data/pgdata/postgresql.conf || echo "max_wal_senders = 10" >> /var/lib/postgresql/data/pgdata/postgresql.conf
grep -qxF 'hot_standby = on' /var/lib/postgresql/data/pgdata/postgresql.conf || echo "hot_standby = on" >> /var/lib/postgresql/data/pgdata/postgresql.conf
grep -qxF 'listen_addresses = '*'' /var/lib/postgresql/data/pgdata/postgresql.conf || echo "listen_addresses = '*'" >> /var/lib/postgresql/data/pgdata/postgresql.conf

# Add host-based authentication for replication user (repl_user)
# Check if the line already exists to prevent duplicates
if ! grep -q "host replication repl_user 0.0.0.0/0 md5" /var/lib/postgresql/data/pgdata/pg_hba.conf; then
  echo "host replication repl_user 0.0.0.0/0 md5" >> /var/lib/postgresql/data/pgdata/pg_hba.conf
fi

echo "pgprimary configuration complete."

scripts/init-replica.sh:

#!/bin/bash
set -e

echo "Initializing pgreplica from pgprimary..."

# Ensure we wait for the primary to be fully ready before attempting basebackup
echo "Waiting for pgprimary to be healthy..."
until pg_isready -h "$PGPRIMARYHOST" -p "$PGPRIMARYPORT" -U "$PGREPLICATIONUSER"; do
  echo "Primary not yet ready for replication. Retrying in 2 seconds..."
  sleep 2
done
echo "pgprimary is ready. Proceeding with replica initialization."

# Check if PGDATA already contains data. If so, and it's not a replica, remove and re-initialize.
if [ -s "$PGDATA/postgresql.conf" ] && ! grep -q "primary_conninfo" "$PGDATA/postgresql.conf"; then
    echo "Existing PostgreSQL data found. Removing and re-initializing as replica."
    rm -rf "$PGDATA"/*
fi

# Use pg_basebackup to initialize the replica from the primary
if [ ! -s "$PGDATA/postgresql.conf" ]; then
    echo "Performing pg_basebackup from $PGPRIMARYHOST:$PGPRIMARYPORT..."
    PGPASSWORD="$PGREPLICATIONPASSWORD" pg_basebackup -h "$PGPRIMARYHOST" -p "$PGPRIMARYPORT" -U "$PGREPLICATIONUSER" -D "$PGDATA" -F p -Xs -P -R -w
    echo "pg_basebackup complete."

    # Append hot_standby and listen_addresses to postgresql.conf
    echo "hot_standby = on" >> /var/lib/postgresql/data/pgdata/postgresql.conf
    echo "listen_addresses = '*'" >> /var/lib/postgresql/data/pgdata/postgresql.conf

    # Ensure permissions are correct
    chown -R postgres:postgres "$PGDATA"
    chmod -R 0700 "$PGDATA"
else
    echo "Replica data already exists. Skipping pg_basebackup."
fi

echo "pgreplica initialization complete."

Step 3: Configure Odoo

Create an odoo.conf file in the config directory. This tells Odoo how to connect to the PostgreSQL primary.

config/odoo.conf:

[options]
; Odoo connects to the primary PostgreSQL server by its service name.
db_host = pgprimary
db_port = 5432
db_user = odoo
db_password = odoo_password
addons_path = /mnt/extra-addons
; Uncomment and adjust if you have specific Odoo server settings
; workers = 0
; log_level = info
; logfile = /var/log/odoo/odoo.log
; xmlrpc_port = 8069

Step 4: Launching Your Odoo PostgreSQL Replication Docker Stack

With all configuration files in place, you can now bring up your services.

1. Clean up any old Docker volumes (optional, but good for fresh starts):

   docker compose down -v
   

2. Start your Docker containers in detached mode:

   docker compose up -d
   

   This command will build the images (if not cached), start pgprimary, then pgreplica, and finally the odoo service.

3. Create the PostgreSQL replication user:
   The init-primary.sh script prepares the primary for replication, but the repl_user needs to be explicitly created. Execute this command against your pgprimary container. Find the container ID or name (e.g., odoo-pg-replication-pgprimary-1) using docker ps.

   docker exec -it odoo-pg-replication-pgprimary-1 psql -U postgres -c "CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'repl_password_secure';"
   

   Ensure the password matches PGREPLICATIONPASSWORD in docker-compose.yml.

4. Restart pgreplica to pick up the new user and initiate replication:

   docker compose restart pgreplica
   

   The pgreplica service will now connect to the primary using the repl_user and start streaming replication.

Step 5: Verify Your Odoo PostgreSQL Replication Docker Setup

Now, let’s confirm that our Odoo PostgreSQL Replication Docker setup is functioning as expected, with Odoo writing to the primary and reads being handled by the replica.

1. Access Odoo:
   Open your web browser and navigate to http://localhost:8069. You should see the Odoo setup page or login screen.

   • Create a new database (if none exists) and log in as admin/admin.
   • Install a module, for instance, the “Sales” module. This will generate database write operations.

2. Monitor PostgreSQL Logs:
   You can watch the logs of both primary and replica to see activity.

   • For primary: docker compose logs -f pgprimary
   • For replica: docker compose logs -f pgreplica

   You should see LOG: database system is a standby in the replica logs and LOG: starting replication WAL sender in the primary logs, confirming replication is active.

3. Demonstrate Read/Write Separation:
   This is the core of verifying Odoo PostgreSQL Replication Docker. Odoo’s default behavior is to write to the primary database specified in its configuration (db_host = pgprimary). To leverage read replicas, Odoo typically needs to be configured with a connection pooler or custom logic to direct read queries to replicas.

   • Create Data: Inside Odoo (connected to pgprimary), go to “Sales” -> “Products” and create a new product, e.g., “Test Product 001”. Save it. This is a write operation that goes to pgprimary.

   • Verify on Primary (via direct psql):

     docker exec -it odoo-pg-replication-pgprimary-1 psql -U odoo -d odoo_db -c "SELECT name FROM product_template WHERE name = 'Test Product 001';"
     

     You should see “Test Product 001” listed.

   • Verify on Replica (via direct psql):

     docker exec -it odoo-pg-replication-pgreplica-1 psql -U odoo -d odoo_db -c "SELECT name FROM product_template WHERE name = 'Test Product 001';"
     

     You should also see “Test Product 001” listed, confirming that the data written to the primary has been replicated to the replica.

   • Understanding Odoo’s Read Replica Usage:
     While PostgreSQL streaming replication ensures data synchronization, Odoo itself doesn’t automatically direct read queries to replica servers out-of-the-box. The odoo.conf file only points to one db_host. To fully utilize read replicas for performance, you would typically implement one of the following:

     • Connection Pooler (e.g., PgBouncer): Configure PgBouncer to manage connections, directing writes to the primary and reads to the replicas based on query types. Your Odoo instance would then connect to PgBouncer.
     • Custom Odoo Logic: Develop custom modules or configurations within Odoo to dynamically switch between primary and replica connections for different types of database operations.

     For this tutorial, the primary goal is to establish the base Odoo PostgreSQL Replication Docker setup and confirm data sync. Implementing a connection pooler like PgBouncer would be the next logical step to fully leverage the read scalability benefits.

Advanced Considerations for a Production-Ready Setup

Building on your foundational Odoo PostgreSQL Replication Docker setup, here are crucial considerations for a production environment:

• PgBouncer Integration: As mentioned, a connection pooler like PgBouncer is vital. It centralizes database connections, reduces overhead, and allows for intelligent routing of read/write queries, truly maximizing the benefits of your Odoo PostgreSQL Replication Docker architecture. Odoo would connect to PgBouncer, and PgBouncer would distribute connections to your primary and replica(s).

• Automated Failover: Manual failover (promoting a replica to primary) is possible but prone to human error and slower. Solutions like Patroni or Corosync/Pacemaker can automate failover processes, ensuring minimal downtime in case of a primary server failure. This is critical for true high availability.

• Monitoring and Alerting: Implement robust monitoring for your PostgreSQL instances (primary and replica) and Odoo. Track metrics like replication lag, connection counts, disk I/O, CPU usage, and Odoo server performance. Tools like Prometheus and Grafana, or dedicated PostgreSQL monitoring solutions, are invaluable. Set up alerts for any anomalies.

• Backup and Recovery Strategy: While replication provides redundancy, it’s not a substitute for a comprehensive backup strategy. Regularly back up your primary database, and ideally, test your recovery process periodically. Consider tools like pg_basebackup (which we used for replica initialization) for full backups and WAL-E or Barman for continuous archiving and point-in-time recovery.

• Security:

  • Use strong, unique passwords for all database users (Odoo, replication, postgres superuser).
  • Do not use POSTGRES_HOST_AUTH_METHOD: trust in production; use md5 or scram-sha-256.
  • Limit network exposure of your database ports to only what’s necessary (e.g., only Odoo and PgBouncer containers, and potentially internal management IPs).
  • Keep your Docker images (Odoo, PostgreSQL) up to date with security patches.

• Resource Allocation: Ensure your Docker hosts have sufficient CPU, memory, and fast I/O for both Odoo and PostgreSQL. Proper resource allocation prevents performance bottlenecks.

• Persistent Volumes: Always use Docker volumes for your database data (pg_primary_data, pg_replica_data) and Odoo data (odoo_data). This ensures your data persists even if containers are removed or recreated.

Conclusion: Empower Your Odoo with Robust Replication

You’ve now successfully navigated the intricate process of setting up Odoo PostgreSQL Replication Docker. This architecture is a cornerstone for any serious Odoo deployment, providing the critical foundation for high availability, read scalability, and superior data redundancy. By leveraging the power of Docker Compose, you’ve streamlined a traditionally complex setup, making your Odoo environment more resilient and manageable.

While this guide establishes the core replication, remember that the journey to a fully optimized, production-grade system involves further enhancements like a connection pooler for intelligent read/write routing and automated failover solutions. Embrace these best practices to ensure your Odoo instance is not just running, but thriving, always ready to support your business’s growth and operational demands. This investment in a robust database infrastructure will pay dividends in terms of uptime, performance, and peace of mind.

For more insights into optimizing your Odoo deployment, check out our article on Advanced Odoo Performance Tuning Strategies.