During the 2018 season I observed a number of databases throwing a long series of errors of the form
invalid command \N
(like thousands of such errors).
The latest observation was when I tried to restore the 2017 Emergency Response disaster database.
At least in this particular case, it appears that's an issue because of the occurrence of the \N character representation throughout the data, as in here:
COPY public.address (ogc_fid, objectid, address_id, add_num, add_num_ch, leading_ze, str_predir, str_name, str_type_c, str_postdi, str_nm_ful, unit_value, add_full, city, state, state_abbr, zip_code, zip4, juris, county, status_mar, state_id, tlid, source, x, y, wkb_geometry) FROM stdin;
1 2001 530233 15652 15652 \N SW JAYLEE ST \N SW JAYLEE ST \N 15652 SW JAYLEE ST BEAVERTON OREGON OR 97007 2624 UNINCORPORATED WASHINGTON Active 1S117CA07800 1S117CA07800 WASH 7602498.091000000000000 670808.488999999940000 0104000020E610000001000000010100000055CEC973A4B55EC08C7F02FDCDBD4640
2 2002 789635 16693 16693 \N SE RIVER RD \N SE RIVER RD \N 16693 SE RIVER RD MILWAUKIE OREGON OR 97267 4505 UNINCORPORATED CLACKAMAS Active 21E13BB07500 21E13BB07500 CLAC 7653522.493999999900000 640054.792999999950000 0104000020E6100000010000000101000000E4F1392FB6A85EC03B87770E82B34640
3 2003 5856351 1950 1950 \N NW 192ND AVE \N NW 192ND AVE 315 1950 NW 192ND AVE APT 315 HILLSBORO OREGON OR 97006 6514 HILLSBORO WASHINGTON Active 1N236AB12700 1N236AB12700 HILL 7594029.031999999700000 689654.520999999950000 0104000020E61000000100000001010000004E756E96E4B75EC015AB97D054C44640
...
etc.
Repro
The standardized approach to backup for our PostgreSQL databases is from Step (4) in HOWTO create a backup for new database creation:
pg_dump -Fp -v -C -c --if-exists -d <database> | gzip -c > <database>.sql.gz
The standardized approach to restore for our PostgreSQL databases is from Step (4) of Restore from .sql.gz in HOWTO: Rebuild the Centralized Database service:
gzip -dc BACKUP_FILE.sql.gz | sudo -u postgres psql
The 2017 Emergency Response backups are stored here:
s3://hacko-data-archive/2017-emergency-response/database-backups/
This has also been observed a few weeks ago for the 2018 Housing Affordability database, which is here:
s3://hacko-data-archive/2018-housing-affordability/database-backup/
During the 2018 season I observed a number of databases throwing a long series of errors of the form
invalid command \N(like thousands of such errors).The latest observation was when I tried to restore the 2017 Emergency Response
disasterdatabase.At least in this particular case, it appears that's an issue because of the occurrence of the
\Ncharacter representation throughout the data, as in here:Repro
The standardized approach to backup for our PostgreSQL databases is from Step (4) in HOWTO create a backup for new database creation:
pg_dump -Fp -v -C -c --if-exists -d <database> | gzip -c > <database>.sql.gzThe standardized approach to restore for our PostgreSQL databases is from Step (4) of Restore from .sql.gz in HOWTO: Rebuild the Centralized Database service:
gzip -dc BACKUP_FILE.sql.gz | sudo -u postgres psqlThe 2017 Emergency Response backups are stored here: s3://hacko-data-archive/2017-emergency-response/database-backups/
This has also been observed a few weeks ago for the 2018 Housing Affordability database, which is here: s3://hacko-data-archive/2018-housing-affordability/database-backup/