主页 / dba / 问题 / 28655
Accepted
Gnanam
Asked: 2012-11-15 02:27:06 +0800 CST

数据库归档解决方案

  • 772

继续我发布的一个问题，将大容量和高访问量的表移动到单独的数据库是个好主意吗？，正在寻找可用于 PostgreSQL 中的数据库归档的不同技术/解决方案。

我能想到的几个解决方案是：

  1. 表分区
  2. 单独的表空间和/或模式
  3. 将存档的记录/表格移动到不同的硬盘

任何其他建议/指针/解决方案都非常受欢迎和赞赏。

注意：我们在 CentOS5.2 上运行 PostgreSQL v9.1.3

postgresql partitioning

1 个回答

  1. Best Answer

    我对归档的建议：

    1. 创建archive_tablespace（如果需要，可以在存档中分离硬件）

    2. 创建表。例如，我们要归档表格帖子。 

      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 上编写触发器以将插入重定向到帖子表。如果你有分区，它会更复杂。对于正在运行的应用程序，在旧数据迁移之前，您无需更改应用程序代码中的任何内容即可使用此方法。

    3. 为逻辑分离创建模式存档。如果可能的话，我的建议是按某个时间段（年或月）分隔存档数据（archive_2005）。

    4. 在 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;

      之后，您将在模式archive_2005 中拥有新表posts，并且postgresql planer 将知道那里的数据仅在设计的时间段内。如果您按另一个时间段查询，postgresql 将不会在此表中搜索。

    5. 创建函数/过程/触发器以将数据移动到存档表。

    6. 在一段时间内（此处为年份）归档一次并清理旧表或通过触发器自动执行（在 autovacuum 上更重）。这两种技术都有许多优点和缺点。

    如果实施：

    1. 可以分别查询归档（select * from posts_archive）、all（select * from posts_all）和生产（select * from public.posts）数据
    2. 可以单独转储存档模式并以简单的方式对其进行级联。pg_dump -s archive_2005 datase_name 删除模式 archive_2005 级联；--小心，因为它会删除所有相关的表
    3. 旧数据在物理上由表空间分隔，在逻辑上由模式分隔。
    4. 管理归档过程的结构相当复杂
    5. 可以在生产表和存档表上创建不同的索引以优化对两者的查询（更小和专用的索引 = 更快的查询和更少的空间需求）
    6. 如果您有分区表（按年或月）存档过程将只是将整个表移动到archive_tablespace或只是将其更改为从 posts_archive 继承（我没有测试这个）
    7. 如果您不想访问旧的（存档的）数据，则无需更改应用程序中的任何内容。

    这是通用技术，您应该根据自己的需要进行调整。有什么建议可以改进吗？

    延伸阅读：PostgreSQL 继承、分区

    • 14

相关问题