issue when trying to create template database postgresql for postgis

Question

I'm using ubuntu 12.04, I have postgis2.1 installed and postgresql9.1... and I'm a newbie for all!... I follow this tuto to create a template database: http://linfiniti.com/2012/05/installing-postgis-2-0-on-ubuntu/ and I'm stuck and don't know what to do now...

When I try to execute the following command:

psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.0/postgis.sql

I have the following errors (I copy paste only the end since it is the same mistake for different line numbers):

psql:/usr/share/postgresql/9.1/contrib/postgis-2.1/postgis.sql:6050: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:/usr/share/postgresql/9.1/contrib/postgis-2.1/postgis.sql:6056: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

I've noticed that each error is linked to a command starting by "LANGUAGE...." in the script postgis.sql that I execute I have seen on the internet that this kind of error can be due to plpgsql which is not installed but it is not my case since when I type:

createlang -d template_postgis2 plpgsql

(as was given in the tuto) the machine returns

createlang: language "plpgsql" is already installed in database "template_postgis2"

Does anyone has an idea on what's going on?? and/or what I should do? maybe since I'm using postgis2.1 and not 2.0, as in the tutorial linfinity.com, it messes things up?

EDIT: the beginning of the error message is the following:

$ psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.1/postgis.sql
SET
BEGIN
psql:/usr/share/postgresql/9.1/contrib/postgis-2.1/postgis.sql:47: ERROR:  permission denied for language c
psql:/usr/share/postgresql/9.1/contrib/postgis-2.1/postgis.sql:52: ERROR:  current transaction is aborted, commands ignored until end of transaction block

The current transaction is aborted, commands ignored until end of transaction block error means that the transaction has failed, and it is ignoring subsequent statements. The useful message, at the start of the errors, has been omitted. Please edit the question to show it. — kgrittn
– kgrittn, Commented Sep 14, 2012 at 15:57
It would appear that this script must be run under a database superuser login, and you're not doing so. Also, @AlexanderLysenko makes a good point; under PostgreSQL version 9.1 and later,most add-ons should be installed with the CREATE EXTENSION statement. You might want to look for more up-to-date instructions for installing PostGIS. — kgrittn
– kgrittn, Commented Sep 18, 2012 at 2:15
Indeed it works if executing the script as a superuser: thanks! Now I've started installing things this way maybe I shouldn't change to the new way i.e. CREATE EXTENSION statement to not mess things up?? or is it OK? — Lane
– Lane, Commented Sep 18, 2012 at 16:58
If you found instructions which are working for you, I don't know enough about PostGIS to try to talk you out of it. Pretty much everything is moving to CREATE EXTENSION as the preferred technique, so you probably want to look at that when you can; but it's basically just the old technique with a little bookkeeping to support more graceful management, including upgrades. If you see that the directory for it has a .sql file with "unpackaged" as part of the filename, that would be a script to move you from the old technique to the new one using CREATE EXTENSION. — kgrittn
– kgrittn, Commented Sep 18, 2012 at 18:18

Oleksandr Lysenko · Accepted Answer · 2012-09-17 09:40:36Z

3

There is a new way to create spatial database in Postgres 9.1:

psql -d template_postgis2 -c "CREATE EXTENSION postgis;"

answered Sep 17, 2012 at 9:40

Oleksandr Lysenko

3041 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kgrittn Over a year ago

Does that need to be run using a database superuser login?

Lane Over a year ago

Thank you for the link + recommendation!

Oleksandr Lysenko Over a year ago

@kgrittn You don't have to use superuser to create database but your user should have privileges to do this. You could specify a user name: psql -U username ...

Valentin Kantor · Accepted Answer · 2013-08-09 13:54:51Z

0

just found on some forum:

sudo ldconfig

UPDATE: found this recommendation in the official documentation:

On Linux platforms, it may be necessary to run the ldconfig command after installing each library.

that fixed postres errors which may appear while creating database from postgis template

edited Aug 9, 2013 at 13:54

answered Aug 6, 2013 at 20:57

Valentin Kantor

1,8742 gold badges24 silver badges32 bronze badges

Collectives™ on Stack Overflow

issue when trying to create template database postgresql for postgis

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related