PostgreSQL12安装及配置

发表于 | 分类于
PostgreSQL安装

前言

PostgreSQL数据库具有以下优势：

  • PostgreSQL数据库是目前功能最强大的开源数据库，它是最接近工业标准SQL92的查询语言，至少实现了SQL:2011标准中要求的179项主要功能中的160项（注：目前没有哪个数据库管理系统能完全实现SQL:2011标准中的所有主要功能）。

  • 稳定可靠：PostgreSQL是唯一能做到数据零丢失的开源数据库。目前有报道称国内外有部分银行使用PostgreSQL数据库。

  • 开源省钱： PostgreSQL数据库是开源的、免费的，而且使用的是类BSD协议，在使用和二次开发上基本没有限制。

  • 支持广泛：PostgreSQL 数据库支持大量的主流开发语言，包括C、C++、Perl、Python、Java、Tcl以及PHP等。

  • PostgreSQL社区活跃：PostgreSQL基本上每3个月推出一个补丁版本，这意味着已知的Bug很快会被修复，有应用场景的需求也会及时得到响应。

相对Mysql，PostgreSQL有以下几个优点：

  • 功能强大：支持所有主流多表连接查询的方式

    • 支持Nest loop
    • 支持Hash JOIN
    • Sort Merge JOIN
    • 支持绝大多数的SQL语法
    • 对正则表达式支持强
    • 内置函数丰富
    • 字段类型支持数组
    • 支持存储过程

  • 性能优化工具与度量信息丰富

    • 有大量的性能视图
    • 方便定位问题
  • 在线操作功能好
    • 增加空值列，在系统表定义，无须对物理结构做更新，可以瞬间完成
    • 支持在线建索引，在创建索引的过程可以不锁更新操作。
  • 从PostgreSQL9.1开始，支持同步复制功能，通过Master和Slave之间的复制可以实现零数据丢失的高可用方案。
  • 可以方便地写插件来扩展PostgreSQL数据库的功能
  • 支持复杂SQL，支持大量的分析函数，适合做数据仓库
  • 空间索引

安装及配置

安装

导入yum源

1
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装PostgreSQL服务

1
sudo yum install -y postgresql12 postgresql12-server

数据库初始化

1
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

启动PostgreSQL服务

1
2
3
4
5
#启动PostgreSQL服务
sudo systemctl start postgresql-12

#设置PostgreSQL服务为开机启动
sudo systemctl enable postgresql-12

初始化后有提示默认路径： /var/lib/pgsql/data

配置

修改数据库用户密码

PostgreSQL安装成功之后，会默认创建一个名为postgres的Linux用户，

初始化数据库后，会有名为postgres的数据库，来存储数据库的基础信息，例如用户信息等等，相当于MySQL中默认的名为mysql数据库。

postgres数据库中会初始化一名超级用户postgres

为了方便我们使用postgres账号进行管理，我们可以修改该账号的密码

1、进入PostgreSQL命令行

通过su命令切换linux用户为postgres会自动进入命令行

1
su postgres

2、启动SQL Shell

1
psql

3、修改密码

1
ALTER USER postgres WITH PASSWORD 'NewPassword';

开启远程连接

1、开放端口

1
2
sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

2、修改IP绑定

1
2
3
4
5
6
#修改配置文件
vi /var/lib/pgsql/12/data/postgresql.conf

#将监听地址修改为*
#默认listen_addresses配置是注释掉的，所以可以直接在配置文件开头加入该行
listen_addresses='*'

3、允许所有IP访问

1
2
3
4
5
#修改配置文件
vi /var/lib/pgsql/12/data/pg_hba.conf

#在文件尾部加入
host  all  all 0.0.0.0/0 md5

4、重启PostgreSQL服务

1
2
#重启PostgreSQL服务
sudo systemctl restart postgresql-12

配置完成后即可使用客户端进行连接

Shell语法

启动SQL shell：

1
2
su postgres
psql

退出

1
2
quit
su root

数据库相关

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#创建数据库
CREATE DATABASE mydb;

#查看所有数据库
\l

#切换当前数据库
\c mydb

#创建表
CREATE TABLE test(id int,body varchar(100));

#查看当前数据库下所有表
\d

用户与访问授权

1
2
3
4
5
6
7
8
#新建用户
CREATE USER zhangjian WITH PASSWORD '123456';

#赋予指定账户指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO zhangjian;

#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb TO zhangjian

权限代码：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE

表操作

主键自增

serial类型实现自增

这种方法用的是pg的serial类型实现自增，drop表的时候指定的序列也会drop掉

1
2
3
4
5
create table tuser01(
    id serial PRIMARY KEY,
    name varchar,
    age int4
);

使用自增序列

创建自增序列

1
CREATE SEQUENCE seq_user_id START 1;

创建表

1
2
3
4
5
6
CREATE TABLE "tuser02" (
  "id" int4 NOT NULL DEFAULT nextval('seq_user_id'),
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "age" int4,
  PRIMARY KEY ("id")
);

如果已经建过表，

需要添加自增字段

1
ALTER TABLE tuser02 ADD COLUMN id int8 NOT NULL DEFAULT nextval('seq_user_id');

需要更新自增字段

1
alter table tuser02 alter column id set default nextval('seq_user_id');