PostGIS – Import Entire Geopackage via DB Manager

dbmanagergeopackagepostgispostgresqlqgis

I am trying to important an entire geopackage to a PostGIS database via the Import Layer/File button in DB Manager. However when I do this only one of the layers in the geopackage gets successfully imported. Do I have to import each layer individually or there a way I can import the entire geopackage at once? It would be ideal if I could do this in DB Manager as QGIS is the software I am most familiar with.

Best Answer

The import option in the DB Manager is made to work for one layer at a time. I would do the job with ogr2ogr. If the GPKG layers are not listed in the command, or defined by the -sql option, then all layers are converted.

ogr2ogr -f PostgreSQL PG:"<connection parameters>" input.gpkg

Related Solutions

QGIS – Deleted Layer in Geopackage Still Visible in QGIS: How to Fix

QGIS sets the GeoPackage SQLite database into Write ahead logging (WAL) mode https://sqlite.org/wal.html. Changes that are made into a database from one connection will not exist for the other connections which were open at the time of editing. All the changes are written into the database at least when the last connection is closed. The .wal file disappears by the same. That is also a simple indicator for a user: if .wal file exists there are some changes pending and the database may look a bit different for each open connection.

PyQGIS – How to Load a GeoPackage Layer into Memory

Thanks to @MrXsquared and @Taras.

My final code that works:

    def load_db_layers_from_gpkg(self):
        db_path = os.path.join(self.project.project_data_dir(), DB_NAME)
        for layername in ['Pipes', 'Tanks', 'Reservoirs', 'Junctions', 'ValveLinks', 'PumpLinks', 'ValveNodes', 'PumpNodes',
                      'Demand_BASE', 'Status', 'Controls', 'Rules', 'Pump_Patterns', 'Patterns', 'Curves',
                      'Options']:
            uri = f'{db_path}|layername={layername}'
            gpkg_layer = QgsVectorLayer(uri, layername, 'ogr')
            request = QgsFeatureRequest().setFilterFids(gpkg_layer.allFeatureIds())
            self.layer_objects[layername] = gpkg_layer.materialize(request)
            del gpkg_layer

Best Answer

Related Solutions

QGIS – Deleted Layer in Geopackage Still Visible in QGIS: How to Fix

PyQGIS – How to Load a GeoPackage Layer into Memory

Related Question