Welcome to DBA Master ! This blog is dedicated to all things related to database administration , SQL optimization , and performance tuning . Whether you're a beginner or a seasoned DBA, you'll find practical guides, troubleshooting tips, and real-world tutorials to help you work smarter with data. What to Expect: SQL performance tuning tips Indexing strategies Backup and recovery best practices High availability and replication techniques Database creation, configuration, and setup Monitoring queries and scripts for proactive performance management Migration guides across different database platforms Security essentials and best practices Recommended tools for DBAs Real-world error fixes and how to solve them Stay tuned — exciting content is coming soon. Feel free to bookmark and share: www.dbamaster.com ! Thanks for visiting!
Streaming Replication:
- asynchronous replication
- synchronous replication
1.asynchronous replication
Primary Node(192.168.1.201) //install bin and cluster(data) path
Standby Node (192.168.1.202) // only install bin path
step 1: ssh for both servers
primary node:
su - postgres
ssh-keygen
standby node:
su - postgres
ssh-keygen
primary node:
cd .ssh
ssh-copy-id postgres@192.168.1.202
standby node:
cd .ssh
cat id_rsa.pub >> autheraized_keys
scp autheraized_keys postgres@192.168.1.201:/var/lib/pgsql/.ssh
step 2:primary server configuration:(201-Master/Primary)
cd /var/lib/pgsql/11/data
vi pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 192.168.1.201/32 trust
host all all 192.168.1.202/32 trust
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 192.168.1.201/32 trust
host replication all 192.168.1.202/32 trust
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
vi postgresql.conf (201-Primary)
listern_address=*
archive_mode=on
archive_command='cp %p /master_arch/%f && scp %p postgres@192.168.1.202:/slave_arch/%f'
wal_level=replica
full_page_writes=on
max_wal_sender=6
wal_keep_segement=500
max_replication_slot=2
log_statement='all'
Restart the primary server
step 3: take base backup(202-standby-slave)-Install only same version bin Path
su - postgres
cd /usr/pgsql-11/bin
without tablespace:
./pg_basebackup -h 192.168.1.201 -p 5432 -P -v -X stream or fetch -c fast -R -Fp -D /backup/bk_1
with tablespace
./pg_basebackup -h 192.168.1.201 -p 5432 -P -v -c fast -X stream -R -T /tbs_1=/tbs_new1 -T /tbs_2=/tbs_new2mn -Fp -D /backup/bk_1
cd /backup/bk_1
vi recovery.conf (add commands)
restore_command = 'cp /slave_arch/%f %p'
trigger_file = '/etc/error.txt'
vi postgresql.conf
#archive_command='cp %p /master_arch/%f && scp %p postgres@192.168.1.202:/slave_arch/%f' // disable
hot_standby = on //enable
start the slave server
step 4: check status
check primary server (201)status:
select * from pg_stat_replication;
check standby server (202) status:
select * from pg_is_in_recovery();
select * from pg_stat_wal_receiver;
2.synchronous replication
same configuration for asynchronous replication but change
step 1:primary server (extra add parameter)
vi postgresql.conf
synchronous_standby_names = 'siva'
step 2:standby server
vi recovery.conf
primary_conninfo = 'user=postgres host=192.168.1.201 port=5432 application_name=siva'
start the server
Comments
Post a Comment