6

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?

5
  • 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, 2018 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, 2018 at 17:32
  • @CL I'm thinking maybe use SQL - CREATE TABLE AS to make my own backups? Jul 30, 2018 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. Jul 30, 2018 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! Jul 31, 2018 at 1:06

1 Answer 1

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.

3
  • 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? Jul 30, 2018 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? Jul 30, 2018 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! Jul 31, 2018 at 1:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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