search

minus34 / gnaf-loader

A quick way to get started with Geoscape's open GNAF & Admin Boundaries
Apache License 2.0
185 stars 66 forks source link

restore not working #43

Closed peopzen closed 5 years ago

peopzen commented 5 years ago

I am trying to restore the dump file on Postgres 11+ with PostGIS 2.5+. The bat file is edited for right file location. But the restore process report a serial of errors like: "pg_restore: [archiver (db)] could not execute query: Error: ......"

The final database, admin schema has one table, and gnaf has 8 tables. I think it is wrong. Any ideas? Thanks.

minus34 commented 5 years ago

Is there anything else in the error messages? I've verified the dump files (albeit on a Mac) load into Postgres 11.5 with Postgis 2.5.2 successfully.

You should have 12 gnaf and 32 admin bdys tables

peopzen commented 5 years ago

Hi Minus. Thanks for your quick response.

An error example:

pg_restore: [archiver ] Error from TOC entry 25168; 1259 24118731 INDEX abs_2016_sa1_geom_idx postgres pg_restore: [archiver ] could not execute query: ERROR: relation ädmin_bdys_201908.abs_2016_sa1"does not exist Command was: CREATE INDEX abs_2016_sa1_geom_idx ON admin_bdys_201908_abs_2016_sa1 USING gist (geom); ALTER TABLE admin_bdys_201908.abs_2016_sa1 CLUSTER ON abs_2016_sa1_geom_idx;

Any special settings required for "geo" database besides Postgres 11 with PostGIS 2.5.2? A postgis_25_sample database was successfully installed after PostGIS 2.5.2 installation.

minus34 commented 5 years ago

Can you please capture all the errors - that error is a consequence of the table not loading successfully earlier on.

Something like this should output all the errors to a file which you can then post here:

"C:\Program Files\PostgreSQL\11\bin\pg_restore" -Fc -d geo -p 5432 -U postgres "C:\git\minus34\admin-bdys-201908.dmp" > errors.txt

peopzen commented 5 years ago

I think I found the problem. On my Windows 7 machine 'psql' wasn't set up in path, the first command ' psql -d geo -p 5432 -U postgres -c "CREATE EXTENSION IF NOT EXISTS postgis;" ' didn't run correctly. Once I properly run this command, it works perfectly. Although I had a postgis_25_sample database after PostGIS 2.5.2 installation, the "CREATE EXTENSION" is still required for "geo" database. Many thanks for your wonderful work.

minus34 commented 5 years ago

Thanks, no probs.

Yes, Postgis still needs to be added to each database after installation