1 简介
PostgreSQL 是由 PostgreSQL 社区全球志愿者开发团队开发/维护的 对象-关系型数据库管理系统(Object-Relational DataBase Management System).
PostgreSQL 源于 UC Berkeley 1997 年的 Ingres 计划.
在1994 年, 两个 UC Berkeley 的研究生 Andrew Yu 和 Jolly Chen 增加了一个 SQL 语言解释器来替代 基于 Ingres 的 QUEL 系统, 建立了 Postgres95.
为了反映数据库新 SQL 查询语言特性, Postgres95 在 1996 年更名为 PostgreSQL, 并第一次发行了以 PostgreSQL 命名的 6.0 版本.
在 2005 年, PostgreSQL 发行了以原生方式运行在 Windows 系统下的 8.0 版本.
2010 年 PostgreSQL 9.0 发布, PostgreSQL 进入黄金发展时期.
目前(2021年), PostgreSQL 的最新稳定版本是 PostgreSQL 14.
注:
Many people continue to refer to PostgreSQL as “Postgres” because of tradition or because it is easier to pronounce. This usage is widely accepted as a nickname or alias.
1.1 PostgreSQL 的特点
PostgreSQL 支持多种操作系统, 包括各种 Linux 发行版, UNIX 以及 Windows, 例如: AIX, BSD, Mac OS 等等.
它有丰富的编程接口, 如 C/C++, Go, Java, Python 等和 开发数据库连接(ODBC) 的编程接口.
PostgreSQL 支持广泛的数据类型: 数字类型, 字符类型, 数组, json/jsonb 等.
支持大部分的 SQL 标准, 支持复杂的 SQL 查询, 支持 SQL 子查询.
支持主键, 外键, 触发器, 视图, 还支持多种语言编写存储过程.
支持并行计算和基于 MVCC(Multi-Version Concurrency Control), 支持同步/异步 流复制.
支持逻辑复制和订阅.
更多特性请参考官网.
2 安装
如果是 Redhat 系的操作系统, 可以参考 安装PostgreSQL.
如果是其他系统请 参考官网安装教程.
若想通过源码安装, 请参考 tutorial - Installation.
3 通过 psql 连接数据库
psql 是 PostgreSQL 自带的命令行客户端工具. 类似于 Oracle 的 sqlplus.
通过 psql 命令(客户端) 连接数据库:
$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "miyan" does not exist
报错了, 这是因为 PostgreSQL 默认使用 Peer authentication 认证, 就是说 数据库用户和 操作系统用户的 name 必须一样.
我的系统用户名为 miyan, 而数据库中没有 miyan 这个用户, 所以报错无法连接.
为了解决这个问题, 我们可以先通过默认的用户 postgres 连接数据, 然后在数据库中创建一个 miyan 用户, 这样就可以通过操作系统用户 miyan 直接连接数据库了.
Tip: 可以通过 psql --help 命令查看 psql 的使用帮助.
3.1 查看操作系统用户 postgres 是否存在
名为 postgres 的数据库用户一般在安装 PostgreSQL 后就会存在, 这个用户是 超级用户, 相当于管理员, 可以通过 postgres 创建其他用户.
先验证一下操作系统里有没有 postgres 用户:
$ id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)
可以看到, 我本地(CentOs)是有 postgres 这个用户的.
通过官方的脚本安装 PostgreSQL 是会 postgres 这个操作系统用户的. 如果通过其他渠道安装导致没有 postgres 用户, 可以通过下面的命令创建.
先查看下 当前 Linux 系统的 group id, 防止后面创建 gid 时重复:
cat /etc/passwd | gawk -F: '{print $1,$3,$4}' | sort -n -k2
新建一个 postgres 用户/组(uid 和 gid 不在 /etc/passwd 文件中), 比如把 uid 和 gid 都设置为 1001:
# 创建 postgres 组
groupadd -g 1001 postgres
# 创建 postgres 用户
useradd -g 1001 -u 1001 postgres
# 检查是否创建成功
id postgres
3.2 创建一个新的数据库用户 miyan
登录 postgres 用户:
$ sudo su postgres
[sudo] password for miyan:
通过 psql 客户端连接数据库 (默认连接的是和用户同名的数据库, 即 postgres):
bash-4.2$ psql
could not change directory to "/home/miyan": Permission denied
psql (14.0)
Type "help" for help.
创建名称为 miyan 的数据库用户:
postgres=# CREATE ROLE miyan LOGIN;
CREATE ROLE
3.3 psql 连接数据库 postgres
通过 psql 命令连接 默认的 postgres 数据库:
$ whoami
miyan
$ psql postgres
psql (14.0)
Type "help" for help.
postgres=>
4 新建数据库
在创建一个新的数据库对象之前, 先登录 postgres 用户, 赋予 miyan 用户一个数据库 超级用户 的权限, 这样方便以后操作:
postgres=# ALTER USER miyan SUPERUSER;
如果只想要创建数据库的权限, 不想要 超级用户 权限, 可以执行下面 DCL(Data Control Language):
postgres=# ALTER USER miyan CREATEDB;
创建 数据库 mydb :
$ psql postgres
psql (14.0)
Type "help" for help.
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=> CREATE DATABASE mydb
postgres-> WITH OWNER = miyan
postgres-> ENCODING = 'UTF8';
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | miyan | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
先通过 psql 连接数据库 postgres,
然后通过 元命令 \l 列出数据库系统已存在的数据库.
然后通过 CREATE DATABASE mydb WITH OWNER = miyan ENCODING = 'UTF8'; DDL(Data Definition Language) 创建了一个 mydb 数据库.
最后通过 元命令 \l 看到 mydb 创建成功.
备注:
PostgreSQL 支持的元命令很多, 忘记了的话, 可以通过 \? 列出所有原命令.
其中有一个 \h 元命令, 可以输出 SQL 命令语法帮助, 比如:
postgres=> \h ALTER USER
Command: ALTER USER
Description: change a database role
Syntax:
ALTER USER role_specification [ WITH ] option [ ... ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
ALTER USER name RENAME TO new_name
退出, 连接 mydb 数据库, 并创建一个表测试一下:
$ psql mydb
psql (14.0)
Type "help" for help.
mydb=# create table test1(id serial, name varchar);
CREATE TABLE
mydb=# insert into test1(name) values('rosie');
INSERT 0 1
mydb=# select * from test1;
id | name
----+-------
1 | rosie
(1 row)
5 通过 pgAdmin4 连接数据库
pgAdmin4 是一个有图形化界面的 PostgreSQL 的客户端, 可以从这里下载. 下载后直接安装即可.
通过 pgAdmin4 连接 mydb.
先配置 ssh tunnel:
再配置数据库连接:
save 后即可连接成功.
打开一个 query tool, 写一个 select 语句 测试一下:
注意:
默认情况下, PostgreSQL 是不允许远程访问数据库的.
我这里是通过 ssh tunnel 进行远程连接的, 对于 PostgreSQL 数据库来说, 就相当于本地连接.
如果想要远程连接数据库, 需要修改 postgresql.conf 文件 和 pg_hba
.conf 文件的配置 以支持远程访问.
这两个文件所在的目录:
$ sudo find / -name 'postgresql.conf' 2>/dev/null;
/var/lib/pgsql/14/data/postgresql.conf
$ sudo find / -name 'pg_hba.conf' 2>/dev/null;
/var/lib/pgsql/14/data/pg_hba.conf
修改 postgresql.conf 文件的 listen_addresses 配置项为 *.
(注: 默认的 localhost 只监听本地环回地址的连接, 不允许 TCP/IP 建立远程连接)
修改 pg_hba.conf 文件, 配置一行:
host all all 0.0.0.0/0 md5
每个参数的含义在配置文件中都有备注说明, 请参考其说明.
Reference
[1]. PostgreSQL 实战 - 谭峰
[2]. PostgreSQL docs
更多推荐
PostgreSQL 入门
发布评论