CODEWITHSUNDEEP
postgresqldockerrestoredatabase-backups
ConstantineK
ConstantineK

Reputation: 362

Cannot restore data from pg_dump due to blank strings being treated as nonexistant data

I have a database currently on a PostgreSQL 9.3.9 server that I am backing up with pgdump in the simplest possible fashion, eg pg_dump orb > mar_9_2018.db.

One of those tables (linktags) has the following definition:

CREATE TABLE linktags (
    linktagid integer NOT NULL,
    linkid integer,
    tagval character varying(1000)
);

When attempting to restore the database on PostgreSQL 11.2 via

cat mar_9_2018.db | docker exec -i pg-docker psql -U postgres

(docker container restore) the table returns empty because of the following error - 

ERROR:  missing data for column "tagval"
CONTEXT:  COPY linktags, line 737: "1185    9325"
 setval

I checked the db file and found that there are missing tabs where I would expect some sort of information, and clearly the restore process does as well.

demonstration of tabs missing with regular expressions

I also verified that the value in the database is a blank string.

So -

Edit: I did some further research and found that I was incorrect in the original checking of NULLs, it was instead blank strings that are causing the issue.

If I make an example table with null strings and then blank strings, I can see the NULLs get a newline but the blank does not null values showing a \n and blanks showing no \n

Upvotes: 3

Views: 3138

Answers (2)

TTS
TTS

Reputation: 108

Using inserts slows down restoration a lot. I tested it for quite the small database and I had to cancel the import after 5 minutes without finishing.

However, you can use the --quote-all-identifiers flag (see the pg_dump documentation for further information) to enclose empty strings in quotes. This way the copy command works as expected with empty strings.

Upvotes: 1

Tombart
Tombart

Reputation: 32436

pg_dump has an option to use INSERT instead of COPY

pg_dump -d db_name --inserts

as the manual warns, it might make restoration slow (and much larger dump file). Even in case of some inconsistencies tables will be filled with valid rows.

Another problem is with empty tables, pg_dump generates empty copy statement like:

COPY config (key, value) FROM stdin;
\.

in this case you'll be getting errors on reimport like:

 ERROR:  invalid input syntax for type smallint: "  "
CONTEXT:  COPY config, line 1, column group: "  "

which doesn't happen with --insert option (no insert statement is generated).

Upvotes: 7

Related Questions