pgsql查看数据库表的更新时间

你可以使用以下 SQL 查询语句来查看 PostgreSQL 数据库中表的更新时间：

SELECT relname, n_tup_upd, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public';

这将显示所有 `public` 模式下的表的名称、最近更新的行数以及最近的 VACUUM、ANALYZE 操作的时间。其中，`last_vacuum` 和 `last_autovacuum` 字段表示最后一次手动或自动执行 VACUUM 操作的时间，`last_analyze` 和 `last_autoanalyze` 字段表示最后一次手动或自动执行 ANALYZE 操作的时间。

相关问题

pgsql 查看表创建时间

你可以使用以下 SQL 查询来查看 PostgreSQL 数据库中表的创建时间：

SELECT pg_stat_user_tables.relname, pg_stat_user_tables.last_analyze, pg_stat_user_tables.last_autoanalyze, pg_stat_user_tables.last_vacuum, pg_stat_user_tables.last_autovacuum, pg_class.reltuples, pg_class.relpages, pg_class.relkind, pg_class.relname, pg_class.relnamespace, pg_namespace.nspname, pg_class.reltuples, pg_class.relpages, pg_class.reltoastrelid, pg_class.reltablespace, pg_class.reloptions, pg_description.description, pg_indexes.indexdef, pg_trigger.tgname, pg_proc.proname, pg_proc.prolang, pg_attrdef.adsrc, pg_constraint.conname, pg_constraint.contype, pg_constraint.conkey, pg_constraint.confkey, pg_constraint.conrelid, pg_constraint.confrelid, pg_constraint.confupdtype, pg_constraint.confdeltype, pg_constraint.confmatchtype, pg_constraint.conislocal, pg_constraint.coninhcount, pg_constraint.connoinherit, pg_constraint.convalidated, pg_constraint.condeferrable, pg_constraint.condeferred, pg_conversion.conname, pg_conversion.connamespace, pg_conversion.conowner, pg_conversion.conforencoding, pg_conversion.contoencoding, pg_cast.castsource, pg_cast.casttarget, pg_cast.castfunc, pg_collation.collname, pg_collation.collnamespace, pg_collation.collowner, pg_collation.collprovider, pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolreplication, pg_authid.rolbypassrls, pg_auth_members.member, pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid, pg_shdepend.refclassid, pg_shdepend.deptype, pg_shdepend.refobjid, pg_shdepend.refobjsubid, pg_seclabel.provider, pg_seclabel.label, pg_depend.classid, pg_depend.objid, pg_depend.objsubid, pg_depend.refclassid, pg_depend.refobjid, pg_depend.refobjsubid, pg_depend.deptype, pg_largeobject_metadata.lomowner, pg_largeobject_metadata.lomacl, pg_largeobject_metadata.lomdesc, pg_largeobject_metadata.loinvafter, pg_largeobject_metadata.loremovable, pg_largeobject_metadata.lobjid, pg_largeobject_metadata.lobjsubid, pg_largeobject_metadata.locreation, pg_largeobject.loid, pg_largeobject.lovalue, pg_inherits.inhrelid, pg_inherits.inhparent, pg_publication.pubname, pg_publication.pubowner, pg_publication.puballtables, pg_publication.pubinsert, pg_publication.pubupdate, pg_publication.pubdelete, pg_publication.pubtruncate, pg_replication_origin.roident, pg_replication_origin.rolastxact, pg_replication_origin.rocommit, pg_replication_origin.rosend, pg_replication_origin.roapply, pg_replication_origin.roident_seq
FROM pg_stat_user_tables
LEFT JOIN pg_class ON pg_stat_user_tables.relid = pg_class.oid
LEFT JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
LEFT JOIN pg_description ON pg_class.oid = pg_description.objoid
LEFT JOIN pg_indexes ON pg_class.oid = pg_indexes.indexrelid
LEFT JOIN pg_trigger ON pg_class.oid = pg_trigger.tgrelid
LEFT JOIN pg_proc ON pg_trigger.tgfoid = pg_proc.oid
LEFT JOIN pg_attrdef ON pg_class.oid = pg_attrdef.adrelid
LEFT JOIN pg_constraint ON pg_class.oid = pg_constraint.conrelid
LEFT JOIN pg_conversion ON pg_namespace.oid = pg_conversion.connamespace
LEFT JOIN pg_cast ON pg_cast.castcontext = 'a'::"char"
LEFT JOIN pg_collation ON pg_collation.collnamespace = pg_namespace.oid
LEFT JOIN pg_authid ON pg_authid.oid = pg_namespace.nspowner
LEFT JOIN pg_auth_members ON pg_auth_members.member = pg_authid.oid
LEFT JOIN pg_shdepend ON pg_class.oid = pg_shdepend.objid
LEFT JOIN pg_seclabel ON pg_class.oid = pg_seclabel.objoid
LEFT JOIN pg_depend ON pg_class.oid = pg_depend.refobjid
LEFT JOIN pg_largeobject_metadata ON pg_class.oid = pg_largeobject_metadata.lobjoid
LEFT JOIN pg_largeobject ON pg_largeobject_metadata.oid = pg_largeobject.loid
LEFT JOIN pg_inherits ON pg_class.oid = pg_inherits.inhrelid
LEFT JOIN pg_publication ON pg_class.oid = pg_publication.pubrelid
LEFT JOIN pg_replication_origin ON pg_replication_origin.roident = pg_depend.refobjid
WHERE pg_stat_user_tables.relname = 'your_table_name';

其中 `your_table_name` 是要查询的表的名称。查询结果中的 `pg_stat_user_tables.last_analyze` 和 `pg_stat_user_tables.last_autoanalyze` 列可以用于确定表的创建时间。

linux查看pgsql的数据库操作历史

在 Linux 中，可以使用 PostgreSQL 的日志功能来查看数据库操作历史。下面是具体的步骤：

1. 打开 PostgreSQL 的配置文件 `postgresql.conf`，找到 `logging_collector` 选项并将其设置为 `on`，表示开启日志收集器。

2. 打开 PostgreSQL 的日志配置文件 `pg_hba.conf`，找到 `log_connections` 和 `log_disconnections` 选项并将它们设置为 `on`，表示记录连接和断开连接的事件。

3. 重启 PostgreSQL 服务使配置生效。

4. 使用以下命令查看 PostgreSQL 日志文件：

tail -f /var/log/postgresql/postgresql-<version>-main.log

其中 `<version>` 表示 PostgreSQL 的版本号。

在日志文件中，可以看到每个 SQL 查询的详细信息，包括查询语句、执行时间、执行结果等。