Rename postGIS table and updating the GeoServer?

Rename postGIS table and updating the GeoServer?

I want to rename one of my postGIS tables. However, when I do this by applying a simple

ALTER TABLE old_name RENAME TO new_name;

The spatial data, as I understand it, isn't updated with the new name. First, can these references be fixed by running the function:

SELECT probe_geometry_columns()

Is the table itself OK after this? Or should other measures be taken also? Second, how do I update GeoServer so that it finds this new table? As of now, if I run the above commands, and edit the layer info on the GeoServer to point to the new name… I still get an error (GeoServer still tries to find it under the old name). Exactly, where should the layer name be changed on the GeoServer?

In order to rename the table you will have to change the name in the metadata of two places. First on PostGIS, then on the Geoserver config.

To change the PostGIS one, you can update the geometry_columns directly. Although probe_geometries will have the effect of inserting that entry into geometry columns, it will also have the effect of DESCRIBING the schema of all tables an inserting any missing geometry columns entries. If you have a lot of tables this can get expensive, so why not just update the geometry_columns table directly?

For updating geoserver you have various options. Certainly you can do it manually through the GUI, but I have a feeling you want to automate it. Writing a shell script that edits the config files in the geoserver installation folder and restarting geoserver, but that is also the long way of doing things. I would recommend you use the geoserver REST config API which allows you to manipulate the layer configs through RESTful calls.


In case the "update geometry columns" is not obvious do the following:

UPDATE geometry_columns set f_table_name = 'my_new_table_name' where f_table_name = 'my_old_table_name';

Update 8 years(!!!) later (2018):

Like some people mentioned below, after PostGIS 2.0, the metadata tables get updated automatically after the ALTER statement.

Note that in PostGIS 2.0+, all you have to do is the normal

ALTER TABLE old_name RENAME TO new_name;

The entry in the geometry_columns view (or geography_columns) will also be updated.

SELECT Probe_Geometry_Columns();

is a handy utility.

First, when we append a geometry column to an existing table with

SELECT AddGeometryColumn('my_table', 'geo_column', 1234, 'MULTIPOINT', 2);

we're feeding the function everything it needs to tack the column of type geometry (geo_column) to the specified table (my_table) and write the important details like SRID (1234), geometry type (MULTIPOINT) and number of dimensions (2) to the geometry_columns table. In essence, it is an ALTER and three UPDATES.

Creating geometry columns by other means (loaded from shapefile, selected in a CREATE TABLE AS, etc.) can lead to spatial tables that are invisible to external applications, although they work just fine within the database. Without the right details stored in geometry_columns, the actual geometry values appear as nonsensical character strings to applications looking for projected points, lines or polygons.

Calling the probe function checks each column of type geometry, adding new values to geometry_columns and reporting conflicts.

Circling back to your question, GeoServer doesn't believe the renamed table contains spatial data if the name change isn't reflected in geometry_columns. Something else to consider is that the probe function creates a duplicate record reflecting the new table name but does not get rid of the original record--another potential hang-up for GeoServer.

All that said, I would suggest you: 1) run the probe then immediately delete the old record; or 2) follow up your name change with an ALTER on geometry_columns to change the f_table_name value.

Sorry for the wordiness, but I hope it helps.

I'm not sure what SELECT probe_geometry_columns() does, but you can easily check the geometry_columns table to see if your new table name is in there or if it is still pointing to the old one.

I suspect that you need to reload the GeoServer store to make sure it "notices" the changes. Alternatively a stop and start will do it.

Watch the video: GeoServer Web GIS: Symbology in GeoServer with QGIS EN