SpatiaLite

​SpatiaLite is a GIS file format based on ​SQLite, which stores multiple layers and data tables into a single file. It is an alternative format to ESRI Shapefiles, which have several legacy issues (such as storage types and lengths of column names, etc.) Furthermore it is simple to distribute and run, as there is only one file.

Exporting from PostGIS to SpatiaLite

Use ​ogr2ogr built with ​SQLite support.

To create the SpatiaLite file and export a first layer:

$ ogr2ogr -f SQLite -dsco SPATIALITE=yes myfile.sqlite PG:"dbname=mydb user=myuser password=mypass" myschema.mytable

And for additional layer/tables:

$ ogr2ogr -f SQLite -update myfile.sqlite b PG:"dbname=mydb user=myuser password=mypass" myschema.my2ndtable

To load all the tables of a schema from Postgres to SpatiaLite (with or without geometry) and without the views:

$ ogr2ogr --config PG_LIST_ALL_TABLES YES --config PG_SKIP_VIEWS YES -f "SQLite" mydb.sqlite -progress PG:"dbname='mydb' \
  active_schema=myschema schemas=myschema host='localhost' port='5432' user='myuser' password='mypass'" -lco LAUNDER=yes \
  -dsco SPATIALITE=yes -lco SPATIAL_INDEX=yes -gt 65536

To load a table (mytable) (with a geometry columns named "geometry") from a SQLite/SpatiaLite (mysqlite.sqlite) into a schema (myschema) of a Postgres/PostGIS:

ogr2ogr --config SQLITE_LIST_ALL_TABLES YES -f "PostgreSQL" PG:"dbname='mydb' active_schema=myschema schemas=myschema host='localhost' port='5432' user='myuser' password='mypass'" -overwrite -lco SCHEMA=myschema -lco GEOMETRY_NAME=geometry -lco SPATIAL_INDEX=YES mysqlite.sqlite mytable

PostgreSQL FDW

A Postgres Foreign Data Wrapper for SQLite which supports PostGIS types is available ​on GitHub.