I'm trying to restore my dump file, but it caused an error:
我试图恢复我的转储文件，但它导致了一个错误:
psql:psit.sql:27485: invalid command \N
Is there a solution? I searched, but I didn't get a clear answer.
有解决方案吗?我搜索了一下，但没有得到一个明确的答案。
9 个解决方案
#1
121
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.
Postgres使用“\N”作为空值的替代符号。但是所有的psql命令都是从反斜杠“\”符号开始的。因此，当可能复制语句失败时，您可以得到此消息，但是转储的加载仍在继续。这条信息只是虚惊一场。你必须先搜索一条线，才能解释为什么复制语句失败。
Is possible to switch psql to "stop on first error" mode and to find error:
可以将psql切换到“第一个错误”模式，并发现错误:
psql -v ON_ERROR_STOP=1
#2
25
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来恢复我的转储并完全避免\N错误。
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， -- -在重新创建之前清除(删除)数据库对象。
#3
4
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:
我在过去也遇到过这个错误。Pavel是正确的，它通常是pg_restore创建的脚本中某些内容失败的标志。因为所有的“/N”错误，您没有看到真正的问题在最上面的输出。我建议:
- 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.dumpand 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.
在我的例子中，我还没有安装“hstore”扩展，所以脚本在顶部失败了。我在目标数据库上安装了hstore，我又回到了业务中。
#4
3
You can generate your dump using INSERTS statements, with the --inserts parameter.
您可以使用插入参数来生成转储文件。
#5
3
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
我知道这是一个旧的帖子，但是我遇到了另一个解决方案:postgis没有安装在我的新版本上，这使我在pg_dump上犯了相同的错误。
#6
2
Install postgresql-(your version)-postgis-scripts
安装postgresql - -postgis-scripts(您的版本)
#7
1
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 withpsql < 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行中指定。
#8
0
Most times, the solution is to install postgres-contrib package.
大多数时候，解决方案是安装postgres- b包。
#9
0
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的操作之后。你可能需要增加可用的磁盘空间。
#1
121
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.
Postgres使用“\N”作为空值的替代符号。但是所有的psql命令都是从反斜杠“\”符号开始的。因此，当可能复制语句失败时，您可以得到此消息，但是转储的加载仍在继续。这条信息只是虚惊一场。你必须先搜索一条线，才能解释为什么复制语句失败。
Is possible to switch psql to "stop on first error" mode and to find error:
可以将psql切换到“第一个错误”模式，并发现错误:
psql -v ON_ERROR_STOP=1
#2
25
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来恢复我的转储并完全避免\N错误。
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， -- -在重新创建之前清除(删除)数据库对象。
#3
4
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:
我在过去也遇到过这个错误。Pavel是正确的，它通常是pg_restore创建的脚本中某些内容失败的标志。因为所有的“/N”错误，您没有看到真正的问题在最上面的输出。我建议:
- 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.dumpand 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.
在我的例子中，我还没有安装“hstore”扩展，所以脚本在顶部失败了。我在目标数据库上安装了hstore，我又回到了业务中。
#4
3
You can generate your dump using INSERTS statements, with the --inserts parameter.
您可以使用插入参数来生成转储文件。
#5
3
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
我知道这是一个旧的帖子，但是我遇到了另一个解决方案:postgis没有安装在我的新版本上，这使我在pg_dump上犯了相同的错误。
#6
2
Install postgresql-(your version)-postgis-scripts
安装postgresql - -postgis-scripts(您的版本)
#7
1
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 withpsql < 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行中指定。
#8
0
Most times, the solution is to install postgres-contrib package.
大多数时候，解决方案是安装postgres- b包。
#9
0
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的操作之后。你可能需要增加可用的磁盘空间。