Rules on geometry_columns make noisy backup/restore

[pramsey]

On an ordinary database, using CREATE EXTENSION to enable PostGIS, and dumping using pg_dump as follows

pg_dump --file=nyc.backup --format=c --port=54321 --username=postgres nyc
createdb -p 54321 nyc2
pg_restore --dbname=nyc2 --port 54321 --username=postgres nyc.backup

The restore generates the following errors

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3249; 2618 19424 RULE geometry_columns_delete postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  rule "geometry_columns_delete" for relation "geometry_columns" already exists
    Command was: CREATE RULE geometry_columns_delete AS ON DELETE TO geometry_columns DO INSTEAD NOTHING;

pg_restore: [archiver (db)] Error from TOC entry 3247; 2618 19422 RULE geometry_columns_insert postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  rule "geometry_columns_insert" for relation "geometry_columns" already exists
    Command was: CREATE RULE geometry_columns_insert AS ON INSERT TO geometry_columns DO INSTEAD NOTHING;

pg_restore: [archiver (db)] Error from TOC entry 3248; 2618 19423 RULE geometry_columns_update postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  rule "geometry_columns_update" for relation "geometry_columns" already exists
    Command was: CREATE RULE geometry_columns_update AS ON UPDATE TO geometry_columns DO INSTEAD NOTHING;

This is unfortunate, since our dump/restore story is now supposed to be pristine in the new world of extensions.

[robe]

pramsey,

I thought thsi was something they were going to fix upstream. No? or you just logging as an issue?

[rburhum]

Running into this. The restore *seems* to have worked, but the error code of pg_restore is not 0. Can this error be safely ignored?

[robe]

Yes it can be ignored. As I've been snooping on hackers list I recall seeing they'll have a fix for it in 9.3 and probably backport to 9.2 and 9.1.

We were the only extension who had the nerve to use RULES on our views (possibly to use views too) forcing them to make changes to extension model :)

Details here: ​http://postgresql.1045698.n5.nabble.com/pg-dump-with-postgis-extension-dumps-rules-separately-td5751087.html

[rburhum]

Excellent. I'll just parse the output in my application and ignore the error if I see it. Thank you Regina!

[rburhum]

Actually Regina, I tracked down a bug with this process. It seems that this error is not completely harmless. It does not restore the permissions properly for the postgis tables

My tables look like this before the pg_dump:

                  List of relations
 Schema |           Name            | Type  | Owner
--------+---------------------------+-------+--------
 public | fcf_agricultural_worker_8 | table | user_1
 public | fcf_jail_prison_19        | table | user_1
 public | fcf_jail_prison_6         | table | user_1
 public | has_police_station_7      | table | user_1
 public | planning_neighborhoods_2  | table | user_1
 public | planning_neighborhoods_20 | table | user_1
 public | route_points_30           | table | user_1
 public | routes_28                 | table | user_1
 public | spatial_ref_sys           | table | user_1
 public | track_points_31           | table | user_1
 public | tracks_29                 | table | user_1
 public | waypoints_27              | table | user_1
 public | waypoints_3               | table | user_1
 public | wtc_pump_station_10       | table | user_1
 public | wtc_pump_station_11       | table | user_1
 public | wtc_pump_station_12       | table | user_1
 public | wtc_pump_station_13       | table | user_1
 public | wtc_pump_station_14       | table | user_1
 public | wtc_pump_station_15       | table | user_1
 public | wtc_pump_station_16       | table | user_1
 public | wtc_pump_station_17       | table | user_1
 public | wtc_pump_station_18       | table | user_1
 public | wtc_pump_station_9        | table | user_1

after a pg_dump and a pg_restore, I get the following error/warnings:

[localhost] local: pg_restore -U postgres  -d project_5 /tmp/daily/pg_dumps/project_5.dmp
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3109; 2618 18169 RULE geometry_columns_delete user_1
pg_restore: [archiver (db)] could not execute query: ERROR:  rule "geometry_columns_delete" for relation "geometry_columns" already exists
    Command was: CREATE RULE geometry_columns_delete AS ON DELETE TO geometry_columns DO INSTEAD NOTHING;



pg_restore: [archiver (db)] Error from TOC entry 3107; 2618 18167 RULE geometry_columns_insert user_1
pg_restore: [archiver (db)] could not execute query: ERROR:  rule "geometry_columns_insert" for relation "geometry_columns" already exists
    Command was: CREATE RULE geometry_columns_insert AS ON INSERT TO geometry_columns DO INSTEAD NOTHING;



pg_restore: [archiver (db)] Error from TOC entry 3108; 2618 18168 RULE geometry_columns_update user_1
pg_restore: [archiver (db)] could not execute query: ERROR:  rule "geometry_columns_update" for relation "geometry_columns" already exists
    Command was: CREATE RULE geometry_columns_update AS ON UPDATE TO geometry_columns DO INSTEAD NOTHING;



WARNING: errors ignored on restore: 3

Warning: local() encountered an error (return code 1) while executing 'pg_restore -U postgres  -d project_5 /tmp/daily/pg_dumps/project_5.dmp'

Although the database data seems to have been restored properly, the permissions for the geometry_column table was not

project_5=> \dt
                   List of relations
 Schema |           Name            | Type  |  Owner
--------+---------------------------+-------+----------
 public | fcf_agricultural_worker_8 | table | user_1
 public | fcf_jail_prison_19        | table | user_1
 public | fcf_jail_prison_6         | table | user_1
 public | has_police_station_7      | table | user_1
 public | planning_neighborhoods_2  | table | user_1
 public | planning_neighborhoods_20 | table | user_1
 public | route_points_30           | table | user_1
 public | routes_28                 | table | user_1
 public | spatial_ref_sys           | table | postgres
 public | track_points_31           | table | user_1
 public | tracks_29                 | table | user_1
 public | waypoints_27              | table | user_1
 public | waypoints_3               | table | user_1
 public | wtc_pump_station_10       | table | user_1
 public | wtc_pump_station_11       | table | user_1
 public | wtc_pump_station_12       | table | user_1
 public | wtc_pump_station_13       | table | user_1
 public | wtc_pump_station_14       | table | user_1
 public | wtc_pump_station_15       | table | user_1
 public | wtc_pump_station_16       | table | user_1
 public | wtc_pump_station_17       | table | user_1
 public | wtc_pump_station_18       | table | user_1
 public | wtc_pump_station_9        | table | user_1
(23 rows)

project_5=>

Trying to query geometry_columns fails, which make all the client code that relies on that (i.e everything) fail.

project_5=> SELECT f_geometry_column, srid FROM geometry_columns WHERE f_table_name='FCF_Agricultural_Worker_8';
ERROR:  permission denied for relation geometry_columns

I am going to just manually write something to fix it after the load, but I figured you may want to be aware of this.

[rburhum]

Actually, even some objects like geography_columns were not even created.

[rburhum]

If it helps, I used the custom postgres format for the dump (notice the -FC )

{pg_dump} {pg_conn_string} -Fc -f {output_file_name}

[robe]

Hmm I'll have to check that out. You sure geography_columns was not recreated. That is a view too.

Regarding the permissions issue which version of PostGIS are you running? I think in 2.0.3 (sorry my memory is going, might be 2.0.4SVN not released), I added to the script logic to explicit set views permissions to allow public read.

I suspect that since the views etc are not part of backup, that they are recreated by CREATE EXTENSION that is why no permissions are being backed up, but with the newest version of 2.0 and also upcoming 2.1, the permissions should be fine. So what I am saying is the permission issue may already be fixed.

[rburhum]

There are actually two cases:

1.- pg_dump -FC && pg_restore 2.- pg_dump (default) && psql -f

Versions of source and destination server:

In the server (where the pg_dump is generated):

project_5=# select postgis_version();
            postgis_version
---------------------------------------
 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

/usr/lib/postgresql/9.1/bin/psql --version
psql (PostgreSQL) 9.1.9
contains support for command-line editing

In the local machine (where the pg_restore is called)

project_5=# select postgis_version();
            postgis_version
---------------------------------------
 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)


psql --version
psql (PostgreSQL) 9.2.4

So it looks like they are the same postgis, but different postgresql.

For the second case With pg_dump -FC (the custom postgres format) I don't even get the full postgis tables/views back.

I just switched to using the (default) text-based pg_dump and and pipe to psql for the restore (the 1st case). In that scenario, I get the tables/views back but the permissions are not maintained.

If understood you right, it seems that you may have the 2nd case fixed, but perhaps the first one is not?

[robe]

Please use

SELECT postgis_full_version();

The issue I mentioned is fixed in a micro so not enough info above to go by

[robe]

Requires fix in PostgreSQL which I don't think has been done yet.

[robe]

Created a new PostGIS PostgreSQL section for issues like this since this is an issue that needs fixing upstream. Not sure its fixed yet. Will have to retest.

[strk]

Is there anything we can still do with this ticket ? Seems to be too old to care about ?