自动删除超过90天的表和索引 [英] Automatically Drop Tables and Indexes Older than 90 Days

问题描述

有没有一种方法可以根据创建日期自动删除/删除所有表以及与它们相关的索引？

Is there a way to automatically drop/delete all tables and the indexes associated with them based on the creation date?

我找不到内置的Postgres中的/ native列记录创建表/索引的日期。我正在运行Postgres 8.4。我以为我可以做这样的事情（伪SQL）：

I can't find a built-in/native column in Postgres that records the date when the table/index was created. I'm running Postgres 8.4. I assumed that I would be able to do something like this (pseudo SQL):

select all from pg_tables where creation_date is older than 90 days;

然后删除该选择的结果（与pg_indexes相同）。但是，我找不到任何表格或索引的创建日期。我可以自己组装某种创建日期跟踪程序，但我认为可以肯定的是Postgres中已经有一些本机存储了此信息...

Then drop the results of that select (same for pg_indexes). However, I'm unable to find any sort of table or index creation date. I can rig-up some sort of creation date tracker myself, but I thought for certain there would be something native within Postgres that's already storing this information... is there?

谢谢

推荐答案

您可以像这样获得创建时间

You can get the creation time like this

（假设表名 t_benutzer ）

--select datname, datdba from pg_database;
--select relname, relfilenode from pg_class where relname ilike 't_benutzer';

    -- (select relfilenode::text from pg_class where relname ilike 't_benutzer')




SELECT 
    pg_ls_dir
    ,
    (
        SELECT creation
        FROM pg_stat_file('./base/'
            ||
            (
                    SELECT 
                        MAX(pg_ls_dir::int)::text 
                    FROM pg_ls_dir('./base') 
                    WHERE pg_ls_dir <> 'pgsql_tmp' 
                    AND  pg_ls_dir::int  <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE 't_benutzer')
            )
            || '/' || pg_ls_dir
        )
    ) as createtime 
FROM pg_ls_dir(
    './base/' || 
    (
        SELECT 
            MAX(pg_ls_dir::int)::text 
        FROM pg_ls_dir('./base') 
        WHERE pg_ls_dir <> 'pgsql_tmp' 
        AND  pg_ls_dir::int  <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE 't_benutzer') 
    ) 
) 

WHERE pg_ls_dir = (SELECT relfilenode::text FROM pg_class WHERE relname ILIKE 't_benutzer')

秘密是在各自的表文件上使用pg_stat_file。

The secret is to use pg_stat_file on the respective table file.

-- http://www.greenplumdba.com/greenplum-dba-faq/howtofindtablecreationdateingreenplum


select 
    pg_ls_dir
    , 
    (
        select 
            --size 
            --access
            --modification 
            --change
            creation 
            --isdir
        from pg_stat_file(pg_ls_dir)
    ) as createtime 
from pg_ls_dir('.'); 

根据此帖子中的评论 PostgreSQL：表创建时间
这不是100％可靠的，因为表可能是由于对表的操作而在内部删除和重新创建的，例如

As per comment in this post PostgreSQL: Table creation time this isn't 100% reliable, because the table may have been internally dropped and recreated due to operations on the table, such as CLUSTER.

也是模式

/main/base/<database id>/<table filenode id> 

似乎是错误的，因为在我的机器上，来自不同数据库的所有表都具有相同的数据库ID，并且似乎该文件夹已被替换为任意的inode编号，因此您需要找到其编号最接近表的inode ID的文件夹（最大foldername，其中folderid< = table_inode_id且foldername为数字）

seems to be wrong, as on my machine, all tables from different databases have the same database id, and it seems like the folder has been replaced with some arbitrary inode number, so you need to find the folder whose number is closest to your table's inode id (max foldername where folderid <= table_inode_id and foldername is numeric)

简化版本如下：

SELECT creation 
FROM pg_stat_file(
    './base/'
    ||
    (
        SELECT 
        MAX(pg_ls_dir::int)::text 
        FROM pg_ls_dir('./base') 
        WHERE pg_ls_dir <> 'pgsql_tmp' 
        AND  pg_ls_dir::int  <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE 't_benutzer')
    )
    || '/' || (SELECT relfilenode::text FROM pg_class WHERE relname ILIKE 't_benutzer')
)

然后您可以使用information_schema和cte来制作非常简单，也可以创建自己的视图：

Then you can use information_schema and cte to make the query simple, or create your own view:

;WITH CTE AS
(
    SELECT 
        table_name 

        ,
        (
            SELECT 
                MAX(pg_ls_dir::int)::text 
            FROM pg_ls_dir('./base') 
            WHERE pg_ls_dir <> 'pgsql_tmp' 
            AND  pg_ls_dir::int  <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name)
        ) as folder 


        ,(SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name) filenode

    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
    AND table_schema = 'public'
)

SELECT 
    table_name 
    ,(
        SELECT creation 
        FROM pg_stat_file(
            './base/' || folder || '/' || filenode 
        )
    ) as creation_time
FROM CTE 

（使用nhibernate模式创建的所有表都会创建，因此

(all tables created with nhibernate schema create, so the more or less same time on all tables on the screenshot is correct).

出于风险和副作用，请使用大脑和/或询问医生或药剂师;）

For risk and side effects, use your brain and/or ask your doctor or pharmacist ;)

这篇关于自动删除超过90天的表和索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！