PostgreSQL数据库SQL优化案例:从2秒到2毫秒

零 背景说明

这是生产环境下,一则PostgreSQL数据库SQL优化的案例小结:单条SQL执行时间,从优化前2秒到优化后,降低到2毫秒,性能提升1000倍。数据库版本为PostgreSQL 9.3。原始SQL语句如下:

SELECT t.id,
       t.code,
       t.name,
       t.level,
       t.IS_CLOSED isClosed,
       t.PARENT_ID parentId,
       t.dir,
       t.remark
FROM t_report_type t
WHERE 1=1
  AND t.code IN
    (SELECT report_type
     FROM t_report_temp
     WHERE corp_id IS NULL
       AND partner_id IS NULL)
ORDER BY t.code

优化前执行计划如下:

EXPLAIN ANALYZE
SELECT t.id,
       t.code,
       t.name,
       t.level,
       t.IS_CLOSED isClosed,
       t.PARENT_ID parentId,
       t.dir,
       t.remark
FROM t_report_type t
WHERE 1=1
  AND t.code IN
    (SELECT report_type
     FROM t_report_temp
     WHERE corp_id IS NULL
       AND partner_id IS NULL)
ORDER BY t.code

┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                       QUERY PLAN                                                                       │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort  (cost=3179.42..3179.93 rows=204 width=165) (actual time=2179.224..2179.250 rows=383 loops=1)                                                     │
│   Sort Key: t.code                                                                                                                                     │
│   Sort Method: quicksort  Memory: 126kB                                                                                                                │
│   ->  Nested Loop Semi Join  (cost=0.55..3171.59 rows=204 width=165) (actual time=26.092..2178.526 rows=383 loops=1)                                   │
│         ->  Seq Scan on t_report_type t  (cost=0.00..16.59 rows=459 width=165) (actual time=0.002..0.187 rows=459 loops=1)                             │
│         ->  Index Only Scan using t_report_temp_01 on t_report_temp  (cost=0.55..1873.80 rows=428 width=7) (actual time=4.744..4.744 rows=1 loops=459) │
│               Index Cond: ((corp_id IS NULL) AND (partner_id IS NULL) AND (report_type = (t.code)::text))                                              │
│               Heap Fetches: 53                                                                                                                         │
│ Total runtime: 2179.301 ms                                                                                                                             │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)

一 分析问题

结合执行来分析SQL语句,查看表结构,数据量分布:

zyd=> \d t_report_type
                 Table "zyd.t_report_type"
┌───────────┬────────────────────────┬─────────────────────┐
│  Column   │          Type          │      Modifiers      │
├───────────┼────────────────────────┼─────────────────────┤
│ id        │ character(32)          │ not null            │
│ code      │ character varying(32)  │ not null            │
│ name      │ character varying(256) │ not null            │
│ parent_id │ character varying(32)  │                     │
│ dir       │ character varying(256) │                     │
│ level     │ smallint               │                     │
│ is_closed │ character(1)           │ default '0'::bpchar │
│ remark    │ character varying(200) │                     │
└───────────┴────────────────────────┴─────────────────────┘
Indexes:
    "t_report_type_pkey" PRIMARY KEY, btree (id)
    "t_report_type_01" btree (code, parent_id, dir)

zyd=> select count(*) from t_report_type;
┌───────┐
│ count │
├───────┤
│   459 │
└───────┘
(1 row)

zyd=> select count(*) from t_report_temp;
┌────────┐
│ count  │
├────────┤
│ 366132 │
└────────┘
(1 row)

zyd=> \d t_report_temp
                 Table "zyd.t_report_temp"
┌─────────────┬────────────────────────────────┬───────────┐
│   Column    │              Type              │ Modifiers │
├─────────────┼────────────────────────────────┼───────────┤
│ id          │ character(32)                  │ not null  │
│ report_code │ character varying(40)          │ not null  │
│ report_name │ character varying(100)         │ not null  │
│ report_type │ character varying(32)          │ not null  │
│ corp_id     │ character(32)                  │           │
│ partner_id  │ character(32)                  │           │
│ industry_id │ character(32)                  │           │
│ is_default  │ character(1)                   │           │
│ remark      │ character varying(200)         │           │
│ source_type │ character varying(255)         │           │
│ is_show     │ character(1)                   │ default 0 │
│ create_ts   │ timestamp(6) without time zone │           │
│ update_ts   │ timestamp(6) without time zone │           │
│ is_simple   │ character(1)                   │           │
└─────────────┴────────────────────────────────┴───────────┘
Indexes:
    "t_report_temp_pkey" PRIMARY KEY, btree (id)
    "t_report_temp_01" btree (corp_id, industry_id, partner_id, report_code, report_type, report_name)
    "t_report_temp_02" btree (industry_id, partner_id, report_type)
    "t_report_temp_03" btree (industry_id, report_type)
    "t_report_temp_04" btree (report_name, corp_id, report_code)
    "t_report_temp_index_1" btree (corp_id, report_code)

zyd=> select count(*) from t_report_temp where corp_id is null and partner_id is null;
┌───────┐
│ count │
├───────┤
│  1126 │
└───────┘
(1 row)

zyd=>

看到,这是一个简单的2张表做join连接的SQL查询语句,优化器选择了嵌套循环nested loop的关联方式,其中一张表t_report_type为小表,数据量为459,优化器选择全表扫描的方式获取数据,另外一张表t_report_temp数据量相对多一些,366132条数据,但是满足条件的数据却只有1126条,同时选择了index only scan的方式,单次执行耗时4.744毫秒。

这是从执行计划看到的基本信息,能优化的突破口基本也就是对于表的访问能不能更快?虽然这里选择的是index only scan的方式。

三 解决问题

先尝试重新收集表的统计信息,再查看其执行计划是否有好转?

zyd=> analyze verbose t_report_temp;
INFO:  analyzing "zyd.t_report_temp"
INFO:  "t_report_temp": scanned 9494 of 9494 pages, containing 366132 live rows and 1345 dead rows; 30000 rows in sample, 366132 estimated total rows
ANALYZE
zyd=> analyze verbose t_report_type;
INFO:  analyzing "zyd.t_report_type"
INFO:  "t_report_type": scanned 12 of 12 pages, containing 459 live rows and 0 dead rows; 459 rows in sample, 459 estimated total rows
ANALYZE
zyd=>
...查看执行计划
...
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                       QUERY PLAN                                                                       │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort  (cost=3179.42..3179.93 rows=204 width=165) (actual time=1973.808..1973.836 rows=383 loops=1)                                                     │
│   Sort Key: t.code                                                                                                                                     │
│   Sort Method: quicksort  Memory: 126kB                                                                                                                │
│   ->  Nested Loop Semi Join  (cost=0.55..3171.59 rows=204 width=165) (actual time=29.583..1973.127 rows=383 loops=1)                                   │
│         ->  Seq Scan on t_report_type t  (cost=0.00..16.59 rows=459 width=165) (actual time=0.002..0.175 rows=459 loops=1)                             │
│         ->  Index Only Scan using t_report_temp_01 on t_report_temp  (cost=0.55..1873.80 rows=428 width=7) (actual time=4.297..4.297 rows=1 loops=459) │
│               Index Cond: ((corp_id IS NULL) AND (partner_id IS NULL) AND (report_type = (t.code)::text))                                              │
│               Heap Fetches: 53                                                                                                                         │
│ Total runtime: 1973.885 ms                                                                                                                             │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)

zyd=>

发现,并没有好转。观察到其执行计划中,对于相对大的那个表t_report_temp,优化器选择了通过t_report_temp_01这个index来做的index only scan,每次耗时4毫秒左右,但是loops=459,累积起来就1800多毫秒了。

同时,该indext_report_temp_01是一个联合index,基于(corp_id, industry_id, partner_id, report_code, report_type, report_name) 6个字段创建的联合index,观察表和该index的大小:

zyd=> \dt+ t_report_temp
                        List of relations
┌────────┬───────────────┬───────┬───────┬───────┬──────────────┐
│ Schema │     Name      │ Type  │ Owner │ Size  │ Description  │
├────────┼───────────────┼───────┼───────┼───────┼──────────────┤
│ zyd    │ t_report_temp │ table │ zyd   │ 74 MB │ 报表模板主表 │
└────────┴───────────────┴───────┴───────┴───────┴──────────────┘
(1 row)

zyd=> \di+ t_report_temp_01
                                 List of relations
┌────────┬──────────────────┬───────┬───────┬───────────────┬───────┬─────────────┐
│ Schema │       Name       │ Type  │ Owner │     Table     │ Size  │ Description │
├────────┼──────────────────┼───────┼───────┼───────────────┼───────┼─────────────┤
│ zyd    │ t_report_temp_01 │ index │ zyd   │ t_report_temp │ 80 MB │             │
└────────┴──────────────────┴───────┴───────┴───────────────┴───────┴─────────────┘
(1 row)

zyd=>

发现,这个index 80MB,比表本身74MB还大。是不是推断出,此种情况下,优化器选择通过该index来获取数据不够好呢,或者说还不如直接扫描这个表呢?

开始验证:

zyd=> set enable_indexscan =off;
SET
zyd=> set enable_indexonlyscan =off;
SET
zyd=> set enable_bitmapscan =off;
SET
zyd=> explain analyze..
..
..
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                             QUERY PLAN                                                              │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Merge Join  (cost=13420.55..13423.58 rows=204 width=165) (actual time=69.095..69.417 rows=383 loops=1)                              │
│   Merge Cond: ((t.code)::text = (t_report_temp.report_type)::text)                                                                  │
│   ->  Sort  (cost=36.88..38.03 rows=459 width=165) (actual time=0.513..0.569 rows=459 loops=1)                                      │
│         Sort Key: t.code                                                                                                            │
│         Sort Method: quicksort  Memory: 145kB                                                                                       │
│         ->  Seq Scan on t_report_type t  (cost=0.00..16.59 rows=459 width=165) (actual time=0.002..0.121 rows=459 loops=1)          │
│   ->  Sort  (cost=13383.67..13383.79 rows=49 width=7) (actual time=68.576..68.600 rows=383 loops=1)                                 │
│         Sort Key: t_report_temp.report_type                                                                                         │
│         Sort Method: quicksort  Memory: 42kB                                                                                        │
│         ->  HashAggregate  (cost=13381.80..13382.29 rows=49 width=7) (actual time=68.253..68.309 rows=383 loops=1)                  │
│               ->  Seq Scan on t_report_temp  (cost=0.00..13163.63 rows=87269 width=7) (actual time=0.005..67.787 rows=1126 loops=1) │
│                     Filter: ((corp_id IS NULL) AND (partner_id IS NULL))                                                            │
│                     Rows Removed by Filter: 365114                                                                                  │
│ Total runtime: 69.480 ms                                                                                                            │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(14 rows)

zyd=> 

此时,可以看到,当我们禁用index only scan,index scan,bitmap index scan之后,使优化器对于表t_report_temp选择全表扫描之后,整个SQL的执行效率反而更好。
于是,尝试在(report_type,corp_id,partner_id)创建联合index,观察一下SQL效率:

zyd=> create index CONCURRENTLY idx_3 on t_report_temp(report_type,corp_id,partner_id);
CREATE INDEX
zyd=> explain analyze ....
..
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                QUERY PLAN                                                                 │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort  (cost=267.54..268.05 rows=204 width=165) (actual time=2.525..2.554 rows=383 loops=1)                                                │
│   Sort Key: t.code                                                                                                                        │
│   Sort Method: quicksort  Memory: 126kB                                                                                                   │
│   ->  Nested Loop Semi Join  (cost=0.42..259.72 rows=204 width=165) (actual time=0.041..2.083 rows=383 loops=1)                           │
│         ->  Seq Scan on t_report_type t  (cost=0.00..16.59 rows=459 width=165) (actual time=0.002..0.053 rows=459 loops=1)                │
│         ->  Index Only Scan using idx_3 on t_report_temp  (cost=0.42..29.58 rows=428 width=7) (actual time=0.004..0.004 rows=1 loops=459) │
│               Index Cond: ((report_type = (t.code)::text) AND (corp_id IS NULL) AND (partner_id IS NULL))                                 │
│               Heap Fetches: 27                                                                                                            │
│ Total runtime: 2.600 ms                                                                                                                   │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)

zyd=>

发现,此时SQL的执行耗时从之前的2秒降低到2.6毫秒。

四 小结

通过深入分析SQL执行计划、结合表结构、数据量及数据分布,是做好SQL优化的基本出发点。需要更为深入学习和分析SQL执行计划,能看懂SQL执行计划,对SQL执行计划有感觉,多看多读执行计划,多思考。

PostgreSQL官方文档中,关于执行计划描述,有这么一句话:

Plan-reading is an art that requires some experience to master, but this section attempts to cover the basics.

wordpress迁移步骤和遇到的问题及解决

零 背景说明:

我的这个站点目前托管在香港,采用的是LAMP,即Linux+Apache+MySQL+PHP架构。将于2018年9月6日到期。在到期之前,研究了一下,如果需要迁移到新主机的话,大致流程和步骤,这里简单总结一下。

即,模拟将现有的oracleonlinux.cn(LAMP架构)迁移至本地机器,并且启用新的blog.com域名(这个域名是虚拟的,通过NGINX模拟反向解析,然后配置本地hosts文件)采用LNMP架构。把之前的Apache换成NGINX,域名也更换一下。

一 备份站点文件

即备份主机上的Wordpress及其全部子目录即可,可以采用Linux系统的tar命令来打包。我这里,由于主机服务商暂时不提供SSH远程访问权限,无法连到主机上直接执行备份,但是有Cpanel控制台,我是通过Cpanel控制台上选择的全部备份,然后将备份文件下载到本地:

[root@localhost ~]# ll backup-9.3.2018_08-30-42_oracleon.tar.gz 
-rw-r--r-- 1 nginx nginx 260642919 9月   3 21:06 backup-9.3.2018_08-30-42_oracleon.tar.gz
[root@localhost ~]#

二 备份MySQL数据库

同样,通过Cpanel控制台上选择备份数据库,然后下载到本地:

[root@localhost ~]# ll oracleon_oracle.sql.gz 
-rw-r--r--. 1 root root 3155154 9月   3 19:53 oracleon_oracle.sql.gz
[root@localhost ~]#

三 本地搭建LNMP环境

Linux:Centos 6.5,本地IP为172.16.11.80:

[root@localhost ~]# cat /etc/redhat-release 
CentOS release 6.5 (Final)
[root@localhost ~]# uname -rm
2.6.32-431.el6.x86_64 x86_64
[root@localhost ~]# ifconfig 
eth0      Link encap:Ethernet  HWaddr 00:50:56:99:3D:E7  
          inet addr:172.16.11.80  Bcast:172.16.11.255  Mask:255.255.255.0
          inet6 addr: fe80::250:56ff:fe99:3de7/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:2903715683 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3509490556 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:1030640015360 (959.8 GiB)  TX bytes:3030630388115 (2.7 TiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:414816117 errors:0 dropped:0 overruns:0 frame:0
          TX packets:414816117 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:469426552437 (437.1 GiB)  TX bytes:469426552437 (437.1 GiB)

[root@localhost ~]#

NGINX:直接通过yum安装;

[root@localhost ~]# yum install nginx
已加载插件:fastestmirror
设置安装进程
Loading mirror speeds from cached hostfile
 * base: mirrors.163.com
 * epel: mirrors.ustc.edu.cn
 * extras: mirrors.shu.edu.cn
 * updates: mirrors.zju.edu.cn
解决依赖关系
--> 执行事务检查
---> Package nginx.x86_64 0:1.10.2-1.el6 will be 安装
--> 处理依赖关系 nginx-filesystem = 1.10.2-1.el6,它被软件包 nginx-1.10.2-1.el6.x86_64 需要
--> 处理依赖关系 nginx-all-modules = 1.10.2-1.el6,它被软件包 nginx-1.10.2-1.el6.x86_64 需要
--> 处理依赖关系 nginx-filesystem,它被软件包 nginx-1.10.2-1.el6.x86_64 需要
--> 执行事务检查
---> Package nginx-all-modules.noarch 0:1.10.2-1.el6 will be 安装
--> 处理依赖关系 nginx-mod-stream = 1.10.2-1.el6,它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
--> 处理依赖关系 nginx-mod-mail = 1.10.2-1.el6,它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
--> 处理依赖关系 nginx-mod-http-xslt-filter = 1.10.2-1.el6,它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
--> 处理依赖关系 nginx-mod-http-perl = 1.10.2-1.el6,它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
--> 处理依赖关系 nginx-mod-http-image-filter = 1.10.2-1.el6,它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
--> 处理依赖关系 nginx-mod-http-geoip = 1.10.2-1.el6,它被软件包 nginx-all-modules-1.10.2-1.el6.noarch 需要
---> Package nginx-filesystem.noarch 0:1.10.2-1.el6 will be 安装
--> 执行事务检查
---> Package nginx-mod-http-geoip.x86_64 0:1.10.2-1.el6 will be 安装
---> Package nginx-mod-http-image-filter.x86_64 0:1.10.2-1.el6 will be 安装
---> Package nginx-mod-http-perl.x86_64 0:1.10.2-1.el6 will be 安装
---> Package nginx-mod-http-xslt-filter.x86_64 0:1.10.2-1.el6 will be 安装
---> Package nginx-mod-mail.x86_64 0:1.10.2-1.el6 will be 安装
---> Package nginx-mod-stream.x86_64 0:1.10.2-1.el6 will be 安装
--> 完成依赖关系计算

依赖关系解决

=================================================================================================================================
 软件包                                       架构                    版本                           仓库                   大小
=================================================================================================================================
正在安装:
 nginx                                        x86_64                  1.10.2-1.el6                   epel                  462 k
为依赖而安装:
 nginx-all-modules                            noarch                  1.10.2-1.el6                   epel                  7.7 k
 nginx-filesystem                             noarch                  1.10.2-1.el6                   epel                  8.5 k
 nginx-mod-http-geoip                         x86_64                  1.10.2-1.el6                   epel                   14 k
 nginx-mod-http-image-filter                  x86_64                  1.10.2-1.el6                   epel                   16 k
 nginx-mod-http-perl                          x86_64                  1.10.2-1.el6                   epel                   26 k
 nginx-mod-http-xslt-filter                   x86_64                  1.10.2-1.el6                   epel                   16 k
 nginx-mod-mail                               x86_64                  1.10.2-1.el6                   epel                   43 k
 nginx-mod-stream                             x86_64                  1.10.2-1.el6                   epel                   36 k

事务概要
=================================================================================================================================
Install       9 Package(s)

总下载量:629 k
Installed size: 1.6 M
确定吗?[y/N]:y
下载软件包:
(1/9): nginx-1.10.2-1.el6.x86_64.rpm                                                                      | 462 kB     00:00     
(2/9): nginx-all-modules-1.10.2-1.el6.noarch.rpm                                                          | 7.7 kB     00:00     
(3/9): nginx-filesystem-1.10.2-1.el6.noarch.rpm                                                           | 8.5 kB     00:00     
(4/9): nginx-mod-http-geoip-1.10.2-1.el6.x86_64.rpm                                                       |  14 kB     00:00     
(5/9): nginx-mod-http-image-filter-1.10.2-1.el6.x86_64.rpm                                                |  16 kB     00:00     
(6/9): nginx-mod-http-perl-1.10.2-1.el6.x86_64.rpm                                                        |  26 kB     00:00     
(7/9): nginx-mod-http-xslt-filter-1.10.2-1.el6.x86_64.rpm                                                 |  16 kB     00:00     
(8/9): nginx-mod-mail-1.10.2-1.el6.x86_64.rpm                                                             |  43 kB     00:00     
(9/9): nginx-mod-stream-1.10.2-1.el6.x86_64.rpm                                                           |  36 kB     00:00     
---------------------------------------------------------------------------------------------------------------------------------
总计                                                                                             719 kB/s | 629 kB     00:00     
运行 rpm_check_debug 
执行事务测试
事务测试成功
执行事务
  正在安装   : nginx-filesystem-1.10.2-1.el6.noarch                                                                          1/9 
  正在安装   : nginx-mod-http-geoip-1.10.2-1.el6.x86_64                                                                      2/9 
  正在安装   : nginx-mod-stream-1.10.2-1.el6.x86_64                                                                          3/9 
  正在安装   : nginx-mod-http-perl-1.10.2-1.el6.x86_64                                                                       4/9 
  正在安装   : nginx-mod-http-image-filter-1.10.2-1.el6.x86_64                                                               5/9 
  正在安装   : nginx-mod-http-xslt-filter-1.10.2-1.el6.x86_64                                                                6/9 
  正在安装   : nginx-1.10.2-1.el6.x86_64                                                                                     7/9 
  正在安装   : nginx-mod-mail-1.10.2-1.el6.x86_64                                                                            8/9 
  正在安装   : nginx-all-modules-1.10.2-1.el6.noarch                                                                         9/9 
  Verifying  : nginx-mod-mail-1.10.2-1.el6.x86_64                                                                            1/9 
  Verifying  : nginx-mod-http-geoip-1.10.2-1.el6.x86_64                                                                      2/9 
  Verifying  : nginx-mod-stream-1.10.2-1.el6.x86_64                                                                          3/9 
  Verifying  : nginx-all-modules-1.10.2-1.el6.noarch                                                                         4/9 
  Verifying  : nginx-mod-http-perl-1.10.2-1.el6.x86_64                                                                       5/9 
  Verifying  : nginx-mod-http-image-filter-1.10.2-1.el6.x86_64                                                               6/9 
  Verifying  : nginx-1.10.2-1.el6.x86_64                                                                                     7/9 
  Verifying  : nginx-filesystem-1.10.2-1.el6.noarch                                                                          8/9 
  Verifying  : nginx-mod-http-xslt-filter-1.10.2-1.el6.x86_64                                                                9/9 

已安装:
  nginx.x86_64 0:1.10.2-1.el6                                                                                                    

作为依赖被安装:
  nginx-all-modules.noarch 0:1.10.2-1.el6                     nginx-filesystem.noarch 0:1.10.2-1.el6                            
  nginx-mod-http-geoip.x86_64 0:1.10.2-1.el6                  nginx-mod-http-image-filter.x86_64 0:1.10.2-1.el6                 
  nginx-mod-http-perl.x86_64 0:1.10.2-1.el6                   nginx-mod-http-xslt-filter.x86_64 0:1.10.2-1.el6                  
  nginx-mod-mail.x86_64 0:1.10.2-1.el6                        nginx-mod-stream.x86_64 0:1.10.2-1.el6                            

完毕!
[root@localhost ~]# nginx -v
nginx version: nginx/1.10.2
[root@localhost ~]#

MySQL:通过RPM包安装:

[root@localhost ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar
..
..
100%[=======================================================================================>] 227,512,320 1.21M/s   in 2m 26s  

2018-09-04 14:51:22 (1.48 MB/s) - 已保存 “MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar” [227512320/227512320])

[root@localhost ~]# ll MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar 
-rw-r--r--. 1 root root 227512320 6月  18 16:11 MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar
[root@localhost ~]# tar -zxvf MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar 

gzip: stdin: not in gzip format
tar: Child returned status 1
tar: Error is not recoverable: exiting now
[root@localhost ~]# tar xvf MySQL-5.6.41-1.el6.x86_64.rpm-bundle.tar 
MySQL-client-5.6.41-1.el6.x86_64.rpm
MySQL-shared-compat-5.6.41-1.el6.x86_64.rpm
MySQL-test-5.6.41-1.el6.x86_64.rpm
MySQL-server-5.6.41-1.el6.x86_64.rpm
MySQL-devel-5.6.41-1.el6.x86_64.rpm
MySQL-shared-5.6.41-1.el6.x86_64.rpm
MySQL-embedded-5.6.41-1.el6.x86_64.rpm
[root@localhost ~]# rpm -ivh MySQL-server-5.6.41-1.el6.x86_64.rpm 
warning: MySQL-server-5.6.41-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
        libnuma.so.1()(64bit) is needed by MySQL-server-5.6.41-1.el6.x86_64
        libnuma.so.1(libnuma_1.1)(64bit) is needed by MySQL-server-5.6.41-1.el6.x86_64
        libnuma.so.1(libnuma_1.2)(64bit) is needed by MySQL-server-5.6.41-1.el6.x86_64
[root@localhost ~]#

根据错误提示,安装numactl:

[root@localhost ~]# yum install numactl
已加载插件:fastestmirror
设置安装进程
Loading mirror speeds from cached hostfile
 * base: mirrors.163.com
 * epel: mirrors.ustc.edu.cn
 * extras: mirrors.shu.edu.cn
 * updates: mirrors.zju.edu.cn
解决依赖关系
--> 执行事务检查
---> Package numactl.x86_64 0:2.0.9-2.el6 will be 安装
--> 完成依赖关系计算

依赖关系解决

=================================================================================================================================
 软件包                        架构                         版本                                仓库                        大小
=================================================================================================================================
正在安装:
 numactl                       x86_64                       2.0.9-2.el6                         base                        74 k

事务概要
=================================================================================================================================
Install       1 Package(s)

总下载量:74 k
Installed size: 171 k
确定吗?[y/N]:y
下载软件包:
numactl-2.0.9-2.el6.x86_64.rpm                                                                            |  74 kB     00:00     
运行 rpm_check_debug 
执行事务测试
事务测试成功
执行事务
  正在安装   : numactl-2.0.9-2.el6.x86_64                                                                                    1/1 
  Verifying  : numactl-2.0.9-2.el6.x86_64                                                                                    1/1 

已安装:
  numactl.x86_64 0:2.0.9-2.el6                                                                                                   

完毕!
[root@localhost ~]# rpm -ivh MySQL-server-5.6.41-1.el6.x86_64.rpm 
warning: MySQL-server-5.6.41-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
        file /usr/share/mysql/czech/errmsg.sys from install of MySQL-server-5.6.41-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.71-1.el6.x86_64
..
..
file /usr/share/mysql/charsets/swe7.xml from install of MySQL-server-5.6.41-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.71-1.el6.x86_64
[root@localhost ~]#

再根据提示,系统自带mysql-libs-5.1.71与当前要安装的依赖冲突,先删除掉:

[root@localhost ~]# yum erase mysql-libs-5.1.71-1.el6.x86_64
已加载插件:fastestmirror
设置移除进程
解决依赖关系
--> 执行事务检查
---> Package mysql-libs.x86_64 0:5.1.71-1.el6 will be 删除
--> 处理依赖关系 libmysqlclient.so.16()(64bit),它被软件包 2:postfix-2.6.6-2.2.el6_1.x86_64 需要
--> 处理依赖关系 libmysqlclient.so.16(libmysqlclient_16)(64bit),它被软件包 2:postfix-2.6.6-2.2.el6_1.x86_64 需要
--> 处理依赖关系 mysql-libs,它被软件包 2:postfix-2.6.6-2.2.el6_1.x86_64 需要
--> 执行事务检查
---> Package postfix.x86_64 2:2.6.6-2.2.el6_1 will be 删除
--> 处理依赖关系 /usr/sbin/sendmail,它被软件包 cronie-1.4.4-12.el6.x86_64 需要
--> 执行事务检查
---> Package cronie.x86_64 0:1.4.4-12.el6 will be 删除
--> 处理依赖关系 cronie = 1.4.4-12.el6,它被软件包 cronie-anacron-1.4.4-12.el6.x86_64 需要
--> 执行事务检查
---> Package cronie-anacron.x86_64 0:1.4.4-12.el6 will be 删除
--> 处理依赖关系 /etc/cron.d,它被软件包 crontabs-1.10-33.el6.noarch 需要
--> 使用新的信息重新计算依赖关系
--> 执行事务检查
---> Package crontabs.noarch 0:1.10-33.el6 will be 删除
--> 完成依赖关系计算

依赖关系解决

=============================================================================================================================================================================
 软件包                              架构                        版本                                    仓库                                                           大小
=============================================================================================================================================================================
正在删除:
 mysql-libs                          x86_64                      5.1.71-1.el6                            @anaconda-CentOS-201311272149.x86_64/6.5                      4.0 M
为依赖而移除:
 cronie                              x86_64                      1.4.4-12.el6                            @anaconda-CentOS-201311272149.x86_64/6.5                      174 k
 cronie-anacron                      x86_64                      1.4.4-12.el6                            @anaconda-CentOS-201311272149.x86_64/6.5                       43 k
 crontabs                            noarch                      1.10-33.el6                             @anaconda-CentOS-201311272149.x86_64/6.5                      2.4 k
 postfix                             x86_64                      2:2.6.6-2.2.el6_1                       @anaconda-CentOS-201311272149.x86_64/6.5                      9.7 M

事务概要
=============================================================================================================================================================================
Remove        5 Package(s)

Installed size: 14 M
确定吗?[y/N]:y
下载软件包:
运行 rpm_check_debug 
执行事务测试
事务测试成功
执行事务
  正在删除   : cronie-anacron-1.4.4-12.el6.x86_64                                                                                                                        1/5 
  正在删除   : crontabs-1.10-33.el6.noarch                                                                                                                               2/5 
  正在删除   : cronie-1.4.4-12.el6.x86_64                                                                                                                                3/5 
  正在删除   : 2:postfix-2.6.6-2.2.el6_1.x86_64                                                                                                                          4/5 
  正在删除   : mysql-libs-5.1.71-1.el6.x86_64                                                                                                                            5/5 
  Verifying  : crontabs-1.10-33.el6.noarch                                                                                                                               1/5 
  Verifying  : cronie-anacron-1.4.4-12.el6.x86_64                                                                                                                        2/5 
  Verifying  : cronie-1.4.4-12.el6.x86_64                                                                                                                                3/5 
  Verifying  : 2:postfix-2.6.6-2.2.el6_1.x86_64                                                                                                                          4/5 
  Verifying  : mysql-libs-5.1.71-1.el6.x86_64                                                                                                                            5/5 

删除:
  mysql-libs.x86_64 0:5.1.71-1.el6                                                                                                                                           

作为依赖被删除:
  cronie.x86_64 0:1.4.4-12.el6           cronie-anacron.x86_64 0:1.4.4-12.el6           crontabs.noarch 0:1.10-33.el6           postfix.x86_64 2:2.6.6-2.2.el6_1          

完毕!
[root@localhost ~]#

接着安装MySQL-server:

[root@localhost ~]# rpm -ivh MySQL-server-5.6.41-1.el6.x86_64.rpm 
warning: MySQL-server-5.6.41-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]
warning: user mysql does not exist - using root
warning: group mysql does not exist - using root
warning: user mysql does not exist - using root
warning: group mysql does not exist - using root
2018-09-04 15:09:54 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-09-04 15:09:54 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2018-09-04 15:09:54 0 [Note] /usr/sbin/mysqld (mysqld 5.6.41) starting as process 1647 ...
2018-09-04 15:09:54 1647 [Note] InnoDB: Using atomics to ref count buffer pool pages
..
..
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

..
..
New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

[root@localhost ~]# 

安装MySQL客户端:

[root@localhost ~]# rpm -ivh MySQL-client-5.6.41-1.el6.x86_64.rpm 
warning: MySQL-client-5.6.41-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]
[root@localhost ~]#

启动MySQL,用系统生成的随机口令先登录数据库,然后修改root口令,再创建新的库和用户blog,用于恢复原站点的数据库:

[root@localhost ~]# cat .mysql_secret 
# The random password set for the root user at Tue Sep  4 15:09:55 2018 (local time): QKkCVRsNEhJZEZj0

[root@localhost ~]# service mysql status
 ERROR! MySQL is not running
[root@localhost ~]# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/localhost.localdomain.err'.
... SUCCESS! 
[root@localhost ~]# service mysql status
 SUCCESS! MySQL running (2098)
[root@localhost ~]# mysql -h localhost -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET PASSWORD=password('localblog');
Query OK, 0 rows affected (0.00 sec)

mysql> create database blog CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'blog'@'localhost' identified by 'blog123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on blog.* to 'blog'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# 

PHP:yum安装。

[root@localhost ~]# yum install php php-fpm
已加载插件:fastestmirror
..
..
已安装:
  php.x86_64 0:5.3.3-49.el6                                                           php-fpm.x86_64 0:5.3.3-49.el6                                                          

作为依赖被安装:
  apr.x86_64 0:1.3.9-5.el6_9.1                   apr-util.x86_64 0:1.3.9-3.el6_0.1      apr-util-ldap.x86_64 0:1.3.9-3.el6_0.1      httpd.x86_64 0:2.2.15-69.el6.centos     
  httpd-tools.x86_64 0:2.2.15-69.el6.centos      mailcap.noarch 0:2.1.31-2.el6          php-cli.x86_64 0:5.3.3-49.el6               php-common.x86_64 0:5.3.3-49.el6        

完毕!
[root@localhost ~]# yum install php php-fpm

四 配置NGINX

这里,由于我是在本地服务器上模拟站点迁移,并没有新的域名。于是,我用NGINX模拟反向解析一个新的域名:blog.com,然后,把blog.com配置到本地hosts文件。

NGINX的配置文件路径为,/etc/nginx/conf.d。内容如下:

[root@localhost blog]# cat /etc/nginx/conf.d/blog.conf 
#
# The default server
#
server {
    listen       80;
    server_name  blog.com;

    #charset koi8-r;

    #access_log  logs/host.access.log  main;

    location / {
        root   /blog;
        index index.php  index.html index.htm;
    }

    error_page  404              /404.html;
    location = /404.html {
        root   /usr/share/nginx/html;
    }

    # redirect server error pages to the static page /50x.html
    #
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   /usr/share/nginx/html;
    }

    # proxy the PHP scripts to Apache listening on 127.0.0.1:80
    #
    #location ~ \.php$ {
    #    proxy_pass   http://127.0.0.1;
    #}

    # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
    #
    location ~ \.php$ {
        root           /blog;
        fastcgi_pass   127.0.0.1:9000;
        fastcgi_index  index.php;
        fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
        include        fastcgi_params;
    }

    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    #
    #location ~ /\.ht {
    #    deny  all;
    #}
}
[root@localhost blog]#

该配置文件中的blog.com就是我要通过NGINX来反向代理的站点,/blog指的是我的站点的根目录。
然后,本地hosts文件中,把blog.com,指向172.16.11.96,添加如下一条记录即可:

172.16.11.80 blog.com
且,可以正常访问:
$ ping blog.com
PING blog.com (172.16.11.80): 56 data bytes
64 bytes from 172.16.11.80: icmp_seq=0 ttl=63 time=0.561 ms
64 bytes from 172.16.11.80: icmp_seq=1 ttl=63 time=0.638 ms
^C
--- blog.com ping statistics ---
2 packets transmitted, 2 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 0.561/0.600/0.638/0.039 ms

asher at MacBook-Air-3 in ~
$ 

五 安装Wordpress

WordPress官方网站,下载最新的安装包,然后解压,放到/blog路径下,修改/blog机器子路径的权限,owner为NGINX的启动用户。这里需要配合前面的第4步骤,前面的NGINX把blog.com反向解析到哪个路径,这里就需要把Wordpress安装在哪儿。

[root@localhost ~]# wget https://wordpress.org/latest.tar.gz
..
[root@localhost ~]# mkdir -p /blog
[root@localhost ~]# mv latest.tar.gz /blog/
[root@localhost ~]# cd /blog/
[root@localhost blog]# tar -zxvf latest.tar.gz 
[root@localhost blog]# mv ./wordpress/* .
[root@localhost blog]# chown -R nginx:nginx /blog/

启动,NGINX、php-fpm,执行安装:

[root@localhost ~]# service nginx restart
停止 nginx:                                               [确定]
正在启动 nginx:                                           [确定]
[root@localhost ~]# service php-fpm restart
停止 php-fpm:                                             [失败]
正在启动 php-fpm:                                         [确定]
[root@localhost ~]#

浏览器打开blog.com,遇到下述错误:
Your PHP installation appears to be missing the MySQL extension which is required by WordPress.
经排查,是PHP版本太低,卸载前面安装的低版本PHP和PHP-fpm,然后安装高版本PHP和PHP-fpm,以及相关依赖包:

[root@localhost ~]# php -v
PHP 5.3.3 (cli) (built: Mar 22 2017 12:27:09) 
Copyright (c) 1997-2010 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies
[root@localhost ~]# yum erase php php-fpm
..
..
[root@localhost ~]# rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
Retrieving http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
Preparing...                ########################################### [100%]
   1:remi-release           ########################################### [100%]
[root@localhost ~]# yum install --enablerepo=remi --enablerepo=remi-php56 php php-fpm php-opcache php-devel php-mbstring php-mcrypt php-mysqlnd php-phpunit-PHPUnit php-pecl-xdebug php-pecl-xhprof 
..
..

再启动NGINX、php-fpm,执行安装:

[root@localhost blog]# php -v
PHP 5.6.37 (cli) (built: Jul 19 2018 20:06:19) 
Copyright (c) 1997-2016 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies
    with Xdebug v2.5.5, Copyright (c) 2002-2017, by Derick Rethans
[root@localhost blog]# service nginx restart
停止 nginx:                                               [确定]
正在启动 nginx:                                           [确定]
[root@localhost blog]# service php-fpm restart
停止 php-fpm:                                             [确定]
正在启动 php-fpm:                                         [确定]
[root@localhost blog]#

开始安装:

根据提示,填入前面配置MySQL数据库的时候,创建的数据库名blog,用户名blog及密码:

根据提示,安装程序无法创建wp-config.php配置文件,手工拷贝内容,在/blog路径下,创建该文件,内容为上述信息。继续安装:

设置超管用户的密码,这里只是测试,简单起见,设置blog、blog,真实环境下,要注意复杂度。

六 还原MySQL数据库:

解压缩之前创建的MySQL数据库备份文件:

[root@localhost ~]# gzip -d oracleon_oracle.sql.gz
[root@localhost ~]# ll oracleon_oracle.sql
-rw-r--r--. 1 root root 15662095 9月 3 19:53 oracleon_oracle.sql
[root@localhost ~]#

blog用户登录数据库blog,执行脚本恢复:

  
[root@localhost ~]# mysql -h localhost -u blog -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use blog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------------+
| Tables_in_blog        |
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_termmeta           |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+
12 rows in set (0.00 sec)

mysql> source ./oracleon_oracle.sql
..
..
Query OK, 0 rows affected (0.00 sec)

mysql>

七 修改数据库中关于旧站点的连接信息:

mysql> UPDATE wp_options SET option_value = replace(option_value, 'www.oracleonlinux.cn','blog.com');
Query OK, 6 rows affected (0.02 sec)
Rows matched: 367  Changed: 6  Warnings: 0

mysql> UPDATE wp_options SET option_value = replace(option_value, 'oracleonlinux.cn','blog.com');
Query OK, 2 rows affected (0.01 sec)
Rows matched: 367  Changed: 2  Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'www.oracleonlinux.cn','blog.com');
Query OK, 95 rows affected (0.06 sec)
Rows matched: 735  Changed: 95  Warnings: 0

mysql> UPDATE wp_posts SET post_content = replace(post_content, 'oracleonlinux.cn','blog.com');
Query OK, 13 rows affected (0.07 sec)
Rows matched: 735  Changed: 13  Warnings: 0

mysql> UPDATE wp_comments SET comment_content = replace(comment_content, 'www.oracleonlinux.cn', 'blog.com');
Query OK, 8 rows affected (0.01 sec)
Rows matched: 284  Changed: 8  Warnings: 0

mysql> UPDATE wp_comments SET comment_content = replace(comment_content, 'oracleonlinux.cn', 'blog.com');
Query OK, 1 row affected (0.01 sec)
Rows matched: 284  Changed: 1  Warnings: 0

mysql> UPDATE wp_comments SET comment_author_url = replace(comment_author_url, 'www.oracleonlinux.cn', 'blog.com');
Query OK, 66 rows affected (0.01 sec)
Rows matched: 284  Changed: 66  Warnings: 0

mysql> UPDATE wp_comments SET comment_author_url = replace(comment_author_url, 'oracleonlinux.cn', 'blog.com');
Query OK, 10 rows affected (0.01 sec)
Rows matched: 284  Changed: 10  Warnings: 0

mysql> 

八 登录后台查看信息:

注意,这里需要使用旧站点的管理员用户和密码,因为后台的MySQL数据库中已经导入之前的数据库备份信息了。

登录之后,可以看到之前站点上发布的所有文章和数据。这里,需要重新安装以及启用之前站点上的主题和插件。

九 设置NGINX的静态解析

由于之前的网站是LAMP,用Apache解析的,现在改为NGINX代理的话,需要重新配置静态解析。否则的话,会出现主页访问正常,但是其它页面以及标签页和单篇文章打开时报404错误。

在nginx的配置文件的location / 位置处,加上下述信息:

 if (!-e $request_filename) {
            rewrite ^([_0-9a-zA-Z-]+)?(/wp-.*) $2 last;
            rewrite ^([_0-9a-zA-Z-]+)?(/.*\.php)$ $2 last;
            rewrite ^ /index.php last;
        }

完整的用于该站点的反向代理解析文件如下:

#
# The default server
#
server {
    listen       80;
    server_name  blog.com;

    #charset koi8-r;

    #access_log  logs/host.access.log  main;

    location / {
        root   /blog;
        index index.php  index.html index.htm;
        
        if (!-e $request_filename) {
            rewrite ^([_0-9a-zA-Z-]+)?(/wp-.*) $2 last;
            rewrite ^([_0-9a-zA-Z-]+)?(/.*\.php)$ $2 last;
            rewrite ^ /index.php last;
        }
    }

    error_page  404              /404.html;
    location = /404.html {
        root   /usr/share/nginx/html;
    }

    # redirect server error pages to the static page /50x.html
    #
    error_page   500 502 503 504  /50x.html;
    location = /50x.html {
        root   /usr/share/nginx/html;
    }

    # proxy the PHP scripts to Apache listening on 127.0.0.1:80
    #
    #location ~ \.php$ {
    #    proxy_pass   http://127.0.0.1;
    #}

    # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
    #
    location ~ \.php$ {
        root           /blog;
        fastcgi_pass   127.0.0.1:9000;
        fastcgi_index  index.php;
        fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
        include        fastcgi_params;
    }

    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    #
    #location ~ /\.ht {
    #    deny  all;
    #}
}

最后,可以在本地看到的站点如下:

PostgreSQL数据库psql客户端工具的设置

在使用psql客户端工具来管理、维护PostgreSQL数据库的过程中,经常遇到一些格式的问题:输出结果被折叠覆盖。对于熟悉Oracle数据库的sql*plus客户端工具的DBA来说,这简直是梦魇。这里,收录一些psql客户端工具的设置。

1 输出结果被折叠覆盖

ai_task1=> \d t_ai_prd_task_field                
                  Table "ai_task1.t_ai_prd_task_field"
          Column           |              Type              | Modifiers 
---------------------------+--------------------------------+-----------
 id                        | character varying(32)          | not null
 task_id                   | character varying(32)          | 
 job_field_id              | character varying(32)          | 
 item_id                   | character varying(32)          | 
 ...
 ...
 sort_no                   | smallint                       | 
 template_display_name     | character varying(128)         | 
Indexes:
    "t_ai_prd_task_field_pkey" PRIMARY KEY, btree (id)
    "idx_task_field_applicant_name" btree (applicant_name)
    "idx_task_field_bill_code" btree (bill_code)
    "idx_task_field_item_id" btree (item_id)
    "idx_task_field_job_field_id" btree (job_field_id)
    "idx_task_field_job_id" btree (job_id)
    "idx_task_field_packet_code" btree (packet_code)
    "idx_task_field_status" btree (status)
    "idx_task_field_submit_time" btree (submit_time)
    "idx_task_field_task_id" btree (task_id)
tegory, cust_category, template_name, input_source)name, applicant_name, task_ca--More--

ai_task1=>

查看表结构时,后面带出index信息中,对于最后一行,完全不知道索引的相关信息,取而代之的是–More–,这不要命么?可以通过在命令行上设置,\pset pager off。

ai_task1=> \pset pager off
Pager usage is off.
ai_task1=> \d t_ai_prd_task_field                
                  Table "ai_task1.t_ai_prd_task_field"
          Column           |              Type              | Modifiers 
---------------------------+--------------------------------+-----------
 id                        | character varying(32)          | not null
 task_id                   | character varying(32)          | 
 job_field_id              | character varying(32)          | 
 item_id                   | character varying(32)          | 
 ...
 ...
 sort_no                   | smallint                       | 
 template_display_name     | character varying(128)         | 
Indexes:
    "t_ai_prd_task_field_pkey" PRIMARY KEY, btree (id)
    "idx_task_field_applicant_name" btree (applicant_name)
    "idx_task_field_bill_code" btree (bill_code)
    "idx_task_field_item_id" btree (item_id)
    "idx_task_field_job_field_id" btree (job_field_id)
    "idx_task_field_job_id" btree (job_id)
    "idx_task_field_packet_code" btree (packet_code)
    "idx_task_field_status" btree (status)
    "idx_task_field_submit_time" btree (submit_time)
    "idx_task_field_task_id" btree (task_id)
    "idx_task_field_union_gin" gin (template_field_name, applicant_name, task_category, cust_category, template_name, input_source)

ai_task1=>

瞬间变得神清气爽,清清楚楚。

2 设置输出回显的linestyle和边框

ai_task1=> \pset border 2
Border style is 2.
ai_task1=> \pset linestyle unicode 
Line style is unicode.
ai_task1=> \d t_07 
                      Table "ai_task1.t_07"
┌───────────────────────────┬────────────────────────┬───────────┐
│          Column           │          Type          │ Modifiers │
├───────────────────────────┼────────────────────────┼───────────┤
│ bill_code                 │ character varying(12)  │           │
│ template_no               │ character varying(32)  │           │
│ template_field_identifier │ character varying(64)  │           │
│ company_name              │ character varying(255) │           │
│ submit_value              │ character varying      │           │
└───────────────────────────┴────────────────────────┴───────────┘

ai_task1=>

整齐划一、干净好看。

 

3 换行截断

postgres=# select * from t;
 id |                                                                                                                                                                                                                                            content                                                                                                                                                                                                                                             
----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | If you are used to psql, you may notice in the picture above, some content is wrapped. This is \pset format wrapped option.
  2 | Some commands take an SQL identifier (such as a table name) as argument. These arguments follow the syntax rules of SQL: Unquoted letters are forced to lowercase, while double quotes (") protect letters from case conversion and allow incorporation of whitespace into the identifier. Within double quotes, paired double quotes reduce to a single double quote in the resulting name. For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and "A weird"" name" becomes A weird" name.+
    |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               +
    | 
(2 rows)

postgres=#

 

对于terminal window比较小的场景下,这个查询结果并不够友好。可以,通过设置:\pset format wrapped

postgres=# \pset format wrapped 
Output format is wrapped.
postgres=# select * from t;     
 id |                                       content                                        
----+--------------------------------------------------------------------------------------
  1 | If you are used to psql, you may notice in the picture above, some content is wrappe.
    |.d. This is \pset format wrapped option.
  2 | Some commands take an SQL identifier (such as a table name) as argument. These argum.
    |.ents follow the syntax rules of SQL: Unquoted letters are forced to lowercase, while.
    |. double quotes (") protect letters from case conversion and allow incorporation of w.
    |.hitespace into the identifier. Within double quotes, paired double quotes reduce to .
    |.a single double quote in the resulting name. For example, FOO"BAR"BAZ is interpreted.
    |. as fooBARbaz, and "A weird"" name" becomes A weird" name.                          +
    |                                                                                     +
    | 
(2 rows)

postgres=#

4 设置编辑器

通过设置执行psql命令的用户的环境变量EDITOR=vi,可以在psql命令行上通过执行\e 来调用vi编辑SQL语句。需要注意的是:

a \e 只能编辑修改最后1条SQL语句;

b \e 不能编辑从命令行读取的SQL脚本中的命令,这个有别于Oracle数据库。

c  \e 如果出现E437: terminal capability “cm” required时,有可能是用户的环境变量TERM没设置,修改一下即可。

[enterprisedb@ppasdev ~]$ export EDITOR=vi
[enterprisedb@ppasdev ~]$ export TERM=xterm
[enterprisedb@ppasdev ~]$ sh local 
psql.bin (9.3.11.33)
Type "help" for help.

postgres=# select * from t;
..
..
postgres=# \e
调用vi编辑SQL语句。

5 小结

可以把关于psql的设置,配置到需要执行psql命令行工具用户的家目录下的名为.psqlrc文件中:

[enterprisedb@ecs-public2 ~]$ cat ~/.psqlrc 
\pset pager off
\pset border 2
\pset linestyle unicode 
\pset format wrapped 
[enterprisedb@ecs-public2 ~]$

可以把关于环境变量的设置,配置到用户的家目录下的.bash_profile中:

[enterprisedb@ecs-public2 ~]$ cat ~/.bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/ppas/9.3as/bin

export PATH

export LANG=en_US.UTF-8
export PGDATA=/ppas/9.3as/data
export EDITOR=vi
export TERM=xterm
[enterprisedb@ecs-public2 ~]$

这些相关设置,更为具体的可以参考官方文档:https://www.postgresql.org/docs/9.1/static/app-psql.html

这里,只是做个简单记录,解决我个人目前遇到的几个棘手问题,后续再读文档时更新本文。