I've tried the standardized backup and restore procedure with a PostgreSQL 9.5 database, and the database fails to successfully restore.
Along with the thousands of invalid command \N errors (see #204), there's also a number of data-specific warnings such as:
ERROR: syntax error at or near "15"
LINE 1: 15 B 0 NONE 122220 0.75 CU 0 20' WEL SW MONTGOMERY DR 0.7...
which ties to this specific line of the disaster.sql backup file that I'm attempting to restore:
179589 48102600 2660 2660 SW MONTGOMERY DR MELBOURNE N20' 14; 15 B 0 NONE 122220 0.75 CU 0 20' WEL SW MONTGOMERY DR 0.75 3' SSL SW FERN ST 17' EWL SW MONTGOMERY DR 0.619999999999999996 12 CI 3226 ACT 1 567536 \N \N CL STAIRS \N \N 336907 JENNIEG 2016-11-22 08:00:00+00 3' 2660 776217 \N \N 1 \N \N E-567536 \N \N \N NO \N \N \N \N \N \N \N \N \N 3995623600 \N \N \N \N 30.555640729986699 0105000020110F0000010000000102000000030000003A014D4CAA0D6AC1984C15E4E5BF5541201FF4E6A70D6AC1D800DE66E3BF55411AE258E9A60D6AC1604BC85BE2BF5541
These warnings seem to tie to the use of the ; semicolon character in the data record that's been backed up.
Repro
I installed a PostgreSQL 9.5.13 server (since the ER 2017 databases are hosted on a PostgreSQL 9.5.x server that was installed last year) to which to restore these databases, and ran the following to initialize the database:
initdb /usr/local/var/postgres
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 that I attempted to restore are stored here:
s3://hacko-data-archive/2017-emergency-response/database-backups/
I've tried the standardized backup and restore procedure with a PostgreSQL 9.5 database, and the database fails to successfully restore.
Along with the thousands of
invalid command \Nerrors (see #204), there's also a number of data-specific warnings such as:which ties to this specific line of the
disaster.sqlbackup file that I'm attempting to restore:These warnings seem to tie to the use of the
;semicolon character in the data record that's been backed up.Repro
I installed a PostgreSQL 9.5.13 server (since the ER 2017 databases are hosted on a PostgreSQL 9.5.x server that was installed last year) to which to restore these databases, and ran the following to initialize the database:
initdb /usr/local/var/postgresThe 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 that I attempted to restore are stored here: s3://hacko-data-archive/2017-emergency-response/database-backups/