navicat登录Postgres数据库

1、登录数据库

[root@localhost postgis-3.0.5]# su - postgres

Last login: Fri Mar 3 13:47:40 CST 2023 on pts/1
su: warning: cannot change directory to /home/postgres: No such file or directory

[postgres@localhost postgis-3.0.5]$ psql

Password for user postgres:         # 输入密码postgres
psql (12.5)
Type "help" for help.

postgres=#

　2、安装插件

创建控件数据库：
create database gisdb;

 　切换到gisdb，提示gisdb=# 表示切换到gisdb

\c gisdb
 

需要给gisdb安装扩展gis插件：
CREATE EXTENSION postgis;

gisdb=# CREATE EXTENSION postgis;
ERROR:  could not open extension control file "/export/servers/app/postgresql-12.5/share/extension/postgis.control": No such file or directory
翻译：ERROR:无法打开扩展控制文件"/export/servers/app/postgresql-12.5/share/extension/postgis.control":没有这样的文件或目录
处理方法：

[root@localhost auto_explain]# find / -name postgis  # 查找postgis有关文件路径
/usr/local/postgis-3.0.5/postgis
/usr/local/postgis-3.0.5/extensions/postgis

[root@localhost auto_explain]# pwd
/export/servers/app/postgresql-12.5/contrib/auto_explain   # 找到进入postgresql-12.5源码包解压后的，auto_explain目录，编译和安装

[root@localhost auto_explain]# make && make install

[root@localhost auto_explain]# make
make -C ../../src/backend generated-headers
make[1]: Entering directory '/export/servers/app/postgresql-12.5/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory '/export/servers/app/postgresql-12.5/src/backend/catalog'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/export/servers/app/postgresql-12.5/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory '/export/servers/app/postgresql-12.5/src/backend/utils'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/export/servers/app/postgresql-12.5/src/backend/utils'
make[1]: Leaving directory '/export/servers/app/postgresql-12.5/src/backend'
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2 -fPIC -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o auto_explain.o auto_explain.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2 -fPIC -shared -o auto_explain.so auto_explain.o  -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/export/servers/app/postgresql-12.5/lib',--enable-new-dtags  
[root@localhost auto_explain]# make install
make -C ../../src/backend generated-headers
make[1]: Entering directory '/export/servers/app/postgresql-12.5/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory '/export/servers/app/postgresql-12.5/src/backend/catalog'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/export/servers/app/postgresql-12.5/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory '/export/servers/app/postgresql-12.5/src/backend/utils'
make[2]: Nothing to be done for 'distprep'.
make[2]: Nothing to be done for 'generated-header-symlinks'.
make[2]: Leaving directory '/export/servers/app/postgresql-12.5/src/backend/utils'
make[1]: Leaving directory '/export/servers/app/postgresql-12.5/src/backend'
/usr/bin/mkdir -p '/export/servers/app/postgresql-12.5/lib'
/usr/bin/install -c -m 755  auto_explain.so '/export/servers/app/postgresql-12.5/lib/auto_explain.so'

　　安装结束，无报错。接下来添加shared_preload_libraries，重启数据库，创建auto_explain扩展测试。

报错：ERROR: could not load library "/export/servers/app/postgresql-12.5/lib/postgis-3.so": libgeos_c.so.1: cannot open shared object file: No such file or directory 

问题处理：原因是找不到链接库，新增动态链接库的配置文件 

vim /etc/ld.so.conf.d/postgres.conf
/usr/local/geos-3.7.4/lib
/usr/local/gdal-3.1.1/lib
/usr/local/proj-6.2.1/lib

#生效
[root@Rz01 ]# ldconfig

navcat连接postgres数据库：提示：2013 - Lost connection to MySQL server at 'reading initial communication packet , system error: 0

（2013年-在读取初始通信包时失去与MySQL服务器的连接，系统错误：0）

重启MySQL服务未解决

navicat 连接银河麒麟postgres数据库失败 2013 - Lost connection to MySQL server at 'reading initial communication pac