![\"\"](\"https:\/\/www.fatihacar.com\/blog\/resimler\/postgresql-12-streaming-replication.jpg\")
<\/p>\nPostgreSQL has few feature for standby technology. I generally use streaming replication. This is very easy and confident. Streaming replication use a replication user for replication operations.Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the standby as they’re generated, without waiting for the WAL file to be filled.<\/p>\n
Streaming replication is asynchronous by default, in which case there is a small delay between committing a transaction in the primary and the changes becoming visible in the standby.<\/p>\n
If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. If you set up a WAL archive that’s accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments.<\/p>\n
<\/p>\n
PostgreSQL 12 New Standby Structure<\/strong><\/p>\n recovery.conf file is no longer used. Parameters will write in postgresql.conf file and postgresql.auto.conf file. Some parameter names changed. trigger_file parameter has been changed to promote_trigger_file<\/strong>. standby_mode parameter is no longer used. standby.signal<\/strong> file will use to determine standby database.<\/p>\n Also, you can change parameters with ALTER SYSTEM<\/strong> command. Reload is adequate to apply changes for archive_cleanup_command, promote_trigger_file, recovery_end_command, recovery_min_apply_delay parameters. You can reload database with psql -c “select pg_reload_conf()” or PSQL> select pg_reload_conf(); or systemctl reload postgresql-12.service commands.<\/p>\n Test Systems Features<\/strong><\/p>\n PG Version :<\/strong> 12.1 1- Keygen Generate And Copy<\/strong><\/p>\n [postgres@pg01 ~] ssh-keygen -t rsa 2- Create Streaming Replication User on Primary DB<\/strong><\/p>\n psql# createuser -U postgres -p 5432 repuser -P –replication<\/p><\/blockquote>\n 3- Primary DB pg_hba.conf configuratio<\/strong>n<\/p>\n [root@pg01 ~] vi \/pgdata\/data\/pg_hba.conf<\/p>\n Add below line<\/p>\n host replication repuser 192.168.10.12\/32 md5<\/p>\n [root@pg01 ~] systemctl reload postgresql-12.service<\/p><\/blockquote>\n 4- Create Wal Archive Directory on Standby Server<\/strong><\/p>\n [root@pg02 ~] mkdir -p \/pgdata\/ARCHIVELOG<\/p><\/blockquote>\n 5- Configure postgresql.conf on Primary DB<\/strong><\/p>\n [root@pg01 ~] vi \/pgdata\/data\/postgresql.conf<\/p>\n Parameters change like below<\/p>\n listen_addresses = ‘192.168.10.10’<\/p>\n archive_mode = on # enables archiving; off, on, or always [root@pg01 ~] systemctl restart postgresql-12.service<\/p><\/blockquote>\n 6- Take basebackup<\/strong><\/p>\n You can use pg_basebackup while primary db is start or stop. Stop status is more confident.<\/p>\n \/pgdata\/data directory has to be empty on standby server. pg_basebackup command will fill up that directory and will create postgresql.auto.conf<\/strong> and standby.signal<\/strong> file.<\/p>\n [postgres@pg02 ~] \/usr\/pgsql-12\/bin\/pg_basebackup -h 192.168.10.10 -D \/pgdata\/data -U repuser -p 5432 -v -P –wal-method=stream –write-recovery-conf<\/p><\/blockquote>\n 7- Postgresql.conf configuration on Standby Server<\/strong><\/p>\n Primary DB configuration has to be disabled with # mark.<\/p>\n listen_address = ‘*’<\/p>\n #archive_mode = on restore_command = ‘cp \/pgdata\/ARCHIVELOG\/%f %p’ # e.g. ‘cp \/mnt\/server\/archivedir\/%f %p’ If you want to delay to apply committed values. You can use recovery_min_apply_delay parameter.<\/p>\n recovery_min_apply_delay = 5min<\/p>\n This parameter provides 5 minutes delay. When you commit a transaction at primary side, hot standby will apply this transaction 5 minutes later.<\/p><\/blockquote>\n <\/p>\n 8- Check postgresql.auto.conf file on Standby Server<\/strong><\/p>\n [root@pg02 ~] more \/pgdata\/data\/postgresql.auto.conf<\/p>\n primary_conninfo = ‘user=repuser password=yourpass host=192.168.10.10 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any’<\/p><\/blockquote>\n 9- Start Hot Standby PostgreSQL Service<\/strong><\/p>\n [root@pg02 ~] systemctl start postgresql-12.service<\/p><\/blockquote>\n 10- Test<\/strong><\/p>\n Primary DB<\/strong> psql# create table test (name text); Hot Standby DB<\/strong> Receiver process check psql# select * from test;<\/p>\n name Failover Operations<\/strong><\/p>\n We have 3 methods to do failover operation.<\/p>\n Step 1<\/strong><\/p>\n Method 1<\/strong><\/p>\n Create \/pgdata\/data\/failover.uygula file that is wrote in postgresql.conf file promote_trigger_file parameter.<\/p>\n Standby DB will be primary db (read-write mode) when promote_trigger_file exist.<\/p>\n Also, you can see standby.signal file disappear.<\/p>\n Method 2<\/strong><\/p>\n If you want to use pg_ctl promote<\/strong> or to call pg_promote<\/strong> to failover operation, promote_trigger_file is not required.<\/p>\n [postgres@pg02 ~] pg_ctl promote -D \/pgdata\/data\/<\/p>\n waiting for server to promote…. done<\/p>\n server promoted<\/p>\n Method 3<\/strong><\/p>\n psql# select pg_promote();<\/p>\n pg_promote Step 2<\/strong><\/p>\n If you use virtual service IP address for primary database, you can take virtual service IP on Standby Server<\/p>\n ifconfig eth0:0 192.168.10.10 netmask 255.255.255.0<\/p><\/blockquote>\n Source : www.postgresql.org<\/p>\n","protected":false},"excerpt":{"rendered":" PostgreSQL has few feature for standby technology. I generally use streaming replication. This is very easy and confident. Streaming replication use a replication user for replication operations.Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the…<\/p>\n","protected":false},"author":37,"featured_media":2742,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[10],"tags":[14,88,108,109,127],"class_list":["post-2741","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administration-postgresql","tag-database-administration","tag-postgresql-administration","tag-postgresql-high-availability","tag-postgresql-hot-standby","tag-postgresql-streaming-replication"],"jetpack_featured_media_url":"http:\/\/www.fatihacar.com\/blog\/resimler\/postgresql-12-streaming-replication.jpg","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-Id","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2741","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=2741"}],"version-history":[{"count":17,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2741\/revisions"}],"predecessor-version":[{"id":2779,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2741\/revisions\/2779"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media\/2742"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=2741"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=2741"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=2741"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nPrimary DB IP :<\/strong> 192.168.10.11
\nPimary DB Hostname :<\/strong> pg01
\nHot Standby DB IP :<\/strong> 192.168.10.12
\nHost Standby DB Hostname :<\/strong> pg02
\nVirtual Service IP :<\/strong> 192.168.10.10
\nOS :<\/strong> Centos 7 x64
\nData directory :<\/strong> \/pgdata\/data
\nArchive direcytory :<\/strong> \/pgdata\/ARCHIVELOG<\/p><\/blockquote>\n
\n[postgres@pg02 ~] ssh-keygen -t rsa
\n[postgres@pg01 ~] ssh-copy-id -i .ssh\/id_rsa.pub postgres@pg02
\n[postgres@pg02 ~] ssh-copy-id -i .ssh\/id_rsa.pub postgres@pg01<\/p><\/blockquote>\n
\narchive_command = ‘rsync -a %p postgres@192.168.10.12:\/pgdata\/ARCHIVELOG\/%f’<\/p>\n
\n#archive_command = ‘rsync -a %p postgres@192.168.10.12:\/pgdata\/ARCHIVELOG\/%f’<\/p><\/blockquote>\n
\narchive_cleanup_command = ‘\/usr\/pgsql-12\/bin\/pg_archivecleanup \/pgdata\/ARCHIVELOG %r<\/p><\/blockquote>\n
\nReplication check
\npsql# select * from pg_stat_replication ;<\/p>\n
\npsql# insert into test values(‘Fatih Acar’);<\/p>\n
\nReceiver check
\npsql# select * from pg_stat_wal_receiver;<\/p>\n
\nps -ef | grep receiver
\npostgres 20936 20919 0 17:04 ? 00:00:00 postgres: walreceiver\u00a0 streaming 0\/9000268<\/p>\n
\n———–
\nFatih Acar<\/p><\/blockquote>\n\n
\n—————
\nt
\n(1 row)<\/p><\/blockquote>\n