目录
[toc]
4 psql工具
4.1 连接到数据库
psql [OPTION]... [DBNAME [USERNAME]]
OPTION:
-d, --dbname=DBNAME 连接到指定数据库(默认：root)
-h, --host=HOSTNAME 数据库服务器主机名或者ip(默认：127.0.0.1)
-p, --port=PORT 数据库服务器端口(默认：5432)
-U, --username=USERNAME 数据库用户名(默认：root)
-W, --password 强制使用密码
方法1：postgres用户连接数据库
[root@localhost ~]# su - postgres
[postgres@localhost ~]$ psql
psql (10.2)
Type "help" for help.
postgres=#
方法2：root用户环境下连接
[root@localhost ~]# psql -U postgres
psql (10.2)
Type "help" for help.
postgres=#
4.2 psql常用命令
| 命令 | 作用 |
|---|---|
| \l | 列出全部的数据库 |
| \c [ 库名 ] | 连接到数据库 |
| \d | 显示当前数据库中有哪些表 |
| \timing | 显示SQL执行时间开关 |
| \encoding [ 字符集 ] | 指定字符集的命令 |
| \x | 逐行显示开关 |
| \i [ 文件名 ] | 执行外部SQL脚本 |
| \echo [ string ] | 显示信息命令 |
| \q | 退出 |
4.2.1 \l 命令
\l 查看库命令
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
pqa | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
新安装好的PostgreSQL数据库，默认会有一个叫postgres的数据库，还有两个模板数据库template0和template1。
当用户在新建数据库时，默认是从模板数据库template1克隆出来的，所以通常可以定制template1数据库中的内容，比如往template1中添加一些表和函数，这样后续创建的数据库就会继承template1中的内容，也会拥有这些表和函数。
而template0是一个最简化的模板库，创建数据库时，如果明确指定从此数据库中继承，将创建出一个简化的数据库。
4.2.2 \c 命令
\c [ 数据库名 ] 切换到数据库
postgres=# \c pqa
You are now connected to database "pqa" as user "postgres".
pqa=#
4.2.3 \d 命令
\d [ pattern ]
\d [ pattern ]+
\d 命令可以显示每个匹配关系(表、试图、索引、序列)的信息，包括对象所有的列、它们的类型、表空间和任何特殊属性(诸如NOT NULL或默认值等)等。
与唯一约束相关的索引、规则、约束、触发器也会同样显示出来。
如果关系是一个视图，还会显示视图的定义。
1. 如果\d命令后面什么都不带，将列出当前数据库中所有的表；
pqa=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------------------------------+----------+----------
public | auth_group | table | postgres
public | auth_group_id_seq | sequence | postgres
......
public | qa_usermanage_workplace_id_seq | sequence | postgres
(67 rows)
2. \d 后面跟一个表明，表示显示这个表的结构定义；
pqa=# \d qa_task_batch
Table "public.qa_task_batch"
Column | Type | Collation | Nullable | Default
---------+------------------------+-----------+----------+-------------------------------------------
id | integer | | not null | nextval('qa_task_batch_id_seq'::regclass)
batchId | character varying(256) | | not null |
date | date | | not null |
Indexes:
"qa_task_batch_pkey" PRIMARY KEY, btree (id)
3. \d 也可以显示索引信息；
pqa=# \d qa_task_batch_pkey
Index "public.qa_task_batch_pkey"
Column | Type | Definition
--------+---------+------------
id | integer | id
primary key, btree, for table "public.qa_task_batch"
4. \d 后面也可以跟通配符如 "*" 或 "?"；显示所有相关的信息(表、索引等)
5. \d+ 该命令更够显示比 \d 更详细的信息，除了前面写到的，还会显示任何与表列关联的注释，以及表中出现的OID；
pqa=# \d+ qa_task_batch;
Table "public.qa_task_batch"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+------------------------+-----------+----------+-------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('qa_task_batch_id_seq'::regclass) | plain | |
batchId | character varying(256) | | not null | | extended | |
date | date | | not null | | plain | |
Indexes:
"qa_task_batch_pkey" PRIMARY KEY, btree (id)
6. 匹配不同对象类型的 \d 命令；
- \dt 只显示匹配的表；
- \di 只显示索引；
- \ds 只显示序列；
- \dv 只显示视图；
- \df 只显示函数；
7. 显示SQL的执行时间，使用 \timing；
pqa=# \timing
Timing is on.
pqa=# select * from qa_task_batch;
id | batchId | date
----+---------+------
(0 rows)
Time: 0.665 ms
8. 列出所有的schemas，可以使用 \dn 命令；
pqa=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
9. 显示所有的表空间，可以使用 \db 命令；
pqa=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
10. 列出数据库中所有的角色或用户，可以使用 \du 或 \dg 命令；
\du 和 \dg 命令等价，原因是在 PostgreSQL中不区分用户和角色；
pqa=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
pqa=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
11. 显示表的权限分配情况，可以使用 \dp 或 \z 命令；
pqa=# \dp qa_task_batch;
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------------+-------+-------------------+-------------------+----------
public | qa_task_batch | table | | |
(1 row)
4.2.4 \encoding 指定字符集的命令
当客户端的字符编码与服务器的不一样是，可能会显示乱码，可以使用 \encoding 命令指定客户端字符编码；
pqa=# \encoding gbk;
pqa=# \encoding utf8;
4.2.5 \x 命令
\x 可以把表中每一行每一列的数据都拆分为单行显示；这个命令与 MySQL 的命令后面加 "\G" 的功能类似；
pqa=# \x
Expanded display is on.
pqa=# \l
List of databases
-[ RECORD 1 ]-----+----------------------
Name | postgres
Owner | postgres
Encoding | UTF8
Collate | zh_CN.UTF-8
Ctype | zh_CN.UTF-8
Access privileges |
-[ RECORD 2 ]-----+----------------------
Name | pqa
Owner | postgres
Encoding | UTF8
Collate | zh_CN.UTF-8
Ctype | zh_CN.UTF-8
Access privileges | =Tc/postgres +
| postgres=CTc/postgres
4.2.6 \i 执行外部文件中的SQL命令
\i 文件路径
[postgres@localhost ~]$ cat test-script.sql
CREATE DATABASE test_script;
pqa=# \i /home/postgres/test-script.sql
CREATE DATABASE
pqa=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
pqa | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test_script | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(5 rows)
4.2.7 \echo 显示信息命令
\echo 可以输出一行信息；
postgres=# \echo hello world!
hello world!
4.3 自动提交设置
在psql中事务是自动提交的。比方说，在执行完一条delete或update语句后，事务就自动提交了；如果不想自动提交，方法有两种：
方法1：
运行begin;命令，然后执行dml语句，最后再执行commit或rollback语句；
方法2：
使用sql命令关闭自动提交功能。
\set AUTOCOMMIT off
注意：该命令中的"AUTOCOMMIT"是大写的，不能使用小写；
5 数据备份和还原
5.1 逻辑备份
PostgreSQL 提供了 pg_dump 和 pg_dumpall 命令进行数据库的逻辑备份。
pg_dumpall 是将一个PostgreSQL数据库集群全部转储到一个脚本文件中；
pg_dump 命令可以选择一个数据库或部分表进行备份。
pg_dump 生成的备份文件可以是一个SQL脚本或归档文件；SQL脚本中包含许多SQL命令，可以使用psql工具来执行这个SQL脚本达到恢复数据的效果；而归档文件必须和pg_restore一起使用，它允许pg_restore选择恢复哪些数据，甚至可以在恢复之前对需要恢复的数据条目重新排序。
pg_dump生成的归档格式的备份文件分为两种：
最灵活的输格式是 "custom" 自定义格式(使用 -Fc 参数指定)，它允许对归档元素进行选取和重新排列，并且默认是压缩的；
另一种格式是 tar 格式(使用 -Ft 参数指定)，这种格式的文件不是压缩的，并且加载时不能重排列，但是它可以使用 tar 工具进行处理；
通常使用 "custom" 自定义格式。
5.2 pg_dump 命令
一： 纯文件格式的脚本：
示例：
1. 只导出postgres数据库的数据，不包括模式 -s
pg_dump -U postgres -f /postgres.sql -s postgres(数据库名)
2. 导出postgres数据库（包括数据）
pg_dump -U postgres -f /postgres.sql postgres(数据库名)
3. 导出postgres数据库中表test01的数据
create database "test01" with owner="postgres" encoding='utf-8';(单引号，双引号不能错)
pg_dump -U postgres -f /postgres.sql -t test01 postgres(数据库名)
4. 导出postgres数据库中表test01的数据,以insert语句的形式
pg_dump -U postgres -f /postgres.sql -t test01 --column-inserts postgres(数据库名)
5. 恢复数据到bk01数据库
psql -U postgres -f /postgres.sql bk01
二、 使用归档文件格式：
pg_restore
使用pg_restore纯文本恢复纯文本格式的脚本，无法恢复
[root@localhost postgres-9.3.5]# pg_restore -U postgres -d bk01 /mnt/hgfs/window\&ubuntu\ shared\ folder/vendemo.sql
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.
pg_restore和归档文件格式一起使用重建数据库。
