CODEWITHSUNDEEP
postgresqlpostgresql-9.5pg-dump
tomaytotomato
tomaytotomato

Reputation: 4028

PostgresSQL dump file is breaking because of apostrophes and other unescaped chars?

I have a docker container running remotely that I am trying to dump the sql data from, like so:

docker exec -t test_db pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

When I try to run this dump file locally using the command

psql reports < dump_31-10-2017_14_38_13.sql

I get errors like this:

Expected " char

invalid command \N

ERROR: syntax error at or near "il" LINE 1: il faut un ticket parking pour qu'il s'ouvre 750 1000 8 0 f

On inspection of the dump file using Intellij, the syntax highlighting shows there is breakage in the data ( caused by emojis, apostrophes and other characters)

enter image description here

How can I prevent this in postgreSQL when dumping the data?

PostgreSQL version : 9.5.7

Upvotes: 0

Views: 288

Answers (1)

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61626

This kind of error is generally the fallout of a previous SQL CREATE statement that fails. The first error mentioned : Expected " char could be something about a table not being created, and as a result the following COPY into that table would fail, and all data after that would be interpreted as SQL which would massively fail.

invalid command \N is also a sign of that, since \N represents NULL in COPY data, and this error occurs when psql confuses out-of-sync COPY data with backslash-starting meta-commands.

You could reload your dump with psql -v ON_ERROR_STOP=on and focus on the first error.

Upvotes: 1

Related Questions