PostgreSQL source code customization: online global read only

PostgreSQL source code customization: online global read only

Based on a certain functional requirement on the cloud, a function similar to MySQL global read only has been recently implemented. PostgreSQL's read only function does not need to be implemented by restarting PG. You can directly change the PG instance level online.

Global read only and global read write Functions to quickly implement master-slave switchover. This greatly shortens the master-slave switchover time and improves the high availability of PG. It makes up for PG's shortcomings in this function.

The PostgreSQL global read only version customized by source code has many obvious advantages:

1. When global read only is set, the new query will not be blocked.

When a new session is introduced, read only takes effect directly and the PG instance does not need to be restarted. When global read only is set, the new session will not be blocked. This avoids connection congestion.

2. Ongoing transactions are treated in different levels

A. If it is a transaction block, that is, the "BEGIN" Statement initiated by the user, the statements that have been executed are not affected. The statements executed after this event are restricted by read only and cannot be executed by DML. This transaction will be terminated.

B. If it is a running statement, such as a large insert or update statement. This operation will not be affected. To set global read only, you must wait until the operation is completed before returning.

The global read only operation waits for all running DML operations to be completed and then returns a response after "Immediate Checkpoint" is completed. The reason for doing so is to ensure database status consistency.

Especially in the case of master-slave switchover, it is more critical and important.

3. interrupt handling

If global read only fails, it will be rolled back and reset to read write status.

The following shows the implementation result of source code patch:

Session 1: view the read only status of the current database and display the status "Read Write", that is, PG instance-level read/Write.

Session 2: starts a transaction. The transaction contains two insert statements. We first execute an insert statement, but do not commit the transaction.

Table creation statement:

Create table grl_test (id int );

Begin;

Insert into grl_test values (1 );

Session 1: Set the PG instance to global read only. At this point, we can see that the operation cannot be set to "Read Only" status, and "Read Only" is not returned.

The reason is that session 2 is not submitted. This is in line with our original design intention, that is, when the read only setting is successful, the database is in the consistent State, and there is no user-level write transaction since then.

Session 2: attempt to initiate the second insert statement. We can see that the insert operation failed. The reason is that when session 1 is set to global read only, although no operation is returned,

However, any new DML operations and new transactions have been bound to the read only state. It is easy to understand that new write transactions are not allowed, but why cannot DML operations be performed in previously initiated transactions?

The reason for doing so is:

We do not want to run the transaction block that is earlier than the read only setting without limit. This causes the global read only operation to continue. If the read only setting fails,

It directly affects the fast switchover between the master and slave nodes. Careful students may find that this is limited to transaction blocks. Indeed, for the difference between transaction blocks and general transactions, see my other article "PostgreSQL transaction model introduction".

Because the General transaction is only at the command level, it will end after the execution. We can wait, which is easy to understand. If we forcibly terminate such operations, it will have a great impact on the application. General command

Level transactions always end very quickly, especially in OLTP systems, they are basically simple command-level transactions. Transaction blocks are generally complicated in logic, which is also used for data consistency consideration,

Failed. Just run it again. In general, this is based on the actual needs of OLTP application systems on the market.

Insert into grl_test values (2 );

Session 1: Now let's look at session 1. The global read only setting is successful. Session 2 is terminated because the second command violates read only. Therefore, there is no

Running transaction. After setting global read only, the checkpoint is pushed forward. We can use this checkpoint to perform master-slave consistency switching.

The read only status of a PostgreSQL instance is changed online. Let's change the instance online back to read write. Is it very convenient?

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: