Master:
test01-sandbox-pgsql.mydomain.com
Slave: test02-sandbox-pgsql.mydomain.com
Slave: test02-sandbox-pgsql.mydomain.com
1. Configure the master database cluster As the postgres software owner, execute the following:
echo "CREATE ROLE replicate WITH REPLICATION LOGIN password 'mypassword';" | psql echo "grant app_user to replicate;" | psql echo "alter system set wal_level = replica;" | psql echo "select pg_reload_conf();" | psql echo "alter system set synchronous_commit = local;" | psql echo "alter system set track_commit_timestamp = on;" | psql2. Restart the postgres server
systemctl restart postgresql-13.service3. Configure the slave database cluster
As the root user, stop the postgres server
systemctl stop postgresql-13.service4. Remove any files created in the deployment of the slave. They will be replaced anyway
rm -rf /data/*5. take a base backup of the master database cluster
pg_basebackup -h test01-sandbox-pgsql.mydomain.com -D /data/ -P -U replicate --wal-method=stream6. Set some parameters
echo "hot_standby = on" >> /pgconfig/postgresql.auto.conf echo " primary_conninfo = 'host=masterhost.mydomain.com port=5432 user=replicate password=mypassword' promote_trigger_file = '/tmp/MasterNow' " > /pgconfig/postgresql.conf7. Create a file necessary for the master/slave role determination
touch /data/standby.signal8. Start the postgres server
systemctl start postgresql-13.service9. Verification checks
On the master, to check that everything works fine, use the dictionary view pg_stat_replication, which, according to the documentation "contain one row per WAL sender process, showing statistics about replication to that sender's connected standby server. Only directly connected standbys are listed; no information is available about downstream standby servers."
echo 'select usename,state,sync_state,sync_priority,client_hostname from pg_stat_replication;' | psql usename | state | sync_state | sync_priority | client_hostname -----------+-----------+------------+---------------+----------------------------------- replicate | streaming | async | 0 | test02-sandbox-pgsql.mydomain.com (1 row)The check the slave status, use the dictionary view pg_stat_wal_receiver. According to the documentation the view "contain only one row, showing statistics about the WAL receiver from that receiver's connected server":
psql psql (13.6) Type "help" for help. postgres=# \x Expanded display is on. postgres=# select status,sender_host,conninfo from pg_stat_wal_receiver; -[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- status | streaming sender_host | test01-sandbox-pgsql.mydomain.com conninfo | user=replicate password=******** channel_binding=prefer dbname=replication host=test01-sandbox-pgsql.mydomain.com port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=anyCheck that recovery is in progress:
echo 'SELECT pg_is_in_recovery();' | psql pg_is_in_recovery ------------------- t (1 row)The equivalent query on the master node would state "f" (false). Read more about
Note that some important changes to replication were introduced in PostgreSQL version 12
No comments:
Post a Comment