Setup logical replication using operator v5

[honne23]

I've been trying to setup permissions for logical replication in operator v5 and I can't seem to do so. I'm running a custom registry.developers.crunchydata.com/crunchydata/crunchy-postgres-ha:centos8-13.4-0 dockerfile that I've edited to run the timescaledb extension.

I've tried two methods to enable logical replication; one by editing /opt/crunchy/conf/postgres-ha/pg_hba.conf directly, and another by replacing /opt/crunchy/conf/postgres-ha/postgres-ha-pghba-bootstrap.yaml with my own file. Neither seem to be working as I keep getting the following error from my client:

level=error msg="no slotName provided" error="FATAL: no pg_hba.conf entry for replication connection from host \"10.20.0.75\", user \"postgres\", SSL off (SQLSTATE 28000)"

I've been unable to set it up correctly so far, could someone please point me where I'm going wrong?

This is my dockerfile:

FROM registry.developers.crunchydata.com/crunchydata/crunchy-postgres-ha:centos8-13.4-0

USER root

RUN curl -sSL -o /etc/yum.repos.d/timescale_timescaledb.repo "https://packagecloud.io/install/repositories/timescale/timescaledb/config_file.repo?os=el&dist=8" && \
    yum --disablerepo=crunchypg13 update -y && \
    yum --disablerepo=crunchypg13 install -y timescaledb-2-postgresql-13 && \
    yum clean all

COPY schema.sql .
RUN ls /opt/crunchy/bin/postgres-ha/sql/
RUN sed -i '/shared_preload_libraries:/ s/$/,timescaledb/' /opt/crunchy/conf/postgres-ha/postgres-ha-initdb.yaml && \
    sed -i '/\\c "\${PGHA_DATABASE}"$/a CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;' /opt/crunchy/bin/postgres-ha/sql/setup.sql && \
    sed -i '/SET application_name="container_setup";$/a CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;' /opt/crunchy/bin/postgres-ha/sql/setup.sql && \
    cat schema.sql >> /opt/crunchy/bin/postgres-ha/sql/setup.sql && \
    echo "host    replication     all             0.0.0.0/0            trust" >> /opt/crunchy/conf/postgres-ha/pg_hba.conf
RUN rm /opt/crunchy/conf/postgres-ha/postgres-ha-pghba-bootstrap.yaml
COPY postgres-ha-pghba-bootstrap.yaml /opt/crunchy/conf/postgres-ha/
USER 26

My postgres-ha-pghba-bootstrap.yaml

---
postgresql:
  pg_hba:
    - local all postgres peer
    - host replication postgres 0.0.0.0/0 trust

[jkatz]

Timescale is already in the container; you don't need to add it in yourself. Similarly, getting logical replication set up by modifying the container is the incorrect approach, as you can get it through configuration via the custom resource.

It's probably worth us adding an example for how to set up logical replication in the documentation.

[honne23]

@jkatz if it was possible I would really love to see how to do via the custom resource, or point me to the custom resource docs, you would really be saving my bacon!

[honne23]

@jkatz After further investigation, it seems this feature was added in 5.1.0 whereas the operator example repo is only up to date with v5.0.0

[jkatz]

The examples repo is up to 5.0.2. Both Timescale and the ability to use logical replication are available in 5.0.0 and beyond. I was able to get logical replication running locally on my copy of 5.0.2 and have been in the process of preparing documentation.

If you need immediate support please refer to the support page.

[honne23]

@jkatz I've just tried applying host replication params by following this related thread and finding this document and I am still receiving errors when trying to create a replication connection:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: my-db
  namespace: postgres-operator
spec:
  image: eu.gcr.io/<my-db>/timescale-custom:v38
  postgresVersion: 13
  users:
    - name: postgres
    - name: pgdelta
      databases:
        - mydb
      options: "SUPERUSER"
  instances:
    - name: instance1
      replicas: 3
      dataVolumeClaimSpec:
        accessModes:
          - "ReadWriteOnce"
        resources:
          requests:
            storage: 100Gi
  patroni:
    dynamicConfiguration:
      postgresql:
        parameters:
          wal_level: logical
        pg_hba:
          - host replication postgres 0.0.0.0/0 trust
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.33-2
      repoHost:
        dedicated: {}
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - "ReadWriteOnce"
              resources:
                requests:
                  storage: 300Gi

(Although I'm pretty sure wal_level is probably set to logical by default)

[honne23]

@jkatz any progress on this issue?

[honne23]

Following up; @jkatz

[jkatz]

Referenced in e3ea540.