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 15:20] – 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 132: Line 143:
 ==== 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 148: Line 164:
 \dRp \dRp
 SELECT * FROM PG_REPLICATION_SLOTS; SELECT * FROM PG_REPLICATION_SLOTS;
 +SELECT * FROM PG_STAT_REPLICATION;
  
 -- On Subscriber -- On Subscriber
Line 156: Line 173:
 DDL changes will not be replicated to the subscriber.\\ DDL changes will not be replicated to the subscriber.\\
 May be possible to replicate via an Event Trigger?\\ 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/]] [[https://blog.dbi-services.com/can-i-do-it-with-postgresql-16-ddl-triggers/]]
  
  
postgresqlreplication.1629904813.txt.gz · Last modified: 2025/03/08 22:23 (external edit)