CentOS7 yum PostgreSQL(11) 并启用 python3编写存储过程(Python3.6)
一、安装python3.6
- 下载Python3.6.6.tgz 至 /tmp 下
[
root@localhost tmp
]
# wget -c https://www.python.org/ftp/python/3.6.6/Python-3.6.6.tgz
- 解压 Python3.6.6.tgz
[
root@localhost tmp
]
# tar -zvxf Python-3.6.6.tgz
- 安装需要的依赖包(dependence)
[
root@localhost tmp
]
# yum install gcc openssl-devel bzip2-devel expat-devel gdbm-devel readline-devel sqlite-devel -y
- 编译安装
[
root@localhost tmp
]
# cd Python-3.6.6/
[
root@localhost Python-3.6.6
]
# ./configure --prefix=/usr/local/Python36 --enable-shared
[
root@localhost Python-3.6.6
]
# make && make install
………………………………省略………………………………
Looking
in
links: /tmp/tmpjbfza4ut
Collecting setuptools
Collecting pip
Installing collected packages: setuptools, pip
Successfully installed pip-10.0.1 setuptools-39.0.1
- 将python3.6 与pip3 软链接到/usr/local/bin目录下
[
root@localhost bin
]
# ln -s /usr/local/Python36/bin/python3.6 /usr/local/bin/python3
[
root@localhost bin
]
# ln -s /usr/local/Python36/bin/pip3 /usr/local/bin/pip3
- 添加动态库配置文件
[
root@localhost ~
]
# python3
python3: error
while
loading shared libraries: libpython3.6m.so.1.0: cannot
open
shared object file: No such
file
or directory
<=
==
执行python3 报错
[
root@localhost ~
]
# ldd /usr/local/bin/python3
linux-vdso.so.1
=
>
(
0x00007ffd451fc000
)
libpython3.6m.so.1.0
=
>
not found
<=
==
此动态文件未找到
libpthread.so.0
=
>
/lib64/libpthread.so.0
(
0x00007f725d3d5000
)
libdl.so.2
=
>
/lib64/libdl.so.2
(
0x00007f725d1d1000
)
libutil.so.1
=
>
/lib64/libutil.so.1
(
0x00007f725cfce000
)
libm.so.6
=
>
/lib64/libm.so.6
(
0x00007f725cccc000
)
libc.so.6
=
>
/lib64/libc.so.6
(
0x00007f725c8ff000
)
/lib64/ld-linux-x86-64.so.2
(
0x00007f725d5f1000
)
[
root@localhost ~
]
# echo /usr/local/Python36/lib/ >> /etc/ld.so.conf.d/python3.6.conf
[
root@localhost ~
]
# ldconfig
[
root@localhost ~
]
# python3
Python 3.6.6
(
default, May 17 2019, 14:04:59
)
[
GCC 4.8.5 20150623
(
Red Hat 4.8.5-36
)
]
on linux
Type
"help"
,
"copyright"
,
"credits"
or
"license"
for
more
information.
>>
>
exit
(
)
二、安装PG11
-
PostgreSQL官网
下载PG的repo包
[
root@localhost tmp
]
# wget -c https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
--2019-05-17 13:43:55-- https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Resolving download.postgresql.org
(
download.postgresql.org
)
..
. 72.32.157.246, 87.238.57.227, 204.145.124.244,
..
.
Connecting to download.postgresql.org
(
download.postgresql.org
)
|
72.32.157.246
|
:443
..
. connected.
HTTP request sent, awaiting response
..
. 200 OK
Length: 5720
(
5.6K
)
[
application/x-redhat-package-manager
]
Saving to: ‘pgdg-redhat-repo-latest.noarch.rpm’
100%
[
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
==
=
>
]
5,720 5.66KB/s
in
1.0s
2019-05-17 13:43:57
(
5.66 KB/s
)
- ‘pgdg-redhat-repo-latest.noarch.rpm’ saved
[
5720/5720
]
- 安装PostgreSQL源
[
root@localhost tmp
]
# yum install pgdg-redhat-repo-latest.noarch.rpm -y
- 安装PostgresSQL11 客户端与服务端
[
root@localhost ~
]
# yum install postgresql11 postgresql11-server -y
PostgreSQL版本与其对应支持的python3版本
| PostgreSQL Version | Python Version |
|---|---|
| 9.X | 3.3 |
| 10.X | 3.4 |
| 11.X | 3.6 |
- 将python3.6对应的plpython3.so 文件添加到/usr/pgsql-11/lib/中
[
root@localhost ~
]
# wget -c https://gitee.com/CodeForLive/PGEnablePlpython3/raw/master/py36/plpython3.so -C /usr/pgsql-11/lib
plpython3.so 100% 143KB 9.9MB/s 00:00
[
root@localhost ~
]
# chmod 755 /usr/pgsql-11/lib/plpython3.so
- 初始化数据库
[
root@localhost ~
]
# /usr/pgsql-11/bin/postgresql-11-setup initdb
数据库文件一般在/var/lib/pgsql/11/目录下
6. 启动PostgreSQL服务
[
root@localhost ~
]
# systemctl start postgresql-11 && systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded
(
/usr/lib/systemd/system/postgresql-11.service
;
disabled
;
vendor preset: disabled
)
Active: active
(
running
)
since Fri 2019-05-17 14:27:53 CST
;
14ms ago
Docs: https://www.postgresql.org/docs/11/static/
Process: 14099 ExecStartPre
=
/usr/pgsql-11/bin/postgresql-11-check-db-dir
${PGDATA}
(
code
=
exited, status
=
0/SUCCESS
)
Main PID: 14106
(
postmaster
)
Tasks: 8
CGroup: /system.slice/postgresql-11.service
├─14106 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─14107 postgres: logger
├─14109 postgres: checkpointer
├─14110 postgres: background writer
├─14111 postgres: walwriter
├─14112 postgres: autovacuum launcher
├─14113 postgres: stats collector
└─14114 postgres: logical replication launcher
May 17 14:27:53 localhost.localdomain systemd
[
1
]
: Starting PostgreSQL 11 database server
..
.
May 17 14:27:53 localhost.localdomain postmaster
[
14106
]
: 2019-05-17 14:27:53.839 CST
[
14106
]
LOG: listening on IPv6 addr
..
. 5432
May 17 14:27:53 localhost.localdomain postmaster
[
14106
]
: 2019-05-17 14:27:53.839 CST
[
14106
]
LOG: listening on IPv4 addr
..
. 5432
May 17 14:27:53 localhost.localdomain postmaster
[
14106
]
: 2019-05-17 14:27:53.841 CST
[
14106
]
LOG: listening on Unix sock
..
.5432
"
May 17 14:27:53 localhost.localdomain postmaster[14106]: 2019-05-17 14:27:53.843 CST [14106] LOG: listening on Unix sock...5432"
May 17 14:27:53 localhost.localdomain postmaster
[
14106
]
: 2019-05-17 14:27:53.860 CST
[
14106
]
LOG: redirecting log output
..
.ocess
May 17 14:27:53 localhost.localdomain postmaster
[
14106
]
: 2019-05-17 14:27:53.860 CST
[
14106
]
HINT: Future log output wil
..
.log".
May 17 14:27:53 localhost.localdomain systemd
[
1
]
: Started PostgreSQL 11 database server.
Hint: Some lines were ellipsized, use -l to show
in
full.
- 设置为开机启动
[
root@localhost ~
]
# systemctl enable postgresql-11
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.
- 登陆postgreSQl 并修改密码
[
root@localhost ~
]
# su - postgres
-bash-4.2$ psql
psql
(
11.3
)
Type
"help"
for
help.
postgres
=
# \password
Enter new password:
Enter it again:
- 查看plpython3 是否可以使用
postgres=# create language plpython3u;
CREATE LANGUAGE
postgres=# CREATE FUNCTION pymax (a integer, b integer)
postgres-# RETURNS integer
postgres-# AS $$
postgres$# if a > b:
postgres$# return a
postgres$# return b
postgres$# $$ LANGUAGE plpython3u;
CREATE FUNCTION
postgres=# select pymax(20,12);
pymax
-------
20
(1 row)
postgres=# select pymax(10,12);
pymax
-------
12
(1 row)
-
设置远程连接
- 修改postgresql.conf 文件
[ root@localhost ~ ] # vim /var/lib/pgsql/11/data/postgresql.conf 59 listen_addresses = '*' # what IP address(es) to listen on; 60 # comma-separated list of addresses; 61 # defaults to 'localhost'; use '*' for all 62 # (change requires restart) 63 #port = 5432 # (change requires restart) 64 max_connections = 100 # (change requires restart) 65 #superuser_reserved_connections = 3 # (change requires restart)- 修改pg_hba.conf文件 最后一行添加
[ root@localhost ~ ] # vim /var/lib/pgsql/11/data/pg_hba.conf host all all 0.0.0.0/0 md5- 打开防火墙5432端口并重启postgresql服务
[ root@localhost ~ ] # vim /var/lib/pgsql/11/data/postgresql.conf [ root@localhost ~ ] # vim /var/lib/pgsql/11/data/pg_hba.conf [ root@localhost ~ ] # firewall-cmd --zone=public --add-port=5432/tcp --permanent success [ root@localhost ~ ] # firewall-cmd --reload success- 重启postgresql-11 服务并测试
[ root@localhost ~ ] # systemctl restart postgresql-11
