minus34
commented
6 years ago I haven't encountered that before - possibly a PG memory issue?
What happens if you comment out lines 68 to 89 (e.g. the lines below), restart the database service and then try again from that point?
PART 1 - load gnaf from PSV files
logger.info("")
start_time = datetime.now()
logger.info("Part 1 of 4 : Start raw GNAF load : {0}".format(start_time))
drop_tables_and_vacuum_db(pg_cur, settings)
create_raw_gnaf_tables(pg_cur, settings)
populate_raw_gnaf(settings)
index_raw_gnaf(settings)
if settings['primary_foreign_keys']:
create_primary_foreign_keys(settings)
else:
logger.info("\t- Step 6 of 7 : primary & foreign keys NOT created")
analyse_raw_gnaf_tables(pg_cur, settings)
set postgres search path back to the default
pg_cur.execute("SET search_path = public, pg_catalog")
logger.info("Part 1 of 4 : Raw GNAF loaded! :
{0}".format(datetime.now() - start_time)) #
PART 2 - load raw admin boundaries from Shapefiles
logger.info("")
start_time = datetime.now()
logger.info("Part 2 of 4 : Start raw admin boundary load :
{0}".format(start_time))
load_raw_admin_boundaries(pg_cur, settings)
prep_admin_bdys(pg_cur, settings) create_admin_bdys_for_analysis(settings) logger.info("Part 2 of 4 : Raw admin boundaries loaded! : {0}".format(datetime.now() - start_time))
On 28 February 2018 at 18:23, Andrew Harvey notifications@github.com wrote:
I'm trying to update openaddresses with the FEB18 release but when I run from a clean install of Debian 9 with the following setup:
apt update && apt install byobu git curl zip unzip parallel python-psycopg2 postgresql-9.6 postgresql-9.6-postgis-2.3 postgis
git clone https://github.com/minus34/gnaf-loader.git
TMP=/tmp/work mkdir $TMP mkdir $TMP/gnaf $TMP/gnaf-admin $TMP/tablespace chown postgres:postgres $TMP/tablespace
sudo -u postgres psql -c "CREATE USER gnafun WITH SUPERUSER PASSWORD 'gnafpw'" sudo -u postgres psql -c "CREATE TABLESPACE gnafts OWNER gnafun LOCATION '$TMP/tablespace'" sudo -u postgres psql -c 'CREATE DATABASE gnafdb OWNER gnafun TABLESPACE gnafts' sudo -u postgres psql -c 'CREATE EXTENSION postgis'
curl -s --retry 10 --location 'https://data.gov.au/dataset/bdcf5b09-89bc-47ec-9281-6b8e9ee147aa/resource/53c24b8e-4f55-4eed-a189-2fc0dcca6381/download/feb18adminboundsesrishapefileordbffile20180219141148.zip' -o $TMP/gnaf-admin.zip & curl -s --retry 10 --location 'https://data.gov.au/dataset/19432f89-dc3a-4ef3-b943-5326ef1dbecc/resource/4b084096-65e4-4c8e-abbe-5e54ff85f42f/download/feb18gnafpipeseparatedvalue20180219141901.zip' -o $TMP/gnaf.zip &wait parallel "unzip -d $TMP/{} $TMP/{}.zip" ::: gnaf gnaf-admin
GNAF_DIR="$(find $TMP -type d | grep 'G-NAF' | grep 'Authority Code' | xargs -I {} dirname {} | head -n1)" BOUNDARY_DIR="$(find $TMP -type d | grep 'Administrative Boundaries' | head -n1 | xargs -I {} dirname {})"
/tmp/work/gnaf/FEB18_GNAF_PipeSeparatedValue_20180219141901/G-NAF/G-NAF FEBRUARY 2018# /tmp/work/gnaf-admin/FEB18_AdminBounds_ESRIShapefileorDBFfile_20180219141148
change local all all connection to md5 to accept password connections locally
service postgresql restart
python gnaf-loader/load-gnaf.py --pguser gnafun --pgdb gnafdb --pgpassword gnafpw --gnaf-schema gnaf --gnaf-tables-path "$GNAF_DIR" --admin-bdys-path "$BOUNDARY_DIR" --raw-unlogged --no-boundary-tag
I get the following output:
root : INFO root : INFO Start gnaf-loader root : INFO - running Python 2.7.13 with Psycopg2 2.6.2 root : INFO - on Linux #1 SMP Debian 4.9.30-2+deb9u1 (2017-06-18) root : INFO - using Postgres 9.6.6 on x86_64-pc-linux-gnu and PostGIS 2.3.1 (with GEOS 3.5.1-CAPI-1.9.1) root : INFO root : INFO Part 1 of 4 : Start raw GNAF load : 2018-02-28 18:08:09.408937 root : INFO - Step 1 of 7 : tables dropped : 0:00:00.069420 root : INFO - Step 2 of 7 : database NOT vacuumed root : INFO - Step 3 of 7 : UNLOGGED tables created : 0:00:00.019004 root : INFO - Loading state ACT root : INFO - Loading state NSW root : INFO - Loading state NT root : INFO - Loading state OT root : INFO - Loading state QLD root : INFO - Loading state SA root : INFO - Loading state TAS root : INFO - Loading state VIC root : INFO - Loading state WA root : INFO - Step 4 of 7 : tables populated : 0:01:28.195092 root : INFO - Step 5 of 7 : indexes created: 0:03:00.082667 root : INFO - Step 6 of 7 : primary & foreign keys NOT created root : INFO - Step 7 of 7 : tables analysed : 0:00:04.827586 root : INFO Part 1 of 4 : Raw GNAF loaded! : 0:04:33.199275 root : INFO root : INFO Part 2 of 4 : Start raw admin boundary load : 2018-02-28 18:12:42.608401 root : INFO - Step 1 of 3 : raw admin boundaries loaded : 0:06:51.062249 Traceback (most recent call last): File "gnaf-loader/load-gnaf.py", line 940, in
if main(): File "gnaf-loader/load-gnaf.py", line 90, in main prep_admin_bdys(pg_cur, settings) File "gnaf-loader/load-gnaf.py", line 513, in prep_admin_bdys .format(settings['admin_bdys_schema'], settings['pg_user'])) psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block Looking at the database the raw_ schema and tables are all there, but the non-raw schema doesn't exist and I can't work out why it's not being created. Any ideas?
This is the postgresql log:
2018-02-28 18:19:33.668 AEDT [5607] gnafun@gnafdb ERROR: current transaction is aborted, commands ignored until end of transaction block 2018-02-28 18:19:33.668 AEDT [5607] gnafun@gnafdb STATEMENT: SET CLIENT_ENCODING TO UTF8; SET STANDARD_CONFORMING_STRINGS TO ON; BEGIN; INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('1','AGRICULTURAL',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('2','COMMERCIAL',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('3','EDUCATION',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('4','HOSPITAL/MEDICAL',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('5','INDUSTRIAL',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('6','NOUSUALRESIDENCE',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('7','PARKLAND',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('8','RESIDENTIAL',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('9','SHIPPING',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('10','TRANSPORT',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('11','WATER',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('12','OTHER',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('13','ANTARCTICA',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('14','MIGRATORY',NULL); INSERT INTO "raw_admin_bdys_201802"."aus_mb_category_class_aut" ("code","name","descriptio") VALUES ('15','OFFSHORE',NULL); COMMIT; ANALYZE "raw_admin_bdys_201802"."aus_mb_category_class_aut";
2018-02-28 18:19:33.679 AEDT [5607] gnafun@gnafdb ERROR: current transaction is aborted, commands ignored until end of transaction block 2018-02-28 18:19:33.679 AEDT [5607] gnafun@gnafdb STATEMENT: CREATE SCHEMA IF NOT EXISTS admin_bdys_201802 AUTHORIZATION gnafun
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/minus34/gnaf-loader/issues/29, or mute the thread https://github.com/notifications/unsubscribe-auth/ABucvpQrubC6d2dJWlXNY4qFtKdkDsyXks5tZP73gaJpZM4SWMO8 .
andrewharvey
skellert0
tomoliveri
cndrla
CMCDragonkai
unverified-contact
duelran
I'm trying to update openaddresses with the FEB18 release but when I run from a clean install of Debian 9 with the following setup:
I get the following output:
Looking at the database the raw_ schema and tables are all there, but the non-raw schema doesn't exist and I can't work out why it's not being created. Any ideas?
This is the postgresql log: