[postgis-users] Problems installing on Linux

[James David Smith]

Hi there,

I'm trying to make and compile PostGIS 2.0 with PostgreSQL 9.0. My
verrsion of PostgreSQL is:

PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit

A work colleague and I havee been following the steps here:

http://postgis.net/docs/postgis_installation.html

However when we try to do this command it fails:

psql -d james_test -f /usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql

The messages we get are all similar to this:

psql:/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql:4783: ERROR:
current transaction is aborted, commands ignored until end of
transaction block

Could the problem be that Postgis 1.5 is still installed/present? I
ask, as if I run the following command itseems to work fine:

psql -d james_test -f /usr/pgsql-9.0/share/contrib/postgis-1,5/postgis.sql

Any ideas? By the way, I appreciate that the ideal solution might be
to upgrade PostgreSQL to 9.1 but having got this far I'd like to try
and 'finish' this if possible.

Thanks

James
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[Nicolas Ribot]

Hi,

What is the *first* error message returned by the command:

psql -d james_test -f /usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql

Nicolas

[Mike Toews]

On 6 August 2013 21:50, Nicolas Ribot <nicola...@gmail.com> wrote:
> Hi,
>
> What is the *first* error message returned by the command:
> psql -d james_test -f /usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql
>
> Nicolas

A good way to do this is to stop psql on the first error (off by default), e.g.:

psql -v ON_ERROR_STOP=1 -d james_test -f
/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql

-Mike

[James David Smith]

Thanks both. The first error is:

psql:/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql:49: ERROR:
could not load library "/usr/pgsql-9.0/lib/postgis-2.0.so":
/usr/pgsql-9.0/lib/postgis-2.0.so: undefined symbol:
GEOSRelatePatternMatch

Thanks

James

[Mike Toews]

On 6 August 2013 22:02, James David Smith <james.da...@gmail.com> wrote:
> Thanks both. The first error is:
>
> psql:/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql:49: ERROR:
> could not load library "/usr/pgsql-9.0/lib/postgis-2.0.so":
> /usr/pgsql-9.0/lib/postgis-2.0.so: undefined symbol:
> GEOSRelatePatternMatch
>
> Thanks
>
> James

You will need a newer GEOS library. Check your current version with:

$ geos-config --version

See the handy PostGIS Support Matrix to help you out:
http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS

[James David Smith]

Hi Mike,

Thanks for the reply. I thought that I had the latest version of GEOS,
so I just did 'locate geos' and it found quite a few files. They seem
to be grouped into the below folders:

/depot/shared/geos-3.0.3/
/depot/shared/geos-3.3.5/
/opt/geos-3.3.8/include/geos/
/depot/shared/pgrouting/geos-3.0.0/
/depot/shared/pgrouting/geos-3.3.5/
/opt/Geos/

I guess that this is causing the problem.

Though when I do the "./configure" command for PostGIS I have been
pointing the it towards:

/opt/geos-3.3.8/bin/geos-config

Which should make it use the latest version of GEOS no?

Sorry for not being very technical, I'm slowly getting the hang of linux...

Thanks

James

[Mike Toews]

On 6 August 2013 22:49, James David Smith <james.da...@gmail.com> wrote:
>
> Hi Mike,
>
> Thanks for the reply. I thought that I had the latest version of GEOS,
> so I just did 'locate geos' and it found quite a few files. They seem
> to be grouped into the below folders:
>
> /depot/shared/geos-3.0.3/
> /depot/shared/geos-3.3.5/
> /opt/geos-3.3.8/include/geos/
> /depot/shared/pgrouting/geos-3.0.0/
> /depot/shared/pgrouting/geos-3.3.5/
> /opt/Geos/
>
> I guess that this is causing the problem.
>
> Though when I do the "./configure" command for PostGIS I have been
> pointing the it towards:
>
> /opt/geos-3.3.8/bin/geos-config
>
> Which should make it use the latest version of GEOS no?
>
> Sorry for not being very technical, I'm slowly getting the hang of linux...
>
> Thanks
>
> James

Technical is always good here. Sometimes it is necessary to update the
shared library cache by running "ldconfig" after various "make
install" commands. It might need to be run as root (or with sudo, if
you have that).

Also, sometimes, it could be also necessary to either add the LIBDIR
to /etc/ld.so.conf, or add a .conf file in /etc/ld.so.conf.d/, or to
fiddle around with the LD_LIBRARY_PATH environment variable to point
to the directory with the current versions of .so shared libraries.
After any changes with these, run "ldconfig" to update the system
cache. Yet another good tool is "ldd" to see which libraries are being
loaded. For example:

$ ldd /usr/pgsql-9.0/lib/postgis-2.0.so

should show you which other .so objects are being referenced.

[Rémi Cura]

Hello,
I had trouble with geos.
Strangely, I had to turn postgres down, then make clean in geos, then build and install, then clean postgis, then build and install, then restart postgres.
I had error if not stopping my (local) server, and if not cleaning geos and postgis before building

Cheers,

Rémi-C

2013/8/6 Mike Toews <mwt...@gmail.com>

[James David Smith]

Hi Mike/Remy/all,

Thanks for your help. I'm making progress. Please see below. My
install is now using geos-3.3.8. Or seems to be.

ldd /usr/pgsql-9.0/lib/postgis-2.0.so

linux-vdso.so.1 => (0x00007fff735fc000)
libgeos_c.so.1 => /opt/Geos/lib/libgeos_c.so.1 (0x00002b4411d4d000)
libproj.so.0 => /opt/Proj/lib/libproj.so.0 (0x00002b4411f74000)
libjson.so.0 => /opt/json/lib/libjson.so.0 (0x00002b44121ca000)
libxml2.so.2 => /opt/LibXml/lib/libxml2.so.2 (0x00002b44123cb000)
libz.so.1 => /usr/lib64/libz.so.1 (0x00002b441270e000)
libm.so.6 => /lib64/libm.so.6 (0x00002b4412922000)
libc.so.6 => /lib64/libc.so.6 (0x00002b4412ba5000)
libgeos-3.3.8.so => /opt/Geos/lib/libgeos-3.3.8.so
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00002b44132ab000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00002b44135ab000)
libjson-c.so.2 => /opt/json/lib/libjson-c.so.2 (0x00002b44137ba000)
libdl.so.2 => /lib64/libdl.so.2 (0x00002b44139c3000)
/lib64/ld-linux-x86-64.so.2 (0x0000003f53000000)

The following commands now seem to work:

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

And when I go into my database and do SELECT postgis_full_version I
get the following:

POSTGIS="2.0.4SVN r11660" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.6.1, 21
August 2008" LIBXML="2.7.6" LIBJSON="UNKNOWN" TOPOLOGY

However it says that Topology and Raster support are not present. So I
thought that I could now run the following commands to install those
too:

psql -d yourdatabase -f rtpostgis.sql
psql -d yourdatabase -f raster_comments.sql
psql -d yourdatabase -f topology/topology.sql
psql -d yourdatabase -f doc/topology_comments.sql

However when I run them I get an error. The first line is:

psql:/usr/pgsql-9.0/share/contrib/postgis-2.0/rtpostgis.sql:48: ERROR:
could not load library "/usr/pgsql-9.0/lib/rtpostgis-2.0.so":
libgeos-3.2.0.so: cannot open shared object file: No such file or
directory

Any idea why there is still mention of libgeos-3.2.0 ? I thought I'd
sorted that out now?

Thanks

James

[Wilkins, Brian]

I am coming in a little late, but when I did an upgrade, I had to run my dump through one of the postgis update scripts in the contrib directory. Did you do that?

Brian

[James David Smith]

Sorry Brian, but I'm not sure what that means. There are a few scripts in the postgis 2 contrib directory. Do I just run them like I've been running the above commands e.g. psql -d yourdatabase -f postgis.sql

[Wilkins, Brian]

Essentially, yes. See here: http://postgis.net/docs/postgis_installation.html#hard_upgrade

 

I am not completely clear on your upgrade path, but this is how I did a recent upgrade:

 

1.      Untarred postgis

2.      Ran configure with --with-geoconfig and --with-projdir set

3.      Moved the old postgis install to new name

4.      Make postgis

5.      Make install DESTDIR=/opt/postgis

6.      Changed ownership of my postgis build directory and share/pgsql directory to postgres:users

7.      Modified the postgis-version/utils/postgis_restore.pl to remove the portion where the database is created

8.      Backed up my existing database

a.      pg_dumpall -h localhost -p 5432 -U <username> -globals-only > globals.sql

b.      pg_dump -d -Fc <database name> > /tmp/<dumpname>.dmp

c.      pg_dump <database name> -t <special table you want to dump – this is optional> > /tmp/<table>.sql (optional – used for testing upon restore)

9.      Login as postgres user

10.   $ psql

11.   postgres=# drop database <database name>;

12.   DROP DATABASE

13.   postgres=# \q

14.   $ psql -U postgres –d postgres –f globals.sql (may not be needed but I did it anyways because I upgraded the DB too)

15.   $ createdb -U <username>

16.   $ createdb -U <username> database

17.   $ createlang -U postgres plpgsql -d database

18.   $ psql -U postgres -f postgis.sql -d database

19.   $ psql -U postgres -f spatial_ref_sys.sql -d database

20.   $ sh share/pgsql/contrib/utils/postgis_restore.pl <upgrade sql script under contrib> <database name> <dump file name> &> restore.log

21.   Finally after examing for any errors:

22.   psql -c ‘select postgis_full_version();’ -d <database name>

23.   And finally, run some tests. For instance, I had a non-postgis table that I dumped again and diff’d with my original dump. Or you can execute an ST_Extent on a geolocation.. up to you

[Wilkins, Brian]

Also, I had to modify the postgis.sql to point to the proper location of my postgis install, so I executed:

 

$ sed –i “s/\$libdir/\/opt\/postgis\/lib64\/pgsql/g” postgis.sql

 

It didn’t know what $libdir was so I had to tell it.

 

 

From: Wilkins, Brian

Sent: Tuesday, August 06, 2013 2:05 PM
To: 'PostGIS Users Discussion'

[BladeOfLight16]

On Tue, Aug 6, 2013 at 12:53 PM, James David Smith <james.da...@gmail.com> wrote:

However it says that Topology and Raster support are not present. So I
thought that I could now run the following commands to install those
too:

<snip>

psql:/usr/pgsql-9.0/share/contrib/postgis-2.0/rtpostgis.sql:48: ERROR:
 could not load library "/usr/pgsql-9.0/lib/rtpostgis-2.0.so":
libgeos-3.2.0.so: cannot open shared object file: No such file or
directory

Any idea why there is still mention of libgeos-3.2.0  ?  I thought I'd
sorted that out now?

Could rtpostgis-2.0.so be a pg-routing binary? I noticed two of your copies of GEOS are in subdirectories of /depot/shared/pgrouting/. Maybe the routing binary is still linked to the old GEOS.

As for raster, did you install GDAL? I'm pretty sure that GDAL is required for raster support.

Honestly, you have a lot of different pieces and versions installed, and since you installed a bunch of them manually, cleaning them out does not seem practical. Have you considered just installing on a clean machine that's never had PostgreSQL? Or maybe even backing up your data and then wiping this machine (assuming there even is data to preserve)? Even if it costs a little bit to get a clean machine, I bet it's less than the cost of your time (assuming you're on the job).

[BladeOfLight16]

On Tue, Aug 6, 2013 at 6:32 PM, BladeOfLight16 <bladeof...@gmail.com> wrote:

Could rtpostgis-2.0.so be a pg-routing binary? I noticed two of your copies of GEOS are in subdirectories of /depot/shared/pgrouting/. Maybe the routing binary is still linked to the old GEOS.

Forgive my ignorance. It's raster. Would the raster binaries have even built without GDAL? If not, this could explain why this binary is still linked to old GEOS; it could have been built against old GEOS and not replaced.

[James David Smith]

> _______________________________________________

Hi all,

Just reporting back. Have made progress again. We had to clean and
then re-make and install GDAL. If I'm honest I'm not quite sure why,
but once we had done this everything seemed to work ok. :-)

[James David Smith]

Hi there,

Not quite done I'm afraid. Sorry and thanks for your continued help.
I've now got a database in my cluster called 'james_traffic_restored'
which is a postgis2 database with topology and raster support enabled.
Great.

Before I started all this I dumped my old database using pg_dump.

pg_dump mydb > db.sql

I saved it as a SQL file and also as a dump file.

/home/james/james_traffic_08082013.dump
/home/james/james_traffic_08082013.sql

I now want to put this data back into my newly database called
'james_traffic_restored'. So I do this:

pg_restore -d james_traffic_restored james_traffic_08082013.dump

However I get lots of errors. I've not bothered providing them, as I
think that this is wrong anyway. I think I need to do something like
this instead:

sh /usr/pgsql-9.0/share/contrib/postgis-2.0/postgis_restore.pl
/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql
james_traffic_restored james_traffic_08082013.dump >
restore.log

But I get the following errors:

/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis_restore.pl: line 31:
use: command not found
/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis_restore.pl: line 32:
use: command not found
/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis_restore.pl: line 34:
my: command not found
/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis_restore.pl: line 36:
my: command not found
/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis_restore.pl: line 37:
syntax error near unexpected token `newline'
/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis_restore.pl: line 37:
`Usage: $me [-v] <dumpfile>'

I've had a look at the postgis_restore file around these lines, but
I'm not sure what the problem is.

[Wilkins, Brian]

Do you have perl installed? It seems like it is unable to execute the perl script.

-----Original Message-----
From: postgis-us...@lists.osgeo.org [mailto:postgis-us...@lists.osgeo.org] On Behalf Of James David Smith
Sent: Thursday, August 08, 2013 10:53 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Problems installing on Linux

[James David Smith]

Hi Brian,

Yes, I think I have Perl installed. If I do:

instmodsh
l

It tells me...: Installed modules are: Perl

[James David Smith]

Also if I run " perl -v " then I get:

" This is perl, v5.8.8 built for x86_64-linux-thread-multi "

[Wilkins, Brian]

See here: http://stackoverflow.com/questions/2517106/why-do-i-get-use-command-not-found-when-i-run-my-perl-script

Could be your shell.

-----Original Message-----
From: postgis-us...@lists.osgeo.org [mailto:postgis-us...@lists.osgeo.org] On Behalf Of James David Smith
Sent: Thursday, August 08, 2013 11:04 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Problems installing on Linux

On 8 August 2013 16:02, James David Smith <james.da...@gmail.com> wrote:
> On 8 August 2013 15:57, Wilkins, Brian <bwil...@harris.com> wrote:

[snip]

> James

Also if I run " perl -v " then I get:

" This is perl, v5.8.8 built for x86_64-linux-thread-multi "
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[James David Smith]

Great stuff Brian. I've no idea how you knew that. But putting 'perl'
at the start seems to get it started. Though another problem
unfortunately!

------------WHEN I RUN THE SCRIPT--------
perl /usr/pgsql-9.0/share/contrib/postgis-2.0/postgis_restore.pl
/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql
james_traffic_restored james_traffic_05082013.sql > restore.log

---------THIS IS WHAT I GET ----------------
Converting /usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql to
ASCII on stdout...
Reading list of functions to ignore...
Writing manifest of things to read from dump file...
: Cannot open manifest file
'/usr/pgsql-9.0/share/contrib/postgis-2.0/postgis.sql.lst'

[Wilkins, Brian]

postgis.sql.lst is the manifest file used to read in the sql functions, tables, etc. It is the original SQL plus .lst concatenated onto the filename.

Make sure /usr/pgsql-9.0/share/contrib/postgis-2.0/ has permissions set to 755. For example, chmod 755 /usr/pgsql-9.0/share/contrib/postgis-2.0/

Or you can copy the script to like /tmp along with your sql and make sure your user can write to /tmp.

[James David Smith]

I've made a tmp folder in my home directory.

/home/james/temp

It now has the following in it:

postgis_upgrade_20_minor.sql
topology.sql
legacy_gist.sql
raster_comments.sql
topology_upgrade_20_minor.sql
legacy_minimal.sql
restore.log
uninstall_legacy.sql
legacy.sql
rtpostgis_legacy.sql
uninstall_postgis.sql
postgis_comments.sql
rtpostgis.sql
uninstall_rtpostgis.sql
postgis_restore.pl
rtpostgis_upgrade_20_minor.sql
uninstall_topology.sql
postgis.sql
spatial_ref_sys.sql
postgis.sql.lst
topology_comments.sql

I run the following:

perl temp/postgis-2.0/postgis_restore.pl temp/postgis-2.0/postgis.sql
james_traffic_restored traffic_backup_08082013.sql > restore.log

But still get error:

Converting postgis.sql to ASCII on stdout...

Reading list of functions to ignore...
Writing manifest of things to read from dump file...

pg_restore: [archiver] input file does not appear to be a valid archive
postgis_restore.pl: pg_restore returned an error

It sounds like it doesn't like the file type. So I did a new dump like so:

pg_dump --verbose -F t --file traffic_backup_08082013_v3.sql
james_traffic_backup

I thought that this would make a TAR file which is what it seems to
want. So I now do pg_restore again:

perl temp/postgis-2.0/postgis_restore.pl temp/postgis-2.0/postgis.sql
james_traffic_restored traffic_backup_08082013_v3.sql > restore.log

However I still get the same error:

Converting postgis.sql to ASCII on stdout...

Reading list of functions to ignore...
Writing manifest of things to read from dump file...

pg_restore: [archiver] input file does not appear to be a valid archive
postgis_restore.pl: pg_restore returned an error

Aaaargh. What an earth file format does it want it to be? Or maybe
this error is misleading?

Thanks

James

[Wilkins, Brian]

I don't know how you created your dump, but this is how I created my dump about a month ago and got it to work:

First,

$ pg_dumpall -h localhost -p 5432 -U postgres -globals-only > globals.sql

$ pg_dump -d -Fc <database-name> > /tmp/<dump name>.dmp

I always dump the globals just in case...

Brian

[James David Smith]

Did you mean to put '-d' in that dump command Brian? It says invalid
and isn't on the command list? I removed it and did:

pg_dumpall -U james --globals-only > globals.sql

pg_dump -Fc james_traffic_backup > temp/postgis-2.0/traffic_dump.dmp

It creates the files ok.

Then:

Converting temp/postgis-2.0/postgis.sql to ASCII on stdout...

Reading list of functions to ignore...
Writing manifest of things to read from dump file...
pg_restore: [archiver] input file does not appear to be a valid archive

temp/postgis-2.0/postgis_restore.pl: pg_restore returned an error

Weird. I've been googling this too obviously and a few people have had
the same issue. They tend to resolve it by dumping as a tar or
similar, but I think I'm doing that. I've certainly tried too.

Can't thank you enough for your help.

[Wilkins, Brian]

Hmm, sorry you are having such a hard time. You could try pg_dump -Ft which will do it in a tar format, but -Fc is the most flexible format and I wrote detailed notes on the upgrade that I tested on two other systems here, so the procedures are sound. I am not sure why you are having issues there... I don't know why I said to do -d because you are right, it's not a valid option.

[James David Smith]

Just thought I'd respond to say I've finally got this working. Thank
you to all of your for your help. It turned out that running the below
command before trying to use pg_restore sorted my problem:

psql -d [yourdatabase] -f legacy.sql

So in summary I have just upgraded PostgresSQL 9.0 with PostGIS 1.5
----> PostgreSQL 9.0 with PostGIS 2.0. I followed the instructions
here:

http://postgis.net/docs/manual-dev/postgis_installation.html#hard_upgrade

Along with these:

http://postgis.net/docs/manual-dev/postgis_installation.html#create_new_db

Thanks again,

James

[Wilkins, Brian]

Glad to hear you got it working! What is legacy.sql? Did my other steps help?

[James David Smith]

To be honest Brian I'm not quite sure what legacy.sql did. It is
listed here though, and it's not something I'd noticed before so
thought it was worth a go!

http://postgis.net/docs/manual-dev/postgis_installation.html#create_new_db

I guess it deals with compatibility issues somehow.

James

[Wilkins, Brian]

Perfect, I will make a note of this myself.

[BladeOfLight16]

On Mon, Aug 12, 2013 at 7:54 AM, James David Smith <james.da...@gmail.com> wrote:

To be honest Brian I'm not quite sure what legacy.sql did. It is
listed here though, and it's not something I'd noticed before so
thought it was worth a go!

http://postgis.net/docs/manual-dev/postgis_installation.html#create_new_db

legacy.sql adds back old, deprecated functions that were removed. See http://postgis.refractions.net/docs/PostGIS_FAQ.html#legacy_faq. For example, I had to get GeomFromWKB and SetSRID, which have now become ST_GeomFromWKB and ST_SetSRID, for a third party library.