Upload multiple tables from a single Geopackage file to PostgreSQL (PostGIS)

Question

Context

I have a single GeoPackage file containing several layers (tables) with different geometry types that I want to push to a PostgreSQL (+PostGIS) database. Each table in the GPKG has a unique geometry type.
But the geometry type varies between tables.

What I've tried so far

Here is a simple example with two layers in the GPKG file (containing POINT and LINESTRING geometries only, see figure 1), but the question can be extended to multiples situations with the same idea in mind.
It could be especially interesting to extend it to mixture of geometry types in a single GPKG layer or geometry collections, and to multiples geometry columns in a single table (even if they are of the same type).

Fig.1: Importing the GPKG file in QGIS shows two layers.

The content of the GPKG file looks something like that (I just decided to focus on the two first layers here, notice lines 12 and 13 are not yet implemented on my side, but I want them in the future):

$ ogrinfo multiple_vector_data.gpkg 
INFO: Open of `multiple_vector_data.gpkg'
      using driver `GPKG' successful.
1: el_points (Point)
2: el_lines (Line String)
3: ec_project1_pts (Point)
4: ec_project1_lin (Line String)
5: ep_project2_ply (Polygon)
6: ep_project2_lin (Line String)
7: atc_buildings (Multi Polygon)
8: atc_contours (Multi Line String)
9: atc_centers (Multi Point)
10: atc_head_1 (Line String)
11: atc_head_2 (Multi Point)
12: atc_head_3 (Geometry Collection)
13: atc_head_4 (Geometry Collection)
14: ekp_1 (Line String)
15: ekp_2 (Point)
16: ekp_3 (Polygon)
17: ekp_4 (Polygon)
18: ekp_5 (Multi Polygon)
19: ekp_6 (Multi Polygon)
20: ekp_7 (Multi Polygon)

This command where I specify two target (PostGIS) tables with the -nln option fails miserably:

ogr2ogr -append --config OGR_TRUNCATE YES --config PG_USE_COPY YES \
-nlt PROMOTE_TO_MULTI \
-nln vector_data.el_lines \
-nln vector_data.el_points \
-f "PostgreSQL" PG:"host=localhost user=user dbname=my_database" \
-a_srs EPSG:4326 "${gpkgdir}/multiple_vector_data.gpkg" 

by returning this error:

Warning 1: Geometry to be inserted is of type Multi Line String, whereas the layer geometry type is Multi Point.
Insertion is likely to fail
ERROR 1: COPY statement failed.
ERROR:  Geometry type (MultiLineString) does not match column type (MultiPoint)
CONTEXT:  COPY el_points, line 1222, column wkb_geometry: "010500...B30AFC8..."

ERROR 1: COPY statement failed.
ERROR:  Geometry type (MultiLineString) does not match column type (MultiPoint)
CONTEXT:  COPY el_points, line 1, column wkb_geometry: "010500...40390E1714..."

And if I revert the order of these options:

-nln vector_data.el_lines \
-nln vector_data.el_points \

to:

-nln vector_data.el_points \
-nln vector_data.el_lines \

The error is somewhat the opposite:

Warning 1: Geometry to be inserted is of type Multi Point, whereas the layer geometry type is Multi Line String.
Insertion is likely to fail
ERROR 1: COPY statement failed.
ERROR:  Geometry type (MultiPoint) does not match column type (MultiLineString)
CONTEXT:  COPY el_lines, line 1, column wkb_geometry: "010400...AFC854674740"

ERROR 1: COPY statement failed.
ERROR:  Geometry type (MultiPoint) does not match column type (MultiLineString)
CONTEXT:  COPY el_lines, line 1, column wkb_geometry: "010400...3DF5A3634740"

Question

How could I achieve that?
Could we specify a kind of layers reading order (if this makes sense)?
Or specifying a mapping between the GPKG layers and the PostGIS tables?

---

Related question

Uploading Geopackage contents to PostgreSQL

---

Environment

$ ogr2ogr --version
GDAL 2.4.0, released 2018/12/14

psql    

select version();
"PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

select postgis_version();
"2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"

Answer 1

You must run two separate commands

ogr2ogr -append --config OGR_TRUNCATE YES --config PG_USE_COPY YES \
-nlt PROMOTE_TO_MULTI \
-nln vector_data.el_lines \
-f "PostgreSQL" PG:"host=localhost user=user dbname=my_database" \
-a_srs EPSG:4326 "${gpkgdir}/multiple_vector_data.gpkg" "el_lines" 

ogr2ogr -append --config OGR_TRUNCATE YES --config PG_USE_COPY YES \
-nlt PROMOTE_TO_MULTI \
-nln vector_data.el_points \
-f "PostgreSQL" PG:"host=localhost user=user dbname=my_database" \
-a_srs EPSG:4326 "${gpkgdir}/multiple_vector_data.gpkg" "el_points" 

You wrote:

It could be especially interesting to extend it to mixture of geometry types in a single GPKG layer or geometry collections, and to multiples geometry columns in a single table (even if they are of the same type).

Mixture of geometry types is supported in GeoPackage when the geometry type is set to generic "geometry". With ogr2ogr it means -nlt geometry. QGIS does not support multiple geometry types on the same layer so it will split the layer into layer group. Some other clients like OpenJUMP do support mixed geometry types on the same layer.

Multiple geometry columns in the same table are not allowed in GeoPackage. For SpatiaLite they are OK and you can have for example native and simplified geometries in the same table. If you create a layer from such table with QGIS you must select which geometry to use.