| |
Based on the functional requirements of a cloud, recently achieved similar to the MySQL global read only function. PG read only function, no longer need to achieve through the re-start to achieve PG. You can change the PG instance level directly online
Global read only and global read write function, in order to achieve rapid switching between the main function. Greatly shortening the main switchover time, improve the PG's high-performance. Make up for the lack of PG in this function.
The source code to change the realization of the PG version of the global read only There are many obvious advantages:
1. When setting global read only, the new query will not be blocked
New session, read only directly into effect, do not need to re-start PG instance. And does not block new sessions when setting global read only. Thus avoiding connection congestion.
2. Are running the business, the level of treatment
A. If the transaction block, that is, the user-initiated "BEGIN" statement, then the statement has been executed is not affected. For this statement will be executed later in the transaction, will be read only constraints, can not perform DML operations. This transaction is terminated.
B. If you are running the statement, for example, there is a large insert or update. Then this operation will not be affected, set global read only need to wait for this operation is complete before returning.
Global read only operation will wait for all running DML operation is completed, and done "Immediate Checkpoint", and then return to the response. The reason for this is to ensure the consistency of the database state.
Especially in the case of active and standby switching, more critical and important.
3. Interrupt handling
If the setting global read only fails, it will be rolled back, will be re-set to read write state.
The following shows the source code to achieve the following results:
Session 1: View the current database read only status, showing the current "Read Write" state, that is, PG instance level can read and write.
Session 2: from the affairs of the two insert sentences. 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: Try setting the PG instance to global read only. At this point you can see, can not be set to "Read Only" state, set the "Read Only" operation did not return.
The reason is not submitted for session 2. This is in line with our original intention is to set read only successful return, the database for the same state, since there is no user-level write transactions.
Session 2: Try to launch a second insert statement. Insert failure can be seen. The reason is that session 1 tries to set global read only, although the operation did not return,
But any new DML operations and new transactions have been constrained to the read only state. Does not allow the new write transaction is easy to understand, but why not allow the transaction has been initiated before, not DML operation?
The reason for this is:
We do not want to set before the read only before the transaction block, unlimited run down. This will allow the setting global read only operation has been going on. If the read only setting is unsuccessful,
Which directly affects the fast switchover between active and standby. Careful students may find that this is limited to the transaction block. Indeed, the difference between a transaction block and a normal transaction can be found in another article, PostgreSQL Transaction Model Presentation.
Because the general affairs, only in the command level, finished on the end. We can wait, the average user is also easy to understand, if we force the termination of such operations, the impact will be relatively large. General command
Level of affairs is always very fast end, especially in the OLTP system, are basically a simple command-level transactions. General logic of the transaction block is more complex, with this limitation, but also for data consistency considerations,
Failure, at most, re-run on the line. In general, this is based on the current market OLTP class application system and the actual needs of the custom.
Insert into grl_test values (2);
Session 1: At this point we look at session 1, set global read only has been successful. Session 2 because the second order violates the read only, resulting in the termination of the transaction. Therefore, there is no
Running transaction. Global read only When set is complete, the checkpoint is pushed forward. We can take this checkpoint shall prevail, the main and backup consistency switch.
Above full realization of the online change PG instance read only state. We'll change the instance back to read write. Is not very convenient? |
|
|
|