每天自动删除PostgreSQL数据表

Question

我有一个场景，其中我有一个中央服务器和一个节点。服务器和节点都可以运行PostgreSQL，但节点的存储空间有限。节点以高速收集数据并将数据写入其本地数据库。 服务器需要从节点复制数据。我计划使用Slony-I或Bucardo来实现这一点。 节点需要能够在一定时间间隔内从其表中删除所有记录，以最小化磁盘空间的使用。我应该使用pgAgent，其中包含一个脚本作业吗？

DELETE FROM tablex, tabley, tablez;

实际批处理文件运行脚本的位置可能是类似于这样的东西。

@echo off
C:\Progra~1\PostgreSQL\9.1\bin\psql -d database -h localhost -p 5432 -U postgres -f C:\deleteFrom.sql

?

我只是想听听意见，是否这是完成此任务的最佳方式，或者是否有更有效的方法从远程数据库中提取数据并清除该远程数据库以节省远程节点上的空间。感谢您的时间。

- babcoccl

Answer 1

您最有效的命令是TRUNCATE命令。

使用TRUNCATE，您可以链接表格，就像您的示例一样：

TRUNCATE tablex, tabley, tablez;

这里是从PostgreSQL文档中提取的说明：

TRUNCATE可以快速地从一组表中删除所有行。它对每个表具有与未经限定的DELETE相同的效果，但由于它不会实际扫描表格，因此它更快。此外，它立即回收磁盘空间，而不需要后续的VACUUM操作。这对于大型表格非常有用。

您也可以添加CASCADE作为参数：

自动截断所有具有外键引用任何命名表或由于级联而添加到组中的任何表的表。

- Bohemian

---

谢谢您的帮助。我会使用截断而不是删除。 - babcoccl

Answer 2

根据您的确切需求和工作流程，最好的两个选择是截断（如@Bohemian所建议的）或创建一个新表，重命名，然后删除。

在我们的一个主要项目中，我们使用了类似于后者创建/重命名/删除方法。这种方法的优点在于您需要能够非常快速地从表中删除一些数据，但不是所有数据。基本工作流程如下：

1. 创建一个与旧表完全相同的模式的新表

   CREATE new_table LIKE ...

2. 在事务中同时重命名旧表和新表：

   BEGIN; RENAME table TO old_table; RENAME new_table TO table; COMMIT;

3. [可选]现在您可以对旧表进行操作，而新表正在愉快地接受新插入。您可以将数据转储到集中式服务器上，运行查询等。

4. 删除旧表

   DROP old_table;

这是一种特别有用的策略，当你想保留7天的数据并一次性丢弃第8天的数据时，使用DELETE命令会非常慢。通过将数据存储在分区中（每天一个分区），可以轻松地一次性删除整个一天的数据。

- Jonathan Hall

---

如果在Postgres中定义了表的视图，会发生什么？答案是：这些命令将会崩溃。 - Bohemian

@Bohemian：你可以删除并重新创建视图...或者你可以使用一个父表，只删除/重新创建子表。我们非常有效地使用这种策略。 - Jonathan Hall

是的，我知道你可以这样做，但是你必须要知道要重新创建哪些视图。你如何维护它？如果有人添加/删除了一个视图-你必须也编辑你的脚本！你只是不必要地将操作过程与模式问题结合在一起。这变成了一个不必要的维护麻烦。此外，你的解决方案根本不是一个好方法-它的性能不佳，如果你的表很大，你可能会吹掉日志。TRUNCATE是正确的方法。我建议你自己尝试一下。相信我...你会很高兴你这样做了。 - Bohemian

@Bohemian：我坚决不同意这个说法，即我的解决方案在所有情况下“根本不是一个好的解决方案”。我也不会说它总是最好的解决方案。有时候TRUNCATE完全不是正确的解决方案——比如当您不需要删除所有记录时（正如我答案的最后一段所提到的）。 - Jonathan Hall

@Bohemian：如果你正在处理一个高度不稳定的模式，那么重新创建视图确实可能是一项挑战。为此，我建议采用父/子表方法——如果TRUNCATE无法使用的话。 - Jonathan Hall