环境申请
由于环境要国产化，选择openEuler 22.03 LTS系统，这里测试一下在openEuler上安装PG，过程如下。
docker rm -f lhropeneuler22
docker run -itd --name lhropeneuler22 -h lhropeneuler22
-p 15432:5432 -p 7389:3389
--privileged=true
-v /sys/fs/cgroup:/sys/fs/cgroup
lhrbest/openeuler22:2.0
init
docker exec -it lhropeneuler22 bash
[root@lhropeneuler22 /]# cat /etc/os-release
NAME="openEuler"
VERSION="22.03 LTS"
ID="openEuler"
VERSION_ID="22.03"
PRETTY_NAME="openEuler 22.03 LTS"
ANSI_COLOR="0;31"
安装PG
代码语言：javascript复制-- 默认安装PG 13.3.7
yum install -y postgresql postgresql-server postgresql-contrib
[root@lhropeneuler22 soft]# rpm -aq| grep postgres
postgresql-13.3-7.oe2203.x86_64
postgresql-server-13.3-7.oe2203.x86_64
postgresql-contrib-13.3-7.oe2203.x86_64
-- 初始化
/usr/bin/postgresql-setup initdb
systemctl enable postgresql.service
systemctl start postgresql.service
systemctl status postgresql.service
[root@lhropeneuler22 /]# systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2023-03-10 12:28:14 CST; 9s ago
Process: 4453 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
Main PID: 4455 (postmaster)
Tasks: 8 (limit: 411139)
Memory: 14.0M
CGroup: /docker/54a7256274ea4b7de2c11b57c7f07226a520c34178effcb3a06b2da35ea96144/system.slice/postgresql.service
├─4455 /usr/bin/postmaster -D /var/lib/pgsql/data
├─4456 "postgres: logger " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─4458 "postgres: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─4459 "postgres: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─4460 "postgres: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─4461 "postgres: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" ""
├─4462 "postgres: stats collector " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
└─4463 "postgres: logical replication launcher " "" "" ""
Mar 10 12:28:14 lhropeneuler22 systemd[1]: Starting PostgreSQL database server...
Mar 10 12:28:14 lhropeneuler22 postmaster[4455]: 2023-03-10 12:28:14.227 CST [4455] LOG: redirecting log output to logging collector process
Mar 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".
Mar 10 12:28:14 lhropeneuler22 systemd[1]: Started PostgreSQL database server.
[root@lhropeneuler22 /]#
修改密码
代码语言：javascript复制-- 本地登陆
su - postgres
psql
-- 安装插件
create extension pageinspect;
create extension pg_stat_statements;
select * from pg_extension ;
select * from pg_available_extensions order by name;
postgres=# select * from pg_extension ;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
------- -------------------- ---------- -------------- ---------------- ------------ ----------- --------------
13490 | plpgsql | 10 | 11 | f | 1.0 | |
16384 | pageinspect | 10 | 2200 | t | 1.8 | |
16416 | pg_stat_statements | 10 | 2200 | t | 1.8 | |
(3 rows)
postgres=#
-- 修改postgres密码
alter user postgres with encrypted password 'lhr'; 或 password
select * from pg_tables;
select version();
postgres=# select version();
version
-----------------------------------------------------------------------------------------
PostgreSQL 13.3 on x86_64-openEuler-linux-gnu, compiled by gcc_old (GCC) 10.3.1, 64-bit
(1 row)
postgres=#
配置远程登录
代码语言：javascript复制-- 配置允许PG远程登录，注意版本：
cat >> /var/lib/pgsql/data/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5432
unix_socket_directories='/var/lib/pgsql/data'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF
cat > /var/lib/pgsql/data/pg_hba.conf << EOF
#TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all ::1/128 trust
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
host replication all 0.0.0.0/0 md5
EOF
pg_ctl restart
psql -U postgres -h 172.71.0.30 -d postgres -p5432
[root@lhrdb ~]# psql -U postgres -h 172.71.0.35 -d postgres -p5432
Password for user postgres:
psql (14.7, server 13.3)
Type "help" for help.
postgres=# create database lhrdb;
CREATE DATABASE
postgres=# l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------- ---------- ---------- ------------- ------------- -----------------------
lhrdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
| | | | | postgres=CTc/postgres
(4 rows)
postgres=#
参考
https://www.xmmup.com/dbbao67shiyongyumlaianzhuangpostgresql13-3shujuku.html
