PostgreSQL转储临时表 [英] PostgreSQL dump Temp table

问题描述

我使用以下查询在PostgreSQL数据库中创建了临时表

I created a temp table in my PostgreSQL DB using the following query

SELECT * INTO TEMP TABLE tempdata FROM data WHERE id=2004;

现在我要创建此临时表的备份 tempdata 。

所以我使用以下命令行执行

Now I want to create a backup of this temp table tempdata.
So i use the following command line execution

"C:\Program Files\PostgreSQL\9.0\bin\pg_dump.exe" -F t -a -U my_admin -t tempdata myDB >"e:\mydump.backup"  

我收到一条消息

pg_dump: No matching tables were found  

是否可以创建 temp表的转储？

我做对了吗？

Is it possible to create a dump of temp tables?
Am I doing it correctly?

P.S。 ：我也想恢复原状。我不想使用任何额外的组件。

P.S. : I would also want to restore the same.I don't want to use any extra components.

TIA。

推荐答案

我认为您不能在该临时表中使用 pg_dump 。问题是临时表仅存在于创建它们的会话中：

I don't think you'll be able to use pg_dump for that temporary table. The problem is that temporary tables only exist within the session where they were created:

PostgreSQL而是要求每个会话都发出自己的 CREATE TEMPORARY TABLE 命令用于要使用的每个临时表。这允许不同的会话出于不同的目的使用相同的临时表名称，而标准的方法将给定临时表名称的所有实例约束为具有相同的表结构。

PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure.

因此，您将在一个会话中创建临时表，但 pg_dump 将使用没有临时表的其他会话。

So you'd create the temporary table in one session but pg_dump would be using a different session that doesn't have your temporary table.

但是， COPY 应该可以工作：

However, COPY should work:

COPY 在PostgreSQL表和标准文件系统文件之间移动数据。

COPY moves data between PostgreSQL tables and standard file-system files.

，但是您要么复制数据到标准输出或数据库服务器上的文件（需要超级用户访问权限）：

but you'll either be copying the data to the standard output or a file on the database server (which requires superuser access):

使用文件名进行COPY指示PostgreSQL服务器执行以下操作：直接从文件读取或写入文件。服务器必须可以访问该文件，并且必须从服务器的角度指定该名称。

[...]

COPY命名文件只允许数据库超级用户使用，因为它允许读取或写入服务器有权访问的任何文件。

COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible to the server and the name must be specified from the viewpoint of the server.
[...]
COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access.

因此，使用COPY将临时表直接转储到文件可能不是一个选择。虽然您可以将其复制到标准输出，但是效果如何取决于您访问数据库的方式。

So using COPY to dump the temporary table straight to a file might not be an option. You can COPY to the standard output though but how well that will work depends on how you're accessing the database.

如果您不使用，则可能会有更好的运气临时表。当然，您将必须管理唯一的表名，以避免与其他会话冲突，并且您必须注意确保在处理完非临时临时表后将其删除。

You might have better luck if you didn't use temporary tables. You would, of course, have to manage unique table names to avoid conflicts with other sessions and you'd have to take care to ensure that your non-temporary temporary tables were dropped when you were done with them.

这篇关于PostgreSQL转储临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！