0%

源码安装PostgreSQL数据库

安装PG数据库

配置基础环境

安装依赖包

1
yum install -y bison flex readline readline-devel  zlib-devel gcc gcc-c++ make wget

新建postgres用户,并设置密码

1
2
useradd postgres
passwd postgres

新建目录

1
2
3
4
5
mkdir -p /usr/local/pg12
mkdir -p /pgdata/12/data
chown -R postgres. /pgdata
chown -R postgres. /usr/local/pg12/
chmod 700 /pgdata/12/data/ -R

修改系统参数

vim /etc/sysctl.conf

1
2
3
4
5
6
7
8
9
10
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

vim /etc/security/limits.conf

1
2
3
4
5
6
7
8
* soft   nofile    131072
* hard nofile 131072
* soft nproc 131072
* hard nproc 131072
* soft core unlimited
* hard core unlimited
* hard memlock 50000000
* soft memlock 50000000

sysctl -p

安装PG数据库

下载源码包,编译安装数据库

wget https://ftp.postgresql.org/pub/source/v12.6/postgresql-12.6.tar.gz

tar xf postgresql-12.6.tar.gz
cd /usr/local/src/postgresql-12.6
./configure --prefix=/usr/local/pg12/
gmake world
gmake install -world

配置环境变量

切换到postgres用户

su - postgres
vim ~/.bash_profile

1
2
3
4
5
6
7
8
export PGDATA=/pgdata/12/data
export LANG=en_US.utf8
export PGHOME=/usr/local/pg12
LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PATH=$PGHOME/bin:$PATH:.

使配置的环境变量生效

source ~/.bash_profile

初始化数据库

简易初始化

1
initdb -D /pgdata/12/data/ -W

建议生产环境初始化命令

1
initdb -A md5 -D $PGDATA -E utf8 --locale=C -W

01

启动、关闭

手动方式

启动:pg_ctl start
重启:pg_ctl restart -mf
关闭:pg_ctl stop -mf

查询数据库版本,检验环境变量是否正确

psql --version

脚本方式

/usr/local/src/postgresql-12.6/contrib/start-scripts/linux

登录连接数据库

1
2
3
4
5
6
7
$ psql --help

-d 数据库
-h 主机名/ip地址
-p 端口号
-U 用户
-W 密码

连接命令

psql -d postgres -h 192.168.111.128 -p 5432 -U postgres

1
2
3
4
新装的pg数据库,默认只允许使用本地socket方式登录,不允许远程登录
psql: error: could not connect to server: Connection refused
Is the server running on host "192.168.111.128" and accepting
TCP/IP connections on port 5432?

修改配置文件,实现远程登录

vim $PGDATA/pg_hba.conf

1
2
# IPv4 local connections:
host all all 192.168.111.0/24 md5

vim $PGDATA/postgresql.conf

1
2
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'

01

用户管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
--获取帮助
postgres=# \help create user
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid

URL: https://www.postgresql.org/docs/12/sql-createuser.html

postgres=# create user user1 with PASSWORD '1'; --有login权限
CREATE ROLE
postgres=# create role user2 with PASSWORD '1'; --无login权限
CREATE ROLE
--查看用户信息
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
user1 | | {}
user2 | Cannot login | {}


--创建一个超级用户user1
postgres=# create user user1 with SUPERUSER PASSWORD '111111';
CREATE ROLE
--创建一个用户user2,允许登录,设置过期时间
postgres=# create user user2 with LOGIN PASSWORD 'user#123' VALID UNTIL '2022-09-30';
CREATE ROLE
--删除用户
postgres=# drop user user1;
DROP ROLE
--修改用户密码
postgres=# alter user user2 with password '1';
ALTER ROLE

权限管理

权限级别

  • cluster权限:实例权限通过pg_hba.conf配置。
  • database权限: 数据库权限通过grant和revoke操作schema配置。
  • TBS权限:表空间权限通过grant和revoke操作表、物化视图、索引、临时表配置。
  • schema权限:模式权限通过grant和revoke操作模式下的对象配置。
  • object权限:对象权限通过grant和revoke配置

权限的定义

  • database权限设置
1
GRANT create ON DATABASE db1 to user1;
  • schema权限配置
1
2
ALTER SCHEMA abc OWNER to user1;
GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA abc to user1;
  • object权限设置
1
GRANT select,insert,update,delete ON abc.tb1 TO USER1;

案例:创建一个业务用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
postgres=# create database db1;
CREATE DATABASE
postgres=# \c db1;
You are now connected to database "db1" as user "postgres".
db1=# create schema abc;
CREATE SCHEMA
db1=# create user u1 with login password '111';
CREATE ROLE
db1=# alter schema abc owner to u1;
ALTER SCHEMA
db1=# GRANT select,insert,delete,update ON ALL TABLES IN SCHEMA abc to u1;
GRANT

# 设置一个用户A可以使用用户B的数据
GRANT USAGE ON SCHEMA USERB to USERA;

#切换schema
set search_path to [new_schema]

文档根据视频整理:https://www.bilibili.com/video/BV1WQ4y1f76h