postgres 查看历史sql执行记录

默认是没有日志记录的,需要做如下调整:

1,修改配置文件,postgres.conf  # 该文件在你的data 文件夹下,比如我的:/var/lib/postgresql/data/

　　调整的参数:

　　修改前注意备份postgres.conf

log_statement = 'all'

# This is used when logging to stderr:
logging_collector = on        # Enable capturing of stderr and csvlog
                    # into log files. Required to be on for
                    # csvlogs.
                    # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'log'            # directory where log files are written,
                    # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
                    # can include strftime() escapes
log_file_mode = 0600            # creation mode for log files,
                    # begin with 0 to use octal notation
#log_truncate_on_rotation = off        # If on, an existing log file with the
                    # same name as the new log file will be
                    # truncated rather than appended to.
                    # But such truncation only occurs on
                    # time-driven rotation, not on restarts
                    # or size-driven rotation.  Default is
                    # off, meaning append to existing files
                    # in all cases.
#log_rotation_age = 1d            # Automatic rotation of logfiles will
                    # happen after that time.  0 disables.
#log_rotation_size = 10MB        # Automatic rotation of logfiles will
                    # happen after that much log output.
                    # 0 disables.

2, 重启posgres 服务,

3,在该路径下查看你的日志文件:

　　/var/lib/postgresql/data/log

 会得到类似如下的日志记录:

2020-01-04 08:38:14.236 UTC [237] LOG:  statement: SELECT "ir_module_module"."id" as "id",COALESCE("ir_module_module__summary"."value", "ir_module_module"."summary") as "summary","ir_module_module"."contributors" as "contributors","ir_module_module"."license" as "license","ir_module_module"."write_date" as "write_date","ir_module_module"."demo" as "demo","ir_module_module"."published_version" as "published_version","ir_module_module"."menus_by_module" as "menus_by_module","ir_module_module"."author" as "author","ir_module_module"."application" as "application","ir_module_module"."url" as "url","ir_module_module"."reports_by_module" as "reports_by_module","ir_module_module"."maintainer" as "maintainer","ir_module_module"."to_buy" as "to_buy","ir_module_module"."create_uid" as "create_uid","ir_module_module"."name" as "name","ir_module_module"."icon" as "icon","ir_module_module"."views_by_module" as "views_by_module","ir_module_module"."website" as "website","ir_module_module"."write_uid" as "write_uid",COALESCE("ir_module_module__shortdesc"."value", "ir_module_module"."shortdesc") as "shortdesc","ir_module_module"."sequence" as "sequence","ir_module_module"."create_date" as "create_date","ir_module_module"."category_id" as "category_id",COALESCE("ir_module_module__description"."value", "ir_module_module"."description") as "description","ir_module_module"."state" as "state","ir_module_module"."auto_install" as "auto_install","ir_module_module"."latest_version" as "latest_version" FROM "ir_module_module" LEFT JOIN 
                    (SELECT res_id, value FROM "ir_translation"
                     WHERE type='model' AND name='ir.module.module,summary' AND lang='zh_CN' AND value!='')
                 as "ir_module_module__summary" ON ("ir_module_module"."id" = "ir_module_module__summary"."res_id") LEFT JOIN 
                    (SELECT res_id, value FROM "ir_translation"
                     WHERE type='model' AND name='ir.module.module,shortdesc' AND lang='zh_CN' AND value!='')
                 as "ir_module_module__shortdesc" ON ("ir_module_module"."id" = "ir_module_module__shortdesc"."res_id") LEFT JOIN 
                    (SELECT res_id, value FROM "ir_translation"
                     WHERE type='model' AND name='ir.module.module,description' AND lang='zh_CN' AND value!='')
                 as "ir_module_module__description" ON ("ir_module_module"."id" = "ir_module_module__description"."res_id") WHERE "ir_module_module".id IN (254)
2020-01-04 08:38:14.238 UTC [237] LOG:  statement: SELECT "ir_module_module"."id" as "id",COALESCE("ir_module_module__summary"."value", "ir_module_module"."summary") as "summary","ir_module_module"."contributors" as "contributors","ir_module_module"."license" as "license","ir_module_module"."write_date" as "write_date","ir_module_module"."demo" as "demo","ir_module_module"."published_version" as "published_version","ir_module_module"."menus_by_module" as "menus_by_module","ir_module_module"."author" as "author","ir_module_module"."application" as "application","ir_module_module"."url" as "url","ir_module_module"."reports_by_module" as "reports_by_module","ir_module_module"."maintainer" as "maintainer","ir_module_module"."to_buy" as "to_buy","ir_module_module"."create_uid" as "create_uid","ir_module_module"."name" as "name","ir_module_module"."icon" as "icon","ir_module_module"."views_by_module" as "views_by_module","ir_module_module"."website" as "website","ir_module_module"."write_uid" as "write_uid",COALESCE("ir_module_module__shortdesc"."value", "ir_module_module"."shortdesc") as "shortdesc","ir_module_module"."sequence" as "sequence","ir_module_module"."create_date" as "create_date","ir_module_module"."category_id" as "category_id",COALESCE("ir_module_module__description"."value", "ir_module_module"."description") as "description","ir_module_module"."state" as "state","ir_module_module"."auto_install" as "auto_install","ir_module_module"."latest_version" as "latest_version" FROM "ir_module_module" LEFT JOIN 
                    (SELECT res_id, value FROM "ir_translation"
                     WHERE type='model' AND name='ir.module.module,summary' AND lang='zh_CN' AND value!='')
                 as "ir_module_module__summary" ON ("ir_module_module"."id" = "ir_module_module__summary"."res_id") LEFT JOIN 
                    (SELECT res_id, value FROM "ir_translation"
                     WHERE type='model' AND name='ir.module.module,shortdesc' AND lang='zh_CN' AND value!='')
                 as "ir_module_module__shortdesc" ON ("ir_module_module"."id" = "ir_module_module__shortdesc"."res_id") LEFT JOIN 
                    (SELECT res_id, value FROM "ir_translation"
                     WHERE type='model' AND name='ir.module.module,description' AND lang='zh_CN' AND value!='')
                 as "ir_module_module__description" ON ("ir_module_module"."id" = "ir_module_module__description"."res_id") WHERE "ir_module_module".id IN (28)
2020-01-04 08:38:14.240 UTC [237] LOG:  statement: SELECT "ir_module_module"."id" as "id",COALESCE("ir_module_module__summary"."value", "ir_module_module"."summary") as "summary","ir_module_module"."contributors" as "contributors","ir_module_module"."license" as "license","ir_module_module"."write_date" as "write_date","ir_module_module"."demo" as "demo","ir_module_module"."published_version" as "published_version","ir_module_module"."menus_by_module" as "menus_by_module","ir_module_module"."author" as "author","ir_module_module"."application" as "application","ir_module_module"."url" as "url","ir_module_module"."reports_by_module" as "reports_by_module","ir_module_module"."maintainer" as "maintainer","ir_module_module"."to_buy" as "to_buy","ir_module_module"."create_uid" as "create_uid","ir_module_module"."name" as "name","ir_module_module"."icon" as "icon","ir_module_module"."views_by_module" as "views_by_module","ir_module_module"."website" as "website","ir_module_module"."write_uid" as "write_uid",COALESCE("ir_module_module__shortdesc"."value", "ir_module_module"."shortdesc") as "shortdesc","ir_module_module"."sequence" as "sequence","ir_module_module"."create_date" as "create_date","ir_module_module"."category_id" as "category_id",COALESCE("ir_module_module__description"."value", "ir_module_module"."description") as "description","ir_module_module"."state" as "state","ir_module_module"."auto_install" as "auto_install","ir_module_module"."latest_version" as "latest_version" FROM "ir_module_module" LEFT JOIN 
                    (SELECT res_id, value FROM "ir_translation"
                     WHERE type='model' AND name='ir.module.module,summary' AND lang='zh_CN' AND value!='')
                 as "ir_module_module__summary" ON ("ir_module_module"."id" = "ir_module_module__summary"."res_id") LEFT JOIN 
                    (SELECT res_id, value FROM "ir_translation"
                     WHERE type='model' AND name='ir.module.module,shortdesc' AND lang='zh_CN' AND value!='')
                 as "ir_module_module__shortdesc" ON ("ir_module_module"."id" = "ir_module_module__shortdesc"."res_id") LEFT JOIN 
                    (SELECT res_id, value FROM "ir_translation"
                     WHERE type='model' AND name='ir.module.module,description' AND lang='zh_CN' AND value!='')
                 as "ir_module_module__description" ON ("ir_module_module"."id" = "ir_module_module__description"."res_id") WHERE "ir_module_module".id IN (264)

 

ps: 该postgres服务是使用dockers搭建的.