DB Wiki

User Tools

Site Tools

Trace:
postgresqlreplication

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
postgresqlreplication [2021/08/25 13:51] – [Copy Schema Definitions] z0hpvkpostgresqlreplication [2025/03/08 22:24] (current) – external edit 127.0.0.1
Line 44: Line 44:
 pg_basebackup -D <OutputDirectory> -h <HostName> -U <User> -v -P -c fast -X stream -R pg_basebackup -D <OutputDirectory> -h <HostName> -U <User> -v -P -c fast -X stream -R
  
 +-D        Data Directory
 -v        Verbose -v        Verbose
 -P        Progress Reporting -P        Progress Reporting
Line 51: Line 52:
 </code> </code>
  
-Once the copy has completed, you will see the following message in the nohup.out file: \\+Once the copy has completed, you will see the following message: \\
 <code> <code>
 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: waiting for background process to finish streaming ...
Line 60: Line 61:
 <code> <code>
 hot_standby = on hot_standby = on
 +</code>
 +
 +==== Create a Replication Slot on the Primary ====
 +<code>
 +SELECT * FROM pg_create_physical_replication_slot('primary_slot');
 </code> </code>
  
Line 66: Line 72:
 standby_mode = on standby_mode = on
 primary_conninfo = 'host=[Primary IP] port=5432 user=repmgr password=[Password]' primary_conninfo = 'host=[Primary IP] port=5432 user=repmgr password=[Password]'
 +primary_slot_name = 'primary_slot'
 </code> </code>
  
Line 76: Line 83:
 ==== Check Replication Progress ==== ==== Check Replication Progress ====
 <code> <code>
 +cd $HOME/11/main/pg_wal
 ls -lrt $(ps -ef | grep postgres | grep recovering | grep -v grep | awk {'print $12'}) ls -lrt $(ps -ef | grep postgres | grep recovering | grep -v grep | awk {'print $12'})
  
Line 88: Line 96:
 select TXID_CURRENT_SNAPSHOT(); select TXID_CURRENT_SNAPSHOT();
 select * from PG_LAST_XACT_REPLAY_TIMESTAMP(); 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 -- Note some function names changed in PostgreSQL 10
 +-- Run on subscriber server
 select pg_is_in_recovery(), select pg_is_in_recovery(),
 pg_is_wal_replay_paused(), pg_is_wal_replay_paused(),
Line 119: Line 130:
 ==== Copy Schema Definitions ==== ==== Copy Schema Definitions ====
 <code> <code>
-pg_dumpall -s > schemadump.sql +pg_dumpall -s > schemas.sql 
-psql -d postgres -f schemadump.sql+psql -d doob -f schemas.sql
 </code> </code>
 Any schema changes after this point will not be replicated.\\ Any schema changes after this point will not be replicated.\\
Line 128: Line 139:
 CREATE ROLE repuser REPLICATION LOGIN PASSWORD '<password>'; CREATE ROLE repuser REPLICATION LOGIN PASSWORD '<password>';
 </code> </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 ==== ==== Create Publisher on Primary ====
 <code> <code>
 +-- All Tables
 CREATE PUBLICATION pub_upgrade FOR 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> </code>
 This needs to be done on every database in the instance.\\ This needs to be done on every database in the instance.\\
Line 141: Line 158:
 PUBLICATION pub_upgrade; PUBLICATION pub_upgrade;
 </code> </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/]]
  
  
postgresqlreplication.1629899515.txt.gz · Last modified: 2025/03/08 22:23 (external edit)