PostgreSQL简介

发表于 | 阅读次数：
本文字数： 5.3k | 阅读时长 ≈ 5 分钟

简介

PostgreSQL 是一个功能强大、特性丰富、结构复杂的开源关系型数据库，它的起源可以追溯到1986年，当时它是加州大学伯克利分校 POSTGRES 项目的一部分。

安装

macOS安装

  1. 使用 HomeBrew 安装

    默认的版本可能会比较低，可以下载安装包进行安装，参考第二种方式。

    1
    2
    3
    4
    5
    6
    7
    $ brew search postgresql
    ==> Formulae
    postgresql      postgresql@10   postgresql@9.4  postgresql@9.5  postgresql@9.6

    ==> Casks
    navicat-for-postgresql                   navicat-for-postgresql
    $ brew install postgresql@10

  2. 下载 Postgres.app 再安装

    参考 postgresapp 官网

Windows安装

进入 下载地址，选择相应的版本下载安装即可。

CentOS安装

  1. 通过 yum 进行安装

    详细可参考 Linux下的安装

    1
    2
    3
    4
    5
    6
    $ yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    $ yum install postgresql11
    $ yum install postgresql11-server # 可选
    $ /usr/pgsql-11/bin/postgresql-11-setup initdb # 初始化，可选
    $ systemctl enable postgresql-11 # 允许自启动，可选
    $ systemctl start postgresql-11 # 启动

  2. 下载 rpm 包安装

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # 安装相应PG版本
    $ rpm -ivh pgdg-centos-XXX.rpm
    # 更新库
    $ yum update
    # 安装相应的版本，包括常用的pgadmin工具
    $ yum --enablerepo=pgdgXX install postgresqlXX-server pgadmin3_XX postgresqlXX-contrib
    # 配置PATH环境变量，并使之生效
    $ cat /etc/profile
    $ source /etc/profile

源码安装

直接从官网 PostgreSQL 下载相应版本的源码包。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 新建PG专用的用户
$ groupadd postgres
$ useradd -g postgres postgres
# 解压编译
$ tar -xf postgresql-X.X.X.tar.bz2 && cd postgresql-X.X.X
$ ./configure --prefix=/opt/postgre
$ make
$ make install
# 编译contrib目录下的一些工具
$ cd contrib && make
$ make install
# 绑定数据库文件存储目录
$ export PGDATA=/home/xxx/Workspace/databases/postgre/data
$ /opt/postgre/bin/initdb -D $PGDATA
# 启动、停止数据库
$ /opt/postgre/bin/pg_ctl -D $PGDATA -l logfile start
$ /opt/postgre/bin/pg_ctl -D $PGDATA -l logfile stop

添加用户和数据库

初次安装后，默认生成一个名为 postgres 的数据库和一个名为 postgres 的数据库用户，同时还生成了一个名为 postgres 的 Linux 系统用户。

使用PostgreSQL控制台新增用户

1
2
3
4
5
6
7
8
9
$ sudo adduser newdbuser # 新建一个Linux新用户
$ sudo su - postgres # 切换用户
$ psql # 登录PostgreSQL控制台,相当于系统用户postgres以同名数据库用户的身份，登录数据库，不用输入密码
$ # 为postgres用户设置一个密码
postgres=# \password postgres 
postgres=# CREATE USER newdbuser WITH PASSWORD 'password'; -- 创建数据库用户newdbuser
postgres=# CREATE DATABASE newdb OWNER newdbuser; -- 创建用户数据库newdb，并指定所有者为newdbuser。
postgres=# GRANT ALL PRIVILEGES ON DATABASE newdb to newdbuser;-- 将newdb数据库的所有权限都赋予newdbuser
postgres=# \q

使用shell命令行新增用户

1
2
3
4
5
6
$ sudo -u postgres createuser --superuser newdbuser # 创建数据库用户 newdbuser，并指定其为超级用户
$ sudo -u postgres psql # 登录数据库控制台
$ # 设置 newdbuser 用户的密码，完成后退出控制台
postgres=# \password newdbuser 
postgres=# \q
$ sudo -u postgres createdb -O newdbuser newdb # 创建数据库 newdb，并指定所有者为 newdbuser

控制台常见命令

1
2
3
4
5
6
7
8
9
\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。

基本数据库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 创建新表
CREATE TABLE xxx_tb(name VARCHAR(20), create_date DATE);
# 插入数据
INSERT INTO xxx_tb(name, create_date) VALUES('xxx', '2019-09-25');
# 查询记录
SELECT * FROM xxx_tb;
# 更新记录
UPDATE xxx_tb set name = 'yyy' WHERE name = 'xxx';
# 删除记录
DELETE FROM xxx_tb WHERE name = 'yyy' ;
# 添加表列
ALTER TABLE xxx_tb ADD mobile_phone VARCHAR(11);
# 更新表结构
ALTER TABLE xxx_tb ALTER COLUMN create_date SET NOT NULL;
# 更改列名
ALTER TABLE xxx_tb RENAME COLUMN create_date TO create;
# 删除列
ALTER TABLE xxx_tb DROP COLUMN mobile_phone;
# 更改表名
ALTER TABLE xxx_tb RENAME TO backup_xxx_tb;
# 删除表
DROP TABLE IF EXISTS backup_xxx_tb;

日志查看

PostgreSQL 日志分为三类，分别是 pg_log、pg_xlog 和 pg_clog，一般保存在 $PGDATA 对应的目录下。

  1. pg_log —— 数据库运行日志，默认开启，可以通过配置 $PGDATA/postgresql.conf 。

    这个日志一般是记录服务器与DB的状态，比如各种Error信息，定位慢查询SQL，数据库的启动关闭信息，发生checkpoint过于频繁等的告警信息，诸如此类。linux自带的路径一般在 /var/log/postgres 下面。该日志有.csv格式和.log。个人建议用前一种，因为一般会按大小和时间自动切割，毕竟查看一个巨大的日志文件比查看不同时间段的多个日志要难得多。另外这种日志是可以被清理删除，压缩打包或者转移，同时并不影响DB的正常运行。当我们有遇到DB无法启动或者更改参数没有生效时，第一个想到的就是查看这个日志。

  2. pg_xlog —— WAL日志，强制开启。

    记录的 PostgreSQL 的 WAL 信息，也就是一些事务日志信息(transaction log)，默认单个大小是16M，源码安装的时候可以更改其大小。这些信息通常名字是类似’000000010000000000000013’这样的文件，这些日志会在定时回滚恢复(PITR)，流复制(Replication Stream)以及归档时能被用到，这些日志是非常重要的，记录着数据库发生的各种事务信息，不得随意删除或者移动这类日志文件，不然你的数据库会有无法恢复的风险

    当你的归档或者流复制发生异常的时候，事务日志会不断地生成，有可能会造成你的磁盘空间被塞满，最终导致DB挂掉或者起不来。遇到这种情况不用慌，可以先关闭归档或者流复制功能，备份 pg_xlog 日志到其他地方，但请不要删除。然后删除较早时间的的 pg_xlog，有一定空间后再试着启动 Postgres。

  3. pg_clog —— 事务提交日志，记录事务的元数据，强制开启。

    pg_clog 这个文件也是事务日志文件，但与 pg_xlog 不同的是它记录的是事务的元数据(metadata)，这个日志告诉我们哪些事务完成了，哪些没有完成。这个日志文件一般非常小，但是重要性也是相当高，不得随意删除或者对其更改信息。

配置文件

*pg_hba.conf *—— 客户端访问和认证受到标准的 PostgreSQL 的基于主机的认证文件的控制。其中的认证文件即 是 pg_hba.conf。一般格式是一组记录，每行一个。一个记录由多个被空格或者制表符分隔的域组成。如果域值被加上引号，域可以包含空格。记录不能跨越多行。配置的具体含义可以参考 pg_hba.conf文件说明与配置。

postgresql.conf —— 配置文件主要影响这服务器实例的基本行为，比如允许的连接数，操作允许占用的最大内存数，指定哪些用户可以用何种方式连接到数据库等等。当然这一切在数据库安装好时都有一个默认值，但是如果你需要对你的数据库进行定制的话，可以对这些数值进行符合需求的修改。配置的具体含义可以参考 PostgreSQL 配置文件postgresql.conf。

防火墙、SELinux 设置

PostgreSQL 默认使用 5432 端口，也可以在 postgresql.conf 文件中设置，可以使用如下命令开启防火墙端口。

1
2
3
4
5
6
# 暂时有效
$ firewall-cmd --add-port=5432/tcp
# 永久生效
$ firewall-cmd --permanent --add-port=5432/tcp
# 在iptables中开启
$ iptables -A INPUT -p tcp --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

如果开启了 SELinux 服务，可能会在使用中遇到各种各样的权限问题。

1
2
3
4
5
6
# 修改数据库的存放位置，必须添加一些新上下文来匹配新位置
$ semanage fcontext -a -t postgresql_db_t "/new/location(/.*)?"
# 默认端口不起作用，需要匹配postgre的端口类型为你想要的端口
$ semanage port -a -t postgresql_port_t -p tcp 5433
# 如果APP需要通过TCP/IP与PG交互，你需要告诉SELinux允许这个操作
$ setsebool -P httpd_can_network_connect_db on

其他

PostgreSQL 诞生于学院派，没有局限于当时的一些硬件条件，MySQL 发展自程序员的业余作品，为当时的小内存小外存的硬件条件做了特殊的处理，随着硬件的不断发展，这些处理在现在看来倒成了一个缺点。

MariaDB 由 MySQL 的创始人 Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司 MySQL AB 卖给了SUN，此后，随着SUN被甲骨文收购，MySQL 的所有权也落入 Oracle 的手中。MariaDB 名称来自 Michael Widenius 的女儿 Maria 的名字。MariaDB 发展势头也很不错，对应版本和 MySQL 也基本兼容。