18

I have Postgres version 8.4.8

select version();
PostgreSQL 8.4.8 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit

Installed Postgis via the synaptic package manager, (postgis and postgresql-8.4-postgis) everything seemed to go fine. Then when I try to verify the Postgis version, things are not fine. Both of these give the same error.

SELECT PostGIS_version();
SELECT PostGIS_full_version();

ERROR:  function postgis_full_version() does not exist
LINE 1: SELECT PostGIS_full_version();
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The package manager claims Postgis is installed. How can I verify that the install worked or didn't work ?

Orn Kristjansson
  • 3,435
  • 4
  • 26
  • 40
  • "If your question generally covers [software tools commonly used by programmers](http://stackoverflow.com/help/on-topic) then you’re in the right place"? – Brad Koch May 05 '15 at 18:21
  • @BradKoch This sounds more database administration related than a tool commonly used by programmers. – Uyghur Lives Matter May 05 '15 at 18:57
  • 1
    Speaking from experience, this is definitely a common problem more frequently encountered when using PostGIS in development. It could reasonably be on topic in at least 3 different SE sites. – Brad Koch May 05 '15 at 19:18

1 Answers1

34

PostGIS needs to be installed per database. Existing databases are not altered automatically. Run the install script as follows.

In PostgreSQL 8.4 you may also need to create the language plpgsql. For 9.0+ it is the default procedural language and installed automatically. In your database:

createlang plpgsql yourdatabase

Can't do harm. If plpgsql is already installed it will just yield an error telling you so. Go to the install dir. In Debian Squeeze the contrib packages lie here (may be different in Ubuntu). In a shell:

cd /usr/share/postgresql/8.4/contrib/postgis-1.5

Then execute (as postgres user or you have to provide username / pw):

psql -d yourdatabase -f postgis.sql
psql -d yourdatabase -f spatial_ref_sys.sql

You may also want to install the comments to your shiny new functions (optional). In Debian Squeeze the install file lies in the /contrib main directory:

cd /usr/share/postgresql/8.4/contrib
psql -d yourdatabase -f postgis_comments.sql

If you want PostGIS to be installed with every new database in the cluster by default, install it to your template1 database also. Read more about that in the manual.

Sources for PostGIS installation (on Ubuntu):

http://postgis.net/docs/manual-2.1/postgis_installation.html
http://www.paolocorti.net/2008/01/30/installing-postgis-on-ubuntu/

PostgreSQL 9.1+

With PostgreSQL 9.1 or newer you can use the more convenient CREATE EXTENSION:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

Your distribution is probably shipping the extension ready for installation. If not, consider the chapter "Building PostGIS Extensions and Deploying them" in the PostGIS manual.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I didn't even think about it being per database, that makes sense, thnx – Orn Kristjansson Dec 10 '11 at 23:58
  • 1
    I haven't been able to get the `PostGIS_full_version();` method to work either but so far postgis is working. I know there are some tutorials out there which say this method confirms it, but try creating some special columns allowed by postgis like a `geometry` column or something and that should work as a test. – boulder_ruby Aug 04 '13 at 20:51
  • 2
    Run "createlang plpgsql yourdatabase" from the shell, not as sql. – Victor Basso Oct 30 '14 at 14:20