D 2018-08-03T16:12:08.588
L PostgreSQL
P 77aaae0fe821aff22b0c99c06b08d10ac06e7ad4
U sandro
W 7672
Back to <a href="https://www.gaia-gis.it/fossil/spatialite_gui/wiki?name=spatialite-gui">main Wiki page</a><hr><br>
<h1>The GUI as a PostgreSQL client</h1>
Starting from version <b>2.1.0</b> the GUI has the capability to support connections to external <b>PostgreSQL / PostGIS</b> databases.<br>
This functionality is mainly based on <a href="https://www.gaia-gis.it/fossil/virtualpg/wiki?name=tutorial">libvirtualpg</a>, that is now a required dependency for the GUI tool.
The present tutorial is intended to quickly explain how the PostgreSQL support is implemented by <b>spatialite_gui</b>.<br><br>
<table cellspacing="6" cellpadding="6" bgcolor="f0ffe0" width="100%" border="1">
<tr><td colspan="2" align="center"><h3>1 - Establishing a Connecting to a PostgreSQL DBMS</h3><hr></td></tr>
<tr>
<td><b>1.a</b> you can press the appropriate button from the Toolbar</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui1.png" alt="connection 1"></td>
</tr>
<tr>
<td><b>1.b</b> you can select the appropriate item from the main Menu</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui2.png" alt="connection 2"></td>
</tr>
</tr>
<tr>
<td><b>1.c</b> and finally you can select the appropriate item from the context Menu that will appear by clicking the <b>left</b> button of the mouse over the <b>PostreSQL</b> main node from the Table Tree.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui3.png" alt="connection 3"></td>
</tr><tr>
<td><b>1.d</b> in all three cases the PostgreSQL connection dialog will appear.<br>
Just insert the required connection arguments and confirm.<br><br>
If the given arguments are invalid an error message will be then displayed, othrwise the connection will be immediately established.<br><br><br><hr><br><br><br>
If you are attempting to connect a remote server, you could probably find useful reading first the tutorial about <a href="https://www.gaia-gis.it/fossil/virtualpg/wiki?name=port-forwarding">SSH tunneling and Port Forwarding</a></td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui4.png" alt="connection 4"></td>
</tr>
<tr><td colspan="2" align="center"><h3>2 - Expanding the PostgreSQL tree</h3><hr></td></tr>
<tr>
<td><b>2.a</b> once that a new Postgres connection has been succesfully established the Postgres node on the main Table Tree will contain all Tables and Views defined on the Postgres Database, accordingly to the usual arrangement adopted by Postgres:
<ul>
<li>Connection <i>aka</i> <b>Database</b>
<ul>
<li><b>Schema</b>
<ul>
<li><b>Table</b> or <b>View</b></li>
</ul></li>
</ul></li>
</ul><br><br>
Each kind of database object will be clearly identified by an appropriate icon; Views will be distinguished from Tables, and <b>PostGIS</b> Tables (containing <b>Geometry</b> columns) will be clearly distinguished from ordinary Tables.<br><br>
<b>Notice</b>: all Postgres Tables and Views (namely: <b>VirtualPostgres</b> Tables) will always have a conventional name starting with a <b>vpg_</b> prefix.<br>
Whenever possible the original Postgres Name will be preserved, otherwise it will be disambiguated by adding an appropriate numeric suffix.<br><br><br>
<hr>
<br><br>
<b>Note</b>: all database objects in the Postgres Tree are inerently transient, and will automatically disappear when the corresponding connection will be closed, or when the current session ends.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui5.png" alt="table tree"></td>
</tr>
<tr>
<td><b>2.b</b> if, for any possible reason (system crash, power failure and alike), the current session will fail to correctly perform the expected final cleanup, your SQLite database will remain in a dirty and inconsistent state.<br>
As the side figure shows, when restarting a new session many <b><i>orphan VirtualPostgres tables</i></b> will badly remain from the last aborted session.<br><br>
If this is your case, don't despair: you just have to select the appropriate item from the Postgres contaxt menu and the DB will be immediately recovered in a safe consistent and clean state.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/orphans.png" alt="orphan tables"></td>
</tr>
<tr><td colspan="3" align="center"><h3>3 - Metadata of PostgreSQL objects</h3><hr></td></tr>
<tr>
<td><b>3.a</b> you can easily query the metadata for each Postgres object; just select the appropriate item from the context menu.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui6.png" alt="metadata info"></td>
</tr>
<tr>
<td><b>3.b</b> Metadata for a <b>Connection</b> <i>aka</i> <b>Database</b> object will report the initial connection arguments.<br><br><br>
<hr>
<br><br>
<b>Note</b>: you can eventually connect more than a single Postgres Database in the same sesson: connecting twice the same Database is allways forbidden.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui7.png" alt="connection metadata"></td>
</tr>
<tr>
<td><b>3.c</b> Metadata for a <b>Schema</b> object aren't really interesting, because just the name is reported.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui8.png" alt="schema metadata"></td>
</tr>
<tr>
<td><b>3.d</b> Metadata for <b>Table</b> or <b>View</b> objects will report all <b>GRANT permissions</b> for the current User.<br><br><br>
<hr>
<br><br>
<b>Note</b>: also in the case of a <b>READ-WRITE</b> enabled connection, all Postgres Tables lacking the permission to execute <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> statements will be allways considered to be <b>READ-ONLY</b>.<br>
Exactly as any other Table not supported by a <b>PRIMARY KEY</b> definition.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui9.png" alt="table metadata"></td>
</tr>
<tr>
<td><b>3.e</b> Metadata for a <b>PostGIS Table</b> objects will report a full qualification of the <b>GEOMETRY</b> column(s), this including the GeometryType, the Srid and the CoordinateDimensions.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui10.png" alt="postgis metadata"></td>
</tr>
<tr>
<td><b>3.f</b> all <b>PostGIS Tables</b> will always have a <b>companion Table</b> sharing the same identical name except that for a final <b>_postgis</b> suffix.<br><br><br>
<b>Note</b>: these are not <b>VirtualPostgres Tables</b>; they are instead Spatialite's own <b>Spatial Views</b>, and their intended scope is to perform a silent and fully transparent conversion of binary Geometries nack and forth from one format to the other in the most user friendly way.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui11.png" alt="postgis wrapper metadata"></td>
</tr>
<tr><td colspan="3" align="center"><h3>4 - Handling PostgreSQL Tables and Views</h3><hr></td></tr>
<tr>
<td><b>4.a</b> PostgreSQL Tables and Views are considered, as far as possible, as if they were native SQLite objects.<br>
So the usual operations supported on SQLite's own objects will be supported also in the case of PostgreSQL objects.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui12.png" alt="table operations"></td>
</tr>
<tr>
<td><b>4.b</b> the above criteria will apply also to PostGIS Geometries.<br><br><br>
<b>Hint</b>: exporting a full Shapefile from a PostGIS Geometry was never more direct and simple than this.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui13.png" alt="geometry operations"></td>
</tr>
</table>
Back to <a href="https://www.gaia-gis.it/fossil/spatialite_gui/wiki?name=spatialite-gui">main Wiki page</a>
Z 67981f4187300530040bf1cdfe751880