每天自动删除一次PostgreSQL表

Question

我有一个场景，其中我有一个中央服务器和一个节点。服务器和节点都可以运行PostgreSQL，但节点上的存储空间有限。节点高速采集数据，并将数据写入本地DB。服务器需要从节点复制数据。我计划用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

如果这是完成此任务的最佳方法，或者是否有人知道从远程数据库提取数据并清除远程数据库以节省远程节点上的空间的更有效的方法，我只是在寻求意见。耽误您时间，实在对不起。

Answer 1

对您来说，最有效的命令是TRUNCATE命令。

使用TRUNCATE，您可以链接表，如您的示例所示：

TRUNCATE tablex, tabley, tablez;

以下是postgres文档中的描述：

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

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

CASCADE自动截断具有对任何指名表或因CASCADE而添加到组中的任何表的外键引用的所有表。

Answer 2

根据您的确切需求和工作流程，最好的两个选项是截断，就像@Bohemian建议的那样，或者创建一个新表，重命名，然后删除。

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

1. 使用与旧表相同的模式创建新表

创建类似...

• 的new_table在事务中，同时重命名新旧表：

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

• Optional现在您可以对旧的表执行操作，而新的表可以愉快地接受新的插入。您可以将数据转储到集中式服务器，在其上运行查询，或对旧表执行whatever.

• Delete

DROP old_table;

这是一个特别有用的策略，当您想要保留，比方说，7天的数据，并只丢弃第8天的数据一次。在这种情况下，执行DELETE可能会非常慢。通过将数据存储在分区(每天一个分区)中，很容易一次删除一整天的数据。