PostgreSQL_11.1_安裝和基礎配置

wait4friend發表於2019-01-04
SQL

環境

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下建立一個只讀使用者的流程是

  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
複製程式碼

相關文章