环境
CentOS 7 x64
PostgreSQL 11.1
OS参数配置
内核参数的配置，这里有一篇文章写的很详细，建议阅读。
sysctl参数
#### 修改系统参数
vi /etc/sysctl.conf
# 修改下列配置(参数根据自己情况修改)
kernel.shmmax = 500000000
kernel.shmmni = 4096
kernel.shmall = 4000000000
kernel.sem = 500 1024000 200 4096
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_forward = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.all.arp_filter = 1
net.ipv4.ip_local_port_range = 1025 65535
net.core.netdev_max_backlog = 10000
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152
vm.overcommit_memory = 2
#### 修改完毕后重新载入
sysctl -p
limits参数
#### 修改文件打开数等限制
vi /etc/security/limits.conf
# 添加如下几行（注意*也需要添加）
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
####
vi /etc/security/limits.d/90-nproc.conf
# 添加如下几行（注意*也需要添加）
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
关闭防火墙
#### 关闭防火墙
chkconfig iptables off
service iptables stop
systemctl stop firewalld
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
#### 检查修改后的结果
sestatus
安装数据库
因为安装过程涉及到新建用户等操作，所以这里使用root用户(或sudo)来进行准备工作。
建立OS用户
useradd postgres
yum安装
#### 先安装RPM库
yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
#### 安装服务端和客户端
yum install -y postgresql11 postgresql11-server
设置目录权限
mkdir -p /data/pgsql/data
mkdir -p /data/pgsql/log
chown -R postgres.postgres /data/pgsql /usr/pgsql-11/
初始化数据库
后续的设置使用前面新建的postgres用户操作
用户环境变量
su - postgres
#### 修改环境变量
vi ~/.bash_profile
# 增加如下内容
export PG_HOME=/usr/pgsql-11
export PGDATA=/data/pgsql/data
export PATH=${PG_HOME}/bin:$PATH
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=postgres
#### 重新载入环境变量
source ~/.bash_profile
初始化并修改参数
#### 使用默认地址${PGDATA}初始化
${PG_HOME}/bin/initdb -D ${PGDATA} -E utf8
#### 设置监听IP和Port
vi ${PGDATA}/postgresql.conf
# 设置监听所有IP(这是一个非常宽松的限制，生产环境慎用)
listen_addresses = '*'
port = 5432
#### 设置数据库白名单
vi ${PGDATA}/pg_hba.conf
# 增加如下一条记录，允许用户密码模式(这是一个非常宽松的限制，生产环境慎用)
host all all 0.0.0.0/0 md5
启动停止命令
这里的启停命令都是用postgres用户执行的。
PS：在生产系统上，我们建议使用系统服务的方式启动，详见日常运维部分。
#### 启动
${PG_HOME}/bin/pg_ctl start
#### 停止
${PG_HOME}/bin/pg_ctl stop
#### 重新载入配置文件(不需要重启)
${PG_HOME}/bin/pg_ctl reload
访问数据库
命令行连接
# 使用postgres用户作为超级用户登录
psql -U postgres -d postgres
-- 建立管理员账号进行日程操作，避免使用内置超级用户
create user admin superuser password 'xxx';
新建DB和USER
-- 新建一个测试用用户
create user demo with password 'demo';
-- 新建一个测试用DB并分配给指定用户
create database demo with encoding='utf8' owner=demo;
-- 修改用户密码
alter user demo password 'xxx';
创建只读用户
在GP下创建一个只读用户的流程是
- 建立用户
- 赋予给定DB的连接权限
- 赋予给定Schema的使用权限
- 赋予给定Table的访问权限
备注：因为我们想要精确控制，所以这里使用的是针对每一个表的显式授权。如果要求不那么严格，可以使用默认授权让新建的表自动获得访问权限。
-- 新建一个测试用用户(gpadmin用户执行)
drop user if exists demoread;
create user demoread password 'demo';
-- 赋予、取消给定DB的连接权限(DB所有者执行，这里是demo用户执行)
grant connect on database demo to demoread;
revoke connect on database demo from demoread;
-- Schema和Table的权限操作用下面语句生成，然后执行
-- (DB所有者执行，这里是demo用户执行)
-- 构造对指定schema下所有表的grant、revoke语句
select
'grant usage on schema ' || t.table_schema || ' to ' || u.uname || ' ;' as schema_grant,
'revoke usage on schema ' || t.table_schema || ' from ' || u.uname || ' ;' as schema_revoke,
'grant select on ' || t.table_schema || '.' || t.table_name || ' to ' || u.uname || ' ;' as table_grant,
'revoke select on ' || t.table_schema || '.' || t.table_name || ' from ' || u.uname || ' ;' as table_revoke
from information_schema.tables t,
(select 'demoread' as uname) as u -- 提供：给定被授权用户名
where t.table_type in ('BASE TABLE', 'VIEW', 'FOREIGN')
and t.table_schema in ('s01','s02') -- 提供：授权schema
order by t.table_schema, t.table_name
;
日常运维
启动服务
因为在初始化数据库的时候我们使用了自定义数据目录，所以在注册服务前我们需要修改默认的地址
### 修改服务中的地址
vi /usr/lib/systemd/system/postgresql-11.service
# 修改Environment=PGDATA=/var/lib/pgsql/11/data/为
Environment=PGDATA=/data/pgsql/data/
### 注册服务并启动
systemctl daemon-reload
systemctl enable postgresql-11
systemctl start postgresql-11
完全卸载
###
systemctl stop postgresql-11
systemctl disable postgresql-11
###
yum remove -y postgresql11 postgresql11-server
###
rm -rf /usr/pgsql-11/ /var/lib/pgsql/ /data/pgsql/
###
userdel -r postgres
备份恢复
修改数据目录
如果在安装中使用的是默认目录，然后需要修改数据目录，执行下列操作
### 关闭数据库
systemctl stop postgresql-11
### 移动目录
mv /var/lib/pgsql/11/* /data/pgsql/
### 修改服务中的地址
vi /usr/lib/systemd/system/postgresql-11.service
### 修改.bash_profile
export PGDATA=/data/pgsql/data
### 注册服务并启动
systemctl daemon-reload
systemctl start postgresql-11
