Convert regular Postgres Database into a spatial database

Question

I have looked everywhere but most tutorials are for creating a spatial database. Is it possible to convert a regular Postgresql db into a spatial one?

I will be using this for GeoDjango.

Answer 1

Does this help? Quoting from the Postgres manual:

Now load the PostGIS object and function definitions into your database by loading the postgis.sql definitions file (located in [prefix]/share/contrib as specified during the configuration step).

psql -d [yourdatabase] -f postgis.sql

For a complete set of EPSG coordinate system definition identifiers, you can also load the spatial_ref_sys.sql definitions file and populate the spatial_ref_sys table. This will permit you to perform ST_Transform() operations on geometries.

psql -d [yourdatabase] -f spatial_ref_sys.sql

If you wish to add comments to the PostGIS functions, the final step is to load the postgis_comments.sql into your spatial database. The comments can be viewed by simply typing \dd [function_name] from a psql terminal window.

psql -d [yourdatabase] -f postgis_comments.sql

Answer 2

(Low reputation -- this deserves to a be a comment instead of an answer)

For those arriving from Google, the top-rated answer applies to PostGres versions lower than 9.1. For 9.1+, all you need is this (from here):

The core postgis extension installs PostGIS geometry, geography, raster, spatial_ref_sys and all the functions and comments with a simple:

CREATE EXTENSION postgis; command.

psql -d [yourdatabase] -c "CREATE EXTENSION postgis;"

Topology is packaged as a separate extension and installable with command:

psql -d [yourdatabase] -c "CREATE EXTENSION postgis_topology;"

If you plan to restore an old backup from prior versions in this new db, run:

psql -d [yourdatabase] -f legacy.sql

You can later run uninstall_legacy.sql to get rid of the deprecated functions after you are done with restoring and cleanup.