Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Uninterrupted Database Switchover Using Session Resource Migration

Uninterrupted Database Switchover Using Session Resource Migration

This article discusses performing an uninterrupted database switchover without impacting user experience using session resource migration.

By digoal

Background

Database migration and switchover are common topics. However, most of the solutions for database migration and switchover can impact user experience.

For example, if a user adopts a bind variable statement, the statement is lost after the active/standby role switchover, leading to an error in the call.

To maintain the primary database hardware, you can uninterruptedly switch the roles of the primary and standby databases at the middleware level. After the hardware maintenance, you can uninterruptedly switch the roles of two databases again.

How to ensure role switchover of primary and standby databases does not impact user sessions? To do so, it's better to know what content is in a session and what content needs to be migrated along with the role switchover at first.

In this article, high availability (HA) refers to HA at the middleware level. It is not the HA of direct connection to the database from applications or of virtual IP (VIP) switchover.

Example of a simple switchover process: First, wait until the transactions in all sessions end. Then, freeze sessions to prevent submitting SQL when all sessions enter the idle state. Finally, switch roles and complete migration of resource state of each session.

Session Resource State

What state is there in sessions? You can use the SQL statement "discard."

DISCARD — discard session state

The "discard all" statement is equivalent to execute the following code:

SET SESSION AUTHORIZATION DEFAULT;  
RESET ALL;  
DEALLOCATE ALL;  
CLOSE ALL;  
UNLISTEN *;  
SELECT pg_advisory_unlock_all();  
DISCARD PLANS;  
DISCARD SEQUENCES;  
DISCARD TEMP;

Currently, the session resource may contain the following (there may be some differences among different PG versions):

Session roles, parameter setting, bind variable statement, cursor, asynchronous message listening, advisory lock, sequence, temporary tables, etc.

The following part describes how to query each type of resource and how to recover resources on new primary databases.

1) Session Authorization

Superusers can set session users as other users, but common users have no permission to do so.

If the current user is postgres, set SESSION AUTHORIZATION to test.

postgres=# set SESSION AUTHORIZATION test;  
SET  
postgres=> show SESSION AUTHORIZATION;  
 session_authorization   
-----------------------  
 test  
(1 row)  
postgres=> select usename from pg_stat_activity where pid=pg_backend_pid();  
 usename    
----------  
 postgres  
(1 row)

Query Method

postgres=> show SESSION AUTHORIZATION;  
 session_authorization   
-----------------------  
 test  
(1 row)  
postgres=> select usename from pg_stat_activity where pid=pg_backend_pid();  
 usename    
----------  
 postgres  
(1 row)

Recovery Method

When pg_stat_activity.usename is not equal to SESSION AUTHORIZATION, recover it in the following way.

postgres=# set SESSION AUTHORIZATION test;  
SET

2) Parameters

Users can set some PostgreSQL parameters in sessions or transactions. Also, users can configure the user_id in a session or transaction when the context is in ('user','superuser').

postgres=# select distinct context from pg_settings ;  
      context        
-------------------  
 superuser-backend  
 sighup  
 superuser  
 postmaster  
 internal  
 user  
 backend  
(7 rows)

Example of the setting:

postgres=> set tcp_keepalives_count=1;  
SET  
  
source表示参数来自哪里的设置，如果来自会话或事务级设置，则显示session  
  
postgres=> select distinct source from pg_settings ;  
        source          
----------------------  
 session  
 default  
 command line  
 configuration file  
 client  
 override  
 environment variable  
(7 rows)  
  
重置方法  
  
postgres=# reset tcp_keepalives_count;  
RESET  
postgres=# select name,setting,reset_val,source,context from pg_settings where name='tcp_keepalives_count';  
         name         | setting | reset_val | source  | context   
----------------------+---------+-----------+---------+---------  
 tcp_keepalives_count | 3       | 0         | default | user  
(1 row)

Query Method

postgres=# select name,setting,reset_val,source,context from pg_settings where source ='session' and setting<>reset_val;  
         name         | setting | reset_val | source  | context   
----------------------+---------+-----------+---------+---------  
 tcp_keepalives_count | 1       | 0         | session | user  
(1 row)

Recovery Method

postgres=> set tcp_keepalives_count=1;  
SET

3) Bind Variable Statement

Bind variables can reduce the parser and plan costs in databases, improve high-concurrency query performance, and avoid SQL injection.

Different drivers have different usage methods.

An example of using bind variables:

CREATE OR REPLACE FUNCTION public.getps()  
 RETURNS void  
 LANGUAGE plpgsql  
 STRICT  
AS $function$  
declare   
  rec record;  
begin  
  for rec in select t from pg_prepared_statements t loop  
    raise notice '%', (rec.*)::text;  
  end loop;  
end;  
$function$;  
  
  
create table ps(id int primary key, info text);  
insert into ps select generate_series(1,10000), 'test';  
  
  
vi test.sql  
  
\set id random(1,10000)  
select * from ps where id=:id;  
select getps();  
  
使用绑定变量的模式，调用SQL  
  
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 1 -j 1  
  
NOTICE:  ("(P0_1,""select * from ps where id=$1;"",""2017-06-19 15:22:21.821454+08"",{integer},f)")  
NOTICE:  ("(P0_2,""select getps();"",""2017-06-19 15:22:21.822045+08"",{},f)")  
.....

Query Method

postgres=# \d pg_prepared_statements  
                  View "pg_catalog.pg_prepared_statements"  
     Column      |           Type           | Collation | Nullable | Default   
-----------------+--------------------------+-----------+----------+---------  
 name            | text                     |           |          |   
 statement       | text                     |           |          |   
 prepare_time    | timestamp with time zone |           |          |   
 parameter_types | regtype[]                |           |          |   
 from_sql        | boolean                  |           |          |   
  
postgres=# select * from pg_prepared_statements;  
 name | statement | prepare_time | parameter_types | from_sql   
------+-----------+--------------+-----------------+----------  
(0 rows)  
  
postgres=# prepare a(int) as select * from ps where id=$1;  
PREPARE  
postgres=# execute a(1);  
 id | info   
----+------  
  1 | test  
(1 row)  
  
postgres=# select * from pg_prepared_statements;  
 name |                    statement                    |         prepare_time         | parameter_types | from_sql   
------+-------------------------------------------------+------------------------------+-----------------+----------  
 a    | prepare a(int) as select * from ps where id=$1; | 2017-06-19 15:23:24.68617+08 | {integer}       | t  
(1 row)

Recovery Method

The recovery method varies with the driver.

Recover the task according to the content in pg_prepared_statements.

NOTICE:  ("(P0_1,""select * from ps where id=$1;"",""2017-06-19 15:22:21.821454+08"",{integer},f)")  
  
PGresult *PQprepare(PGconn *conn,  
                    const char *stmtName,  
                    const char *query,  
                    int nParams,  
                    const Oid *paramTypes);

4) Cursor

If you use the "hold" option, the cursor will not be closed as the transaction ends. So, you should pay attention to such cursors when migrating sessions.

postgres=# \h declare  
Command:     DECLARE  
Description: define a cursor  
Syntax:  
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]  
    CURSOR [ { WITH | WITHOUT } HOLD ] FOR query  
  
postgres=# begin;  
BEGIN  
postgres=# declare cur cursor with hold for select * from ps where id=1;  
DECLARE CURSOR  
postgres=# end;  
COMMIT  
postgres=# select * from pg_cursors ;  
 name |                           statement                           | is_holdable | is_binary | is_scrollable |         creation_time           
------+---------------------------------------------------------------+-------------+-----------+---------------+-------------------------------  
 cur  | declare cur cursor with hold for select * from ps where id=1; | t           | f         | t             | 2017-06-19 15:27:58.604183+08  
(1 row)  
  
postgres=# close cur;  
CLOSE CURSOR  
postgres=# select * from pg_cursors ;  
 name | statement | is_holdable | is_binary | is_scrollable | creation_time   
------+-----------+-------------+-----------+---------------+---------------  
(0 rows)

Query Method

postgres=# select * from pg_cursors ;  
 name |                           statement                           | is_holdable | is_binary | is_scrollable |         creation_time           
------+---------------------------------------------------------------+-------------+-----------+---------------+-------------------------------  
 cur  | declare cur cursor with hold for select * from ps where id=1; | t           | f         | t             | 2017-06-19 15:27:58.604183+08  
(1 row)

Recovery Method

postgres=# declare cur cursor with hold for select * from ps where id=1;  
DECLARE CURSOR

5) Asynchronous Message Listening

You can use asynchronous messages of PostgreSQL to send events. The following is an example:

postgres=# listen a;  
LISTEN  
postgres=# notify a , 'hello i am digoal';  
NOTIFY  
Asynchronous notification "a" with payload "hello i am digoal" received from server process with PID 21412.

Query Method

Query asynchronous listening that has been enabled:

postgres=# select pg_listening_channels();  
 pg_listening_channels   
-----------------------  
 a  
(1 row)

Recovery Method

postgres=# listen a;  
LISTEN

6) Advisory Lock

You can use the advisory lock for flash sales, solving high-concurrency lock conflicts and the problem of sequence values without dilatation.

postgres=# \df *.*advis*  
                                        List of functions  
   Schema   |               Name               | Result data type | Argument data types |  Type    
------------+----------------------------------+------------------+---------------------+--------  
 pg_catalog | pg_advisory_lock                 | void             | bigint              | normal  
 pg_catalog | pg_advisory_lock                 | void             | integer, integer    | normal  
 pg_catalog | pg_advisory_lock_shared          | void             | bigint              | normal  
 pg_catalog | pg_advisory_lock_shared          | void             | integer, integer    | normal  
 pg_catalog | pg_advisory_unlock               | boolean          | bigint              | normal  
 pg_catalog | pg_advisory_unlock               | boolean          | integer, integer    | normal  
 pg_catalog | pg_advisory_unlock_all           | void             |                     | normal  
 pg_catalog | pg_advisory_unlock_shared        | boolean          | bigint              | normal  
 pg_catalog | pg_advisory_unlock_shared        | boolean          | integer, integer    | normal  
 pg_catalog | pg_advisory_xact_lock            | void             | bigint              | normal  
 pg_catalog | pg_advisory_xact_lock            | void             | integer, integer    | normal  
 pg_catalog | pg_advisory_xact_lock_shared     | void             | bigint              | normal  
 pg_catalog | pg_advisory_xact_lock_shared     | void             | integer, integer    | normal  
 pg_catalog | pg_try_advisory_lock             | boolean          | bigint              | normal  
 pg_catalog | pg_try_advisory_lock             | boolean          | integer, integer    | normal  
 pg_catalog | pg_try_advisory_lock_shared      | boolean          | bigint              | normal  
 pg_catalog | pg_try_advisory_lock_shared      | boolean          | integer, integer    | normal  
 pg_catalog | pg_try_advisory_xact_lock        | boolean          | bigint              | normal  
 pg_catalog | pg_try_advisory_xact_lock        | boolean          | integer, integer    | normal  
 pg_catalog | pg_try_advisory_xact_lock_shared | boolean          | bigint              | normal  
 pg_catalog | pg_try_advisory_xact_lock_shared | boolean          | integer, integer    | normal  
(21 rows)

The advisory lock includes transaction-level lock and session-level lock. During session migration, the session is in the IDLE state, so you can only use the session-level lock.

postgres=# select pg_try_advisory_lock(1);  
 pg_try_advisory_lock   
----------------------  
 t  
(1 row)

Query Method

postgres=# select * from pg_locks where locktype='advisory' and pid=pg_backend_pid();  
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |     mode      | granted | fastpath   
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------+---------+----------  
 advisory |    13158 |          |      |       |            |               |       0 |     1 |        1 | 3/123301864        | 21412 | ExclusiveLock | t       | f  
(1 row)

Recovery Method

You should note that the advisory lock should be a shared lock before recovery.

postgres=# select pg_try_advisory_lock(1);  
 pg_try_advisory_lock   
----------------------  
 t  
(1 row)

7) Sequence

After sequences are used, the VAL of the last-used sequence will be stored in the session, as well as the last VAL obtained after each sequence has been used.

postgres=# create sequence seq1;  
CREATE SEQUENCE  
  
没有被调用的序列，返回错误。  
postgres=# select currval('seq');  
ERROR:  currval of sequence "seq" is not yet defined in this session  
  
没有调用过任何序列，返回错误。  
postgres=# select lastval();  
ERROR:  lastval is not yet defined in this session  
  
调用序列  
postgres=# select nextval('seq1');  
 nextval   
---------  
       1  
(1 row)  
  
返回会话中指定序列最后一次调用的VAL  
postgres=# select currval('seq1');  
 currval   
---------  
       1  
(1 row)  
  
返回整个会话中最后一次序列调用的VAL  
postgres=# select lastval();  
 lastval   
---------  
       1  
(1 row)

Query Method

postgres=# select * from seq1;  
 last_value | log_cnt | is_called   
------------+---------+-----------  
          1 |      32 | t  
(1 row)  
postgres=# select nextval('seq1');  
 nextval   
---------  
       2  
(1 row)  
  
postgres=# select * from seq1;  
 last_value | log_cnt | is_called   
------------+---------+-----------  
          2 |      31 | t  
(1 row)

Recovery Method

Although you can set sequences to current values, it will affect the recovery. Therefore, it is not recommended.

Currently, there is no good way to recover the last VAL of sequences in sessions.

8) Temporary Tables

postgres=# create temp table tmp(id int, info text);  
CREATE TABLE  
  
postgres=# select oid,relname from pg_class where relpersistence ='t' and relkind='r' and pg_table_is_visible(oid);  
  oid  | relname   
-------+---------  
 44804 | tmp  
(1 row)

Query Method

postgres=# select oid,relname from pg_class where relpersistence ='t' and relkind='r' and pg_table_is_visible(oid);  
  oid  | relname   
-------+---------  
 44804 | tmp  
(1 row)  
  
********* QUERY **********  
SELECT c.oid,  
  n.nspname,  
  c.relname  
FROM pg_catalog.pg_class c  
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace  
WHERE c.relname ~ '^(tmp)$'  
  AND pg_catalog.pg_table_is_visible(c.oid)  
ORDER BY 2, 3;  
**************************  
  
********* QUERY **********  
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoids, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')  
, c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident  
FROM pg_catalog.pg_class c  
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)  
WHERE c.oid = '44810';  
**************************  
  
********* QUERY **********  
SELECT a.attname,  
  pg_catalog.format_type(a.atttypid, a.atttypmod),  
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)  
   FROM pg_catalog.pg_attrdef d  
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),  
  a.attnotnull, a.attnum,  
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t  
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation, a.attidentity,  
  NULL AS indexdef,  
  NULL AS attfdwoptions,  
  a.attstorage,  
  CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget, pg_catalog.col_description(a.attrelid, a.attnum)  
FROM pg_catalog.pg_attribute a  
WHERE a.attrelid = '44810' AND a.attnum > 0 AND NOT a.attisdropped  
ORDER BY a.attnum;  
**************************  
  
********* QUERY **********  
SELECT inhparent::pg_catalog.regclass,          pg_get_expr(c.relpartbound, inhrelid),          pg_get_partition_constraintdef(inhrelid) FROM pg_catalog.pg_class c JOIN pg_catalog.pg_inherits ON c.oid = inhrelid WHERE c.oid = '44810' AND c.relispartition;  
**************************  
  
********* QUERY **********  
SELECT pol.polname, pol.polpermissive,  
CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,  
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),  
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),  
CASE pol.polcmd  
WHEN 'r' THEN 'SELECT'  
WHEN 'a' THEN 'INSERT'  
WHEN 'w' THEN 'UPDATE'  
WHEN 'd' THEN 'DELETE'  
END AS cmd  
FROM pg_catalog.pg_policy pol  
WHERE pol.polrelid = '44810' ORDER BY 1;  
**************************  
  
********* QUERY **********  
SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname,  
  (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')  
   FROM pg_catalog.unnest(stxkeys) s(attnum)  
   JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND  
        a.attnum = s.attnum AND NOT attisdropped)) AS columns,  
  (stxkind @> '{d}') AS ndist_enabled,  
  (stxkind @> '{f}') AS deps_enabled  
FROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '44810'  
ORDER BY 1;  
**************************  
  
********* QUERY **********  
SELECT pub.pubname  
 FROM pg_catalog.pg_publication pub  
 LEFT JOIN pg_catalog.pg_publication_rel pr  
      ON (pr.prpubid = pub.oid)  
WHERE pr.prrelid = '44810' OR pub.puballtables  
ORDER BY 1;  
**************************  
  
********* QUERY **********  
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '44810' AND c.relkind != 'p' ORDER BY inhseqno;  
**************************  
  
********* QUERY **********  
SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid) FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '44810' AND EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '44810') ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;  
**************************  
  
                                   Table "pg_temp_3.tmp"  
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description   
--------+---------+-----------+----------+---------+----------+--------------+-------------  
id     | integer |           |          |         | plain    |              |   
 info   | text    |           |          |         | extended |              |

Recovery Method

postgres=# create temp table tmp(id int, info text);  
CREATE TABLE

Summary

During primary/standby switchover, the session resource state’s migration can significantly improve user experience and simplify database hardware maintenance and migration.

Middleware needs to maintain the mapping relationship between client connections and database sessions, and the mapping relationship also needs to be consistent after migration.

References

PostgreSQL Database database migration Database Switchover Session Resource Migration
0 0 0
Share on

Read previous post:

How PostgreSQL Is Compatible with SQL Server

Read next post:

PostgreSQL: Risk Control Assistant for Small Loans and Angel Investment Based on Graph Search and Mapping Analysis

digoal

282 posts | 25 followers

You may also like

Comments

digoal

282 posts | 25 followers

Related Products

More Posts by digoal

See All