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?

  • 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 (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 2: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 4 '14 at 9: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 4 '14 at 13:07

Your Answer

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

Browse other questions tagged or ask your own question.