☰

服务器端

tag-icon • tag-icon postgresql sql
服务器端

我有一个 PostgreSQL 数据库，里面有一个多 GB 的表（其中包含某些事件的日志）。我需要将最新事件传递给分析师 - 假设他只需要上个月的事件。

我怎样才能生成仅包含那些具有的行的转储created_at > '2012-05-01'？

答案1

psql -c "COPY (SELECT * FROM my_table WHERE created_at > '2012-05-01') TO STDOUT;" source_db | psql -c "COPY my_table FROM STDIN;" target_db

答案2

另一种方法是使用COPY或者\copy（psql命令），例如：

COPY (SELECT * FROM big_table WHERE created_at > '2012-05-01') TO '/path/to/a/dump/file';

答案3

免责声明：逐字摘自https://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file

您希望将结果文件保存在服务器上，还是保存在客户端上？

服务器端

如果你想要一些易于重复使用或自动化的东西，你可以使用 Postgresql 的内置复制命令。例如

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';

这种方法完全在远程服务器上运行- 它无法写入您的本地 PC。它还需要以 Postgres“超级用户”（通常称为“root”）的身份运行，因为 Postgres 无法阻止它对该机器的本地文件系统进行恶意操作。

这并不意味着你必须以超级用户身份连接（自动化连接会带来另一种安全风险），因为你可以使用选择SECURITY DEFINER​CREATE FUNCTION创建一个函数就像您是超级用户一样运行。

关键部分是，您的函数可以执行额外的检查，而不仅仅是绕过安全性 - 因此您可以编写一个函数来导出您需要的精确数据，或者您可以编写一个可以接受各种选项的函数，只要它们符合严格的白名单。您需要检查两件事：

  1. 哪个文件是否应允许用户在磁盘上进行读取/写入？例如，这可能是一个特定的目录，并且文件名可能必须具有合适的前缀或扩展名。
  2. 哪个表用户是否应该能够在数据库中读取/写入？这通常由GRANT数据库中的 s 定义，但该函数现在以超级用户身份运行，因此通常“超出范围”的表将完全可访问。您可能不想让某人调用您的函数并在“用户”表的末尾添加行……

我写过一篇博客文章详细阐述了这种方法，包括一些导出（或导入）满足严格条件的文件和表的函数示例。

客户端

另一种方法是在客户端进行文件处理即在您的应用程序或脚本中。Postgres 服务器不需要知道您要复制到哪个文件，它只会吐出数据，然后客户端会将其放在某处。

其底层语法是COPY TO STDOUT命令，而像 pgAdmin 这样的图形工具会将其包装在一个漂亮的对话框中。

这psql命令行客户端有一个特殊的“元命令”，称为\copy，它采用与“真实”相同的选项COPY，但在客户端内部运行：

\copy (Select * From foo) To '/tmp/test.csv' With CSV

请注意，没有终止;，因为元命令以换行符终止，这与 SQL 命令不同。

从文档：

不要将 COPY 与 psql 指令 \copy 混淆。\copy 调用 COPY FROM STDIN 或 COPY TO STDOUT，然后将数据提取/存储在 psql 客户端可访问的文件中。因此，使用 \copy 时，文件可访问性和访问权限取决于客户端而不是服务器。

您的应用程序编程语言可能还支持推送或获取数据，但通常不能在标准 SQL 语句中使用COPY FROM STDIN/ TO STDOUT，因为没有办法连接输入/输出流。PHP 的 PostgreSQL 处理程序 (不是PDO）包括非常基本的pg_copy_from和pg_copy_to从 PHP 数组复制的函数对于大型数据集可能效率不高。

答案4

如果 PSQL 用户没有写入文件的权限，那么您可以执行类似的操作。

psql -c "COPY (SELECT * FROM big_table WHERE created_at > '2012-05-01') TO STDOUT;" -h localhost -d my_database -U my_user > path/to/file

相关内容