PostgreSQL被誉为“世界上功能最强大的开源数据库”,是以加州大学伯克利分校计算机系开发的POSTGRES 4.2为基础的对象关系型数据库管理系统。
PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、MVCC。同样,PostgreSQL 可以用许多方法扩展,比如,通过增加新的数据类型、函数、操作符、聚集函数、索引。
开发者可以免费使用、修改和分发 PostgreSQL,不管是私用、商用、还是学术研究使用。
PostgreSQL:是以加州大学伯克利分校计算机系开发的 POSTGRES,现在已经更名为PostgreSQL,版本 4.2为基础的对象关系型数据库管理系统(ORDBMS)。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、MVCC。同样,PostgreSQL 可以用许多方法扩展,比如, 通过增加新的数据类型、函数、操作符、聚集函数、索引。免费使用、修改、和分发 PostgreSQL,不管是私用、商用、还是学术研究使用。
特点与优势:
PostgreSQL 是一个*的对象-关系数据库服务器(数据库管理系统),它在灵活的 BSD-风格许可证下发行。它提供了相对其他开放源代码数据库系统(比如 MySQL 和 Firebird),和专有系统(比如 Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server)之外的另一种选择。
PostgreSQL 不寻常的名字导致一些读者停下来尝试拼读它,特别是那些把SQL拼读为"sequel"的人。PostgreSQL 开发者把它拼读为 "post-gress-Q-L"。它也经常被简略念为 "postgres"。
事实上, PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99,首先,它包括了可以说是目前世界上最丰富的数据类型的支持,其中有些数据类型可以说连商业数据库都不具备, 比如 IP 类型和几何类型等;其次,PostgreSQL 是全功能的*软件数据库,很长时间以来,PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统(MVCC)、数据完整性检查等特性的唯一的一种*软件的数据库管理系统。 Inprise 的 InterBase 以及SAP等厂商将其原先专有软件开放为*软件之后才打破了这个唯一。最后,PostgreSQL拥有一支非常活跃的开发队伍,而且在许多黑客的努力下,PostgreSQL 的质量日益提高。
从技术角度来讲,PostgreSQL 采用的是比较经典的C/S(client/server)结构,也就是一个客户端对应一个服务器端守护进程的模式,这个守护进程分析客户端来的查询请求,生成规划树,进行数据检索并最终把结果格式化输出后返回给客户端。为了便于客户端的程序的编写,由数据库服务器提供了统一的客户端 C 接口。而不同的客户端接口都是源自这个 C 接口,比如ODBC,JDBC,Python,Perl,Tcl,C/C++,ESQL等, 同时也要指出的是,PostgreSQL 对接口的支持也是非常丰富的,几乎支持所有类型的数据库客户端接口。这一点也可以说是 PostgreSQL 一大优点。
PostgreSQL入门教程
一、安装
首先,安装PostgreSQL客户端。
sudo apt-get install postgresql-client
然后,安装PostgreSQL服务器。
sudo apt-get install postgresql
正常情况下,安装完成后,PostgreSQL服务器会自动在本机的5432端口开启。
如果还想安装图形管理界面,可以运行下面命令,但是本文不涉及这方面内容。
sudo apt-get install pgadmin3
二、添加新用户和新数据库
初次安装后,默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是,同时还生成了一个名为postgres的Linux系统用户。
下面,我们使用postgres用户,来生成其他用户和新数据库。好几种方法可以达到这个目的,这里介绍两种。
第一种方法,使用PostgreSQL控制台。
首先,新建一个Linux新用户,可以取你想要的名字,这里为dbuser。
sudo adduser dbuser
然后,切换到postgres用户。
sudo su - postgres
下一步,使用psql命令登录PostgreSQL控制台。
psql
这时相当于系统用户postgres以同名数据库用户的身份,登录数据库,这是不用输入密码的。如果一切正常,系统提示符会变为"postgres=#",表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。
第一件事是使用password命令,为postgres用户设置一个密码。
password postgres
第二件事是创建数据库用户dbuser(刚才创建的是Linux系统用户),并设置密码。
CREATE USER dbuser WITH PASSWORD ';password';;
第三件事是创建用户数据库,这里为exampledb,并指定所有者为dbuser。
CREATE DATABASE exampledb OWNER dbuser;
第四件事是将exampledb数据库的所有权限都赋予dbuser,否则dbuser只能登录控制台,没有任何数据库操作权限。
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;
最后,使用q命令退出控制台(也可以直接按ctrl+D)。
q
第二种方法,使用shell命令行。
添加新用户和新数据库,除了在PostgreSQL控制台内,还可以在shell命令行下完成。这是因为PostgreSQL提供了命令行程序createuser和createdb。还是以新建用户dbuser和数据库exampledb为例。
首先,创建数据库用户dbuser,并指定其为超级用户。
sudo -u postgres createuser --superuser dbuser
然后,登录数据库控制台,设置dbuser用户的密码,完成后退出控制台。
sudo -u postgres psql
password dbuser
q
接着,在shell命令行下,创建数据库exampledb,并指定所有者为dbuser。
sudo -u postgres createdb -O dbuser exampledb
三、登录数据库
添加新用户和新数据库以后,就要以新用户的名义登录数据库,这时使用的是psql命令。
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
上面命令的参数含义如下:-U指定用户,-d指定数据库,-h指定服务器,-p指定端口。
输入上面命令以后,系统会提示输入dbuser用户的密码。输入正确,就可以登录控制台了。
psql命令存在简写形式。如果当前Linux系统用户,同时也是PostgreSQL用户,则可以省略用户名(-U参数的部分)。举例来说,我的Linux系统用户名为ruanyf,且PostgreSQL数据库存在同名用户,则我以ruanyf身份登录Linux系统后,可以直接使用下面的命令登录数据库,且不需要密码。
psql exampledb
此时,如果PostgreSQL内部还存在与当前系统用户同名的数据库,则连数据库名都可以省略。比如,假定存在一个叫做ruanyf的数据库,则直接键入psql就可以登录该数据库。
psql
另外,如果要恢复外部数据,可以使用下面的命令。
psql exampledb < exampledb.sql
四、控制台命令
除了前面已经用到的password命令(设置密码)和q命令(退出)以外,控制台还提供一系列其他命令。
h:查看SQL命令的解释,比如h select。
?:查看psql命令列表。
l:列出所有数据库。
c [database_name]:连接其他数据库。
d:列出当前数据库的所有表格。
d [table_name]:列出某一张表格的结构。
du:列出所有用户。
e:打开文本编辑器。
conninfo:列出当前数据库和连接的信息。
五、数据库操作
基本的数据库操作,就是使用一般的SQL语言。
创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
插入数据
INSERT INTO user_tbl(name, signup_date) VALUES(';张三';, ';2013-12-22';);
选择记录
SELECT * FROM user_tbl;
更新数据
UPDATE user_tbl set name = ';李四'; WHERE name = ';张三';;
删除记录
DELETE FROM user_tbl WHERE name = ';李四'; ;
添加栏位
ALTER TABLE user_tbl ADD email VARCHAR(40);
更新结构
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
更名栏位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
删除栏位
ALTER TABLE user_tbl DROP COLUMN email;
表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;
删除表格
DROP TABLE IF EXISTS backup_tbl;
架构基础
行话:pgsql使用了客户端/服务器模式。一个pgsql会话包含包含两个进程:
服务器进程。管理数据库文件,接收客户端程序的连接请求,代表客户端执行数据库操作。数据库服务器程序叫做postgres。客户端应用程序。进行数据库操作。客户端应用程序本质上可以非常多样化,一个客户端可以是一个面向文本的工具,一个图形界面应用程序,一个通过访问数据库来显示网页的web服务器,或者一个专门的数据库维护工具。有些是pgsql自带的,大部分是用户开发的。
客户端和服务器可以在不同的主机上,通过TCP/IP连接通信。记住这一点很重要,因为客户机上的文件再服务器上可能是访问不到的,或者只能换个文件名访问。
pgsql服务器可以处理多并发的连接,它会为每个连接fork一个新的进程。客户端和新的服务器进程的通信与就服务器进程没有关系。主服务器进程会一直保持运行,等待客户端连接。
创建数据库
创建数据库可以检查你是否连接到了服务器。一个pgsql服务器可以管理很多数据库。
创建一个名为mydb的数据库:
$ createdb mydb
如果回车之后没有任何输出,说明创建数据库成功。你可以跳过本节了。
万一不幸出错了,这里给出了一些解决办法。
1. 找不到命令,错误如下:
createdb: command not found
说明pgsql的bin目录没有添加到环境变量。你可以选择将bin目录添加到环境变量,或者去bin目录下打开命令窗口,再或者使用绝对路径调用createdb命令。
$ /user/local/pgsql/bin/createdb mydb
建议添加还是添加环境变量,一劳永逸。
2. 服务未启动,错误如下:
createdb: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
表示服务器未启动,至少是在createdb执行的时候还未启动。多试试吧,我也不知道该咋办。
3. 没有账户,错误如下:
createdb: could not connect to database postgres: FATAL: role "joe" does not exist
这里joe是操作系统账户,pgsql用户账户与操作系统账户不同。你需要登录Postgres账户,创建一个用户账户。也可能是你被分配的pgsql账户名与操作系统用户名不同,这时,你可以使用-U选项指定账户或设置PGUSER环境变量来指定pgsql的用户名。
$ createdb -U user mydb
其中,user就是你的pgsql账户名。
4. 权限不足,错误如下:
createdb: database creation failed: ERROR: permission denied to create database
这是你的账户没有创建数据库的权限,你可以登录管理员账户进行修改。
pgsql允许创建任意数量的数据库。数据库名称必须以英文字母开头,且不超过63字节。
可以省略数据库名,创建与当前用户同名的数据库,如下:
$ createdb
删除数据库用dropdb命令:
$ dropdb mydb
dropdb命令必须指定数据库名称,即使是与用户名同名的数据库也必须指定,它不会默认用账户名。它会删除与数据库相关联的所有文件,并且不能撤销,所以一定要三思而行。
访问数据库
有三种方式可以访问数据库:
pgsql终端,psql命令行工具;pgAdmin或支持ODBC或JDBC的office套件;编程语言API。
这里只介绍第一种方式,第二种方式不做介绍,第三种方式后面再说。
打开cmd(或PowerShell)命令行窗口,希望你之前已经设置过环境 变量了。输入一下命令启动psql :
$ psql mydb
mydb就是之前创建的数据库。如果不指定数据库名称,默认会访问与当前账户名同名的数据库。
回车后输入密码,你会得到如下输出:
x psql (10.4)Type "help" for help.mydb=>
mydb=>就是命令提示符,你可以输入SQL指令(以;结尾)或psql命令(以\开头)。
如果命令提示符是mydb=#,表示当前账户是超级用户。
你可以输入以下几个命令试试:
mydb=> SELECT version();
mydb=> SELECT current_date;
mydb=> SELECT 2 + 2;
上面三个命令都是SQL命令,它们都以;结尾。
psql内部命令都以\开头。例如\h命令可以获取帮助,\q命令用来退出psql。
mydb=> \h
mydb=> \q
psql与cmd
pgsql安装后会默认安装两个工具,pgAdmin和SQL Shell(psql)。前者是图形界面工具,后者是命令行工具。
---------------------
二、添加新用户和新数据库
初次安装后,默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是,同时还生成了一个名为postgres的Linux系统用户。
下面,我们使用postgres用户,来生成其他用户和新数据库。好几种方法可以达到这个目的,这里介绍两种。
第一种方法,使用PostgreSQL控制台。
首先,新建一个Linux新用户,可以取你想要的名字,这里为dbuser。
sudo adduser dbuser
然后,切换到postgres用户。
sudo su - postgres
下一步,使用psql命令登录PostgreSQL控制台。
psql
这时相当于系统用户postgres以同名数据库用户的身份,登录数据库,这是不用输入密码的。如果一切正常,系统提示符会变为"postgres=#",表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。
第一件事是使用\password命令,为postgres用户设置一个密码。
\password postgres
第二件事是创建数据库用户dbuser(刚才创建的是Linux系统用户),并设置密码。
CREATE USER dbuser WITH PASSWORD 'password';
第三件事是创建用户数据库,这里为exampledb,并指定所有者为dbuser。
CREATE DATABASE exampledb OWNER dbuser;
第四件事是将exampledb数据库的所有权限都赋予dbuser,否则dbuser只能登录控制台,没有任何数据库操作权限。
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;
最后,使用\q命令退出控制台(也可以直接按ctrl+D)。
\q
第二种方法,使用shell命令行。
添加新用户和新数据库,除了在PostgreSQL控制台内,还可以在shell命令行下完成。这是因为PostgreSQL提供了命令行程序createuser和createdb。还是以新建用户dbuser和数据库exampledb为例。
首先,创建数据库用户dbuser,并指定其为超级用户。
sudo -u postgres createuser --superuser dbuser
然后,登录数据库控制台,设置dbuser用户的密码,完成后退出控制台。
sudo -u postgres psql
\password dbuser
\q
接着,在shell命令行下,创建数据库exampledb,并指定所有者为dbuser。
sudo -u postgres createdb -O dbuser exampledb
安装 PostgreSQL 10
下载 PostgreSQL 10,postgresql-10.1-3-linux-x64-binaries.tar.gz。下载地址:https://get.enterprisedb.com/postgresql/postgresql-10.1-3-linux-x64-binaries.tar.gz。
(注:安装脚本如下(需要有 /opt/local
写权限),可使用如下命令创建 /opt/local
目录。)
1 2 |
sudo mkdir /opt/local sudo chown -R $USER:$USER /opt/local |
install_pg.sh
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
OPT_BASE=/opt PGVERSION=10.1 PGBASE=$OPT_BASE/local/pgsql PGHOME=$OPT_BASE/local/pgsql/$PGVERSION PGDATA=$OPT_BASE/var/pgsql/$PGVERSION PG_SOFT_¥TAR="postgresql-10.1-3-linux-x64-binaries.tar.gz" if [ -d $PGHOME ]; then rm -rf $PGHOME elif [ ! -d $PGBASE ]; then mkdir -p $PGBASE fi if [ ! -d $PGDATA ]; then mkdir -p $PGDATA fi echo "Install PostgreSQL" tar zxf $PG_SOFT_TAR -C $PGBASE mv $PGBASE/pgsql $PGHOME cp pg-pwfile $PGHOME echo "Init PostgreSQL" pushd $PGHOME ./bin/initdb --pgdata="$PGDATA" --auth=ident --auth-host=md5 --encoding=UTF-8 --locale=zh_CN.UTF-8 --username=postgres --pwfile=pg-pwfile rm -f pg-pwfile popd cp pg_hba.conf $PGDATA cp postgresql.conf $PGDATA chmod 600 $PGDATA/*.conf echo "Start PostgreSQL" $PGHOME/bin/pg_ctl -D $PGDATA -l logfile start sleep 5 #cp .pgpass ~/ $PGHOME/bin/psql -h localhost -U postgres -d postgres -f pg_init.sql |
install_pg.sh
脚本安装时依赖文件的完整版压缩包在此下载:https://yangbajing.me/files/postgresql10-scripts.tar.gz
-
pg-pwfile:在初始化数据库时设置默认管理员账户的密码
-
pg_hba.conf:默认只允许 127.0.0.1/8 访问数据库,这里改成允许所有网段可访问
-
postgresql.conf:修改数据库监听地址为
*
,监听所有本地网络地址 -
pg_init.sql:创建一个普通账户 yangbajing 和测试用数据库 yangbajing ,密码也设置为
yangbajing
安装后PG数据库管理管理员账号是 postgres
,密码为 postgres
。同时,还创建了一个普通账号:yangbajing
和同名数据库 yangbajing
,密码也是 yangbajing
。
将 /opt/local/pgsql/10.1/bin
目录加入系统环境变量。
1 2 |
echo 'export PATH="/opt/local/pgsql/10.1/bin:$PATH" >> ~/.bashrc . ~/.bashrc |
使用如下命令来启动或停止PostgreSQL 10数据库
启动数据库
1 |
pg_ctl -D /opt/local/var/pgsql/10.1 -l logfile start |
停止数据库
1 |
pg_ctl -D /opt/local/var/pgsql/10.1 -l logfile stop |
体验 PG
输入以下命令访问PG数据库:
1 |
psql -h localhost -U yangbajing -d yangbajing -W |
根据提示输入密码登录,进入 psql 的 REPL 界面。
1 2 3 4 5 |
Password for user yangbajing: psql.bin (10.1) Type "help" for help. yangbajing=> |
先建立一些测试表:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE t_role ( id INT PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at TIMESTAMPTZ ); CREATE TABLE t_user ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, roles INT [] NOT NULL, data JSONB, created_at TIMESTAMPTZ ); INSERT INTO t_role (id, name, created_at) VALUES (1, '超级管理员', now()), (2, '管理员', now()), (3, '用户', now()); INSERT INTO t_user(name, roles, data, created_at) VALUES ('root', '{1}', '{"email":"root@yangbajing.me"}', now()), ('羊八井', '{2,3}', '{"email":"yangbajing"}', now()), ('哈哈', '{3}', '{"email":"haha@yangbajing.me"}', now()); |
先来执行两个简单的 SELECT 查询:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
yangbajing=> select * from t_role; id | name | created_at ----+------------+------------------------------- 1 | 超级管理员 | 2018-02-01 22:03:17.168906+08 2 | 管理员 | 2018-02-01 22:03:17.168906+08 3 | 用户 | 2018-02-01 22:03:17.168906+08 (3 rows) yangbajing=> select * from t_user; id | name | roles | data | created_at ----+--------+-------+---------------------------------------+------------------------------- 2 | root | {1} | {"email": "root@yangbajing.me"} | 2018-02-01 22:06:21.140465+08 3 | 哈哈 | {3} | {"email": "haha@yangbajing.me"} | 2018-02-01 22:06:21.140465+08 1 | 羊八井 | {2,3} | {"email": "yangbajing@yangbajing.me"} | 2018-02-01 22:04:41.580203+08 (3 rows) |
接下来,尝试一些 PG 特色特性。
InsertOrUpdate
插入或更新,是一个很有用的特性,当在主键冲突时可以选择更新数据。在PG中,是使用 ON CONFLICT 来实现这个特性的。
1 2 3 4 |
INSERT INTO t_role (id, name, created_at) VALUES (3, '普通用户', now()) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; |
在常用的 INSERT 语句后面用 ON CONFLICT (...) DO ....
语句来指定在某个/些字段出现冲突时需要执行的语句。在 on CONFLICT (...)
里的参数需要是主键或唯一索引(可以为复合字段)。当冲突发生时则会执行 DO ....
后面的语句,这里我们选择更新 name
字段的值。EXCLUDED
是用户引用在 VALUES ....
部分我们将插入的数据,EXCLUDED.name
在这里就是 '普通用户'
。除 DO UPDATE
,我们还可以使用 DO NOTHING
来简单的忽略插入时的主键冲突。
SERIAL/BIGSERIAL
看看表 t_user
的结构:
1 2 3 4 5 6 7 8 9 10 11 |
yangbajing=> \d t_user Table "public.t_user" Column | Type | Collation | Nullable | Default ------------+--------------------------+-----------+----------+------------------------------------ id | bigint | | not null | nextval('t_user_id_seq'::regclass) name | character varying(255) | | not null | roles | integer[] | | not null | data | jsonb | | | created_at | timestamp with time zone | | | Indexes: "t_user_pkey" PRIMARY KEY, btree (id) |
在建表时 id
字段的类型定义的是 BIGSERIAL ,但这里却是显示的 bigint 类型;另外,还多了一个默认值:nextval('t_user_id_seq'::regclass)
。这是 PG 中的 序列 ,PG中使用序列来实现 自增值 的特性。
序列:t_user_id_seq
1 2 3 4 5 6 |
yangbajing=> \d t_user_id_seq Sequence "public.t_user_id_seq" Type | Start | Minimum | Maximum | Increment | Cycles? | Cache --------+-------+---------+---------------------+-----------+---------+------- bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 Owned by: public.t_user.id |
也可以先创建序列,再设置字段的默认值为该序列的下一个值。
1 |
CREATE SEQUENCE t_user_id2_seq INCREMENT BY 1 MINVALUE 1 START WITH 1; |
这里创建一个序列,设置最小值为1,从1开始按1进行递增。
数组类型
在创建 t_user
表的 roles
字段时,使用了数组类型 INT []
。数组类型对于我们的数据建模来说很有用,使用得好可以大大减少关系表的数量。
根据索引返回值
1 2 3 4 5 6 7 |
yangbajing=> SELECT id, name, roles[2], created_at FROM t_user; id | name | roles | created_at ----+--------+-------+------------------------------- 2 | root | | 2018-02-01 22:06:21.140465+08 3 | 哈哈 | | 2018-02-01 22:06:21.140465+08 1 | 羊八井 | 1 | 2018-02-01 22:04:41.580203+08 (3 rows) |
注意:PG 中,索引下标从0开始
以行的形式输出数组元素
1 2 3 4 5 6 7 8 |
yangbajing=> SELECT id, unnest(roles) AS role_id FROM t_user; id | role_id ----+--------- 2 | 1 3 | 3 1 | 2 1 | 1 (4 rows) |
包含查找
1 2 3 4 5 |
yangbajing=> SELECT * FROM t_user WHERE roles @> ARRAY[1,2]; id | name | roles | data | created_at ----+--------+-------+---------------------------------------+------------------------------- 1 | 羊八井 | {2,1} | {"email": "yangbajing@yangbajing.me"} | 2018-02-01 22:04:41.580203+08 (1 row)
|