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

This article mainly introduces the example analysis of system catalogue and system management in PostgreSQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

I talked about how to stop the session that the user is executing, but here PG provides different ways to terminate it. Here is an extension of why you want to stop the user's connection.

The following reasons can be summarized

1 the query time of users is long, which has affected the normal operation of the system, such as vacuum-related operations.

2 you need to delete the database, but the database that needs to be processed at present is occupied by some threads, so you need to clean up the session of these connections

3 due to accident, the application establishes a large number of connections to the database and reaches the maximum, so it is necessary to temporarily clean up some connections and do some mitigating operations when the application handles exceptions. And release the associated wasted memory.

PG provides two modes of operation: pg_terminate_backend (pid) and pg_cancel_backend (pid). The biggest difference between these two ways is that pg_terminate_backend will disconnect the application from PG, and the application will report a loss of connection, while pg_cancel_backend will only stop the current running transaction and will not make a break between the application and the database.

For example, if you want to delete a database that has always had user connections, you will not be able to clean up the related connections even if you keep using pg_terminate_backend.

Stop the test library

UPDATE pg_database set datallowconn = 'false' WHERE datname =' test'

New connections are blocked by setting the state of whether connections are allowed in the database, and an error will be reported when connecting to a database that forbids connections again.

So one thing, if you learn it systematically, you will find more questions and more answers.

At the end of the above story about database connections and disconnections, here comes the configuration of the database.

It is mentioned in the book that there are three ways to get the parameters set by PostgreSQL

1 get it through the postgresql.conf configuration file

2 through select current_setting ('configured name')

3 provide through show work_mem

It is not mentioned in the book that, for expansion here, although the configuration values of PG can be obtained in all three ways, in fact, the postgresql.conf readout is that the initial value of the system has not changed after startup, and if the parameter values that can be changed during the operation of the system are changed, it cannot be displayed through postgresql.conf.

It is possible to modify some system values through set_config.

For example

Mode 1, which is valid only for the currently running statement

Mode 2 is valid for the current session

In fact, the difference between the two methods is the parameter true or false after set_config. Selecting false indicates that the parameter takes effect throughout the session. When you open a process, it is still consistent with the value of the postgresql.conf configuration file.

Of course, the book also suggests that not all configurations can be adjusted through set_config, and the values that need to be restarted cannot be adjusted through set_config.

How to quickly get the configuration values of the system in postgresql can also be done by the following statement

SELECT name, current_setting (name), source FROM pg_settings

WHERE source IN ('configuration file')

Of course, checking the capacity of the database is also an important task of daily system management.

SELECT pg_database.datname, pg_size_pretty (pg_database_size (pg_database.datname)) AS size FROM pg_database

SELECT tablename, pg_size_pretty (pg_total_relation_size (schemaname | |'. | | tablename)) FROM pg_tables WHERE schemaname = 'public'

The above method is somewhat similar to oracle counting the size of tables in schema. Like sql server, postgresql has both the concept of multiple libraries under one instance and the concept of multiple schema under one library. Both ORACLE and MYSQL are missing in these two concepts, so we will not expand them here.

The problem of how the commonly used PG looks at the index of the current library

SELECT indexrelid::regclass, pg_size_pretty (pg_relation_size (indexrelid::regclass)) FROM pg_index WHERE indexrelid::regclass::text like 'idx%'

Here the need for a unified index of the establishment of the name of the way, more accurate statistics of all qualified memory display and size.

The above is all the contents of the article "sample Analysis of system Catalog and system Management in PostgreSQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.