PostgreSQL_11.1_安装和基础配置

#### 修改系统参数
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下创建一个只读用户的流程是

1. 建立用户
2. 赋予给定DB的连接权限
3. 赋予给定Schema的使用权限
4. 赋予给定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
复制代码