查看: 206|回复: 0

psql invalid command \N while restore sql

dirtysea 发表于 2018-9-15 19:55:04 | 显示全部楼层 |阅读模式
9 个解决方案#1
Postgres uses "\N" as substitute symbol for NULL value. But all psql commands starts by backslash "\" symbol. So you can get this messages, when probably copy statement fails, but a loading of dump continues. This message is only false alarm. You have to search a lines before for reason why COPY statement fails.
Is possible to switch psql to "stop on first error" mode and to find error:
psql -v ON_ERROR_STOP=1

I go the same error message when trying to restore from a binary dump. I simply used pg_restore to restore my dump and completely avoid the \N errors, e.g.
pg_restore -c -F t -f your.backup.tar
pg_restore -c -F t -F t -F, backup.tar。
Explanation of switches:
-f, --file=FILENAME output file name -F, --format=c|d|t backup file format (should be automatic) -c, --clean clean (drop) database objects before recreating
-f, -file=文件名输出文件名-f,格式=c|d|t备份文件格式(应该是自动的)-c, -- -在重新创建之前清除(删除)数据库对象。

I have run into this error in the past as well. Pavel is correct, it is usually a sign that something in the script created by pg_restore is failing. Because of all the "/N" errors, you aren't seeing the real problem at the very top of the output. I suggest:
  • inserting a single, small table (e.g., pg_restore --table=orders full_database.dump > orders.dump )
  • 插入一个小的表(例如,pg_restore—table=orders full_database)。转储>订单。转储)
  • if you don't have a small one, then delete a bunch of records out of the restore script - I just made sure the ./ was the last row to be loaded (e.g., open orders.dump and delete a bunch of records)
  • 如果您没有一个小的,那么从恢复脚本中删除一堆记录—我只是确定了。/是最后一个要加载的行(例如,开放的命令)。转储和删除一堆记录)
  • watch the standard output, and once you find the problem, you can always drop the table and reload
  • 注意标准输出,一旦发现问题,就可以随时删除表并重新加载。
In my case, I didn't have the "hstore" extension installed yet, so the script was failing at the very top. I installed hstore on the destination database, and I was back in business.

You can generate your dump using INSERTS statements, with the --inserts parameter.

I know this is an old post but I came across another solution : postgis wasn't installed on my new version, which caused me the same error on pg_dump

Install postgresql-(your version)-postgis-scripts
安装postgresql - -postgis-scripts(您的版本)

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.
在我最近的经验中,当真正的问题与转义字符或换行符无关时,就有可能得到这个错误。在我的例子中,我使用pg_dump - - -t table_name >转储从数据库a创建了一个转储文件。sql并试图用psql < dump将其恢复到数据库B。sql(当然,在更新了适当的env vars之后)我最终发现的是转储文件,尽管它只是数据(-a选项,因此表结构不是转储的显式部分),是特定于模式的。这意味着,如果不手动修改转储,我就不能使用schema1生成的转储。table_name填充schema2.table_name。手动修改转储很容易,模式在前15行中指定。

Most times, the solution is to install postgres-contrib package.
大多数时候,解决方案是安装postgres- b包。

For me using postgreSQL 10 on SUSE 12, I resolved the invalid command \N error by increasing disk space. Lack of disk space was causing the error for me. You can tell if you are out of disk space if you look at the file system your data is going to in the df -h output. If file system/mount is at 100% used, after doing something like psql -f db.out postgres (see https://www.postgresql.org/docs/current/static/app-pg-dumpall.html) you likely need to increase the disk space available.
对于我在SUSE 12上使用postgreSQL 10,我通过增加磁盘空间来解决无效的命令\N错误。磁盘空间不足导致了我的错误。如果你看一下文件系统,你可以知道你的数据是在df -h输出中。如果文件系统/挂载是100%使用的,那么在做了类似于psql - fdb的操作之后。你可能需要增加可用的磁盘空间。

您需要登录后才可以回帖 登录 | 注册


QQ|小黑屋|手机版|Archiver|运维之家 ( 蜀ICP备12020351号 )

GMT+8, 2018-10-22 05:25 , Processed in 0.052667 second(s), 15 queries .

Powered by Discuz! X3.4

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表