官方文档:link
安装及简单操作
1 安装
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql15-server
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15
[root@gitlab_server ~]# netstat -lnpt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 893/sshd
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 3823/postmaster
tcp 0 0 127.0.0.1:199 0.0.0.0:* LISTEN 889/snmpd
创建普通用户，后续将作为数据库用户
useradd dbuser
passwd dbuser
2 登录控制台
[root@gitlab_server ~]# sudo -u postgres psql
could not change directory to "/root"
psql (9.2.24, server 15.3)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
Type "help" for help.
2.1给Linux系统用户postgres设置密码
postgres-# \password postgres
2.2创建数据库用户dbuser
postgres=# CREATE USER dbuser WITH PASSWORD 'wang123.';
CREATE ROLE
2.3创建用户数据库，并指定所有者
CREATE DATABASE myserver OWNER dbuser;
2.4 授权
postgres=# GRANT ALL PRIVILEGES ON DATABASE myserver to dbuser;
GRANT
2. 5查看所有数据库
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
myserver | dbuser | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/dbuser +
| | | | | dbuser=CTc/dbuser
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
2.6 切换数据库
postgres=# \c myserver
psql (9.2.24, server 15.3)
WARNING: psql version 9.2, server version 15.0.
Some psql features might not work.
You are now connected to database "myserver" as user "postgres".
myserver=#
2.7 使用dbuser登录并创建数据库，并添加表，添加数据等操作了。
[root@gitlab_server ~]# su - dbuser
上一次登录：四 5月 18 15:08:52 CST 2023pts/0 上
[dbuser@gitlab_server ~]$ psql myserver
psql (9.2.24, 服务器 15.3)
警告：psql 版本9.2， 服务器版本15.0.
一些psql功能可能无法工作.
输入 "help" 来获取帮助信息.
myserver=> \l
myserver=> CREATE TABLE UserInfo(Id SERIAL PRIMARY KEY, UserName VARCHAR(32) NULL, Del SMALLINT
myserver(> NULL, SubOn DATE NULL, Mail VARCHAR(128) NULL, Phone VARCHAR(32) NULL, Remark VARCHAR(64) NULL);
CREATE TABLE
#创建表
myserver=> CREATE TABLE UserInfo(Id SERIAL PRIMARY KEY, UserName VARCHAR(32) NULL, Del SMALLINT
myserver(> NULL, SubOn DATE NULL, Mail VARCHAR(128) NULL, Phone VARCHAR(32) NULL, Remark VARCHAR(64) NULL);
CREATE TABLE
#写入数据
myserver=> INSERT INTO UserInfo(UserName,Del, SubOn, Mail, Phone, Remark) VALUES('laoma', 0, '2019-05-16', 'ss@dfs.com', '333', '3333');
INSERT 0 1
#查看表
myserver=> \d
关联列表
架构模式 | 名称 | 型别 | 拥有者
----------+-----------------+--------+--------
public | userinfo | 资料表 | dbuser
public | userinfo_id_seq | 序列数 | dbuser
(2 行记录)
#查看表中数据
myserver=> select * from userinfo;
id | username | del | subon | mail | phone | remark
----+----------+-----+------------+------------+-------+--------
1 | laoma | 0 | 2019-05-16 | ss@dfs.com | 333 | 3333
(1 行记录)
myserver=> select * from userinfo_id_seq;
last_value | log_cnt | is_called
------------+---------+-----------
1 | 32 | t
(1 行记录)
2. 8退出
\q命令退出控制台
ctrl+D
3远程连接
客户端工具
pgAdmin Navicat
3.1 配置远程访问
[root@gitlab_server ~]# find / -name postgresql*
/run/postgresql
/etc/selinux/targeted/active/modules/100/postgresql
/etc/selinux/targeted/tmp/modules/100/postgresql
/etc/systemd/system/multi-user.target.wants/postgresql-15.service
/etc/ld.so.conf.d/postgresql-pgdg-libs.conf
/etc/pam.d/postgresql
/var/lib/pgsql/15
/usr/bin/postgresql-15-setup
/usr/lib/systemd/system/postgresql-15.service
/usr/lib/tmpfiles.d/postgresql-15.conf
/usr/lib/firewalld/services/postgresql.xml
/usr/share/doc/postgresql-libs-9.2.24
/usr/share/doc/postgresql-9.2.24
/usr/share/doc/postgresql15-15.3
/usr/pgsql-15
/logs/postgresql
3.2允许所有地址访问
- postgresql.conf
[root@gitlab_server ~]# cd /var/lib/pgsql/15/data/
[root@gitlab_server data]# cp postgresql.conf postgresql.conf_20230518
[root@gitlab_server data]# vim postgresql.conf
监听端口注释打开.并修改为*
[root@gitlab_server data]# cat postgresql.conf | grep 'listen_addresses'
listen_addresses = '*' # what IP address(es) to listen on;
- pg_hba.conf
[root@gitlab_server data]# cp pg_hba.conf pg_hba.conf_20230518
[root@gitlab_server data]# vim pg_hba.conf
#ipv4
host all all 0.0.0.0/0 trust
重启
[root@gitlab_server ~]# systemctl restart postgresql-15
客户端连接
连接报错
错误:字段"datlastsysoid"不存在 LINE 1: SELECT DISTINCT datlastsysoid FROM pg. database
15版本中系统已经删除该表
参考 https://blog.csdn.net/weixin_47308871/article/details/126652017
