Shulou(Shulou.com)06/01 Report--

This article mainly introduces how the postgresql query from the library is terminated, it has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

In the PG stream replication scenario, under the default configuration, if the query from the PG slave database is executed for a long time, an error will occur. Prompt

ERROR: canceling statement due to conflict with recovery

DETAIL: User query might have needed to see row versions that must be removed.

According to the error information, during the long-time query on the main database, because the records involved in this query may be updated or deleted on the main database, according to PostgreSQL's mvcc mechanism, the updated or deleted data is not immediately deleted from the physical block, but then the autovacuum process VACUUM the old version of the data, and after the VACUUM of the old version of the updated or deleted data on the main database, the same operation will be performed from the database. This conflicts with the current query from the library, causing the query to be interrupted and throwing the above error.

In fact, PostgreSQL provides configuration parameters to reduce or avoid the probability of this situation, including the following two parameters:

Maxstandby_ streaming_delay:

This parameter defaults to 30 seconds. When the slave database executes SQL, it may conflict with the WAL being applied. If the query is not completed in 30 seconds, it will be aborted. Note that 30 seconds is not the maximum execution time allowed for a single query on the slave database, but refers to the maximum WAL delay time allowed when WAL is applied on the slave database, so the execution time of the query on the slave database may be aborted if it is less than the value set by this parameter. This parameter can be set to-1, indicating that when the WAL application process on the slave library conflicts with the query executed on the slave library, the WAL application process waits until the slave query completes.

Hotstandby_feedback:

By default, the master database will not be notified when the query is executed from the slave library. When this parameter is set to on, the master library will be notified when the query is executed from the slave library. During the execution of the query from the slave library, the master database will not clean up the old versions of the data rows needed by the slave database. Therefore, the query on the slave database will not be aborted. However, this method will also bring some disadvantages, and the tables on the master database may be inflated. The expansion of the master database table is related to the write transaction on the table and the execution time of the slave library. This parameter defaults to off.

Case study:

CentOS7.5+PG version 11.5

PgMaster as the main library

PgSlave is the backup library.

Adjust the parameters of the standby library, set up

Max_standby_streaming_delay = 10s # (it is easy to see the effect in the test. This parameter is adjusted relatively low)

Hot_standby_feedback = off

Then the configuration of PG under reload makes it effective.

Create a test table on the main library pgMaster:

\ C postgres

Create table test_per2 (id int, flag int)

Insert into test_per2 (id) select * from generate_series (1jie 1000000)

Write the pgbench stress test script update_per2.sql as follows:

\ set v_id random (11400000)

Update test_per2 set flag='1' where id=:v_id

Start the pressure test:

Pgbench-c 8-T 120-d postgres-Upostgres-n N-M prepared-f update_per2.sql

Then, go to the pgSlave repository to perform the following query operation:

Postgres=# select pg_sleep (12), * from test_per2 limit 10

ERROR: canceling statement due to conflict with recovery

DETAIL: User query might have needed to see row versions that must be removed.

Time: 729.120 ms

Here, this error scene can be easily reproduced.

There are two solutions:

Option 1. Increase the value of max_standby_streaming_delay parameters.

We can adjust max_standby_streaming_delay to-1 to get around this error, or we can increase this value.

For example, adjust the parameter max_standby_streaming_delay of slave database to 120s:

Max_standby_streaming_delay = 120s

Hot_standby_feedback = off

Then use pg_ctl reload to make it effective

Then, go to the pgSlave repository again to perform the next query operation, and you can see that the query can be executed normally:

Postgres=# select pg_sleep (12), id, flag from test_per2 limit 2

Pg_sleep | id | flag

-+-

| | 1 | NULL |

| | 2 | NULL |

(2 rows)

Option 2. Enable hot_standby_feedback parameters.

When the hot_standby_feedback parameter is set to on, the master database will be notified when the slave database executes the query. During the execution of the large query from the slave library, the master database will not clean up the old version of the data rows needed by the slave database.

Parameters that need to be enabled on the repository:

Max_standby_streaming_delay = 10s

Hot_standby_feedback = on # mainly because this parameter can be set to on

Then use pg_ctl reload to make it effective

At this time, if you go to the database to query, you can find that the query is successful:

Postgres=# select pg_sleep (2), id, flag from test_per2 limit 2

Pg_sleep | id | flag

-+-

| | 1 | NULL |

| | 2 | NULL |

(2 rows)

Postgres=# select pg_sleep (12), id, flag from test_per2 limit 2

Pg_sleep | id | flag

-+-

| | 1 | NULL |

| | 2 | NULL |

(2 rows)

In both of the above two ways, there are some disadvantages:

1. Set the max_standby_streaming_delay parameter to-1. In this way, slow queries on the repository may consume a lot of host resources due to long execution. It is recommended to set a reasonable value according to the application situation.

2. Set hot_standby_feedback=on, which may inflate some tables in the main library.

Whichever of the two methods we choose, we should strengthen the monitoring of the slow query of the main database and backup database of convective replication, and analyze whether manual maintenance is needed.

Thank you for reading this article carefully. I hope the article "what to do if the postgresql query from the database is terminated" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.