1、PostgreSQL 数据库入门

本文实践环境

• macOS Mojave 10.14.6
• docker 19.03.1
• docker-compose 1.24.1
• node 11.10.1

什么是 PostgreSQL ？

PostgreSQL简称PG，是一款功能强大且开源免费的关系型数据库。它支持大部分 SQL 标准并且提供了许多现代特性：

• 复杂查询
• 外键
• 触发器
• 可更新视图
• 事务完整性
• 多版本并发控制

同样，PostgreSQL可以用许多方法扩展，比如， 通过增加新的：

• 数据类型
• 函数
• 操作符
• 聚集函数
• 索引方法
• 过程语言

docker 安装

在项目根目录下新建一个 docker-compose.yml 文件：

version: '3'

services:
    postgres:
        container_name: pg
        image: postgres:9.6  # 使用的是 9.6 版本的 pg 数据库
        restart: always
        ports:
            - '54321:5432'   # 把容器的 5432 端口映射到本机的 54321端口
        environment:
            POSTGRES_PASSWORD: 123456 # 设置 postgres 用户的默认密码
        volumes:
            - ./.docker/postgres-data:/var/lib/postgresql/data  # 把数据库目录挂载出来

让pg数据库服务在后台运行

 $ docker-compose up -d

查询容器运行情况

$ docker ps

尝试登录数据库

$ docker exec -it ${容器ID} psql -U postgres

image.png

什么是 psql ？

psql 是 pg 数据库中的一个命令行交互式客户端工具
使用 psql 连接数据库的方法：
psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]

这些连接参数也可以用环境变量指定，比如：

• export PGDATABASE=testdb
• export PGHOST=127.0.0.1
• export PGPORT=5432
• export PGUSER=postgres

psql 的常用命令

psql 的命令都是以斜杠 \ 开头的。
\h 查看帮助信息
\l 查看所有数据库
\q 退出 psql
\d [ pattern ] 该命令将显示每个匹配关系（表、视图、索引、序列）的信息，可以加上 + 查看更多的信息，如 \d+

1. 不加任何参数表示查看当前数据库的所有表。

2. \d tablename 后面跟一个表名，表示显示这个表的结构定义

3. \d indexname 也可以显示索引的信息，如 \d 索引名称

4. \d * 后面也可以跟通配符如 * 或 ?，\d x*

5. \d+将显示比 \d 更详细的信息，还会显示任何与表关系的注释，以及表中出现的 OID

\dt 只显示匹配的表
\di 只显示索引
\ds 只显示序列
\dv 只显示视图
\df 只显示函数

7. \timing on 或 \timing off 显示 SQL 已执行的时间，默认情况下是 off

8. \dn 列出所有的 schema

9. \du 或 \dg 列出所有的数据库用户和角色

10. \db 显示所有的表空间，表空间其实是一个目录，放在这个表空间的表，就是把表的数据文件发到这个表空间下。

11. \dp 或 \z 显示表的权限分配情况

12. \encoding 指定客户端的字符编码，如 \encoding UTF8;

13. \pset 设置输出的格式，\pset border 0 : 表示输出内容无边框。 border 1 ：表示边框只在内部。border 2 ： 内外都有边框

14. \x 把表中的每一行的每列数据都拆分为单行展示，与 MySQL 中的 \G 的功能类似。

15. \echo 用于输出一行信息，通常用于在 .sql 文件中输出一些提示信息。

16. \password 设置密码

17. \conninfo 列出当前数据库连接的信息

18. \dx 查看数据库中安装的扩展 或 select * from pg_extension;

执行存储在外部文件中的 SQL 命令

\i <文件名> 执行存储在外部文件中的 sql 语句
当然也可以在 psql 命令行加 -f <filename> 来执行 SQL 脚本文件中的命令，如 psql -f ./test.sql

psql 的使用技巧和注意事项

1. 在启动 psql 命令后中加 "-E" 参数，就可以把 psql 中各种以 "" 开头的命令执行的实际 SQL 打印出来，如下 psql -E postgres，如果想关闭此功能，可以使用 \set ECHO_HIDDEN on | off

2. 自动提交方面的技巧

psql 中的事务是自动提交的，可以运行 begin; 然后执行 dml 语句，最后再执行 commit 或 rollback 语句。或 直接使用 psql 中的命令关闭自动提交的功能。\set AUTOCOMMIT off

3. 查看数据库、表、索引大小

  select pg_size_pretty(pg_table_size('test'));   
  select pg_size_pretty(pg_database_size('david'));
  select pg_size_pretty(pg_indexes_size('test'));