保留字在 PostgreSQL  里做常规字符使用时用双引号("")， 而 MySQL 里用 backtick(反引号 `)。

PostgreSQL 监控指标

原文地址： PostgreSQL监控指标

PostgreSQL 版本： 8.2.15

数据库状态信息

1. 目前客户端的连接数
   

   SELECT COUNT(*) FROM pg_stat_activity WHERE NOT procpid = pg_backend_pid()
   

2. 连接状态
   

   SELECT procpid, waiting, current_timestamp - LEAST(query_start, xact_start) AS runtime, SUBSTR(current_query, 1, 25) AS current_query FROM pg_stat_activity WHERE NOT procpid = pg_backend_pid()
   

   可以查看每个连接的 procpid，是否发生等待，根据事物或查询统计已执行时间，查询语句等。有多少个连接查询就会有多少条记录。
   
3. 数据库占用空间
   

   SELECT pg_size_pretty(pg_database_size('postgres'))
   

   一个数据库数据文件对应存储在以这个数据库 oid (pid) 命令的文件中，通过统计所有以 oid(pid) 命名文件的总大小，也可以得出这个数据库占用大空间。oid(pid) 可通过 SQL SELECT oid, datname FROM pg_database;获得。
   表占用的空间使用 pg_relation_size() 查询，对应的系统中的文件名和 pg_class 中的 filename 相同，一个热点的表最好一天一个统计大小，获得表的一个增长状况。根据需求提前准备空间应付数据库的增长。
   
4. 等待事件
   

   SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.current_query AS blocked_statement 
   FROM pg_locks bl 
   JOIN pg_stat_activity a ON a.procpid = bl.pid 
   JOIN pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid 
   JOIN pg_stat_activity ka ON ka.procpid = kl.pid WHERE NOT bl.granted;
   

   根据阻塞的语句的会话 PID 做相应处理。
   

数据库统计信息

1. 统计 SQL, 需安装扩展 pg_stat_statements-1.1.sql，调整 postgres.conf: shared_preload_libraries=‘pg_stat_statements’。
   

   SELECT calls, total_time, rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,substr(query,1,25)
   FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
   

   上述查询是按照查询到执行时间排序，可以定位到执行比较慢的 sql， 也可以用来查常用 SQL，以及 SQL 共享内存的命中率等信息。
   
2. 表的共享内存使用情况，需安装扩展 pg_buffercache-1.0.sql
   

   SELECT c.relname, count(*) AS buffers 
   FROM pg_buffercache b INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) 
   AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database())) GROUP BY c.relname ORDER BY 2 DESC LIMIT 5;
   

   表在共享内存中占用的块数，用来查看表是不是在内存中，buffers 的单位是数据块，默认 8K， 如果计算大小等于表的大小，说明全表的数据都在缓存在，这时的查询速度很快。
   
3. 表执行操作的统计
   统计对一个表操作的偏重， INSERT, UPDATE, DELETE 的比率
   

   SELECT relname,cast(n_tup_ins AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS ins_pct,
   cast(n_tup_upd AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS upd_pct, 
   cast(n_tup_del AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS del_pct 
   FROM pg_stat_user_tables where relname='products';
   

4. 表 IO 和索引 IO
   表 IO 主要涉及查询的逻辑块和物理块，归到底也是命中率的问题。一个表存在在内存中的内容越多，相应的查询速度越快。相关视图：pg_stat_user_tables。
   相对于表的大小来说，索引占用的空间要小很多，所以常用的表，可以让其索引一直存在内存中，很多时候保持索引的一个高命中率是非常必要的。相关视图pg_stat_user_indexes。
   

   SELECT indexrelname,cast(idx_blks_hit as numeric) / (idx_blks_hit + idx_blks_read) AS hit_pct,
   idx_blks_hit,idx_blks_read 
   FROM pg_statio_user_indexes WHERE (idx_blks_hit + idx_blks_read)>0 ORDER BY hit_pct;
   

系统监控信息

1. 数据库基本状态信息
   

   $ ps -ef | grep postgres
   $ netstat -altunp | grep 5432
   $ pg_controdata 
   

2. 
   

   $ top -u gpadmin
   $ free
   $ iotop -u gpadmin
   

   用 top 可以分析出系统当前整体的负载状况。当总体负载率很低，在 IO 等待率高的时候可以用 iotop 来定位 IO 具体的进程， top 中的 VIRT RES 表示进程希望获取的内存和占用系统内存的数量。可以据此来判断系统内存的分配情况。内存的值也关联到一些参数的设定，如 postgres 在发生 checkpoint 之前， checkpointer process 进程会消耗比较大的物理内存，直到检查点发生后，占用的内存会被释放掉，所以在设置 checkpoint 时间的时候也要将内存的占用考虑进去。
   

获取 *nix 文件的绝对路径

cd `dirname $0`
path=`pwd`

因为 $0 是指当前路径到执行脚本的相对路径。所以先需切换到执行文件的目录，才能用 pwd得到其绝对路径。

PostgreSQL 管理进程

1. 查看当前服务器的连接情况
   

   SELECT * FROM pg_stat_activity;
   

   返回信息包括如下:
   
   • datid
   • datname 数据库名
   • procpid 进程 ID
   • sess_id 会话 ID
   • usesysid
   • usename 提交 SQL 的用户名
   • current_query 当前 SQL 执行
   • waiting 当前是否等待
   • query_start SQL 提交时间
   • backend_start 后台执行时间
   • client_add SQL 提交的服务器
   • application_name SQL 提交的终端
   • xact_start SQL 执行开始时间
   • waiting_reason 等待原因，与 waiting 字段结合使用
   该查询结果也可在 PgAdmin 的 ”服务器状态“ 里查看到。
   
2. 杀死 IDLE 进程
   

   SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE current_query=''

当 pg 版本 >= 8.4 时，可用以上 SQL Kill 所有 IDLE 进程， pg_terminate_backend 是 pg 的内部方法。另外还有 pg_cancel_backend(procpid) 取消指定进程的查询操作，但它不能释放数据库连接。

PostgreSQL psql 客户端命令

连接数据库

psql -h host_name -U user_name -d database_name -W

• \l 列出所有数据库
• \d 显示当前数据库下的所有表
• \d table_name 列出指定表名的表结构
• \c database_name 切换到指定的数据库，这时会要重新输入用户名和密码
• \q 退出终端

PDO 参数设置

// 只返回字段名的结果
$this->pdo->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC);

// 防止整形在返回结果中变为字符串
$this->pdo->setAttribute(\PDO::ATTR_STRINGIFY_FETCHES, false);
$this->pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

线上环境更新

线上环境更新步骤

1 备份老代码
2 备份老数据
3 只 up 自己改动的脚本

切记切记。

MySQL 导入数据报 server has gone away

用 mysql 命令导数据入库，提示：

ERROR 2006 (HY000) at line 1843: MySQL server has gone away

原因是导入数据过多，需修改参数 max_allowed_packet 至合适的值。

利用存储过程删除库里表

因为 RDS 做了权限隔离，不能直接删除库，但又得删除库里的所有表，而表又无限多，为了一劳永逸，就写了个存储过程来解决。

drop PROCEDURE if exists delete_table;
delimiter //
create PROCEDURE delete_table(t_name VARCHAR(64))
top: begin
declare SQL_FOR_DELETE VARCHAR(500);
if  t_name is NULL OR  t_name = "undelete_table" then
  LEAVE top;
end IF;
SET SQL_FOR_DELETE = CONCAT("DROP TABLE ", t_name);
SET @sql = SQL_FOR_DELETE;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

DROP  PROCEDURE IF EXISTS delete_all_table;
DELIMITER //
CREATE PROCEDURE delete_all_table()
BEGIN
  DECLARE t_name VARCHAR(64);
  DECLARE `stop` INT DEFAULT 0;
  DECLARE table_cursor SELECT  TABLE_NAME FROM  `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'database_name';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET `stop` = 1;
  OPEN table_cursor;
  FETCH table_cursor INTO t_name;
  WHILE (`stop` <> 1) DO 
      call delete_table(t_name);
        FETCH table_cursor INTO t_name;
  END WHILE;
  CLOSE table_cursor;
END

shell 输出换行

str="a\nb\nc\n"

输出为

a
b
c

Mac 下 直接

echo ${str}

但是 Linux 下需要加 -e参数：

echo -e ${str}