I'm constantly trying to avoid Shapefiles but seem to only fail because some software GPS or GIS only supports Shapefiles as the interchange format. Hence bye bye table names longer than 10 Characters (very annoying !) when going from a DB to Shapefile.
Here are some examples that I have recently used in doing some batch conversions here and there between PostGIS, Spatialite and Shapefiles.
My Setup: Windows 7 Pro, 32 Bit, ogr2ogr.exe version GDAL 1.9.2, released 2012/10/08 (Installed using OSGeo4W installer)
Example how to get version of ogr2ogr on my machine, at the command prompt. Note your path to ogr2ogr is most likely not where I have it.
> c:/OSGeo4W/bin/ogr2ogr.exe/ogr2ogr.exe --version
version GDAL 1.9.2, released 2012/10/08
So now the examples
Example 1. upload all Spatialite tables to an existing PostGIS DB and existing tables with same name as the Spatialite table names
c:/OSGeo4W/bin/ogr2ogr.exe/ogr2ogr.exe -append -lco GEOMETRY_NAME=geom -lco SCHEMA=geodata -f "PostgreSQL" PG:"host=localhost port=5432 user=username dbname=nameOfDatabase password=yourDbPassword" -a_srs "EPSG:31468" gisdata.sqlite
-append appends data to existing tables
-lco GEOMETRY_NAME=geom says the name of the geometry column should be "geom"
-lco SCHEMA=geodata says the Postgis schema name is "geodata"
-f "PostgreSQL" says destination is Postgresql Postgis format
PG:"...." says connection information
-a_srs "EPSG:31468" says the coordinate system is EPSG code 31468
gisdata.sqlite this is the source Spatialite file with the tables to import into Postgis
if you just want to import the Spatialite tables into an existing PostGIS database and have the tables automagically auto created for you just ommit the -append option above.
Example 2. How to export all Spatialite tables into individual Shapefiles in an empty folder that exists.
c:/OSGeo4W/bin/ogr2ogr.exe -f "ESRI Shapefile" shp geodata.sqlite -dsco SPATIALITE=yes
shp this is the name of the folder where the newly batch created shapefiles will go.
geodata.sqlite this is the source Spatialite file with the tables to export to shapefiles
-dsco SPATIALITE=yes says the input is in Spatialite format
in the end all the Spatialite tables will be exported to individual shapefiles (name = tableName) into the folder "shp" the folder name can of course be whatever, where ever you want it on your machine.
Example 3. Convert a Spatialite file and all its tables into a new Sqlite table where the geometry is stored as Well Known Text
c:/OSGeo4W/bin/ogr2ogr.exe -f SQlite output.sqlite input_spatialdb.sqlite -dsco SPATIALITE=no -lco FORMAT=WKT
-f SQlite says the desitination format is SQlite
output.sqlite is the name of the newly created Sqlite database (must not exists before running)
input_spatialdb.sqlite is the name of the Spatialite db with the source data
-dsco SPATIALITE=no say destination output Sqlite is not Spatialite
-lco FORMAT=WKT says output format of geometry columns data is Well Known Text format (note: this could also be WKB for Well Known Binary)
So there you have it some conversion, batch and not so batch for you moving data between Spatialite, PostGIS and Shapefiles.
hope it helps you out.