===== Binary Replication =====

==== Settings ====
^ Description ^ Value ^
| Primary Server | 192.168.0.2 |
| Standby Server | 192.168.0.3 |
| Default Port | 5432 |

==== Create Replication User ====
<code>
# su - postgres
# createuser -U postgres repmgr -P -c 5 --replication
</code>

==== Configure pg_hba.conf ====
Add a rule that will allow the database user from the standby access the primary. \\
<code>
# Allow replication connections
host     replication     repmgr         [standby-IP]/32        md5
</code>

==== Configure postgresql.conf (PRIMARY) ====
<code>
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/pg_log_archive/%f && cp %p /var/lib/pgsql/pg_log_archive/%f'
max_wal_senders = 3
</code>

==== Stop PostgreSQL on Standby Server ====
<code>
pg_ctl stop
pg_ctl status
</code>

==== Delete Standby Database and Directory ==== 
<code>
cd /var/lib/postgresql/10
rm -rf main
</code>

==== Backup Primary Database from Standby Server ====
<code>
pg_basebackup -D <OutputDirectory> -h <HostName> -U <User> -v -P -c fast -X stream -R

-D        Data Directory
-v        Verbose
-P        Progress Reporting
-c fast   Fast Checkpoint
-X stream Copy WAL files at the same time
-R        Create minimal recovery.conf file
</code>

Once the copy has completed, you will see the following message: \\
<code>
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
</code>

==== Configure postgresql.conf (STANDBY) ====
<code>
hot_standby = on
</code>

==== Create a Replication Slot on the Primary ====
<code>
SELECT * FROM pg_create_physical_replication_slot('primary_slot');
</code>

==== Edit recovery.conf on Standby ====
<code>
standby_mode = on
primary_conninfo = 'host=[Primary IP] port=5432 user=repmgr password=[Password]'
primary_slot_name = 'primary_slot'
</code>

==== Start PostgreSQL on Standby Server ====
<code>
pg_ctl start
pg_ctl status
</code>

==== Check Replication Progress ====
<code>
cd $HOME/11/main/pg_wal
ls -lrt $(ps -ef | grep postgres | grep recovering | grep -v grep | awk {'print $12'})

tail -f /var/log/postgresql/postgresql-9.4-main.log
WAIT FOR : consistent recovery state reached at ...
           database system is ready to accept read only connections
</code>

<code SQL>
select * from PG_STAT_REPLICATION;
select PG_IS_IN_RECOVERY();
select TXID_CURRENT_SNAPSHOT();
select * from PG_LAST_XACT_REPLAY_TIMESTAMP();
select pg_wal_replay_pause();
select pg_wal_replay_resume();

-- Note some function names changed in PostgreSQL 10
-- Run on subscriber server
select pg_is_in_recovery(),
pg_is_wal_replay_paused(),
pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn(),
pg_last_xact_replay_timestamp(),
round(extract(epoch from now() - pg_last_xact_replay_timestamp())) AS slave_lag;
</code>

===== Logical Replication =====

==== Settings ====
^ Description ^ Value ^ Version ^
| Primary Server | 192.168.0.2 | 10 |
| Standby Server | 192.168.0.3 | 13 |
| Default Port | 5432 |

==== Configure postgresql.conf (PRIMARY) ====
<code>
wal_level = replica
</code>

==== Restart PostgreSQL ====
<code>
pg_ctl restart
pg_ctl status
</code>

==== Copy Schema Definitions ====
<code>
pg_dumpall -s > schemas.sql
psql -d doob -f schemas.sql
</code>
Any schema changes after this point will not be replicated.\\

==== Create Replication User on Primary ====
<code>
CREATE ROLE repuser REPLICATION LOGIN PASSWORD '<password>';
</code>
To copy the initial table data, the replication role must have SELECT privileges on all of the tables (or be a superuser).\\

==== Create Publisher on Primary ====
<code>
-- All Tables
CREATE PUBLICATION pub_upgrade FOR ALL TABLES;
-- Selected Tables
CREATE PUBLICATION pub_upgrade;
ALTER PUBLICATION pub_upgrade ADD TABLE customers;
ALTER PUBLICATION pub_upgrade ADD TABLE orders;
</code>
This needs to be done on every database in the instance.\\

==== Create Subscriber on Standby ====
<code>
CREATE SUBSCRIPTION sub_upgrade 
CONNECTION 'host=192.168.0.2 port=5432 dbname=doob user=repuser password='<password>' 
PUBLICATION pub_upgrade;
</code>

==== Check Replication Progress ====
<code SQL>
-- On Publisher
\dRp
SELECT * FROM PG_REPLICATION_SLOTS;
SELECT * FROM PG_STAT_REPLICATION;

-- On Subscriber
\dRs
</code>

==== Issues ====
DDL changes will not be replicated to the subscriber.\\
May be possible to replicate via an Event Trigger?\\
[[https://www.enterprisedb.com/postgres-tutorials/how-use-event-triggers-postgresql]]\\
[[https://blog.dbi-services.com/can-i-do-it-with-postgresql-16-ddl-triggers/]]