Saturday

• Implementing High Availability with PostgreSQL
• Replication Concepts in postgres 
• Postgres architecture overview
• Creating clusters in Postgres
• PostgreSQL Streaming Replication

Logical Replication Steps

Source Server

1. User and Authentication setup
2. wal_level configuration
3. Create Publication
4. Grant rights to user

Target Server

1. Create target table
2. CREATE Subscription
3. Test Replication

Source Server

sudo su – postgres

psql

CREATE ROLE Replicator REPLICATION LOGIN PASSWORD *****;

Edit Configuration Files

sudo vim /etc/postgresql/10/main/pg_hba.conf

host    all             replicator      192.168.56.103          md5

sudo vim /etc/postgresql/10/main/postgresql.conf

wal_level = logical  

Restart postgres

sudo service postgresql restart

psql

\l

\c dvdrental

\d

Film

CREATE PUBLICATION bpub FOR TABLE film;

GRANT All ON film to Replicator;

Target Server:

create database dvdrental;

sudo vim /etc/postgresql/10/main/pg_hba.conf
Edit configuration Files

host    all             replicator      192.168.56.102          md5

Source Server:

Schema only dump

pg_dump dvdrental -t film -s

pg_dump dvdrental -t film -s | psql dvdrental -h 192.168.56.103

Target Server

\c dvdrental

CREATE SUBSCRIPTION bsub CONNECTION ‘dbname=dvdrental host=192.168.56.102 user=replicator password=Welcome1’ PUBLICATION bpub;

Verify data exists Node 2

select * from film;

Test Replication with adding a new record

Source Server

INSERT INTO public.film(

film_id, title, description, release_year, language_id, rental_duration, rental_rate, 

length, replacement_cost, rating, last_update, special_features, fulltext)

VALUES (1001, ‘Mark Shay’, ‘Crazy Yankee Fan’, 2020, 1, 5, 6.12, 120, 1.99, ‘R’, ‘2020-04-25 14:50:58.951’, ‘{“Deleted Scenes”}’, ‘ace’);

Target Server

select * from film where title =’Mark Shay’;

Sunday, Monday, Tuesday

# Install initial PostgreSQL 10 cluster and verify it exists

sudo pg_lsclusters 

# create a second postgres cluster 

sudo pg_createcluster 10 replica1 

sudo pg_ctlcluster 10 replica1 status 

sudo systemctl status [email protected] 

# create archive directories for both clusters 

sudo -H -u postgres mkdir /var/lib/postgresql/pg_log_archive/main 

sudo -H -u postgres mkdir /var/lib/postgresql/pg_log_archive/replica1 

## Configure Main Cluster (Primary / Master) ################################################### 

# edit configuration file 

sudo nano /etc/postgresql/10/main/postgresql.conf 

  wal_level = replica 

  wal_log_hints = on 

  archive_mode = on # (change requires restart) 

  archive_command = ‘test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/main/%f’ 

  max_wal_senders = 10 

  wal_keep_segments = 64 

  hot_standby = on 

# edit host based access file 

sudo nano /etc/postgresql/10/main/pg_hba.conf 

  local replication rep_user trust # DO NOT USE. Configure your own connection and authentication information 

# create replication user 

sudo -H -u postgres psql -c “CREATE USER rep_user WITH replication;” 

# restart the main cluster 

sudo systemctl restart [email protected] 

## Configure Replica1 Cluster ################################################################### 

# stop replica1 cluster 

sudo systemctl stop [email protected] 

# edit configuration file 

sudo nano /etc/postgresql/10/replica1/postgresql.conf 

  wal_level = replica 

  wal_log_hints = on 

  archive_mode = on # (change requires restart) 

  archive_command = ‘test ! -f /var/lib/postgresql/pg_log_archive/replica1/%f && cp %p /var/lib/postgresql/pg_log_archive/replica1/%f’ 

  max_wal_senders = 10 

  wal_keep_segments = 64 

  hot_standby = on 

# edit host based access file 

sudo nano /etc/postgresql/10/replica1/pg_hba.conf 

  local replication rep_user trust 

## Setup Replica1 Cluster Replication ###########################################################

# remove replica1 existing database files 

sudo su – postgres 

rm -rf /var/lib/postgresql/10/replica1 

# sync replica1 with main cluster 

pg_basebackup -D /var/lib/postgresql/10/replica1 -U rep_user -w -P -R # -X stream 

#configure recovery.conf 

# https://www.postgresql.org/message-id/attachment/8070/recovery.conf

nano /var/lib/postgresql/10/replica1/recovery.conf 

  restore_command = ‘cp /var/lib/postgresql/pg_log_archive/replica1/%f %p’ 

  recovery_target_timeline = ‘latest’ 

# start replica cluster and verify in sync 

sudo pg_ctlcluster 10 replica1 start 

tail -n 100 /var/log/postgresql/postgresql-10-replica1.log 

## Verify Replica1 Cluster In Sync ############################################################# 

# create database with some data 

sudo su – postgres 

psql -c “create database test;” -p 5432 

psql test -c ” 

create table posts ( 

  id integer, 

  title character varying(100), 

  content text, 

  published_at timestamp without time zone, 

  type character varying(100) 

); 

insert into posts (id, title, content, published_at, type) values 

(100, ‘Intro to SQL’, ‘Epic SQL Content’, ‘2018-01-01’, ‘SQL’), 

(101, ‘Intro to PostgreSQL’, ‘PostgreSQL is awesome!’, now(), ‘PostgreSQL’); 

”  

# verify data has been replicated on replica1

psql test -c “select * from posts;” -p 5433 

# stop main cluster (simulate failure condition)

sudo systemctl status [email protected] 

# promote replica1 

sudo pg_ctlcluster 10 replica1 promote 

# verify replica1 is now a master / primary cluster 

tail -n 100 /var/log/postgresql/postgresql-10-replica1.log 

psql test -c “insert into posts (id, title, content, type) values 

(102, ‘Intro to SQL Where Clause’, ‘Easy as pie!’, ‘SQL’), 

(103, ‘Intro to SQL Order Clause’, ‘What comes first?’, ‘SQL’);” -p 5433 

psql test -c “select * from posts;” -p 5433 

Ignore error in log

2020-04-27 00:12:26.278 EDT [3084] LOG:  archive command failed with exit code 1

2020-04-27 00:12:26.278 EDT [3084] DETAIL:  T

ed archive command was: test ! -f /var/lib/postgresql/pg_log_archive/main/00000001000000000000000C && cp pg_wal/00000001000000000000000C /var/lib/postgresql/pg_log_archive/replica/00000001000000000000000C

Wednesday:

PostgreSQL Replication Slots

#  Create replication slot on main

sudo su – postgres

psql 

select * from pg_create_physical_replication_slot(‘replica’);

psql 

“select * from pg_replication_slots;”

sudo nano /var/lib/postgresql/10/replica/recovery.conf

## Add to existing file

primary_slot_name =’replica’

# Restart replica to start using the new slot

sudo systemctl restart [email protected]

tail -n 1000 /var/log/postgresql/postgresql-10-replica.log

# Check Status on Slot

select * from pg_replication_slots;

# Verify Replication is still working

Test Replica as Primary

# Run on Primary

INSERT INTO posts (id, title, content, type) VALUES 

(102, ‘Intro to SQL Where Clause’, ‘Easy as pie!’,  ‘SQL’),

(103, ‘Intro to SQL Order Clause’, ‘What comes first?’, ‘SQL’);

# Run on Replica

Select * from posts;

# failover to replica

# Stop Primary Cluster

sudo systemctl stop [email protected]

#verify Secondary log

tail -n 1000 /var/log/postgresql/postgresql-10-replica.log

sudo pg_ctlcluster 10 replica promote

PostgreSQL Replication Monitoring

— on main

select * from pg_stat_replication;

select * from pg_replication_slots;

— on replica check if in standby_mode

select pg_is_in_recovery();

— stop replica and check change in status

sudo systemctl stop [email protected]

— on main

select * from pg_stat_replication;

select * from pg_replication_slots;

— start replica again

sudo systemctl start [email protected]

— on main compare WAL status using lsn diff

select pg_wal_lsn_diff(‘0/23000738′,’0/230001B0’);

— on main query to track lag in bytes

— sending_lag could indicate heavy load on primary

— receiving_lag could indicate network issues or replica under heavy load

— replaying_lag could indicate replica under heavy load

select

  pid,

  application_name,

  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) sending_lag,

  pg_wal_lsn_diff(sent_lsn, flush_lsn) receiving_lag,

  pg_wal_lsn_diff(flush_lsn, replay_lsn) replaying_lag,

  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) total_lag

from pg_stat_replication;

— on replica can check replica locations or timing

—   pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp()

SELECT

  CASE

    WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()

    THEN 0

    ELSE EXTRACT (EPOCH FROM now() – pg_last_xact_replay_timestamp())

    END AS log_delay;

— Want to know what file a lsn refers to?

select pg_walfile_name(pg_current_wal_lsn());

Thursday:

Manual migrations Steps

• Prepare Schema
• Script out MSSQL Server Schema for table
• Update all non-supported data types 
  • DATETIME-> TIMESTAMP 
  • MONEY -> NUMERIC(19,4)
  • NVARCAR -> character varying
  • sysname -> character varying
  • tinyint ->  int
  • IDENTDITY INT -> NT GENERATED ALWAYS AS IDENTITY
  • varbinary(max) ->bytea
  • SELECT EXTRACT(MONTH  -> Month)
  • SELECT EXTRACT(DAY    -> DAY)
  • DATEDIFF -> DATEPART

Remove all references to:

• NONCLUSTERED
• CLUSTERED
• WITH CHECK
• Remove All ALTER Statements with CHECK Constraint statements
• ASC

Friday: