PostgreSQL 客户端安装配置与连接演示

科技 2024-11-14 07:50 北京

作者 | JiekeXu

来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）

如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)

大家好，我是 JiekeXu,江湖人称“强哥”，荣获 Oracle ACE Pro 称号，墨天轮 MVP，墨天轮年度“墨力之星”，拥有 Oracle OCP/OCM 认证,MySQL 5.7/8.0 OCP 认证以及 PCA、PCTA、OBCA、OGCA、KCP 等众多国产数据库认证证书,今天和大家一起来看看PostgreSQL 客户端安装配置与连接演示,欢迎点击最上方蓝字“JiekeXu DBA之路”关注我的微信公众号，然后点击右上方三个点“设为星标”置顶，更多干货文章才能第一时间推送，谢谢！

前言

上周五快要下班的时候，接到一个需求帮助，问怎么在内网环境的 Linux 上离线安装 PostgreSQL 客户端访问另一套 PG 主备库。原客户的问题比较模糊，转述给我的也就是这样的需求，不知道是需要类似 psql 还是类似 pgadmin 可图形化的工具，这里惯性思维认为 Linux 下就应该是非图形化命令行界面，那么也就按照这个思路往下走了。

正文

PostgreSQL 是一个发展了 20 多年的强大的开源关系型数据库，它支持多种客户端工具，这些工具可以帮助我们更方便地连接、管理和操作 PostgreSQL 数据库。以下是常用的 PostgreSQL 客户端工具：

PG 常用客户端工具

psql 命令行工具

我们都知道当安装完 PostgreSQL 之后，我们便可以通过 psql 客户端工具登录到数据库实例，比如增删改查，创建数据库等一些数据库管理操作。但是如果需要远程通过 A 主机访问 B 主机上的数据库实例，那么 A 主机则必须要有客户端才可访问，所以则需要安装上面提到的任一客户端工具。

由于前面的需求是 Linux 主机，无图形界面的话，首先想到的则是 psql 客户端工具，但由于 PG 官方目前好像没有提供单独的 psql 安装包，我们则可以选择编译安装二进制服务包来达到目的，说干就干。

下载软件包

https://www.postgresql.org/ftp/source/
--我们选择 2023 年发布的 pg14.9，注意不用太新，否则 OS 不支持编译安装报错
postgresql-14.9.tar.gz

创建 postgre 用户

groupadd postgres
useradd -g postgres postgres
echo "postgres" |passwd --stdin postgres
密码:postgres

yum 源安装 rpm 包

--测试 yum 源
yum repolist

yum -y install coreutils glib2 lrzsz mpstat dstatsysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-develpam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc makesmartmontools flex bison perl-devel perl-Ext Utils* openldap-devel jadetex openjade bzip2

解压软件包

su - postgres
mkdir pgdata
tar xvf /var/lib/pgsql/postgresql-14.9.tar.bz2  -C /var/lib/pgsql

编译

cd postgresql-14.9/
./configure --prefix=/var/lib/pgsql/pgdata  --with-perl  --with-python

--看到如下代码则 configure 完成
checking Python.h usability... yes
checking Python.h presence... yes
checking for Python.h... yes
checking for xmllint... /bin/xmllint
checking for xsltproc... /bin/xsltproc
checking for fop... no
checking for dbtoepub... no
checking whether gcc -std=gnu99 supports -Wl,--as-needed... yes
configure: using compiler=gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44)
configure: using CFLAGS=-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
configure: using CPPFLAGS= -D_GNU_SOURCE 
configure: using LDFLAGS=  -Wl,--as-needed
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/include/pg_config_ext.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port

接着执行 gmake world 完成编译

-bash-4.2$ gmake world
gmake -C ./src/backend generated-headers
gmake[1]: Entering directory `/var/lib/pgsql/postgresql-14.9/src/backend'
gmake -C catalog distprep generated-header-symlinks
gmake[2]: Entering directory `/var/lib/pgsql/postgresql-14.9/src/backend/catalog'
gmake[2]: Nothing to be done for `distprep'.
prereqdir=`cd './' >/dev/null && pwd` && \

--看到有如下代码则表示编译完成
gmake[2]: Leaving directory `/var/lib/pgsql/postgresql-14.9/contrib/jsonb_plperl'
gmake -C hstore_plpython all
gmake[2]: Entering directory `/var/lib/pgsql/postgresql-14.9/contrib/hstore_plpython'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I/usr/include/python2.7 -DPLPYTHON_LIBNAME='"plpython2"' -I../../src/pl/plpython -I../../contrib -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o hstore_plpython.o hstore_plpython.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o hstore_plpython2.so  hstore_plpython.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/usr/lib64',--enable-new-dtags   -L/usr/lib64 -lpython2.7 -lpthread -ldl  -lutil -lm
gmake[2]: Leaving directory `/var/lib/pgsql/postgresql-14.9/contrib/hstore_plpython'
gmake -C jsonb_plpython all
gmake[2]: Entering directory `/var/lib/pgsql/postgresql-14.9/contrib/jsonb_plpython'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I../../src/pl/plpython -I/usr/include/python2.7 -DPLPYTHON_LIBNAME='"plpython2"' -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o jsonb_plpython.o jsonb_plpython.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o jsonb_plpython2.so  jsonb_plpython.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/usr/lib64',--enable-new-dtags   -L/usr/lib64 -lpython2.7 -lpthread -ldl  -lutil -lm
gmake[2]: Leaving directory `/var/lib/pgsql/postgresql-14.9/contrib/jsonb_plpython'
gmake -C ltree_plpython all
gmake[2]: Entering directory `/var/lib/pgsql/postgresql-14.9/contrib/ltree_plpython'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I/usr/include/python2.7 -DPLPYTHON_LIBNAME='"plpython2"' -I../../src/pl/plpython -I../../contrib -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o ltree_plpython.o ltree_plpython.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o ltree_plpython2.so  ltree_plpython.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/usr/lib64',--enable-new-dtags   -L/usr/lib64 -lpython2.7 -lpthread -ldl  -lutil -lm
gmake[2]: Leaving directory `/var/lib/pgsql/postgresql-14.9/contrib/ltree_plpython'
gmake[1]: Leaving directory `/var/lib/pgsql/postgresql-14.9/contrib'

可以选择编译安装包含扩展包和文档

gmake install-world  //包含扩展包和文档

--看到有 extension 关键字有如下代码则表示编译完成
/bin/install -c -m 644 ./hstore_plpythonu.control ./hstore_plpython2u.control ./hstore_plpython3u.control '/var/lib/pgsql/pgdata/share/extension/'
/bin/install -c -m 644 ./hstore_plpythonu--1.0.sql ./hstore_plpython2u--1.0.sql ./hstore_plpython3u--1.0.sql  '/var/lib/pgsql/pgdata/share/extension/'
gmake[2]: Leaving directory `/var/lib/pgsql/postgresql-14.9/contrib/hstore_plpython'
gmake -C jsonb_plpython install
gmake[2]: Entering directory `/var/lib/pgsql/postgresql-14.9/contrib/jsonb_plpython'
/bin/mkdir -p '/var/lib/pgsql/pgdata/lib'
/bin/mkdir -p '/var/lib/pgsql/pgdata/share/extension'
/bin/mkdir -p '/var/lib/pgsql/pgdata/share/extension'
/bin/install -c -m 755  jsonb_plpython2.so '/var/lib/pgsql/pgdata/lib/jsonb_plpython2.so'
/bin/install -c -m 644 ./jsonb_plpythonu.control ./jsonb_plpython2u.control ./jsonb_plpython3u.control '/var/lib/pgsql/pgdata/share/extension/'
/bin/install -c -m 644 ./jsonb_plpythonu--1.0.sql ./jsonb_plpython2u--1.0.sql ./jsonb_plpython3u--1.0.sql  '/var/lib/pgsql/pgdata/share/extension/'
gmake[2]: Leaving directory `/var/lib/pgsql/postgresql-14.9/contrib/jsonb_plpython'
gmake -C ltree_plpython install
gmake[2]: Entering directory `/var/lib/pgsql/postgresql-14.9/contrib/ltree_plpython'
/bin/mkdir -p '/var/lib/pgsql/pgdata/lib'
/bin/mkdir -p '/var/lib/pgsql/pgdata/share/extension'
/bin/mkdir -p '/var/lib/pgsql/pgdata/share/extension'
/bin/install -c -m 755  ltree_plpython2.so '/var/lib/pgsql/pgdata/lib/ltree_plpython2.so'
/bin/install -c -m 644 ./ltree_plpythonu.control ./ltree_plpython2u.control ./ltree_plpython3u.control '/var/lib/pgsql/pgdata/share/extension/'
/bin/install -c -m 644 ./ltree_plpythonu--1.0.sql ./ltree_plpython2u--1.0.sql ./ltree_plpython3u--1.0.sql  '/var/lib/pgsql/pgdata/share/extension/'
gmake[2]: Leaving directory `/var/lib/pgsql/postgresql-14.9/contrib/ltree_plpython'
gmake[1]: Leaving directory `/var/lib/pgsql/postgresql-14.9/contrib'

编译完成后，则会在 pgdata 的 bin 目录下面生成我们需要的可执行文件，如下所示，包含 psql 在内的很多 PG 工具的可执行文件，我们可通过这里的 psql 可执行程序登录到远程的 PG 实例。

-bash-4.2$ ll
total 16
drwxr-xr-x 2 postgres postgres 4096 Nov  2 01:35 bin
drwxr-xr-x 6 postgres postgres 4096 Nov  2 01:35 include
drwxr-xr-x 4 postgres postgres 4096 Nov  2 01:35 lib
drwxr-xr-x 8 postgres postgres 4096 Nov  2 01:35 share
-bash-4.2$ pwd
/var/lib/pgsql/pgdata
-bash-4.2$ cd bin
-bash-4.2$ ll psql
-rwxr-xr-x 1 postgres postgres 656808 Nov  2 01:35 psql
-bash-4.2$ ls
clusterdb   ecpg               pg_basebackup   pg_ctl         pg_recvlogical  pg_test_timing   postmaster
createdb    initdb             pgbench         pg_dump        pg_resetwal     pg_upgrade       psql
createuser  oid2name           pg_checksums    pg_dumpall     pg_restore      pg_verifybackup  reindexdb
dropdb      pg_amcheck         pg_config       pg_isready     pg_rewind       pg_waldump       vacuumdb
dropuser    pg_archivecleanup  pg_controldata  pg_receivewal  pg_test_fsync   postgres         vacuumlo

如下所示，通过远程 psql 登录访问则和本机操作无二，我们通过 14.9 的客户端访问了我远程 13.12 的数据库实例，值得注意的是，远程实例需要在 pg_ha.conf 开放访问权限，以及相应的用户也需要有连接权限才可以。

[postgres@jiekexu1 data]$ cat pg_hba.conf| grep -A2 IPv4
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask.  A host name
# that starts with a dot (.) matches a suffix of the actual host name.
--
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.75.0/24         trust
[postgres@jiekexu1 data]$ pwd
/var/lib/pgsql/13/data

pgAdmin 工具

pgAdmin也是我们常用的PG客户端工具，在 Win10 环境下，这个工具用起来实在是特别的顺手，但要是这个需求放在 Linux 环境下，安装使用 pgAdmin 4 还是很困难的。当我通过可上网的环境下载 rpm 包安装 pgadmin4 是报错 404 网页找不到，不太懂这是不是我的问题(我这里使用的 X64 的 centos7.6，OS 可能会自带 PostgreSQL9.2.24，另外 yum 源使用的是阿里云源 mirrors.aliyun.com，希望有看到的大佬解惑)，那么这条路走不通了，我们换条路吧。

rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
yum install pgadmin4
pgadmin4                                                                                  35  B/s | 146  B     00:04    
Errors during downloading metadata for repository 'pgAdmin4':
  - Status code: 404 for https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/redhat/redhat-8-x86_64/repodata/repomd.xml (IP: 72.32.157.246)
Error: Failed to download metadata for repo 'pgAdmin4': Cannot download repomd.xml: Cannot download repodata/repomd.xml: All mirrors were tried

https://www.pgadmin.org/download/pgadmin-4-python/

DBeaver 图形化工具

DBeaver 是一个多数据库管理工具，支持 Windows、macOS 和 Linux 平台，开源免费，界面简洁，支持 SQL 编辑、数据浏览、模式设计等等，所以通过官方网站下载到安装包 dbeaver-ce-24.2.3-stable.x86_64.rpm

https://dbeaver.io/download/

rpm -ivh dbeaver-ce-24.2.3-stable.x86_64.rpm

如果第一次使用，需要开启图形化界面，且可联网下载驱动。

[root@JiekeXu pg]# dbeaver
> Start Eclipse Jobs Mechanism [org.eclipse.core.jobs 3.15.400.v20240619-0602]
> Start Equinox Java Authentication and Authorization Service (JAAS) [org.eclipse.equinox.security 1.4.400.v20240702-1702]
> Start DBeaver Application Standalone [org.jkiss.dbeaver.ui.app.standalone 24.2.3.202410221007]
> Start Eclipse IDE UI Application [org.eclipse.ui.ide.application 1.5.500.v20240711-0817]
2024-11-11 23:55:16.950 - Error parsing command line: Unrecognized option: --launcher.additionalVmargs
> Start Eclipse IDE UI [org.eclipse.ui.ide 3.22.300.v20240828-1234]
> Start DBeaver Model Registry [org.jkiss.dbeaver.registry 1.0.135.202410221007]
> Start jna [com.sun.jna 5.14.0.v20231211-1200]
> Start DBeaver UI [org.jkiss.dbeaver.ui 5.1.156.202410221007]
> Start Expression Language [org.eclipse.core.expressions 3.9.400.v20240413-1529]
> Start DBeaver Usage Statistics [org.jkiss.dbeaver.ui.statistics 1.0.37.202410221007]
> Start DBeaver Desktop Application Core [org.jkiss.dbeaver.core 24.2.3.202410221007]
2024-11-11 23:55:17.118 - Initialize desktop platform...
2024-11-11 23:55:17.212 - BounceCastle bundle found. Use JCE provider BC
> Start Core File Systems [org.eclipse.core.filesystem 1.11.0.v20240824-0952]
> Start Core Resource Management [org.eclipse.core.resources 3.21.0.v20240805-1607]
> Start Team Support Core [org.eclipse.team.core 3.10.500.v20240621-0541]
> Start DBeaver UI Navigator [org.jkiss.dbeaver.ui.navigator 1.0.141.202410221007]
2024-11-11 23:55:17.380 - Initialize base platform...
> Start DBeaver UI Editors - Base [org.jkiss.dbeaver.ui.editors.base 1.0.141.202410221007]
2024-11-11 23:55:17.410 - Platform initialized (292ms)
2024-11-11 23:55:17.416 - DBeaver 24.2.3.202410221007 is starting
2024-11-11 23:55:17.417 - OS: Linux 3.10.0-957.el7.x86_64 (amd64)
2024-11-11 23:55:17.417 - Java version: 17.0.6 by Eclipse Adoptium (64bit)
2024-11-11 23:55:17.417 - Install path: '/usr/share/dbeaver-ce'
2024-11-11 23:55:17.417 - Instance path: 'file:/root/.local/share/DBeaverData/workspace6/'
2024-11-11 23:55:17.418 - Memory available 64Mb/1024Mb
2024-11-11 23:55:17.435 - Create display
2024-11-11 23:55:17.855 - Starting instance server at http://localhost:30939
2024-11-11 23:55:17.862 - Run workbench
> Start Eclipse e4 Workbench SWT [org.eclipse.e4.ui.workbench.swt 0.17.500.v20240807-0911]
> Start Eclipse Application Services [org.eclipse.e4.core.services 2.4.400.v20240413-1529]
> Start Eclipse UI Application Services [org.eclipse.e4.ui.services 1.6.300.v20231201-1637]
> Start Eclipse e4 Progress View [org.eclipse.e4.ui.progress 0.4.600.v20240606-1020]
> Start Eclipse Bindings Support [org.eclipse.e4.ui.bindings 0.14.400.v20240321-1245]
> Start Eclipse e4 core commands [org.eclipse.e4.core.commands 1.1.400.v20240424-0444]
> Start EMF XML/XMI Persistence [org.eclipse.emf.ecore.xmi 2.38.0.v20240721-0634]
> Start Eclipse CSS SWT Theme Support [org.eclipse.e4.ui.css.swt.theme 0.14.400.v20240424-0956]
> Start Eclipse e4 Workbench Add-ons [org.eclipse.e4.ui.workbench.addons.swt 1.5.500.v20240620-1945]
> Start Eclipse e4 Workbench SWT Renderer [org.eclipse.e4.ui.workbench.renderers.swt 0.16.500.v20240727-1037]
> Start Eclipse Dependency Injection Extensions [org.eclipse.e4.core.di.extensions 0.18.300.v20240413-1529]
> Start Eclipse CSS SWT Support [org.eclipse.e4.ui.css.swt 0.15.400.v20240321-1245]
> Start Default Text Editor [org.eclipse.ui.editors 3.18.0.v20240807-0735]
> Start Text Editor Framework [org.eclipse.ui.workbench.texteditor 3.18.0.v20240819-1419]
2024-11-11 23:55:20.136 - Configure workbench window
> Start DBeaver SQL Model [org.jkiss.dbeaver.model.sql 1.0.131.202410221007]
2024-11-11 23:55:20.301 - Total database drivers: 119 (119)
> Start Help System Core [org.eclipse.help 3.10.400.v20240415-0528]
2024-11-11 23:55:20.361 - Create actions
2024-11-11 23:55:20.374 - Disable Eclipse action set 'org.eclipse.ui.edit.text.actionSet.annotationNavigation'
2024-11-11 23:55:20.375 - Disable Eclipse action set 'org.eclipse.ui.WorkingSetActionSet'
2024-11-11 23:55:20.376 - Disable Eclipse action set 'org.eclipse.ui.actionSet.openFiles'
2024-11-11 23:55:20.376 - Create workbench actions
2024-11-11 23:55:20.867 - Initialize workbench window
2024-11-11 23:55:20.868 - Error parsing command line: Unrecognized option: --launcher.additionalVmargs
2024-11-11 23:55:20.873 - Finish initialization
> Start Internet Connection Management UI [org.eclipse.ui.net 1.5.400.v20240413-1529]
> Start Internet Connection Management [org.eclipse.core.net 1.5.500.v20240625-1706]
> Start DBeaver UI Editors - Data [org.jkiss.dbeaver.ui.editors.data 1.0.141.202410221007]
> Start DBeaver UI Editors - SQL [org.jkiss.dbeaver.ui.editors.sql 1.0.141.202410221007]
> Start DBeaver Sample Database [org.jkiss.dbeaver.ext.sample.database 1.0.178.202410221007]
> Start DBeaver Tip of the day [org.jkiss.dbeaver.ext.ui.tipoftheday 1.0.147.202410221007]
> Start GEF Classic Draw2d [org.eclipse.draw2d 3.17.0.202409021815]
> Start GEF Classic GEF (MVC) [org.eclipse.gef 3.19.0.202409021815]
> Start DBeaver ERD UI [org.jkiss.dbeaver.erd.ui 3.0.101.202410221007]
> Start DBeaver ERD Model [org.jkiss.dbeaver.erd.model 1.0.101.202410221007]
> Start DBeaver Dashboard UI [org.jkiss.dbeaver.ui.dashboard 1.0.138.202410221007]

(DBeaver:8107): Gtk-CRITICAL **: 23:55:23.634: gtk_box_gadget_distribute: assertion 'size >= 0' failed in GtkScrollbar

Python 连接 PG

Psycopg 是 Python 编程语言中最流行的 PostgreSQL 适配器，其核心是 Python DB API 2.0 规范的完整实现。一些扩展允许访问 PostgreSQL 提供的许多功能。

https://www.psycopg.org/
https://www.postgresql.org/download/products/2-drivers-and-interfaces/

Psycopg 现在有两个大版本，Psycopg2 和 Psycopg3，Psycopg2 还有两种类型的包(psycopg2 和 psycopg2-binary)，其中 psycopg2-binary 相当于绿色版，不需要编译，是为了初学者用 Python 快速和 PostgreSQL 进行连接而用的，对于生产环境，建议编译安装使用psycopg2。Psycopg 是 Python 编程语言的 PostgreSQL 适配器。它是一个 libpq 的包装器，这是官方的 PostgreSQL 客户端库。

安装 psycopg2 的三个先决条件：

从 3.8 到 3.13 的 Python 版本
PostgreSQL 服务器版本从 7.4 到 17
PostgreSQL 客户端库版本从 9.1 开始

因 Linux7 系统自带 Python2，通过 yum install python3 只是 python3.6.8 版本.

yum install perl-Env zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gcc make libffi-devel python3 -y
yum -y install coreutils glib2 lrzsz mpstat dstatsysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-develpam-devel libxml2-devel libxslt-devel python3-devel tcl-devel gcc makesmartmontools flex bison perl-devel perl-Ext Utils* openldap-devel jadetex openjade bzip2

yum install libpq-dev*

pip3 install -U pip
pip3 install psycopg2-binary

psycopg2 需要 C、python3-devel、libpq-dev* 依赖包。

In order to perform a local installation you need some prerequisites:

a C compiler,
Python development headers (e.g. the python3-dev package).
PostgreSQL client development headers (e.g. the libpq-dev package).
The pg_config program available in the PATH.

[root@JiekeXu pg]# pip3 install psycopg2-binary
WARNING: pip is being invoked by an old script wrapper. This will fail in a future version of pip.
Please see https://github.com/pypa/pip/issues/5599 for advice on fixing the underlying issue.
To avoid this problem you can invoke Python with '-m pip' instead of running pip directly.
Collecting psycopg2-binary
  Using cached psycopg2-binary-2.9.8.tar.gz (383 kB)
  Preparing metadata (setup.py) ... done
Using legacy 'setup.py install' for psycopg2-binary, since package 'wheel' is not installed.
Installing collected packages: psycopg2-binary
    Running setup.py install for psycopg2-binary ... done
Successfully installed psycopg2-binary-2.9.8

## py 导入包 psycopg2
[root@JiekeXu pg]# python3
Python 3.6.8 (default, Oct  5 2022, 16:22:51) 
[GCC 8.5.0 20210514 (Red Hat 8.5.0-15.0.1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> 
>>> import psycopg2
>>> conn = psycopg2.connect(host="192.168.75.11", port="54321", database="postgres", user="postgres", password="postgres")
>>> cur = conn.cursor()
>>> cur.execute("select now()")
>>> print(cur.fetchone()[0])
2024-11-13 00:22:25.910934+08:00
>>>

我们也可以直接编写一个 python 脚本，然后运行测试连接。

[root@JiekeXu pg]# vim py_connect_pg.py
import psycopg2
conn = psycopg2.connect(host="192.168.75.11", port="54321", database="postgres", user="postgres", password="postgres")
# 建立游标用来执行数据库操作
cur = conn.cursor()
# 执行一些 SQL 命令，如下仅是演示
cur.execute("select inet_server_addr(),pg_is_in_recovery(),current_database(),current_user")

# 获取 SELECT 返回的元组
rows = cur.fetchall()
for row in rows:
    print('inet_server_addr: ' + str(row[0]))
    print('pg_is_in_recovery: ' + str(row[1]))
    print('current_database: ' + row[2])
    print('current_user: ' + row[3])

cur.close()
conn.close()

[root@JiekeXu pg]# python3 py_connect_pg.py
inet_server_addr: 192.168.75.11
pg_is_in_recovery: False
current_database: postgres
current_user: postgres

JDBC 连接串示例

示例一：
String url = "jdbc:postgresql://localhost/test";
Properties props = new Properties();
props.setProperty("user", "fred");
props.setProperty("password", "secret");
props.setProperty("ssl", "true");
Connection conn = DriverManager.getConnection(url, props);

示例二：
String url = "jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true";
Connection conn = DriverManager.getConnection(url);

示例三：
Properties props = new Properties();
props.setProperty("options", "-c search_path=test,public,pg_catalog -c statement_timeout=90000");
Connection conn = DriverManager.getConnection(url, props);

String url = "jdbc:postgresql://localhost:5432/postgres?options=-c%20search_path=test,public,pg_catalog%20-c%20statement_timeout=90000";
Connection conn = DriverManager.getConnection(url);

总结

选择合适的 PostgreSQL 客户端工具取决于你的具体需求，包括操作系统、使用习惯、功能需求和预算等因素。对于初学者和日常使用，推荐使用 psql 和 pgAdmin，它们分别提供了强大的命令行和图形界面支持。对于开发人员，DBeaver 和 DataGrip 是不错的选择，因为它们提供了丰富的开发辅助功能。

参考链接

https://www.postgresql.org/ftp/source/
https://www.pgadmin.org/download/
https://www.pgadmin.org/download/pgadmin-4-rpm/
https://dbeaver.io/download/
https://jdbc.postgresql.org/documentation/use/
https://www.psycopg.org/docs/install.html
https://www.postgresql.org/download/products/2-drivers-and-interfaces/

全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

分享几个数据库备份脚本

一文搞懂 Oracle 统计信息

我的 Oracle ACE 心路历程
MOP 系列|MOP 三种主流数据库索引简介
Oracle 主流版本不同架构下的静默安装指南
关机重启导致 ASM 磁盘丢失数据库无法启动
Oracle SQL 性能分析(SPA)原理与实战演练

Oracle 11g 升级到 19c 需要关注的几个问题
Windows 10 环境下 MySQL 8.0.33 安装指南
SQL 大全（四）|数据库迁移升级时常用 SQL 语句
OGG|使用 OGG19c 迁移 Oracle11g 到 19C(第二版)
Oracle 大数据量导出工具——sqluldr2 的安装与使用
Oracle ACE 视角下的国产数据库现状与选型及应对策略
从国产数据库调研报告中你都能了解哪些信息及我的总结建议
使用数据泵利用 rowid 分片导出导入 lob 大表及最佳实践
在归档模式下直接 rm dbf 数据文件并重启数据库还有救吗？

欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
——————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107——————————————————————————

JiekeXu DBA之路

JiekeXu:Oracle ACE-Pro，获 Oracle OCP/OCM 及 MySQL OCP 认证，墨天轮 MVP，利用闲时间记录菜鸟 DBA 学习成长之路，所发布文字属于个人观点和学习笔记，如有错误及不当之处，敬请批评指正！