我试图恢复我的转储文件,但它导致了一个错误:

psql:psit.sql:27485: invalid command \N

有解决办法吗?我找了,但没有得到明确的答案。


当前回答

In my recent experience, it's possible to get this error when the real problem has nothing to do with escape characters or newlines. In my case, I had created a dump from database A with pg_dump -a -t table_name > dump.sql and was trying to restore it to database B with psql < dump.sql (after updating the proper env vars, of course) What I finally figured out was that the dump, though it was data-only (the -a option, so that the table structure isn't explicitly part of the dump), was schema-specific. That meant that without manually modifying the dump, I couldn't use a dump generated from schema1.table_name to populate schema2.table_name. Manually modifying the dump was easy, the schema is specified in the first 15 lines or so.

其他回答

Postgres使用\N作为NULL值的替代符号。但是所有psql命令都以反斜杠\符号开始。当复制语句失败,但转储的加载仍在继续时,您可以获得这些消息。这条消息是假警报。如果希望看到COPY语句失败的真正原因,则必须搜索此错误之前的所有行。

可以切换psql到“第一个错误停止”模式,并找到错误:

psql -v ON_ERROR_STOP=1

我过去也遇到过这种错误。Pavel是正确的,这通常是pg_restore创建的脚本中某些东西失败的标志。由于所有的“/N”错误,您在输出的顶部看不到真正的问题。我建议:

插入单个小表(例如,pg_restore . table) ——表full_database =订单。转储>个订单。转储) 如果你没有一个小的,那么从恢复脚本中删除一堆记录-我只是确保./是最后一行被加载(例如,打开订单。转储和删除一堆记录) 观察标准输出,一旦发现问题,就可以随时放弃 表格和重载

在我的情况下,我还没有安装“hstore”扩展,所以脚本在顶部失败。我在目标数据库上安装了hstore,然后就可以继续工作了。

我有同样的问题,我创建了一个新的数据库,并得到无效的命令\N恢复与psql。 我通过设置与旧数据库相同的表空间来解决这个问题。

例如,旧数据库备份有表空间“pg_default”,我给新数据库定义了相同的表空间,上面的错误已经消失了!

In my recent experience, it's possible to get this error when the real problem has nothing to do with escape characters or newlines. In my case, I had created a dump from database A with pg_dump -a -t table_name > dump.sql and was trying to restore it to database B with psql < dump.sql (after updating the proper env vars, of course) What I finally figured out was that the dump, though it was data-only (the -a option, so that the table structure isn't explicitly part of the dump), was schema-specific. That meant that without manually modifying the dump, I couldn't use a dump generated from schema1.table_name to populate schema2.table_name. Manually modifying the dump was easy, the schema is specified in the first 15 lines or so.

我的解决办法是:

psql -U your_user your_db < your.file.here.sql  2>&1|more

这样我就可以读取错误消息

我希望这能对大家有所帮助。