If you want to do it programmatically with Python then probably GDAL/OGR is the best way to do it. Look at the sample example code that copies on table from PostgreSQL into SHP file. Example is not perfect but you can easily modify it to fit your needs.
import os
os.environ['PATH'] = "c:\\Program Files\\GDAL\\bin" + ';' + os.environ['PATH']
os.environ['GDAL_DRIVER_PATH'] = "c:\\Program Files\\GDAL\\bin\\gdal\\plugins-optional"
os.environ['GDAL_DATA'] = "c:\\Program Files\\GDAL\\bin\\gdal-data"
import ogr
conn=ogr.Open("PG: host=192.168.5.3 dbname=some_database user=postgres password=xxxx")
if conn is None:
print 'Could not open a database or GDAL is not correctly installed!'
sys.exit(1)
output = "d:\\points.shp"
# Schema definition of SHP file
out_driver = ogr.GetDriverByName( 'ESRI Shapefile' )
out_ds = out_driver.CreateDataSource(output)
out_srs = None
out_layer = out_ds.CreateLayer("point", out_srs, ogr.wkbPoint)
fd = ogr.FieldDefn('name',ogr.OFTString)
out_layer.CreateField(fd)
layer = conn.GetLayerByName("point_data")
#layer = conn.ExecuteSQL(sql)
feat = layer.GetNextFeature()
while feat is not None:
featDef = ogr.Feature(out_layer.GetLayerDefn())
featDef.SetGeometry(feat.GetGeometryRef())
featDef.SetField('name',feat.TITLE)
out_layer.CreateFeature(featDef)
feat.Destroy()
feat = layer.GetNextFeature()
conn.Destroy()
out_ds.Destroy()
If your programing environment is Windows, then you can download GDAL/OGR from here.
Some good starting materials you can find here. Hope it helps.