Backup QGIS (3.2.1) layer to independent PostGIS table?

Question

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?

Answer 1

Here's what I discovered that you can do. But it requires renaming the original table. And it's not pretty but it's the only way that I found to do what it is your asking.

Open PgAdmin. Right click the table you want to affect. Backup to a backup file. Rename the original table. Go to schema "public" etc. Right click. Select restore. The table will be restored and you will have two tables that have the same data, and no linkage to one another.