- OpenShift 4.x cluster
- Access to registry.redhat.io
ocCLI tool installed- Storage class available for persistent volumes
# Create new project
oc new-project psql-repl
# Deploy primary PostgreSQL
oc new-app registry.redhat.io/rhel9/postgresql-16~https://github.com/fjcloud/psql-repl.git \
--name=postgres-primary \
-e POSTGRESQL_USER=myuser \
-e POSTGRESQL_PASSWORD=mypassword \
-e POSTGRESQL_DATABASE=mydatabase \
-e POSTGRESQL_REPLICATION_USER=replicator \
-e POSTGRESQL_REPLICATION_PASSWORD=replpassword \
-e POSTGRESQL_ADMIN_PASSWORD=adminpassword \
-e IS_PRIMARY=true
# Create and attach PVC to primary
oc set volume deployment/postgres-primary --add \
--name=postgres-data \
--type=pvc \
--claim-size=10Gi \
--mount-path=/var/lib/pgsql/data
# Wait for primary to be ready
oc wait --for=condition=available deployment/postgres-primary --timeout=120s# Deploy replica PostgreSQL
oc new-app registry.redhat.io/rhel9/postgresql-16~https://github.com/fjcloud/psql-repl.git \
--name=postgres-replica \
-e POSTGRESQL_REPLICATION_USER=replicator \
-e POSTGRESQL_REPLICATION_PASSWORD=replpassword \
-e POSTGRESQL_PRIMARY_HOST=postgres-primary \
-e IS_PRIMARY=false \
-e POSTGRESQL_MIGRATION_REMOTE_HOST=postgres-primary \
-e POSTGRESQL_MIGRATION_ADMIN_PASSWORD=adminpassword \
-e POSTGRESQL_MIGRATION_IGNORE_ERRORS=yes
# Create and attach PVC to replica
oc set volume deployment/postgres-replica --add \
--name=postgres-data \
--type=pvc \
--claim-size=10Gi \
--mount-path=/var/lib/pgsql/data
# Wait for replica to be ready
oc wait --for=condition=available deployment/postgres-replica --timeout=120s# Create test table and function on primary
oc rsh deployment/postgres-primary psql -d mydatabase -c "
-- Create table if not exists
CREATE TABLE IF NOT EXISTS sample_table (
id SERIAL PRIMARY KEY,
data TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create function to generate sample data
CREATE OR REPLACE FUNCTION add_sample_data(num_rows integer DEFAULT 1000)
RETURNS void AS \$\$
BEGIN
INSERT INTO sample_table (data)
SELECT
md5(random()::text)
FROM generate_series(1, num_rows);
END;
\$\$ LANGUAGE plpgsql;"
# Generate test data
oc rsh deployment/postgres-primary psql -d mydatabase -c "SELECT add_sample_data();"
# Verify data on primary
oc rsh deployment/postgres-primary psql -d mydatabase -c "SELECT count(*) FROM sample_table;"
# Verify data on replica
oc rsh deployment/postgres-replica psql -d mydatabase -c "SELECT count(*) FROM sample_table;"# Add more data on primary
oc rsh deployment/postgres-primary psql -d mydatabase -c "SELECT add_sample_data(500);"
# Check counts on both servers
echo "Primary count:"
oc rsh deployment/postgres-primary psql -d mydatabase -c "SELECT count(*) FROM sample_table;"
echo "Replica count:"
oc rsh deployment/postgres-replica psql -d mydatabase -c "SELECT count(*) FROM sample_table;"# On primary
oc rsh deployment/postgres-primary psql -c "SELECT application_name, state, sync_state FROM pg_stat_replication;"
oc rsh deployment/postgres-primary psql -c "SELECT slot_name, active FROM pg_replication_slots;"
# On replica
oc rsh deployment/postgres-replica psql -c "SELECT pg_is_in_recovery();"# On replica
oc rsh deployment/postgres-replica psql -c "SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;"- Promote replica to primary:
# Verify replica is ready
oc rsh deployment/postgres-replica psql -c "SELECT pg_is_in_recovery();"
# Promote replica
oc rsh deployment/postgres-replica pg_ctl promote -D /var/lib/pgsql/data/userdata
# Verify promotion succeeded
oc rsh deployment/postgres-replica psql -c "SELECT pg_is_in_recovery();" # Should return 'f'- Clean up failed primary :
oc delete pvc $(oc get deployment postgres-primary -o jsonpath='{.spec.template.spec.volumes[*].persistentVolumeClaim.claimName}') --wait=false
oc delete all -l app=postgres-primary- Set current primary (former replica) to read-only:
# Set database to read-only mode
oc rsh deployment/postgres-replica psql -c "ALTER SYSTEM SET default_transaction_read_only = on;"
oc rsh deployment/postgres-replica psql -c "SELECT pg_reload_conf();"
# Verify read-only status
oc rsh deployment/postgres-replica psql -c "SHOW default_transaction_read_only;" # Should return 'on'- Deploy new primary using replica's data:
# Deploy primary with migration from current primary (former replica)
oc new-app registry.redhat.io/rhel9/postgresql-16~https://github.com/fjcloud/psql-repl.git \
--name=postgres-primary \
-e POSTGRESQL_REPLICATION_USER=replicator \
-e POSTGRESQL_REPLICATION_PASSWORD=replpassword \
-e IS_PRIMARY=true \
-e POSTGRESQL_MIGRATION_REMOTE_HOST=postgres-replica \
-e POSTGRESQL_MIGRATION_ADMIN_PASSWORD=adminpassword \
-e POSTGRESQL_MIGRATION_IGNORE_ERRORS=yes
# Create and attach PVC to primary
oc set volume deployment/postgres-primary --add \
--name=postgres-data \
--type=pvc \
--claim-size=10Gi \
--mount-path=/var/lib/pgsql/data
oc wait --for=condition=available deployment/postgres-primary --timeout=120s- Reconfigure replica:
# If primary ready you can reconfigure replica
oc delete pvc $(oc get deployment postgres-replica -o jsonpath='{.spec.template.spec.volumes[*].persistentVolumeClaim.claimName}') --wait=false
oc delete all -l app=postgres-replica
oc new-app registry.redhat.io/rhel9/postgresql-16~https://github.com/fjcloud/psql-repl.git \
--name=postgres-replica \
-e POSTGRESQL_REPLICATION_USER=replicator \
-e POSTGRESQL_REPLICATION_PASSWORD=replpassword \
-e POSTGRESQL_PRIMARY_HOST=postgres-primary \
-e IS_PRIMARY=false \
-e POSTGRESQL_MIGRATION_REMOTE_HOST=postgres-primary \
-e POSTGRESQL_MIGRATION_ADMIN_PASSWORD=adminpassword \
-e POSTGRESQL_MIGRATION_IGNORE_ERRORS=yes
oc set volume deployment/postgres-replica --add \
--name=postgres-data \
--type=pvc \
--claim-size=10Gi \
--mount-path=/var/lib/pgsql/dataRemove everything:
# Remove primary
oc delete pvc $(oc get deployment postgres-primary -o jsonpath='{.spec.template.spec.volumes[*].persistentVolumeClaim.claimName}') --wait=false
oc delete all -l app=postgres-primary
# Remove replica
oc delete pvc $(oc get deployment postgres-replica -o jsonpath='{.spec.template.spec.volumes[*].persistentVolumeClaim.claimName}') --wait=false
oc delete all -l app=postgres-replicaoc logs deployment/postgres-primary
oc logs deployment/postgres-replica# On primary
oc rsh deployment/postgres-primary
cat $PGDATA/pg_hba.conf | grep replication
psql -c "SELECT * FROM pg_replication_slots;"
# On replica
oc rsh deployment/postgres-replica
cat $PGDATA/postgresql.auto.confIS_PRIMARY: Set to "true" for primary, "false" for replica
POSTGRESQL_USER: Database userPOSTGRESQL_PASSWORD: Database passwordPOSTGRESQL_DATABASE: Database namePOSTGRESQL_REPLICATION_USER: Replication user (default: replicator)POSTGRESQL_REPLICATION_PASSWORD: Replication passwordPOSTGRESQL_REPLICA_HOST: Replica hostname (optional for primary)POSTGRESQL_ADMIN_PASSWORD: Password for 'postgres' admin user (required)
POSTGRESQL_REPLICATION_USER: Must match primary's replication userPOSTGRESQL_REPLICATION_PASSWORD: Must match primary's replication passwordPOSTGRESQL_PRIMARY_HOST: Primary server hostnamePOSTGRESQL_MIGRATION_REMOTE_HOST: Primary server hostname for initial syncPOSTGRESQL_MIGRATION_ADMIN_PASSWORD: Password for migration userPOSTGRESQL_MIGRATION_IGNORE_ERRORS: Set to "yes" to continue despite migration errors
.
├── postgresql-cfg/ # Configuration files loaded at container start
│ ├── replication.conf # PostgreSQL replication settings
│ └── logging.conf # PostgreSQL logging configuration
├── postgresql-pre-start/ # Scripts run before PostgreSQL starts
│ └── prepare-replica.sh # Replica initialization script
└── postgresql-start/ # Scripts run after PostgreSQL starts
└── configure-replication.sh # Primary configuration script
A Makefile is provided for common operations. Use make help to see all available commands.
# Deploy everything
make deploy-all
# Verify replication status
make verify
# Create test data
make create-test-data
# Promote replica to primary
make promote-replica
# Clean up everything
make clean-allCommon commands:
make deploy-primary- Deploy primary instancemake deploy-replica- Deploy replica instancemake deploy-postgrest- Deploy PostgREST APImake set-readonly- Set replica to read-only modemake verify- Check replication statusmake clean-all- Remove all components
Variables can be overridden:
# Example: Deploy with custom passwords
make deploy-all DB_PASSWORD=custom123 REPL_PASSWORD=repl123