Postgresql常用命令

登录命令：

• psql -U user -h host -d dbname -p 5432

复制表结构、数据库结构

pg_dump 工具可以用来导数据库结构和数据，常用来数据库迁移用。

-s 是只导结构，-t 是指定表名，-s -t 都不带就导整个数据库的结构和数据。

• 导一个数据库dbname 中的表tablename 的结构和数据

  pg_dump -U user -h host -p 5432 -t tablename dbname > /tmp/tablename.sql

• 导一个数据库dbname 中的表tablename 的结构

  pg_dump -U user -h host -p 5432 -s -t tablename dbname > /tmp/tablename.sql

• 导整个数据库dbname 所有表结构

  pg_dump -U user -h host -p 5432 -s dbname> /tmp/dbname.sql

• 远程带密码dump数据库结构

  pg_dump "host=XXX port=5432 user=XXX password=XXX dbname=XXX" -s -t tablename -f /tmp/tablename.sql

创建一个db，并用sql文件生成表

• 创建一个带密码的用户db

  createdb -T template0 -E UTF8 -l zh_CN.UTF-8 -h host -p 5432 -U user dbname -W

• 用sql文件生成数据库里面的表结构

  psql -U user dbname < /tmp/dbname.sql

修改数据库操作

• 修改表中列的默认值

  ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 2400;

• 重置序列的初始值

  ALTER sequence seq_name restart with num;

清空数据库操作

• 清空一个表的数据，连带序列号

  TRUNCATE tablename1, tablename2, ... , RESTART IDENTITY;

• 删除一个表

  DROP TABLE table_name;

• 删除一个数据库

  DROP DATABASE db_name;

COPY 命令

• \copy (select * from game_config where type = 74 or type = 75 or type = 76 or type = 77 or type = 78) to '/tmp/fruit.csv' with csv header;
• \copy game_config FROM '/tmp/fruit.csv' WITH csv header;

CPU过高定位

• 1、慢查询SQL查询

select datname,
       usename,
       pid,
       client_addr,
       application_name,
       state,
       backend_start,
       xact_start,
       xact_stay,
       query_start,
       query_stay,
       replace(query, chr(10), ' ') as query
from
  (select pgsa.datname as datname,
          pgsa.usename as usename,
          pgsa.pid as pid,
          pgsa.client_addr client_addr,
          pgsa.application_name as application_name,
          pgsa.state as state,
          pgsa.backend_start as backend_start,
          pgsa.xact_start as xact_start,
          extract(epoch from (now() - pgsa.xact_start)) as xact_stay,
          pgsa.query_start as query_start,
          extract(epoch from (now() - pgsa.query_start)) as query_stay,
          pgsa.query as query
   from pg_stat_activity as pgsa
   where pgsa.state != 'idle'
     and pgsa.state != 'idle in transaction'
     and pgsa.state != 'idle in transaction (aborted)') idleconnections
order by query_stay desc limit 5;

• 2、杀死慢查询语句
  select pg_cancel_backend(29966);
  select pg_terminate_backend(29966);

数组字段如何查询

• 1、使用array_to_string 函数，参数一是表字段，参数二是分隔字符；
  array_to_string(award_type,'/') like '%37_%';
• 2、直接访问数组内容；
  select award_type,mail_content from add_user_mail_log where to_user_ids[1]=60021093；
• 3、使用ANY函数；
  select award_type, mail_content from add_user_mail_log where 60024958 = ANY(to_user_ids);