ERROR: extension “postgis” does not support SET SCHEMA.
参考: http://postgis.net/2017/11/07/tip-move-postgis-schema/
Move PostGIS extension to a different schema
As of PostGIS 2.3, the postgis extension was changed to no longer allow relocation. All function calls within the extension are now schema qualified.
While this change fixed some issues with database restore, it created the issue of if you installed PostGIS in a schema other than the one you wanted to it is not intuitive how to move it to a different schema. Luckily there is a way to do this.
For this exercise, I will install PostGIS in the default schema and then demonstrate how to move it into another schema location.
You can run these steps using psql or pgAdmin or any other PostgreSQL tool you want.
Most people have their default schema set to public
so not explicitly specifying an install schema will generally install postgis
in the public
schema.
CREATE EXTENSION postgis;
Now I’ll create a new schema to move it and add this schema to search_path
CREATE SCHEMA postgis; ALTER DATABASE mydb SET `search_path` = public,postgis;
ALTER EXTENSION postgis SET SCHEMA postgis;
will fail with error ERROR: extension “postgis” does not support SET SCHEMA.
To allow the move do these steps:
UPDATE pg_extension SET extrelocatable = TRUE WHERE extname = 'postgis'; ALTER EXTENSION postgis SET SCHEMA postgis; ALTER EXTENSION postgis UPDATE TO "2.4.1next"; ALTER EXTENSION postgis UPDATE TO "2.4.1";
next
. The next
version step is needed in order to upgrade all the schema qualified function references to the new schema location. next
is designed to allow upgrading a postgis extension to a version it is already on. Trying to run UPDATE TO “2.4.1” when you are already on 2.4.1 would trigger an error that you are already on that version.
相关文章
- 'hibernate.dialect' must be set when no Connection avalable
- Open Flash Chart IO ERROR Loading test data Error #2032
- set hive.exec.parallel
- ZOJ Problem Set - 3708 Density of Power Network
- [React Testing] Hide console.error Logs when Testing Error Boundaries with jest.spyOn
- EasyPusher华为手机直播推流硬编码[OMX.IMG.TOPAZ.Encoder] failed to set input port definition parameters.
- mysql cascade|restrict|no action|set null__mysql 外键的几种约束
- idea报错解决:Error: A JNI error has occurred, please check your installation and try again
- SAP CRM orgman set的更改会触发Pricing set的创建
- 成功解决torchcuda__init__.py“, line 208, in check_error raise Cuda Error(res) torch.cuda.Cuda Error: C
- Android JNI语法之--C++--list、set、map用法
- 关于appium踩坑 :Encountered internal error running command: Error: Cannot verify the signature of (已解决)
- 嵌入式linux开发,交叉编译tslib-1.22时报错:configure:13646: error: possibly undefined macro: AC_MSG_ERROR
- MongoDB:搭建三节点 Replica Set 环境
- C++中set的使用
- Redis 与 set(无序集合) 相关的常用命令
- 如何解决npm run serve运行vue项目时,出现报错:Error: error:0308010C:digital envelope routines::unsupported
- 【异常】前端ERR! stack Error: Can‘t find Python executable “python“, you can set the PYTHON env variable.
- ./start-dfs.sh 启动错误 ERROR: Cannot set priority of datanode process xxxxx
- 【二】2D测量 Metrology——set_metrology_object_param()算子
- 华为交换机SSH报错Socket error Event: 32 Error: 10053. Connection closing...Socket close.