cancel
Showing results for 
Search instead for 
Did you mean: 

Replication Server 6 Quickstart Multi-Master Replication

EDB Team Member

Objective

Quickly (less than five minutes) install two EPAS database clusters and setup EDB Replication Server to perform streaming multi-master replication between the two clusters.

 

Prerequisites

Running instance of RHEL 6/7 with root access

 

Components

<TBD>

 

Steps (as root user)

 

#!/bin/bash

#
# Setup YUM repository for installing EPAS and Replication Server
#
rpm -Uvh http://yum.enterprisedb.com/edbrepos/edb-repo-latest.noarch.rpm

#
# Set YUM username/password in edb.repo
#
export YUM_USER=<yum user>
export YUM_PASSWORD=<yum password>
sed -i "s/<username>:<password>/$YUM_USER:$YUM_PASSWORD/g" /etc/yum.repos.d/edb.repo

#
# Enable the EPAS 9.5 repo in edb.repo
#
sed -i "\/ppas95/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo

#
# Install EPAS 9.5
#
yum -y install ppas95-server

#
# Create cluster 1 with default port 5444, executed as enterprisedb
#
su - enterprisedb -c "mkdir /var/lib/ppas/9.5/cluster1"
su - enterprisedb -c "chmod 700 /var/lib/ppas/9.5/cluster1"
su - enterprisedb -c "/usr/ppas-9.5/bin/initdb -D /var/lib/ppas/9.5/cluster1"

#
# Create cluster 2 with non-default port 5445, executed as enterprisedb
#
su - enterprisedb -c "mkdir /var/lib/ppas/9.5/cluster2"
su - enterprisedb -c "chmod 700 /var/lib/ppas/9.5/cluster2"
su - enterprisedb -c "/usr/ppas-9.5/bin/initdb -D /var/lib/ppas/9.5/cluster2"
su - enterprisedb -c "sed -i 's/#port = 5444/port = 5445/' /var/lib/ppas/9.5/cluster2/postgresql.conf"

#
# Configure the necessary settings to permit the clusters to stream logs to the Replication Server
# As enterprisedb, modify postgresql.conf and make the following changes:
#   1. set wal_level = logical
#   2. set max_wal_centers = 5
#   3. set max_replication_slots = 5
#   4. set track_commit_timestamp = on
#
# As enterprisedb, edit pg_hba.conf to allow replication
#
su - enterprisedb -c "sed -i 's/#wal_level = minimal/wal_level = logical/' /var/lib/ppas/9.5/cluster*/postgresql.conf"
su - enterprisedb -c "sed -i 's/#max_wal_senders = 0/max_wal_senders = 5/' /var/lib/ppas/9.5/cluster*/postgresql.conf"
su - enterprisedb -c "sed -i 's/#max_replication_slots = 0/max_replication_slots = 5/' /var/lib/ppas/9.5/cluster*/postgresql.conf"
su - enterprisedb -c "sed -i 's/#track_commit_timestamp = off/track_commit_timestamp = on/' /var/lib/ppas/9.5/cluster*/postgresql.conf"
su - enterprisedb -c "sed -i 's/#host[ ]*replication/host replication/g' /var/lib/ppas/9.5/cluster*/pg_hba.conf"

#
# Start the two clusters
#
su - enterprisedb -c "/usr/ppas-9.5/bin/pg_ctl -w -D /var/lib/ppas/9.5/cluster1 start"
su - enterprisedb -c "/usr/ppas-9.5/bin/pg_ctl -w -D /var/lib/ppas/9.5/cluster2 start"

#
# Note your two running Postgres clusters
#
ps -ef | grep edb-postgres

#
# Enable the Replication Server repo in edb.repo
#
sed -i "\/enterprisedb-xdb60/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo

#
# Install Replication Server and Java (which the Replication Server will need to run)
#
yum -y install ppas-xdb
yum -y install java

#
# Set password for enterprisedb database user on each cluster
#
su - enterprisedb -c "psql -d postgres -p 5444 -c 'alter role enterprisedb identified by enterprisedb'"
su - enterprisedb -c "psql -d postgres -p 5445 -c 'alter role enterprisedb identified by enterprisedb'"

#
# Create three tables to be replicated on each cluster
#
su - enterprisedb -c "psql -d postgres -p 5444 -c 'create table public.table1 (id integer primary key)'"
su - enterprisedb -c "psql -d postgres -p 5444 -c 'create table public.table2 (id text primary key)'"
su - enterprisedb -c "psql -d postgres -p 5444 -c 'create table public.table3 (id integer primary key)'"

su - enterprisedb -c "psql -d postgres -p 5445 -c 'create table public.table1 (id integer primary key)'"
su - enterprisedb -c "psql -d postgres -p 5445 -c 'create table public.table2 (id text primary key)'"
su - enterprisedb -c "psql -d postgres -p 5445 -c 'create table public.table3 (id integer primary key)'"

#
# Setup environment to run the CLI commands
#
su - enterprisedb -c "echo 'enterprisedb' > ~/masterpass1.in"
su - enterprisedb -c "echo 'enterprisedb' > ~/masterpass2.in"
su - enterprisedb -c "echo 'enterprisedb' > ~/adminpass.in"

#
# Encrypt passwords for use in Replication Server config files
#
su - enterprisedb -c "java -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar -encrypt -input ~/masterpass1.in -output ~/masterpass1.out"
su - enterprisedb -c "java -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar -encrypt -input ~/masterpass2.in -output ~/masterpass2.out"
su - enterprisedb -c "java -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar -encrypt -input ~/adminpass.in -output ~/adminpass.out"

#
# Setup /etc/edb-repl.conf so pub/sub servers can start
#
su - enterprisedb -c "echo \"admin_user=enterprisedb\" > /etc/edb-repl.conf"
su - enterprisedb -c "echo \"admin_password=`cat ~enterprisedb/adminpass.out`\" >> /etc/edb-repl.conf"
su - enterprisedb -c "echo \"user=enterprisedb\" >> /etc/edb-repl.conf"
su - enterprisedb -c "echo \"password=`cat ~enterprisedb/masterpass1.out`\" >> /etc/edb-repl.conf"
su - enterprisedb -c "echo \"host=localhost\" >> /etc/edb-repl.conf"
su - enterprisedb -c "echo \"port=5445\" >> /etc/edb-repl.conf"
su - enterprisedb -c "echo \"database=postgres\" >> /etc/edb-repl.conf"

#
# Setup pubsvrfile.conf for CLI (Command Line Interface) use
#
su - enterprisedb -c "echo 'user=enterprisedb' > ~/pubsvrfile.conf"
su - enterprisedb -c "echo 'password=`cat ~enterprisedb/adminpass.out`' >> ~/pubsvrfile.conf"
su - enterprisedb -c "echo 'host=localhost' >> ~/pubsvrfile.conf"
su - enterprisedb -c "echo 'port=9051' >> ~/pubsvrfile.conf"

#
# Start pubserver in background and give a few seconds for it to start
#
su - enterprisedb -c "/usr/ppas-xdb-6.0/bin/runPubServer.sh &"
su - enterprisedb -c "sleep 5"

#
# Note the publication server is running
#
ps -ef | grep pubserver

#
# The following steps use the CLI to add masters and configure publications, this can
# also be done via the GUI if desired.
#

#
# Add the first master as a publication database (becomes Master Definition Node - MDN).
# The changesetlogmode W is the indication to use WAL streaming instead of triggers.
#
su - enterprisedb -c "java -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar -addpubdb -repsvrfile ~/pubsvrfile.conf -dbtype enterprisedb -dbhost localhost -dbport 5444 -dbuser enterprisedb -changesetlogmode W -dbpassfile ~/masterpass1.out -database postgres -repgrouptype m -nodepriority 1"

#
# Get the ID of the newly added MDN
#
su - enterprisedb -c "java -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar -printmdndbid -repsvrfile ~/pubsvrfile.conf | grep -v 'Printing' > ~/mdn_id.txt"

#
# Get a list of the tables to be replicated
#
su - enterprisedb -c "psql -d postgres -p 5444 -c \"select '' || table_schema || '.' || table_name from information_schema.tables where table_schema = 'public' and table_name like '%table%'\" | grep \"\.\" > ~/tables.txt"

#
# Create the publication on the existing master
#
su - enterprisedb -c "java -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar -createpub my_pub -repsvrfile ~/pubsvrfile.conf -pubdbid `cat ~enterprisedb/mdn_id.txt` -reptype T -tables `cat ~enterprisedb/tables.txt | tr '\n' ' '` -repgrouptype m"

#
# Add the second master as a publication database.
# The changesetlogmode W is the indication to use WAL streaming instead of triggers.
#
su - enterprisedb -c "java -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar -addpubdb -repsvrfile ~/pubsvrfile.conf -dbtype enterprisedb -dbhost localhost -dbport 5445 -dbuser enterprisedb -changesetlogmode W -dbpassfile ~/masterpass2.out -database postgres -repgrouptype m -nodepriority 2 -replicatepubschema false"

#
# Setup a schedule to perform synchronization every 10 seconds
#
su - enterprisedb -c "java -jar /usr/ppas-xdb-6.0/bin/edb-repcli.jar -confschedulemmr `cat ~enterprisedb/mdn_id.txt` -pubname my_pub -repsvrfile ~/pubsvrfile.conf -realtime 10"

#
# Start background inserts (every 5 seconds) into a table on each master so that each insert will get replicated to the other master
#
(for i in `seq 1 99`;
  do
    su - enterprisedb -c "psql -d postgres -p 5444 -c \"insert into public.table1 values(`echo $i`)\" &> /dev/null"
    su - enterprisedb -c "psql -d postgres -p 5445 -c \"insert into public.table1 values(`echo $i`+100)\" &> /dev/null"
    sleep 5
  done) &

#
# Setup the preferences so that the Replication Console will automatically connect to the publication
# server. The GUI will do this for you the first time you login to the publication server via the GUI.
# We are just doing it here to avoid additional screenshots and to show you where the GUI
# preferences are stored.
#
su - enterprisedb -c "mkdir -p ~/.enterprisedb/xdb/6.0"
su - enterprisedb -c 'echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?><servers><publication-server><host><![CDATA[localhost]]></host><port><![CDATA[9051]]></port><user><![CDATA[enterprisedb]]></user><password><![CDATA[Tqz5czKBURk6qOqzVlhcew==]]></password></publication-server></servers>" > ~/.enterprisedb/xdb/6.0/servers.xml'

#
# Run the Replication Console and see the masterpiece that you have created!
#
su - enterprisedb -c "/usr/ppas-xdb-6.0/bin/runRepConsole.sh"

#
# Congratulations! You have successfully created two Postgres clusters and configured streaming MMR replication between them!
#
 

Tips

 

Summary

This guide is intended to get you up and started with a default installation of Replication Server replicating two Postgres clusters via streaming replication. This not intended to cover all options, just to get a simple working example quickly.