Running PostgreSQL after installing pgRouting?

Question

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?

Answer 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

Answer 2

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.