I'd like to make multiple, progressive backup copies of QGIS (3.2.1) layers that are stored in PostGIS tables. I've tried multiple routes to export them as files on disk, but they all seem to destroy some aspect of the data - shuffle the order, add fields, reduce floats to integers, convert LineStrings to MultiLineStrings... (Long story here)
My destination (HEC-RAS) is rather picky about such details.
Yes, I know how to make incremental backups of the entire Postgre database with pgAdmin4, but I don't know any way to extract a single table from one of them.
I found this and it actually makes a complete snapshot of your QGIS project in PostGIS: http://www.bostongis.com/blog/index.php?/archives/271-New-in-QGIS-3.2-Save-Project-to-PostgreSQL.html
Layer -> Data Source Manager -> PostgreSQL -> Edit check the box Allow saving/loading QGIS projects in the database Database -> DB Manager -> Providers -> (select database) -> Schema -> Create Schema Project -> Save to -> PostgreSQL -> select schema, name project/version
--> Unfortunately it does not save its own copies of the actual layer tables, only references to the tables in the original project. Editing those also edits your "backups"!
Saving a Layer Definition File likewise does not make its own copies of the actual tables: http://getspatial.com/gisblog/tip-of-the-day-create-layer-definition-files-for-reuse-and-consistency/
save as a Layer Definition File. This will save all characteristics of this layer from the source, including connection information if it is a database layer, to projections, spatial filters, styles, and labeling.
--> But not the actual data table...
There are several ways to export a layer to an existing PostGIS table, but I can't find any that will save to a new table. You can create a new table:
Database -> DB Manager -> Providers -> (select schema) -> Table (tab) -> Create Table -> (name)...
--> But you must manually define all the columns and attributes!
This sounded promising:
Processing -> Toolbox -> GDAL -> Import Vector into PostGIS database (available connection) select database, layer, geometry type, CRS, table name, options...
It doesn't error:
Processing algorithm… Algorithm 'Import vector into PostGIS database (available connections)' starting… Input parameters: { 'ADDFIELDS' : False, 'APPEND' : False, 'A_SRS' : 'EPSG:2226', 'CLIP' : False, 'DATABASE' : 'postgres', 'DIM' : 0, 'GEOCOLUMN' : 'geom', 'GT' : '', 'GTYPE' : 4, 'INDEX' : False, 'INPUT' : 'dbname=\'rivergis\' host=127.0.0.1 port=5432 user=\'postgres\' password=\'Ridgeview\' key=\'XsecID\' table=\"MendoFEMA\".\"XSCutLines\" (geom) sql=', 'LAUNDER' : False, 'OPTIONS' : '', 'OVERWRITE' : True, 'PK' : 'id', 'PRECISION' : True, 'PRIMARY_KEY' : None, 'PROMOTETOMULTI' : True, 'SCHEMA' : 'public', 'SEGMENTIZE' : '', 'SHAPE_ENCODING' : '', 'SIMPLIFY' : '', 'SKIPFAILURES' : False, 'SPAT' : None, 'S_SRS' : None, 'TABLE' : 'XSCutLines180726v321viaGDAL', 'T_SRS' : None, 'WHERE' : '' }
GDAL command: cmd.exe /C ogr2ogr.exe -progress --config PG_USE_COPY YES -f PostgreSQL PG:" host=127.0.0.1 port=5432 user='postgres' password='Ridgeview' active_schema=public " -lco DIM=2 C:/Users/loren/AppData/Local/Temp/processing_246b5b53088e4ae9ad8ceeab05118d35/80c5541d7ad249ebbbe6a8430c1df09c/INPUT.shp INPUT -overwrite -nlt LINESTRING -lco GEOMETRY_NAME=geom -lco FID=id -nln public.XSCutLines180726v321viaGDAL -a_srs EPSG:2226 -nlt PROMOTE_TO_MULTI GDAL command output: 0...10...20...30...40...50...60...70...80...90...100 - done.
Execution completed in 0.72 seconds Results: {}
Loading resulting layers Algorithm 'Import vector into PostGIS database (available connections)' finished
--> But no new tables exist in public or anywhere else I can find, no new layers load into the project.
My research patience for today is exhausted. Does anyone know of a realistically simple way to create a backup of a QGIS layer that will be saved into an independent PostGIS table, that can be reloaded into the original project alongside the original layer and remain completely unaffected by edits to the original layer?
180731:
I was wrong earlier about Processing -> Toolbox -> GDAL -> Import Vector into PostGIS database. It did make tables:
QGIS Browser can't see them...
But DB Manager can:
I just tried again, and it rejects any of my rivergis schemas, only seems to work for user postgres and public there. Maybe I'm missing something?
--table
; this might also be possible in pgAdmin).