spatial_ref_sys table cannot be dumped using pg_dump when installed as an extension

[chander]

The current packaging of PostGIS's spatial_ref_sys table means that it's not dump-able using the pg_dump tool.

This means that a full backup of a database does not include dumps of the spatial_ref_sys table when made by standard postgresql backup tools.

Furthermore, even specifying the table name exactly doesn't cause it to get dumped.

Ref this email from Tom Lane re this issue:

​http://archives.postgresql.org/pgsql-admin/2012-05/msg00051.php

Seems like this is a high priority issue - anyone backing up a PostGIS 2.0 install using the standard PostgreSQL dump tools will not retain in their backups any local changes to the spatial_ref_sys table, which may render a restore unusable for certain types of queries (without adding these back in.)

[robe]

Strange I was sure I marked spatial_ref_sys as editable which means the structure doesn't get backed up but the data should. But I see what you mean individual backup of the table doesn't seem to do anything so maybe the include that does this is not being included.

SELECT pg_catalog.pg_extension_config_dump('spatial_ref_sys', '');

[robe]

Okay I just did a full backup of my database and it includes the data of spatial_ref_sys (but not the structure), as I would expect it to. So full backup seems fine.

However if I try to just backup spatial_ref_sys or even public schema, I get nothing.

I suspect this may be a bug in the PostgreSQL extension model unless I am missing something.

[robe]

chander,

I'm afraid this is going to end up being a WONTFIX aside from a documentation update.

See Tom's note:

​http://archives.postgresql.org/pgsql-bugs/2012-05/msg00116.php (BUG: #6640)

But anyrate it's not quite as serious as you think — backing up the database does backup the spatial_ref_sys table, but we'll need to make accommodations to only backup user input ones which is on our todo anyway.

[robe]

See related #1831 and #1834. there isn't much I can do about this and others have complained on the postgresql list as well about custom tables not being backed up.

Also refer to bug ticket: ​http://archives.postgresql.org/pgsql-bugs/2012-05/msg00117.php

and it has been discussed to add a dump extension option: ​http://archives.postgresql.org/pgsql-hackers/2012-01/msg01765.php but to my knowledge this has not happened yet.

I will however consider this a documentation bug and make sure to update the docs accordingly.

[robe]

put in a warning note about this behavior and that you need to backup the whole database for these customs to be backed up.

r9876 for PostGIS 2.0 and r9875 for PostGIS 2.1