南山草舍

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