My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial extent.
My first idea was to select only the features in my spatial extent and then extract these but I'm not sure I can do it without losing some information from the data model.
The second idea is to import only the data that I want in a new db, filtered with spatial extent (quite long).
The third idea is to copy completely the db and then filter the undesired features.
Which idea is the best? Is there a 4th idea that could be easier/quicker? The data model is quite complex : https://github.com/qwat/qwat-data-model
Best Answer
Might not be the most elegant solution, but you can use the pg_dump utility to copy the DB and then overwrite the data in the spatial tables of the new, subset DB using the ogr2ogr utility. Run pg_restore and then ogr2ogr with your clipping method of choice. To overwrite the spatial data so that it is constrained to the extent of a shapefile, for example, run something like:
Check pg_dump and ogr2ogr documentation (linked) for details on the optional switches. Combining the two utilities should get you squared away.