PostgreSQL（一） 系统架构介绍 优点 基础 源码包安装 设置开机自启

1. PostgreSQL 介绍

1.1 基本概念

   PostgreSQL是美国加州大学伯克利分校计算机系开发的一个对象关系型数据库管理系统。PostgreSQL 是一个自由的对象-关系数据库服务器，它在灵活的 BSD-风格许可证下发行。BSD协议是一个对商业集成很友好的协议，任何人都可以以任何目地免费使用、修改、分发PostgreSQL，不管是私用、商用，还是学术研究使用。因此PostgreSQL在其他开放源代码数据库系统（比如 MySQL 和 Firebird），和专有系统（ Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server）之外，为用户又提供了一种选择。

1.2 特性优点

PostgreSQL 支持几乎所有 SQL 构件（包括子查询，事务和用户定 义类型和函数）， 并且可以获得非常广阔范围的（开发）语言绑定 （包括C、C++、Java、perl、tcl和 python）。具体的优点特性如下：

( 1 ).PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99，是目前世界上支持最丰富的数据类型的数据库。

( 2 ).PostgreSQL 是全功能的自由软件数据库，PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统、数据完整性检查等特性的一种自由软件的数据库管理系统。

( 3 ).PostgreSQL 采用的是比较经典的 C/S （client/server）结构，即一个客户端对应一个服务器端守护进程的模式，这个守护进程分析客户端来的查询请求，生成规划树，进行数据检索并最终把结果格式化输出后返回给客户端。

( 4 ).PostgreSQL 对接口的支持也是非常丰富的，几乎支持所有类型的数据库客户端接口。

2. 系统架构

PostgreSQL数据库由连接管理系统（系统控制器）、编译执行系统、存储管理系统、事务系统、系统表五大部分组成，其组成结构和关系如图1-1所示。

( 1 ).连接管理系统接受外部操作对系统的请求，对操作请求进行预处理和分发，起系统逻辑控制作用。

( 2 ).编译执行系统由查询编译器、查询执行器组成，完成操作请求在数据库中的分析处理和转化工作，最终实现物理存储介质中数据的操作。

( 3 ).存储管理系统由索引管理器、内存管理器、外存管理器组成，负责存储和管理物理数据，提供对编译查询系统的支持。

( 4 ).事务系统由事务管理器、日志管理器、并发控制、锁管理器组成，日志管理器和事务管理器完成对操作请求处理的事务一致性支持，锁管理器和并发控制提供对并发访问数据的一致性支持。

( 5 ).系统表是PostgreSQL数据库的元信息管理中心，包括数据库对象信息和数据库管理控制信息。系统表管理元数据信息，将PostgreSQL数据库的各个模块有机地连接在一起，形成一个高效的数据管理系统。

图1-1 PostgreSQL 系统架构

 

3. 源码安装

3.1 环境准备

3.1.1 关闭selinux

vi /etc/selinux/config
SELINUX="" 改为disabled (永久关闭)

3.1.2 关闭防火墙

chkconfig iptables off
systemctl disable firewalld

3.1.3 安装相关依赖包

# 安装gcc
yum -y install gcc
# 安装zlib
yum -y install zlib-devel
# 安装 readline
yum -y install readline-devel

3.1.4 添加用户和组

# 添加postgres组
groupadd postgres
# 添加postgres用户
useradd -g postgres postgres
# 设置postgres用户的密码
passwd postgres

3.1.5 配置环境变量

vi /etc/profile
# 末尾添加下面：
export PGHOME=/usr/local/pgsql
export PGDATA=$PGHOME/data
PATH=$PATH:$PGHOME/bin
# 执行生效：
source /etc/profile

 

3.2 安装部署

3.2.1 创建相关路径

mkdir /usr/local/pgsql/
mkdir /usr/local/pgsql/data
mkdir /usr/local/pgsql/log

3.2.2 解压安装

# 解压postgresql-10.5.tar.gz到任意路径下
tar -zxvf postgresql-10.5.tar.gz
cd postgresql-10.5

# ./configure 命令是用来检查当前环境能否安装postgresql
./configure --prefix=/usr/local/pgsql
make
make install

# 设置/usr/local/pgsql/目录权限给用户postgres
chown -R postgres:postgres /usr/local/pgsql/

# 切换用户为postgres
su - postgres

# 初始化数据库
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

# 先根据需求修改相关配置文件(文件在/usr/local/pgsql/data目录下)：
postgresql.conf(配置文件)：需要修改listen_addresses='*'
和pg_hba.conf(客户端身份验证配置文件)。

# 启动数据库
/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data

# 先根据需求修改相关配置文件    
# 设置postgres role的初始密码，自定义密码
su - postgres && psql
alter role postgres with password 'postgres';

 

3.3 设置开机自启

# 切换root用户 
# 将pg的启动脚本示例拷贝一份到/etc/init.d 目录下并且重命名为postgresql
cp /home/postgresql-10.5/contrib/start-scripts/linux /etc/init.d/postgresql

# 修改脚本中的相关参数：
vi /etc/init.d/postgresql
# 内容修改如下：
--------------------------------------------------------------
## EDIT FROM HERE
# Installation prefix 
prefix=/usr/local/pgsql
# Data directory
PGDATA="/usr/local/pgsql/data"
# Who to run the postmaster as, usually "postgres".  (NOT "root")
PGUSER=postgres 
# Where to keep a log file
PGLOG=/usr/local/pgsql/log/pg_server.log
--------------------------------------------------------------

# 为所有用户添加脚本的可执行权限：
chmod a+x /etc/init.d/postgresql

# 最后通过chkconfig命令将该脚本注册为开机启动
chkconfig --add postgresql

 

3.4 常用参数说明

         以下是一些常用参数，可以根据需求进行自定义设置(还有很多参数选择默认即可)

3.4.1 连接相关参数

listen_addresses：服务器监听TCP/IP地址。默认为localhost,表示只能本地连接。建议配置为’*’或’0.0.0.0’，表示监听本机上的所有IP。如果需要更详细的配置，需要修改pg_hba.conf配置文件。

port：监听的端口，默认为5432。

max_connections：数据库允许的最大并发连接数。默认为100，根据需求自定义设定。

superuser_reserved_connections:超级用户预保留的连接数，默认为3。

3.4.2 内存相关参数

shared_buffers：用于共享缓冲区的内存。

maintenance_work_mem：影响vacuum,analyze,create index,reindex命令执行效率。

work_mem：内部sort和hash操作需要使用的内存量。

effective_cache_size：优化器假设查询可以使用的最大内存。

wal_buffers：WAL(预写入日志)数据的共享内存量。

autovacuum：自动执行vaccum，默认为on。当数据写入频率高时，会占用较多的内存，如果对查询性能要求高，可以设为off，在空闲时间手动执行vaccum。

3.4.3 日志相关参数

logging_collector：否开启日志收集，默认为off；开启之后其他日志参数才会生效。

log_rotation_size：日志文件大小，默认为10MB。建议设置50MB。

log_truncate_on_rotation：当日志文件已经存在时，如果为off,新生成的日志将在尾部追加，如果为on,则会覆盖原来的日志。建议设置为off。

log_destination：日志文件格式，默认为stderr(标准错误)，建议使用csvlog。

log_min_duration_statement：记录执行慢的sql,默认为-1，在性能测试的时候建议开启该参数，设置为1000代表1000ms。

log_statement：记录相关sql语句，默认为none。总共有4种类型：none,ddl,mod,all。none表示不记录；ddl记录所有数据定义命令，比如CREATE,ALTER和DROP语句；mod记录所有dml语句，加上数据修改语句INSERT,UPDATE等；all记录所有执行的语句。

 

4. 基本使用

4.1 psql工具

psql是PostgreSQL的一个命令行交互式客户端工具，通过psql来连接数据库，默认的用户和数据库是postgres

命令行样例:

psql –U username –d databasename

如：psql –U postgres –d postgres

表4-1  psql常用命令

命令	说明
\l	查看数据库列表
\c 库名	切换数据库至指定库
\du	查看用户列表
\d	查看表列表
\d 表名	查看指定表的结构
\di	查看索引
\dv	查看视图
\dx	查看函数
\q	退出psql

 

4.2 常用sql语句

-- 创建新表
CREATE TABLE user (name VARCHAR(20), createdate  DATE);

-- 插入数据
INSERT INTO user (name, createdate) VALUES('张三', '2016-07-22');

-- 选择记录
SELECT * FROM user;

-- 更新数据
UPDATE user set name = '李四' WHERE name = '张三';

-- 删除记录
DELETE FROM user WHERE name = '李四' ;

-- 添加栏位
ALTER TABLE user ADD email VARCHAR(40);

-- 更新结构
ALTER TABLE user ALTER COLUMN createdate SET NOT NULL;

-- 更名栏位
ALTER TABLE user RENAME  COLUMN createdate TO  cdate;

-- 删除栏位
ALTER TABLE user DROP COLUMN email;

-- 表格更名
ALTER TABLE user RENAME TO  backup;

-- 删除表格
DROP TABLE IF  EXISTS backup;

 

4.3 常用系统表和视图

    系统表的概念在上面已经说清楚了，使用系统表能够帮助我们更好的查看数据库的相关信息; 系统视图则提供了查询系统表的一些便利的访问方法，表的具体信息可以通过\d 表名来查看。

    (1). 常用系统表

    pg_database  存储当前数据集簇中数据库的信息。

    pg_class      存储了数据表、索引、序列、视图、复合类型和一些特殊关系类型的元数据。

    pg_attribute 存储所有表(包括系统表，如pg_class)的字段信息。

    pg_index      存储索引的具体信息。

    (2). 常用系统视图

    pg_indexes        索引的相关信息

    pg_stat_activity  系统进程的信息，显示与当前会话的活动进程的一些信息。

    pg_tables         数据表的相关信息

    pg_settings       数据库参数设置相关信息