如何安装
RPM包安装
直接下载旧版本rpm安装(比如9.5),官网下载：https://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/repoview/
#按顺序安装
yum localinstall postgresql95-libs-9.5.25-1PGDG.rhel7
yum localinstall postgresql95-9.5.25-1PGDG.rhel7
yum localinstall postgresql95-server-9.5.25-1PGDG.rhel7
yum localinstall postgresql95-contrib-9.5.25-1PGDG.rhel7
#修改数据库存放目录
mkdir -p /usr/local/pgsql/data
#改变属主
chown -R postgres:postgres /usr/local/pgsql/9.5/data
#修改systemctl启动文件
vim /usr/lib/systemd/system/postgresql-9.5.service
# Environment=PGDATA=/var/lib/pgsql/9.5/data/ #注释此行
Environment=PGDATA=/usr/local/pgsql/9.5/data #新增此行
#初始化数据库
/usr/pgsql-9.5/bin/postgresql95-setup initdb
#启动数据库
systemctl enable postgresql-9.5 #开启启动
systemctl start postgresql-9.5
PostgreSQL Yum Repository
# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Install PostgreSQL:
sudo yum install -y postgresql96-server
# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
sudo systemctl enable postgresql-9.6
sudo systemctl start postgresql-9.6
源码安装
wget https://ftp.postgresql.org/pub/source/v9.6.21/postgresql-9.6.21.tar.gz
tar zxvf postgresql-9.6.21.tar.gz
#安装配置
./configure --prefix=/usr/local/pgsql --with-perl --with-python --with-libxml --with-libxslt
#编译安装
make && make install、
# 添加用户 postgres
useradd postgres
# 创建数据目录
mkdir -p /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
#初始化数据库
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
#启动数据库服务
/usr/local/pgsql/pg_ctl -D /usr/local/pgsql/data
数据库配置
pg_hba.conf配置参数
# 相同的规则，但是使用本地环回 TCP/IP 连接。
# TYPE DATABASE USER ADDRESS METHOD
host all all 127.0.0.1/32 trust
# 和前一行相同，但是使用了一个独立的掩码列
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all 127.0.0.1 255.255.255.255 trust
# IPv6 上相同的规则
# TYPE DATABASE USER ADDRESS METHOD
host all all ::1/128 trust
# 使用主机名的相同规则（通常同时覆盖 IPv4 和 IPv6）。
# TYPE DATABASE USER ADDRESS METHOD
host all all localhost trust
# 允许来自任意具有 IP 地址
# 192.168.93.x 的主机上任意
# 用户以 ident 为该连接所
# 报告的相同用户名连接到
# 数据库 "postgres"
#（通常是操作系统用户名）。
# TYPE DATABASE USER ADDRESS METHOD
host postgres all 192.168.93.0/24 ident
# 如果用户的口令被正确提供，
# 允许来自主机 192.168.12.10
# 的任意用户连接到数据库 "postgres"。
#
# TYPE DATABASE USER ADDRESS METHOD
host postgres all 192.168.12.10/32 md5
postgresql.conf配置参数
常用命令
登陆数据库
# 登陆数据库(本机postgres免密登陆)
-bash-4.2$ psql
psql (10.5)
Type "help" for help.
# 指定账号数据库登陆
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
# 详细参数 参见 psql --help
# 查看数据库
postgres=# \l
# 查看数据库版本
postgres=# select version();
# 退出数据库
postgres=# \q
# 命令指南
postgres=# \?
# 命令详细
postgres=# \h
数据库查看
#切换到dbname库， dbuser用户
postgres=# \c dbname dbuser
#查看数据库的对象
dbname=#\d
#查看指定表对象
dbname=#\d table_name
用户角色数据库模式表空间
用户与角色
用户和角色对PostgresSQL来说是完全相同的两个对象
CREATE ROLE kanon PASSWORD 'kanon'; //默认没有登陆权限
CREATE ROLE kanon PASSWORD 'kanon' LOGIN
#等同于
CREATE USER kanon PASSWORD 'kanon'
#用户密码
ALTER USER kanon WITH PASSWORD 'kanon';
#删除用户
drop user kanon;
创建只读账号
1.创建只读用户
create user readonly with password 'readonly';
#指定只读用户的搜索路径（默认查询的模式）
alter user readonly set search_path='kannon';
2.设置只读
alter user readonly set default_transaction_read_only=on;
3.授权
#授权模式使用权限
grant usage on schema kannon to readonly;
#授权select 权限
grant select on all tables in schema kannon to readonly;
#授权系列(可选)
grant select on all sequences in schema kannon to readonly;
#授权默认权限（后续增加的表也有权限）
alter default privileges in schema kannon grant select on tables to readonly;
数据库与模式
模式（schema）是对数据库（database）逻辑分割，相当于名称空间
通过模式可以实现权限分离，模式间对象共享
在数据库创建的同时，就已经默认为数据库创建了一个模式--public，这也是该数据库的默认模式。所有为此数据库创建的对象（表、函数、试图、索引、序列等）都是常见在这个模式中的。
一般来说创建一个具体数据库会创建一个与用户名相同的模式
CREATE SCHEMA kanon OWNER kanon;
#删除模式
drop scheam kanon;
表空间和数据库
CREATE DATABASE dbname 默认的数据库所有者是当前创建数据库的角色，默认的模板是template1, template1默认的表空间是系统的默认表空间--pg_default
#完整创建数据库的命令
CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename;
表空间是一个存储区域，在一个表空间中可以存储多个数据库
创建表也可以指定表空间，将表的数据存在一个较慢的磁盘上的表空间，而将表的索引存在于一个快速的磁盘上的表空间
表空间和数据库是多对多的关系
#修改数据库owner
alter database dbname owner to dbuser;
#删除数据库
drop database dbname;
#创建表空间（超级用户创建）
CREATE TABLESPACE tskanon OWNER kanon LOCATION '/tmp/data/tskanon';
#表空间授权使用
grant create on tablespace tskanon to otheruser
#解除授权
revoke create on tablespace tskanon from otheruser
#删除表空间
drop tablespace tskanon;
#创建表指定表空间
CREATE TABLE tb_kanon(name varchar(32)) tablespace tskanon;
备份还原
pg_dump命令备份（逻辑备份）
#命令帮助
pg_dump --help
#备份指定数据库
pg_dump -p 5432 -U postgres "test" > /tmp/test.sql
#备份指定表
pg_dump -p 5432 -U postgres -t test1 "test" > /tmp/test.sql
pgsql命令恢复（逻辑恢复）
psql -p 5432 -U postgres -d test < /tmp/test.sql
pg_basebackup(物理备份)
#基础备份到/cache1/backup 目录
pg_basebackup -D /cache1/backup -Ft -Xs -P -z -R -h 127.0.0.1 -U repuser
pg_basebackup(物理还原)
1.删除数据库data目录里的所有文件
2.解压备份文件到至数据库data目录
tar -zxvf base.tar.gz -C ../data/
3.复制pg_wal目录下的日志文件到archive目录下（/cache1/archive_log）
4.修改解压后的recovery.conf文件（其实是指定归档的位置，这样就会进行恢复）
restore_command = '/bin/cp /cache1/archive_log/%f %p'
5.启动数据库
service postgresql-9.5 start
快速备份表
create table test_bak as select * from test;
流复制
流复制方案
1.修改主库的配置文件
vi postgresql.conf
#配置修改为：
wal_level = hot_standby
#synchronous_commit = on（同步复制，-- 实时，如果需要异步改为off）
checkpoint_timeout = 5min
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 2
wal_keep_segments = 16
hot_standby = on
max_standby_archive_delay = 300s
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 1s
hot_standby_feedback = on
wal_receiver_timeout = 60s
vi pg_hba.conf
# 添加IP 可以让备库访问到主库
host replication repuser 192.168.1.200/32 md5
2.创建数据库用户repuser
create role repuser login replication encrypted password '123';
3.备库使用pg_basebackup进行数据库备份恢复
pg_basebackup -Fp -Xs -v -P -D $PGDATA -h 192.168.1.189 -p 5432 -U repuser
4.备库${PGDATA}目录下增加配置文件recovery.conf
vi recovery.conf
recovery_target_timeline='latest'
standby_mode = on
primary_conninfo = 'host=192.168.1.189 port=5432 user=repuser password=repuser'
trigger_file = '/cache1/pgsql/9.5/data/postgresql.trigger'
5.启动备库
systemctl start postgresql-9.5
#ps aux|grep wal
#主库增加一个wal sender process进程，用于日志发送
#备库增加一个wal receiver progress进程，这个是接收wal日志的进程
流复制切断
#在备库/cache1/pgsql/9.5/data目录下创建文件
touch /cache1/pgsql/9.5/data/postgresql.trigger
常见问题
-
查询执行比较久的命令
select pid,usename,query_start,query from pg_stat_activity where query like '%表名称%' order by query_start asc; #强制停止进程 SELECT pg_cancel_backend(pid); -
drop database dbname 失败
#删除数据库的session select 'select pg_terminate_backend('||pid||');' from pg_stat_activity where datname='dbname';
