Postgres log Utility

Postgres log utility has to say that Postgres logs (pg_log, similar to the oracle alter file, rather than pg_xlog) are indeed flexible and feature-rich, the following are some management functions using ipvs logs. The parameters involved are in the file $ PGDATApostgresql. conf. OS: CentOS6.2DB: Postgre

Postgres log utility has to say that Postgres logs (pg_log, similar to the oracle alter file, rather than pg_xlog) are indeed flexible and feature-rich, the following are some management functions using ipvs logs. The parameters involved are in the file $ PGDATA/postgresql. conf. OS: CentOS 6.2 DB: Postgre

Postgres log Utility

I have to say that S logs (pg_log, similar to oracle's alter file, non-pg_xlog) are indeed flexible and feature-rich, the following are some management functions using ipvs logs. The parameters involved are in the file $ PGDATA/postgresql. conf.

OS: CentOS 6.2

DB: ipvs 9.2.3

1. log auditing

Audit records the login and exit of a user and the actions performed in the database after login. You can set different levels of audit based on different security levels,

The parameter files involved here include:

Logging_collector -- whether to enable log collection. The default value is off. to restart the database

Log_destination -- log record type. The default value is stderr. Only error output is recorded.

Log_directory -- Log Path. The default value is $ PGDATA/pg_log.

Log_filename -- log name. The default value is postgresql-% Y-% m-% d _ % H % M % S. log.

Log_connections -- whether to write logs when the user session logs on. The default value is off.

Log_disconnections -- whether to write logs when the user session exits. The default value is off.

Log_rotation_age -- keep the maximum length of a single file. The default value is 1d, which can be 1 h, 1 min, or 1 s. I personally think it is not practical.

Log_rotation_size -- retain the maximum size of a single file. The default value is 10 MB.

Configuration value:

Logging_collector = on

Log_destination = 'svlog'

Log_directory = '/home/S/pg_log'

Log_filename = 'postgresql-% Y-% m-% d _ % H % M % S. Log'

Log_connections = on

Log_disconnections = on

Log_rotation_age = 1d

Log_rotation_size = 20 MB

Restart the database after configuration, and check the log

[S @ localhost pg_log] $ ls-l

Total 4

-Rw -------. 1 postgres 672 Mar 29 postgresql-2013-03-29_000000.csv

-Rw -------. 1 postgres 0 Mar 29 postgresql-2013-03-29_000000.log

[S @ localhost pg_log] $

-- Log on and exit. The log contains the accessed IP address (local), accessed user, login time, and exit time, which is effective for checking logon and exit of Super Users.

[S @ localhost pg_log] $ psql

Psql (9.2.3)

Type "help" for help.

Postgres = # \ q

[Postgres @ localhost pg_log] $ tail-f postgresql-2013-03-29_000000.csv

10:38:36. 934 PDT, 2236, "", 5155d19c. 8bc, 1, "", 10:38:36 PDT, 0, LOG, 00000, "connection already ed: host = [local]", ""

10:38:36. 938 PDT, "postgres", "postgres", 2236, "[local]", 5155d19c. 8bc, 2, "authentication", 10:38:36 PDT, 2/11858, 0, LOG, 00000, "connection authorized: user = s database = s ",,,,,,,,, ""

10:38:42. 365 PDT, "postgres", "postgres", 2236, "[local]", 5155d19c. 8bc, 3, "idle", 10:38:36 PDT, 0, LOG, 00000, "disconnection: session time: 0:00:05. 431 user = s database = postgres host = [local] "," psql"

The operations performed after a user logs on to the database are recorded. The S log is divided into three types, which are controlled by the pg_statement parameter. The default pg_statement parameter value is none, that is, no record is recorded, you can set ddl (record create, drop and alter), mod (record ddl + insert, delete, update and truncate), and all (mod + select ).

Example:

[S @ localhost ~] $ Vi $ PGDATA/postgresql. conf

Log_statement = ddl

S = # show log_statement;

Log_statement

---------------

Ddl

(1 row)

S = # create table t_ken_yon (id int );

CREATE TABLE

S = # drop table t_ken_yon;

DROP TABLE

Postgres = #

[Postgres @ localhost pg_log] $ tail-f postgresql-2013-03-29_000000.csv

11:01:29. 048 PDT, "postgres", "postgres", 2324, "[local]", 5155d681. 914,3, "idle", 10:59:29 PDT, 2/11945, 0, LOG, 00000, "statement: create table t_ken_yon (id int );",,,,,,,,, "psql"

11:01:36. 087 PDT, "postgres", "postgres", 2324, "[local]", 5155d681. 914,4, "idle", 10:59:29 PDT, 2/11948, 0, LOG, 00000, "statement: drop table t_ken_yon;", "psql"

-- Change to mod level and reload

S = # show log_statement;

Log_statement

---------------

Mod

(1 row)

S = # insert into t_ken_yon values (1), (2 );

INSERT 0 2

S = # delete from t_ken_yon where id = 1;

DELETE 1

[Postgres @ localhost pg_log] $ tail-f postgresql-2013-03-29_000000.csv

11:04:08. 148 PDT, 5554, 514933a6. 15b2, 42, 20:57:26 PDT, 0, LOG, 00000, "received SIGHUP, reloading configuration files ",,,,,,,,,""

11:04:08. 151 PDT, 5554, 514933a6. 15b2, 43, 2013-03-19 20:57:26 PDT, 0, LOG, 00000, "parameter" "log_statement" "changed to" "mod """,,,,,,,,, ""

11:05:33. 346 PDT, "postgres", "postgres", 2324, "[local]", 5155d681. 914,6, "idle", 10:59:29 PDT, 2/11952, 0, LOG, 00000, "statement: insert into t_ken_yon values (1), (2 ); "," psql"

11:05:52. 033 PDT, "postgres", "postgres", 2324, "[local]", 5155d681. 914,7, "idle", 10:59:29 PDT, 2/11953, 0, LOG, 00000, "statement: delete from t_ken_yon where id = 1 ;",,,,,,,,, "psql"

-- Change to the all level and reload

S = # show log_statement;

Log_statement

---------------

All

(1 row)

S = # select * from t_ken_yon;

Id

----

2

(1 row)

[Postgres @ localhost pg_log] $ tail-f postgresql-2013-03-29_000000.csv

11:07:14. 820 PDT, 5554, 514933a6. 15b2, 44, 20:57:26 PDT, 0, LOG, 00000, "received SIGHUP, reloading configuration files ",,,,,,,,,""

11:07:14. 821 PDT, 5554, 514933a6. 15b2, 45, 2013-03-19 20:57:26 PDT, 0, LOG, 00000, "parameter" "log_statement" "changed to" "all """,,,,,,,,, ""

11:07:19. 784 PDT, "postgres", "postgres", 2324, "[local]", 5155d681. 914,8, "idle", 10:59:29 PDT, 2/11954, 0, LOG, 00000, "statement: show log_statement;", "psql"

11:07:28. 631 PDT, "postgres", "postgres", 2324, "[local]", 5155d681. 914,9, "idle", 10:59:29 PDT, 2/11955, 0, LOG, 00000, "statement: select * from t_ken_yon;", "psql"

It is enough to set the audit level of the general OLTP system to ddl, because the impact of the output of various SQL statements on performance is still quite large, and the mod mode can be set for those with a higher security level, you can purchase a device at the network layer to monitor and parse it if you have the necessary conditions.

2. Locate slow query SQL

You can set a certain duration parameter (log_min_duration_statement) to record all SQL statements that exceed this duration, which is very effective in identifying slow queries of the current database. For example, if log_min_duration_statement = 2 s, reload is required to record the SQL statement that exceeds 2 s.

Example:

S = # show log_min_duration_statement;

Log_min_duration_statement

----------------------------

2 s

(1 row)

Postgres = # \ timing

Timing is on.

Postgres = # select now (), pg_sleep (1 );

Now | pg_sleep

---------------------------- + ----------

12:36:48. 13353-07 |

(1 row)

Time: 1001.844 MS

Postgres = # select now (), pg_sleep (4 );

Now | pg_sleep

------------------------------- + ----------

12:36:28. 309595-07 |

(1 row)

Time: 4002.273 MS

[Postgres @ localhost pg_log] $ tail-f postgresql-2013-03-29_000000.csv

12:36:19. 265 PDT, "postgres", "postgres", 2324, "[local]", 5155d681. 10:59:29 PDT, 2/0, 0, LOG, 00000, "duration: 4027.183 MS statement: SELECT now (), pg_sleep (4 ); "," psql"

We can see the SQL statement that records only 4 seconds, but not 1 second.

3. Monitor the database checkpoint

When a database performs a large update operation, if the parameter is improperly set, a large amount of warning information will be left in the log. Frequent checkpoint operations will lead to system slowdown, such:

17:01:39. 523 CST, 10350, 50bd676b. 286e, 1, 11:00:59 CST, 0, LOG, 00000, "checkpoints are occurring too frequently (8 seconds apart )",, "Consider increasing the configuration parameter" "checkpoint_segments "". ",,,,,,,""

17:01:50. 427 CST, 10350, 50bd676b. 286e, 2, 11:00:59 CST, 0, LOG, 00000, "checkpoints are occurring too frequently (11 seconds apart )",, "Consider increasing the configuration parameter" "checkpoint_segments "". ",,,,,,,""

But it does not record the normal checkpoint of the system. If you want to see how many checkpoints occur in a day and the details of each checkpoint, such as buffer and sync, you can set log_checkpoints. The default value of this parameter is off. For example, modify log_checkpoints = on:

S = # show log_checkpoints;

Log_checkpoints

-----------------

On

(1 row)

S = # checkpoint;

CHECKPOINT

Postgres = #

[Postgres @ localhost pg_log] $ tail-f postgresql-2013-03-29_000000.csv

12:43:38. 900 PDT, 5557, 514933a7. 15b5, 45, 20:57:27 PDT, 0, LOG, 00000, "checkpoint starting: immediate force wait ",,,,,,,,,""

12:43:38. 941 PDT, 5557, 514933a7. 15b5, 46, 2013-03-19 20:57:27 PDT, 0, LOG, 00000, "checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file (s) added, 0 removed, 0 recycled; write = 0.009 s, sync = 0.000 s, total = 0.040 s; sync files = 0, longest = 0.000 s, average = 0.000 s ",,,,,,,,,""

4. Monitor database locks

Database locks can usually be found in the pg_locks system table, but this is only the current lock table/row information. If you want to check the number of locks exceeding the deadlock time in a day, you can set and view in the log. log_lock_waits is off by default and can be set to enable it. This can distinguish between resource shortage and lock wait when SQL is slow. Example:

Postgres = # show log_lock_waits;

Log_lock_waits

----------------

On

(1 row)

S = # show deadlock_timeout;

Deadlock_timeout

------------------

1 s

(1 row)

-- Simulate lock

S = # begin;

BEGIN

S = # SELECT * FROM t_ken_yon;

Id

----

11

(1 row)

S = # delete from t_ken_yon;

DELETE 1

-- Another session

S = # begin;

BEGIN

S = # delete from t_ken_yon;

-- View logs

[Postgres @ localhost pg_log] $ tail-f postgresql-2013-03-29_000000.csv

14:01:02. 673 PDT, "postgres", "postgres", 3056, "[local]", 5155f4d9. bf0, 6, "DELETE waiting", 13:08:57 PDT, 5/12502, 2659, LOG, 00000, "process 3056 still waiting for ShareLock on transaction 2658 after 1000.398 ms", "delete from t_ken_yon;", "psql"

14:02:06. 208 PDT, "postgres", "postgres", 3056, "[local]", 5155f4d9. bf0, 7, "DELETE waiting", 13:08:57 PDT, 5/12502, 2659, LOG, 00000, "process 3056 acquired ShareLock on transaction 2658 after 64535.339 ms ",,,,,, "delete from t_ken_yon;", "psql"

14:02:06. 209 PDT, "postgres", "postgres", 3056, "[local]", 5155f4d9. bf0, 8, "DELETE", 13:08:57 PDT, 5/12502, 2659, LOG, 00000, "duration: 64536.118 MS statement: delete from t_ken_yon; "," psql"

There are also some Debug functions that are suitable for modifying source code debugging, which are not needed in general systems.