在openEuler 22.03上使用yum安装PostgreSQL单机环境

环境申请

由于环境要国产化，选择openEuler 22.03 LTS
系统，这里测试一下在openEuler上安装PG，过程如下。

 1docker rm -f lhropeneuler22
 2docker run -itd --name lhropeneuler22 -h lhropeneuler22 \
 3-p 15432:5432 -p 7389:3389 \
 4--privileged=true \
 5-v /sys/fs/cgroup:/sys/fs/cgroup \
 6lhrbest/openeuler22:2.0 \
 7init
 8
 9docker exec -it lhropeneuler22 bash 
10
11[root@lhropeneuler22 /]# cat /etc/os-release 
12NAME="openEuler"
13VERSION="22.03 LTS"
14ID="openEuler"
15VERSION_ID="22.03"
16PRETTY_NAME="openEuler 22.03 LTS"
17ANSI_COLOR="0;31"

安装PG

 1-- 默认安装PG 13.3.7
 2yum install -y postgresql postgresql-server postgresql-contrib
 3
 4[root@lhropeneuler22 soft]#  rpm -aq| grep postgres
 5postgresql-13.3-7.oe2203.x86_64
 6postgresql-server-13.3-7.oe2203.x86_64
 7postgresql-contrib-13.3-7.oe2203.x86_64
 8
 9
10-- 初始化
11/usr/bin/postgresql-setup initdb
12systemctl enable postgresql.service
13systemctl start postgresql.service
14systemctl status postgresql.service
15
16
17[root@lhropeneuler22 /]# systemctl status postgresql.service
18● postgresql.service - PostgreSQL database server
19     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
20     Active: active (running) since Fri 2023-03-10 12:28:14 CST; 9s ago
21    Process: 4453 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
22   Main PID: 4455 (postmaster)
23      Tasks: 8 (limit: 411139)
24     Memory: 14.0M
25     CGroup: /docker/54a7256274ea4b7de2c11b57c7f07226a520c34178effcb3a06b2da35ea96144/system.slice/postgresql.service
26             ├─4455 /usr/bin/postmaster -D /var/lib/pgsql/data
27             ├─4456 "postgres: logger " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
28             ├─4458 "postgres: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
29             ├─4459 "postgres: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" ""
30             ├─4460 "postgres: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
31             ├─4461 "postgres: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" ""
32             ├─4462 "postgres: stats collector " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
33             └─4463 "postgres: logical replication launcher " "" "" ""
34
35Mar 10 12:28:14 lhropeneuler22 systemd[1]: Starting PostgreSQL database server...
36Mar 10 12:28:14 lhropeneuler22 postmaster[4455]: 2023-03-10 12:28:14.227 CST [4455] LOG:  redirecting log output to logging collector process
37Mar 10 12:28:14 lhropeneuler22 postmaster[4455]: 2023-03-10 12:28:14.227 CST [4455] HINT:  Future log output will appear in directory "log".
38Mar 10 12:28:14 lhropeneuler22 systemd[1]: Started PostgreSQL database server.
39[root@lhropeneuler22 /]# 

修改密码

 1-- 本地登陆
 2su - postgres
 3psql
 4
 5-- 安装插件
 6create extension pageinspect;
 7create extension pg_stat_statements;
 8
 9select * from pg_extension ;
10select * from pg_available_extensions order by name;
11
12
13postgres=# select * from pg_extension ;
14  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
15-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
16 13490 | plpgsql            |       10 |           11 | f              | 1.0        |           | 
17 16384 | pageinspect        |       10 |         2200 | t              | 1.8        |           | 
18 16416 | pg_stat_statements |       10 |         2200 | t              | 1.8        |           | 
19(3 rows)
20
21postgres=# 
22
23
24
25-- 修改postgres密码
26alter user postgres with encrypted password 'lhr'; 或 \password
27select * from pg_tables;
28select version();
29
30
31postgres=# select version();
32                                         version                                         
33-----------------------------------------------------------------------------------------
34 PostgreSQL 13.3 on x86_64-openEuler-linux-gnu, compiled by gcc_old (GCC) 10.3.1, 64-bit
35(1 row)
36
37postgres=# 

配置远程登录

 1-- 配置允许PG远程登录，注意版本：
 2cat >> /var/lib/pgsql/data/postgresql.conf <<"EOF"
 3listen_addresses = '*'
 4port=5432
 5unix_socket_directories='/var/lib/pgsql/data'
 6logging_collector = on
 7log_directory = 'pg_log'
 8log_filename = 'postgresql-%a.log'
 9log_truncate_on_rotation = on
10EOF
11
12cat > /var/lib/pgsql/data/pg_hba.conf   << EOF 
13
14#TYPE  DATABASE    USER    ADDRESS       METHOD
15
16local     all       all                    trust
17host      all       all    ::1/128         trust
18host      all       all   127.0.0.1/32     trust
19host      all       all    0.0.0.0/0        md5
20host   replication  all    0.0.0.0/0        md5
21EOF
22
23pg_ctl restart
24
25
26
27psql -U postgres -h 172.71.0.30 -d postgres -p5432
28
29[root@lhrdb ~]# psql -U postgres -h 172.71.0.35 -d postgres -p5432 
30Password for user postgres: 
31psql (14.7, server 13.3)
32Type "help" for help.
33
34postgres=# create database lhrdb;
35CREATE DATABASE
36postgres=# \l
37                                  List of databases
38   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
39-----------+----------+----------+-------------+-------------+-----------------------
40 lhrdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
41 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
42 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
43           |          |          |             |             | postgres=CTc/postgres
44 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
45           |          |          |             |             | postgres=CTc/postgres
46(4 rows)
47
48postgres=# 
49

参考

https://www.xmmup.com/dbbao67shiyongyumlaianzhuangpostgresql13-3shujuku.html