3

I'm trying to transition from an old Postgres/Postgis database to a new one. I'm working in a fresh install of Win 7 64 bit. I installed Postgresql 9.3 32 bit, then installed PostGIS 2.1.3. I created a new blank database called medford, and ran

create extension postgis
create extension postgis_topology

without error. I also installed the legacy like this:

psql -d medford -f legacy.sql

Then, on my old system, running Postgres 9.2 and PostGIS 2.0.3, I ran this backup:

pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "c:\backup\pgdumpmedford.backup" medford

and then restored it into the new blank database using a batch file like this:

set PGPORT=5433
set PGHOST=localhost
set PGUSER=postgres
set PGPASSWORD=nottherealpasswrd
set THEDB=medford
set BACKUPFILE=c:\Data\medford.backup
set PGINSTALL=C:\Program Files (x86)\PostgreSQL\9.3
set PATH=%PATH%;"%PGINSTALL%\bin"
perl "%PGINSTALL%\utils\postgis_restore.pl" "%BACKUPFILE%" | psql %THEDB% 2> errors.txt

When I run the command

Select Postgis_full_version();

I get this which looks fine: POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, releaed 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER (1 row). However, when I open QGIS 2.2 and try to add a postgis layer from the new postgres 9.3 database, I can connect and add a layer of type "multipolygon", and the layer name appears in the table of contents layers list, but nothing shows up in the map view. I've read over this post, and that doesn't seem to help. I've checked my Postgres log and I see this there, but I don't know what it means:

2014-05-29 13:39:59 EDT ERROR:  relation "layer_styles" does not exist at character 22
2014-05-29 13:39:59 EDT STATEMENT:  SELECT styleQML FROM layer_styles WHERE f_table_catalog='medford' AND f_table_schema='medford' AND f_table_name='OpequonSubShed2' AND f_geometry_column='the_geom' ORDER BY CASE WHEN useAsDefault THEN 1 ELSE 2 END,update_time DESC LIMIT 1

If there's supposed to be some kind of layer_styles table in my database, I don't see it when using pgAdmin. I also don't see a layer_styles table in my old database. On my previous installation, I had no problems seeing the postgis layers in QGIS. Can someone give me an idea of why I can't see the Postgis layers in my new postgres/postgis and QGIS installation?

Community
  • 1
Jan
  • 535
  • 5
  • 18
  • It sounds like the data belonged to a previous version of PostGis and is incompatible with the new version. It's having a spit about a relation called layer_styles, not a table. – Michael Stimson May 29 '14 at 23:27
  • Sorry, right, it's a relation, not a table. I'm just not sure if this is a problem with QGIS or with PostGIS. I don't understand relations or layers_styles. A while back QGIS had a bug that seem similar (http://hub.qgis.org/issues/7818), but it appears to have been fixed by the version I'm using. When I restored the database, is it possible somehow it doesn't restore this layer style relation? Or is there some command to tell Postgis to use default layer styles? – Jan May 30 '14 at 14:33
  • Whenever I've done a pg_dump and pg_restore it's restored the relations stored in that database too. I've not used postgis_restore though. Is the PostGis version different? If so is there a possibility of using the same version? – Michael Stimson May 31 '14 at 02:04
  • Sounds to me like QGIS did not find the style information for the layer, have you tryed set the symbology manualy in qgis and maybe overwrite it from there an your postgres database. – Julian Jun 04 '14 at 09:59
  • @Julian I have not had a chance to try that. I was hoping to avoid it because I need a way to backup and restore everything without having to do any manual style changes in QGIS after a restore. That may end up being the only way, but I have to believe there's something else causing the problem. I'm working right now to go back to installing PostgreSQL 9.2 instead of 9.3, and trying to install PostGIS 2.0 instead of 2.1, and then doing a restore on that to see if that makes any difference. – Jan Jun 04 '14 at 13:07
  • I just wanted to know if something ist broken in the data or just within the style-informations. Therefore it would be interesting. – Julian Jun 04 '14 at 13:29
  • @MichaelMiles-Stimson I've see some references that pg_restore can take a parameter allowing you to use a specific version of pg_restore that is the same version that was used to backup the database. However, I can't figure out how to pass parameters into the postgis_restore.pl file to do that. There seems to be some thought on that here [link] http://trac.osgeo.org/postgis/ticket/2584 but I can't get it to work, and now that I'm downgrading my postgis that won't work. – Jan Jun 04 '14 at 14:30
  • Total frustration. I've now tried this: Old working Postgres 9.2 and PostGIS 2.0.3, created a pg_dump backup of it. Then on a new computer, Postgres 9.2 and PostGIS 2.0.4, I tried the above restore using the batch file, and the data seems to be restored, everything looks fine, except I can't see the layers in QGIS when I connect to the database. What could possibly be going wrong? Any ideas? – Jan Jun 05 '14 at 15:41

0 Answers0