PostgreSQL db

PostgreSQL|PostgreSQL

Create a template_gis
PostgreSQL 9.1 + PostGIS 1.5

PostgreSQL 9.4 + PostGIS 2.1

Create a template_gis_it
PostgreSQL 9.1 + PostGIS 1.5

PostgreSQL 9.4 + PostGIS 2.1

Create a spatial database
PostgreSQL 9.1 + PostGIS 1.5

PostgreSQL 9.4 + PostGIS 2.1

Dump and restore a databse

 * 
 * 

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql. Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications, even on other SQL database products.

The alternative archive file formats must be used with pg_restore to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.

To dump a database called mydb into a SQL-script file:

To dump a single table named mytab:

To dump a database into a custom-format archive file:

pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats. It will issue the commands necessary to reconstruct the database to the state it was in at the time it was saved.

If a database name is specified, pg_restore connects to that database and restores archive contents directly into the database. Otherwise, a script containing the SQL commands necessary to rebuild the database is created and written to a file or standard output.

To restore a database from a sql dump script into a (freshly created) database named newdb:

Upgrade a databse

 * PostGIS 2.0 upgrade

The dump/reload process is assisted by the postgis_restore.pl script which takes care of skipping from the dump all definitions which belong to PostGIS (including old ones), allowing you to restore your schemas and data into a database with PostGIS installed without getting duplicate symbol errors or bringing forward deprecated objects.