postgresql – 数据库归档解决方案

>创建archive_tablespace(如果您希望在归档时可以分离硬件)
>创建表格.例如,我们想要存档表格帖子.

create table  posts_all ( LIKE public.posts)  ;
create table  posts_archive () inherits  ( public.posts_all)  ;
alter table  public.posts  inherits ( public.posts_all ) ;

之后我们将有2个新表：public.posts_all(在帖子中使用相同的列)查询所有帖子(存档和生产)和public.posts_archive查询所有存档帖子. Public.posts将继承自posts_all.
插入应该以旧方式(到表public.posts)进行,除非您将在posts_all上编写触发器以将插入重定向到posts表.如果你有分区,它会更复杂.使用工作应用程序和旧数据迁移之前,您无需更改应用程序代码中的任何内容即可使用此方法.
>为逻辑分离创建模式存档.我的建议是尽可能将存档数据分开一段时间(年或月)(archive_2005).
>在archive_year模式中创建归档表

create table archive_2005.posts (
  check(record_date >= '2005-01-01 00:00:00'::timestamp 
    and record_date <  '2006-01-01 00:00:00'::timestamp)
) inherits (posts_archive) tablespace archive_tablesapce;

之后,您将在schema archive_2005中拥有新的表帖子,postgresql planer将知道该数据仅在设计的时间段内.如果您通过另一个时间段查询postgresql将不会在此表中搜索.
>创建函数/过程/触发器以将数据移动到归档表.
>存档一次一段时间(这里是一年)和真空旧表或通过触发器自动执行(在autovacuum上更重).这两种技术都有许多优点和缺点.

如果实施：

>可以分别查询存档(从posts_archive中选择*),all(select * from posts_all)和production(select * from public.posts)数据
>可以单独转储归档模式,并以简单的方式在它们上放置级联.
pg_dump -s archive_2005 datase_name
drop schema archive_2005 cascade; – 小心,因为它删除了所有相关的表
>旧数据由表空间物理分隔,逻辑上由模式分隔.
>管理归档过程的结构相当复杂
>可以在生产和归档表上创建不同的索引,以优化对两者的查询(较小和专用索引=更快的查询和更少的空间)
>如果你有分区表(按年或月),归档过程只是将整个表移动到archive_tablespace或只是改变它以从posts_archive继承(我没有测试这个)
>如果您不想访问旧(存档)数据,则无需在应用程序中更改任何内容.

这是一般技术,您应该根据自己的需要进行调整.
有什么建议可以改善吗？

进一步阅读：PostgreSQL inheritance,partitioning