In the world of solution architecture, where we as solution architects are developing configurations to meet specific customer needs, we often consider a great deal of interesting scenarios. One of such scenario my team at EnterpriseDB® (EDB™) encountered recently bears exploring further with this blog.
For a business critical application, transaction durability is crucial. Therefore, we helped one customer recently to design an architecture for near zero data loss.
One of our customers wanted to use two synchronous replication mechanisms within the same data center with a high-speed network between the master and standby for data transfer. Also, the database administrator wanted to have read consistency from the replicas.
In Postgres 9.6, we have synchronous_commit = remote_apply, which helps in getting read-balancing consistency.
In addition to these requirements, the customer wanted to layer another capability into architecture; where if one of his synchronous standby replicas is down, then write transactions should not be stopped/hung. Transactions on the master should go into a “waiting state” if the synchronous replication is down.
EDB Postgres™ Advanced Server 9.6 comes with all building blocks out of the box to include this kind of logic to handle the scenario.
To achieve this capability in EDB Postgres, the following steps can be used:
1 Create the pg_background extension. The following is a link which you can use to compile pg_background module:
https://github.com/vibhorkum/pg_background
Below is a snapshot:
[root@ca420ebfb299 pg_background]# make install
/bin/mkdir -p '/usr/edb/as9.6/lib'
/bin/mkdir -p '/usr/edb/as9.6/share/extension'
/bin/mkdir -p '/usr/edb/as9.6/share/extension'
/bin/install -c -m 755 pg_background.so '/usr/edb/as9.6/lib/pg_background.so'
/bin/install -c -m 644 .//pg_background.control '/usr/edb/as9.6/share/extension/'
/bin/install -c -m 644 .//pg_background--1.0.sql '/usr/edb/as9.6/share/extension/'
[root@ca420ebfb299 pg_background]# psql
psql.bin (9.6.2.7)
Type "help" for help.
edb=# create extension pg_background;
CREATE EXTENSION
edb=#
2. Create the dbms_scheduler and pgagent extension in EDB Postgres as shown below:
edb=# CREATE EXTENSION dbms_scheduler ;
CREATE EXTENSION
edb=# create extension pgagent;
CREATE EXTENSION
3. Now, you can create the procedure which can do following:
- Identify all synchronous standby and check the status of each named synchronous standby in pg_stat_replication
- If named synchronous standby doesn’t exists in pg_stat_replication, then change the synchronous_standby_names parameter in such a way that it doesn’t lose the name of synchronous standbys, however can demote the named synchronous to asynchronous standby. For that, it’s recommended to use following string for synchronous_standby_names parameter:
2(standby1, standby2…)
- After demoting the synchronous standby to asynchronous, send an e-mail to your DBAs group to notify them about demotion and your DBAs can take necessary steps. In EDB Postgres, we have a package UTL_SMTP, which can be used for sending e-mails. The following is an example of such a procedure:
CREATE OR REPLACE PROCEDURE send_mail (
p_sender VARCHAR2,
p_recipient VARCHAR2,
p_subj VARCHAR2,
p_msg VARCHAR2,
p_mailhost VARCHAR2
)
IS
v_conn UTL_SMTP.CONNECTION;
v_crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
v_port CONSTANT PLS_INTEGER := 25;
BEGIN
v_conn := UTL_SMTP.OPEN_CONNECTION(p_mailhost,v_port);
UTL_SMTP.HELO(v_conn,p_mailhost);
UTL_SMTP.MAIL(v_conn,p_sender);
UTL_SMTP.RCPT(v_conn,p_recipient);
UTL_SMTP.DATA(v_conn, SUBSTR(
'Date: ' || TO_CHAR(SYSDATE,
'Dy, DD Mon YYYY HH24:MI:SS') || v_crlf
|| 'From: ' || p_sender || v_crlf
|| 'To: ' || p_recipient || v_crlf
|| 'Subject: ' || p_subj || v_crlf
|| p_msg
, 1, 32767));
UTL_SMTP.QUIT(v_conn);
END;
For more information on the UTL_SMTP package in EDB Postgres, click here.
- If none of the standbys are available, then maintain the setting of synchronous_standby_names as given below:
synchronous_standby_names = 1(standby1, standby2,) The above setting will cover the scenario, where write should be stopped or should be in hanging state in case all standbys are down.
- If replication slots are getting used, then check the lag for replication slots and reset the replication slots, so that we are not overloading pg_xlog.
Note: If you are using synchronous standby with replication slots, it’s recommended to have the same name for synchronous standbys and replication slots.
4. The following is a link procedure which covers all points mentioned in step 3.
https://github.com/vibhorkum/procedure_transition_sync_async
5. After creating the procedure, the user can schedule a job as given below:
EXEC sys.DBMS_SCHEDULER.create_program (
'synchronous_to_asynchrnous',
'STORED_PROCEDURE',
'"public"."transition_sync_async"',
3,true,
'Job which can transition synchronous to asynchronous');
EXEC
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
program_name => 'synchronous_to_asynchrnous',
argument_position => 1,
argument_name => 'allowed_slots_lag',
argument_type => 'NUMERIC',
default_value => '1073741824');
EXEC
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
program_name => 'synchronous_to_asynchrnous',
argument_position => 2,
argument_name => 'p_sender',
argument_type => 'TEXT',
default_value => 'vibhor.aim@gmail.com');
EXEC
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
program_name => 'synchronous_to_asynchrnous',
argument_position => 3,
argument_name => 'p_recipient',
argument_type => 'TEXT',
default_value => 'vibhor.aim@gmail.com');
EXEC
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'every_minute',
start_date => now,
repeat_interval => 'FREQ=MINUTELY;',
comments => 'This schedule executes every minute');
EXEC
DBMS_SCHEDULER.create_job (
job_name => 'job_synchronous_to_asynchrnous',
program_name => 'synchronous_to_asynchrnous',
schedule_name => 'every_minute',
comments => 'Job which can transition synchronous to asynchronous');
Following is a snapshot of manual execution of job:
edb=# exec DBMS_SCHEDULER.RUN_JOB('job_synchronous_to_asynchrnous', TRUE);
INFO: synchronos_standby_names => 2(standby1,standby2)
INFO: standby_name => {2,standby1,standby2}
INFO: old standby count => 2
INFO: synchronous_standby_count => 2
EDB-SPL Procedure successfully completed
For more information on the DBMS_SCHEDULER package in EDB Postgres, click here.
Vibhor Kumar is Director, Solution Architecture at EnterpriseDB.
This post originally appeared on Vibhor's personal blog.