3

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: Import Vector into PostGIS made table, QGIS can't see it

QGIS Browser can't see them...
But DB Manager can: New table in DB Manager

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?

  • I guess you have to access Postgres directly to make a copy of the table (e.g., pg_dump with --table; this might also be possible in pgAdmin). – CL. Jul 30 '18 at 6:42
  • Have you looked at the Qpackage plugin? The description isn't clear whether it works for postGIS tables, but if so it seems like it should do what you want. "Qpackage is a tool to save both your QGIS project and data contained in the project to a new directory. It allows to convert any GIS vector format towards the .shp only. You may also apply a new projection." -plugin description – csk Jul 30 '18 at 17:32
  • @CL I'm thinking maybe use SQL - CREATE TABLE AS to make my own backups? – LorenAmelang Jul 30 '18 at 22:54
  • @csk I tried Qpackage. It looked in the QGIS Browser like it made an independent QGIS project version, which I can select and view in QGIS. No sign of individual project copies in DB Manager or pgAdmin4... BUT I've since changed the files in the original project, and all the changes appear in the Qpackage version! I'm not sure what it is intended for, but obviously not backup. – LorenAmelang Jul 30 '18 at 22:55
  • @csk I was confused in my last comment! That was the Save-Project-to-PostgreSQL method. I just went back in my notes and found QPackage had made big files that drew nothing when I tried it the first time, and I gave up. Just tried again, and after several error reports about the .qgz file, it loaded a project with the name I'd chosen, but said "You must close Qgis to take into account changes". Missed that before. It uses shapefiles, but so far I haven't spotted corruption... Many steps yet to test. Seriously inconvenient to have to close QGIS! – LorenAmelang Jul 31 '18 at 1:06
0

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.

  • Have you tried the ability to save a QGIS project to PostgreSQL/PostGIS? I'm curious if this has backup abilities for not just data (as you've described) but layer settings as well? – DPSSpatial Jul 30 '18 at 16:39
  • @enolan That sounds promising. I got as far as a filename for the backup, and the "..." icon to browse for a location does nothing (Windows 10). Someday when I'm not so swamped maybe I'll dig into that. But I'm still wondering if any of the possible disk storage formats will be any more precise than the things I've already tried. Have you tried the SQL - CREATE TABLE AS route? – LorenAmelang Jul 30 '18 at 23:10
  • DPSSpatial Sounds like you're asking about the Project -> Save to -> PostgreSQL function I wrote about above. As far as I can tell it saves everything and reloads an identical project - as long as you don't change the layers of the original! – LorenAmelang Jul 31 '18 at 1:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.