psql 无效命令 \N 而恢复 sql

psql invalid command \N while restore sql

提问by Vivek Vikranth

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.

有解决办法吗？我搜索了，但没有得到明确的答案。

回答by Pavel Stehule

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" 作为 NULL 值的替代符号。但是所有 psql 命令都以反斜杠“\”符号开头。因此，当可能复制语句失败时，您可以收到此消息，但转储的加载仍在继续。此消息只是误报。由于 COPY 语句失败的原因，您必须先搜索一行。

Is possible to switch psql to "stop on first error" mode and to find error:

可以将 psql 切换到“在第一个错误时停止”模式并查找错误：

psql -v ON_ERROR_STOP=1

回答by stevek

I go the same error message when trying to restore from a binary dump. I simply used pg_restoreto restore my dump and completely avoid the \Nerrors, e.g.

尝试从二进制转储还原时出现相同的错误消息。我只是用来pg_restore恢复我的转储并完全避免\N错误，例如

pg_restore -c -F t -f your.backup.tar

pg_restore -c -F t -f your.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=FILENAME output file name -F, --format=c|d|t backup file format (should be automatic) -c, --clean clean (drop) database objects before recreating

回答by So4ne

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 上出现了同样的错误

回答by oraserrata

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”错误，您在输出的最顶部看不到真正的问题。我建议：

1. inserting a single, small table (e.g., pg_restore --table=orders full_database.dump > orders.dump)
2. 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)
3. watch the standard output, and once you find the problem, you can always drop the table and reload

1. 插入单个小表（例如，pg_restore --table=orders full_database.dump > orders.dump）
2. 如果你没有一个小的，那么从恢复脚本中删除一堆记录 - 我只是确保 ./ 是要加载的最后一行（例如，打开orders.dump并删除一堆记录）
3. 观察标准输出，一旦发现问题，随时可以丢表重载

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，然后我又开始营业了。

回答by Jo?o Neves Filho

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

您可以使用 INSERTS 语句和 --inserts 参数生成转储。

回答by Ekrem Gurdal

Same thing was happened to me today. I handled issue by dumping with --inserts command.

今天同样的事情发生在我身上。我通过使用 --inserts 命令转储来处理问题。

What I do is:

我要做的是：

1) pg_dump with inserts:

1) 带插入的 pg_dump：

pg_dump dbname --username=usernamehere --password --no-owner --no-privileges --data-only --inserts -t 'schema."Table"' > filename.sql

2) psql (restore your dumped file)

2) psql (恢复你的转储文件)

psql "dbname=dbnamehere options=--search_path=schemaname" --host hostnamehere --username=usernamehere -f filename.sql >& outputfile.txt

Note-1 ) Make sure that adding outputfile will increase speed of import.

Note-1 ) 确保添加 outputfile 会提高导入速度。

Note-2 ) Do not forget to create table with exact same name and columns before importing with psql.

注2）在使用psql导入之前，不要忘记创建具有完全相同名称和列的表。

回答by Geets

Install postgresql-(your version)-postgis-scripts

安装 postgresql-(你的版本)-postgis-scripts

回答by Tyrone Hinderson

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 -aoption, 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_nameto populate schema2.table_name. Manually modifying the dump was easy, the schema is specified in the first 15 lines or so.

根据我最近的经验，当真正的问题与转义字符或换行符无关时，可能会出现此错误。就我而言，我已经从数据库 A 创建了一个转储，
pg_dump -a -t table_name > dump.sql
并试图将其恢复到数据库 B
psql < dump.sql（当然，在更新了适当的环境变量之后）
我最终发现的是转储，尽管它是data-only（-a选项，因此表结构不是转储的明确一部分），是特定于架构的。这意味着如果不手动修改转储，我就无法使用生成的转储schema1.table_name来填充schema2.table_name. 手动修改转储很容易，模式在前 15 行左右指定。

回答by Farsheed

Most times, the solution is to install postgres-contribpackage.

大多数情况下，解决方案是安装postgres-contrib包。

回答by mountainclimber

For me using postgreSQL 10 on SUSE 12, I resolved the invalid command \Nerror 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 -houtput. 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，我invalid command \N通过增加磁盘空间解决了错误。磁盘空间不足导致了我的错误。如果您查看数据将在df -h输出中的文件系统，您可以判断是否磁盘空间不足。如果文件系统/挂载使用率为 100%，在执行类似操作psql -f db.out postgres（参见https://www.postgresql.org/docs/current/static/app-pg-dumpall.html）后，您可能需要增加可用磁盘空间.