Back to Converter Index
CLI GuideChannel: postgis sql → geopackage

PostGIS SQL to GeoPackage Converter

Export PostGIS enterprise spatial tables to a single portable database.

Extract live server databases into modular, portable units. This tool allows you to convert PostGIS tables into single-file GeoPackage databases, making it easy to share data with offline clients.

Indirect Mapping Execution Required

Because database connection attributes or local attributes parsing is required, direct web streams are disabled. Use the GDAL CLI script below for precise terminal conversions.

Specification Rules

  • Create localized backups of server spatial databases
  • Distribute spatial datasets to offline users
  • Prepare spatial datasets for field surveys
  • For security reasons, direct remote database connections are restricted online
  • SQL triggers and advanced index structures do not export directly
  • Database schema adjustments must be handled during processing

GDAL Direct Equivalent Terminal Command

sh — gdalready
$ogr2ogr -f GPKG output.gpkg PG:"dbname=my_db user=admin" spatial_table

When to use this conversion

Use when you need a portable snapshot of one or more PostGIS layers for offline use, field collection, or distribution to colleagues without database access. GeoPackage is the right snapshot format because it preserves multiple layers, spatial indexes, and CRS metadata.

Technical Details

ogr2ogr connects to PostgreSQL via the PG driver, runs SELECT queries against PostGIS tables (or a custom -sql query), and writes the results to a SQLite GeoPackage with full geometry and attribute fidelity. R*Tree spatial indexes are built per layer. Credentials are passed via the connection string or environment variables.
Output file size

GeoPackage is typically 80–120% the size of the equivalent PostGIS table data (excluding indexes). The spatial index adds 10–30% on top.

Common Errors & Fixes

  • Credentials in URL leaked to logs: connection strings appear in shell history. Fix: use PGPASSWORD environment variable or a .pgpass file.
  • Large table export times out: default fetch size too small for million-row tables. Fix: -lco SPATIAL_INDEX=NO (build it after) and increase -lco BATCH_SIZE.
  • CRS mismatch: source table has SRID 4326 but you expected projected. Fix: confirm with SELECT Find_SRID() before exporting.
  • Spatial index missing in output: -lco SPATIAL_INDEX=NO was passed accidentally. Fix: re-enable, or run a post-VACUUM in QGIS.

Alternative Tools

  • pgsql2shp / shp2pgsql: legacy command-line tools (Shapefile only, no GPKG)
  • QGIS: connect to PostGIS, export layer as GeoPackage
  • pg_dump for SQL-level backups (not a GIS export — restore needs PostGIS)
  • GeoServer with the GeoPackage extension for served exports

Frequently Asked Questions

Can I run direct migrations without exporting files?

Yes. Our command list demonstrates how to connect to databases locally and pipe spatial tables directly into GeoPackage formats.

Are spatial indexes preserved?

Yes, GDAL automatically indexes the output tables, ensuring fast queries.

How are custom attribute columns analyzed?

Database types (text, integers) map directly to database fields within the SQLite database.

Related Transformations Map

Geopackage To PostgisPostgis To GeojsonCsv To Geopackage