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
- User and Authentication setup
- wal_level configuration
- Create Publication
- Grant rights to user
Target Server
- Create target table
- CREATE Subscription
- 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 postgresql@10-main
# 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 postgresql@10-main
## Configure Replica1 Cluster ###################################################################
# stop replica1 cluster
sudo systemctl stop postgresql@10-replica1
# 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 postgresql@10-main
# 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:
# 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 postgresql@10-replica
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 postgresql@10-main
#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 postgresql@10-replica
— on main
select * from pg_stat_replication;
select * from pg_replication_slots;
— start replica again
sudo systemctl start postgresql@10-replica
— 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:
One comment