CentOS7 yum PostgreSQL(9.4) 并启用 python3编写存储过程(Python3.3)

yum 安装

1. 下载PostgreSQL

2. 安装PostgreSQL源

[[email protected] ~]# yum install pgdg-centos94-9.4-3.noarch.rpm 

3. 安装PostgresSQl9.4 客户端与服务端

[[email protected] ~]# yum install postgresql94 postgresql94-server

4. 初始化数据库

[[email protected] lib]# /usr/pgsql-9.4/bin/postgresql94-setup initdb

数据库文件一般在/var/pgsql/9.4/目录下

5. 启动PostgreSQL服务

[[email protected] ~]# systemctl start postgresql-9.4
[[email protected] ~]# systemctl enable postgresql-9.4

6. 登陆postgreSQl 并修改密码

[[email protected] ~]# su - postgres
Last login: Thu Mar 28 09:44:11 CST 2019 on pts/0
-bash-4.2$ psql
psql (9.4.21)
Type "help" for help.

postgres=# \password
Enter new password: 
Enter it again: 

7. 设置远程方案
   1. 修改postgresql.conf 文件

   [[email protected] ~]# find / -name postgresql.conf
   /var/lib/pgsql/9.4/data/postgresql.conf
   [[email protected] ~]# vim /var/lib/pgsql/9.4/data/postgresql.conf
   # 找到listen_addresses 取消注释并修改成如下
   listen_addresses = '0.0.0.0'
   # listen_addresses = '*' 也可以
   

   
   2. 修改pg_hba.conf文件 最后一行添加

   [[email protected] ~]# vim /var/lib/pgsql/9.4/data/pg_hba.conf 
   host    all             all             0.0.0.0/0              md5
   

   
   3. 打开防火墙5432端口并重启postgresql服务

   [[email protected] data]# firewall-cmd --zone=public --add-port=5432/tcp --permanent
   success
   [[email protected] data]# firewall-cmd --reload
   success
   [[email protected] data]# firewall-cmd --zone=public --list-all
   public (active)
   target: default
   icmp-block-inversion: no
   interfaces: enp0s31f6
   sources: 
   services: ssh dhcpv6-client
   ports: 5432/tcp
   protocols: 
   masquerade: no
   forward-ports: 
   source-ports: 
   icmp-blocks: 
   rich rules: 
   
   [[email protected] data]# systemctl restart postgresql-9.4
   

编译安装Python3.3

PostgreSQL版本与其对应支持的python3版本

PostgreSQL Version	Python Version
9.X	3.3
10.X	3.4
11.X	3.6

1. 下载Python3.3

2. 安装可能需要的依赖包(dependence)

[[email protected] ~]# yum install gcc openssl-devel bzip2-devel expat-devel gdbm-devel readline-devel sqlite-devel -y

3. 解压Python-3.3.6.tgz 并进入文件夹

[[email protected] ~]# tar -zvxf Python-3.3.6.tgz
[[email protected] ~]# cd Python-3.3.6

4. 安装Python3.3

[[email protected] Python-3.3.6]# ./configure --prefix=/usr/local/Python33 --enable-shared
[[email protected] Python-3.3.6]# make & make install

5. 添加软连接到 /usr/local/bin 下，以使用python3 命令

[[email protected] ~]# ln -s /usr/local/Python33/bin/python3 /usr/local/bin/python3
[[email protected] ~]# python3
python3: error while loading shared libraries: libpython3.3m.so.1.0: cannot open shared object file: No such file or directory

6. 添加python3.3动态库配置文件

[[email protected] ~]# echo /usr/local/bin/lib > /etc/ld.so.conf.d/python3.3.conf
[[email protected] ~]# ldconfig
[[email protected] ~]# ldd /usr/local/bin/python3
        linux-vdso.so.1 =>  (0x00007ffcd8d9e000)
        libpython3.3m.so.1.0 => /usr/local/bin/lib/ libpython3.3m.so.1.0 (0x00007fd5cd547000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fd5cd32b000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fd5cd127000)
        libutil.so.1 => /lib64/libutil.so.1 (0x00007fd5ccf24000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fd5ccc22000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fd5cc855000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fd5cda0d000)
[[email protected] ~]# python
Python 2.7.5 (default, Apr 11 2018, 07:36:10) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-28)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> exit()
[[email protected] bin]# python3
Python 3.3.6 (default, Mar 28 2019, 09:38:14) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-36)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> exit()

7. 下载 plpython3.so 并添加至 /usr/pgsql-9.4/lib

[[email protected] ~]# chmod 755 plpython3.so

8. 创建plpython3u语言

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(10,12);
 pymax 
-------
    12
(1 row)

---

源码安装：未完成

1. 下载PostgreSQL

2. 安装gcc

# yum install gcc -y

3. 解压并编译安装

# tar -zvxf postgresql-10.7.tar.gz
# cd postgresql-10.7
# ./configure --with-python
# make && make install

4. 添加环境变量

# vim ~/.bash_profile
# 添加以下内容


# 添加结束 source配置文件
# source ~/.bash_profile

5. 创建postgres用户

# useradd postgres
# passwd postgres

6. 初始化数据库集群

$ initdb -D /usr/local/pgsql/data
#若出现错误查看data文件夹是否存在，若不存在则进行如下步骤创建
# mkdir -pv /usr/local/pgsql/data
# chowm -R postgres:postgres /usr/local/pgsql/data
#创建成功之后再执行initdb的步骤

7. 启动数据库服务器

[[email protected] ~]$ postgres -D /usr/local/pgsql/data >logfile 2>&1 &
#将服务器的stdout和stderr输出写入logfile文件中

8. 添加postgreSQL服务

[[email protected] ~]# vim /etc/systemd/system/postgresql.service
# 添加以下内容
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

出现的问题

testpy3=# create extension if not exists pypython3u;
ERROR:  could not open extension control file "/usr/local/pgsql/share/extension/pypython3u.control": No such file or directory
testpy3=# create extension if not exists plpython3u;
ERROR:  could not access file "$libdir/plpython3": No such file or directory