3

I have PostgreSQL 9.3 installed on the server. It worked just fine. I also had a webservice which connected to a database. That also worked like a charm. Later, I decided to install and use pgRouting. I followed the steps provided here.

After, I copied and pasted the bin, lib and share folders as well as two text files for pgRouting in the following path: C:\Program Files\PostgreSQL\9.3

When I open up pgAdmin III and explore my db, I received the following two errors:

ERROR: column "spclocation" does not exist
LINE1: SELECT ts.oid, spcname, spclocation, spcoptions, pg_get_user...

and

ERROR: could not load library "C:/Program Files/Boundless/OpenGeo/bin/../pgsql/lib/postgis-2.1.dll": %1 is not a valid Win32 application

and when I open my tables, no data exist in it. I am new to PostgreSQL, please help me out! Have I lost all of my data? Unfortunately I do not have a backup! :(

**** update on the progress of work:

So, What I have done so far is that I copied the data directory of PostgreSQL 9.3, un-installed PostgreSQL and all of its dependencies, restarted the server (I am using windows server 2013, 64 bit), and installed PostgreSQL from scratch. It went well, but at last for installing PostGIS it gave following error again:

ERROR: could not load library "C:/Program Files/Boundless/OpenGeo/bin/../pgsql/lib/postgis-2.1.dll": %1 is not a valid Win32 application

So I decided to uninstall OpenGeo Suite as well (from Boundless) and after that tried installing PostGIS again, this time it gave me the following errors:

psql: could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

  • Could be some version mismatch. The tutorial refers to PostGIS 2.0 but the error message talks about v. 2.1. I would delete the files which you installed from the zip and find and copy the postgis-2.1.dll which seems to be missing. You may find it from OpenGeo Suite installation files or perhaps from binary installer zip archive enterprisedb.com/products-services-training/pgbindownload – user30184 Aug 19 '14 at 11:02
  • I tried doing this, but didn't help. Any other suggestions? – Catlover Aug 20 '14 at 8:26
1

Hey first you should try to dump your database using pg_dump (http://www.postgresql.org/docs/9.3/static/app-pgdump.html) before you do anything else. This at least should give you a copy of the current non working data, which you can revert back to. Also make a copy of your data dir (https://stackoverflow.com/questions/1137060/where-does-postgresql-store-the-database)

Also since you seem to use Windows to run the database I would suggest to use the installer from http://postgis.net/windows_downloads to install postgis and pgrouting. To my understanding pgrouting needs postgis to work (I might be wrong, I'm also new to this topic)

Also what bugs me is, that you are talking about PostgreSQL version 9.3, but your config dir missmatches the version and state 9.2. So you should first try to really run 9.3 or 9.2

Hope this helps you find the problem

  • Thanks, but I am having an error with backing up. the error is as below: pg_dump: [archiver (db)] query failed: ERROR: could not load library "C:/Program Files/Boundless/OpenGeo/bin/../pgsql/lib/postgis-2.1.dll": %1 is not a valid Win32 application. – Catlover Aug 19 '14 at 15:18
  • Find the data directory of PostgreSQL, which is by probably located at C:\Program Files\PostgreSQL\9.2\data. So copy the data directory to some place. Then revert the failed installation of postgis which u did using the tutorial, restart postgresql and try again with pg_dump – Benjamin Aug 19 '14 at 18:55
  • I copied the data directory, uninstalled postgreSQL 9.3 and all its dependencies, restarted the server and installed it again, everything went well except the last step for installing postgis 2, it gave an error below: ERROR: could not load library "C:/Program Files/Boundless/OpenGeo/bin/../pgsql/lib/postgis-2.2.dll": %1 is not a valid Win32 application. – Catlover Aug 21 '14 at 8:44
  • Okay lets take it one step at the time. First, tell us again what your Goal is. Second, tell us what software you installed. Third we'll find a solution. – Benjamin Aug 21 '14 at 13:25
0

I don't think OpenGeo ships with pgRouting so I'm guessing you copied the pgRouting files from http://postgis.net/windows_downloads? The question is which ones did you copy. As someone else noted you say you have 9.3 but yet you are copying into a 9.2 folder. Those definitely are incompatible. You need to use 9.3 binaries with 9.3 install and 9.2 with 9.2.

Also while OpenGeo only ships 32-bit the postgis.net downloads page has both 32-bit and 64-bit download files. So if you copied the 64-bit install into your PostgreSQL OpenGeo folder, you will get some eror.

Also the OpenGeo suite builds with a different version of GCC, so there might be incompatibilities with the libxml and CGAL that we ship.

Which files exactly did you copy and from which zip file? People have gotten the OpenGeo package to work with the ones we ship (e.g. the pgRouting), but it wasn't really designed to work together. To do so last person that did used the libgcc files shiped in the postgis.net corresponding postgis package instead of the OpenGeo ones, but their error was very different and only an issue when loading pgRouting.

  • thank you for your comment, however I am not sure if I understand all of it (I'm beginner). I copied the bin, lib and share folders. They were downloaded from here: winnie.postgis.net/download/windows/pg92/buildbot – Catlover Aug 20 '14 at 7:46
  • any other comments that might help? Thanx.... – Catlover Aug 20 '14 at 7:46
  • Catlover which one of those files did you use in that folder. Also I thought you said you are running 9.3 so why are you using 9.2 binaries? – LR1234567 Aug 21 '14 at 3:41
  • That was a typing mistake which I fixed in my question now, I am not using 9.2 binaries. And I copied all the files in that folder. Any suggestions? Thanks in advance – Catlover Aug 21 '14 at 8:34
  • I also tried copying the data directory, uninstalled postgreSQL 9.3 and all its dependencies, restarted the server and installed it again, everything went well except the last step for installing postgis 2, it gave an error below: ERROR: could not load library "C:/Program Files/Boundless/OpenGeo/bin/../pgsql/lib/postgis-2.2.dll": %1 is not a valid Win32 application. – Catlover Aug 21 '14 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.