PostgreSQL数据库

1、常用命令

进入数据库命令行界面

psql -h 127.0.0.1 -p 5432 -U username -d 数据库名

列举数据库

\l

选择数据库

\c 数据库名

查看表结构

\d 表名

显示字符集

\encoding

退出psql

\q

列出所有用户

\du

创建数据库

create database db_test

创建数据库指定参数信息，with owner：所有者，encoding：编码格式

create database db_test with owner = postgres encoding = 'uft-8'

修改数据库名称

alter database db_test rename to db_test2

修改连接限制数

alter database db_test connection limit 20

删除数据库

drop database db_test

创建表

create table t_test(
  xxx int,
  xxx bigint,
  xxx varchar(30),
  xxx date,
  xxx numeric(5,2),
  primary key(一个或多个列)
);

删除表

drop table t_test;

增

insert into t_test(xxx,xxx,xxx,xxx) values(xxx,xxx,xxx,xxx);

删

delete from t_test;

改

update t_test set xxx=aaa;

查

select * from t_test;

修改表字段类型

alter table t_test alter column xxx type int;
alter table t_test alter column xxx type varchar(10);
alter table t_test alter column xxx type date;

添加表字段

alter table t_test add column xxx varchar(10);

删除表字段(会删除数据)

alter table t_test drop column "xxx";

修改表字段名

alter table t_test rename column xxx to xxx2;

表数据库字典

select
    a.attname as "字段名",
    --col_description(a.attrelid,a.attnum) as "注释",
    concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as "字段类型",
    format_type(a.atttypid, a.atttypmod) as 类型
from
    pg_class as c,
    pg_attribute as a,
    pg_type as t
where
    c.relname = 't_test'
    and a.atttypid = t.oid
    and a.attrelid = c.oid
    and a.attnum>0;

删除主键

alter table t_test drop constraint xxxx;

备份整个数据库

pg_dump -O -x -h 127.0.0.1 -U postgres -p 5432 db_test -f dbbak.sql

还原数据库

create database db_test;
psql -d db_test -U postgres -f dbbak.sql

备份表

pg_dump -O -x -h 127.0.0.1 -U xxx -p 5432 db_test --column-inserts -a -t t_test1 -t t_test2 -t t_test3  -f dbbak.sql;

查看数据库死锁的sql

select datname,pid,usename,client_addr,client_port,wait_event_type,wait_event,backend_start,query_start,state  from pg_stat_activity where wait_event_type='Lock';

select * from pg_stat_activity where datname='vcloud' and wait_event_type='Lock';
select * from pg_stat_activity where datname='';
-- 查可能被锁的表
select * from pg_locks where relation=(select oid from ps_class where relname='表');

-- 查询阻塞的sql（死锁了，没有执行通过的sql）
select 
	pg_stat_activity.*,
	pg_locks.locktype,
	pg_locks.database,
	pg_locks.pid,
	pg_locks.mode,
	pg_locks.relation,
	pg_class.relname
from 
	pg_stat_activity,pg_locks,pg_class
where
	pg_stat_activity.pid=pg_locks.pid
and pg_locks.relation=pg_class.oid
and pg_stat_activity.waiting='t'
and upper(pg_class.relname)='AS_ASSETS_EXT';

杀死死锁进程来解除死锁

-- 取消后台操作，回滚未提交事物 
select pg_cancel_backend('pid');

-- 中断session，回滚未提交事物（pg_stat_activity表state字段中值为idle in transaction的，可以使用下面的中断函数解锁）
select pg_terminate_backend(pid);

数据库查询缓慢
表大小：

select pg_size_pretty(pg_table_size('t_test'));
select count(1) from t_test;
select reltuples from pg_class where relname = 't_test';

清理垃圾数据，是否空间

vacuum full analyze public.tbl_xxxx;

生成 1-10 之间的整数

select ceil(random() * 10) as randint;

随机字母

select  chr(int4(random()*26)+65);

随机4位字母

select  repeat(chr(int4(random()*26)+65),4);

随机数字 十位不超过6的两位数

select  (random()*(6^2))::integer;

三位数

select  (random()*(10^3))::integer;

产生10位的随机数

select array_to_string(array(select substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' from (ceil(random()*62))::int for 1) from generate_series(1, 10)), '');

产生随机uuid（md5）

select md5(clock_timestamp()::text)::uuid;

postmaster.pid不存在，重新标记日志位置

su postgres
./pg_resetwal -f 数据库数据目录
su root
service postgresql start

查看每张表占用空间

SELECT table_name, pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_indexes_size(table_name) AS indexes_size, pg_total_relation_size(table_name) AS total_size FROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name FROM information_schema.tables ) AS all_tables ORDER BY total_size DESC ) AS pretty_sizes;

检查回环

select a.uidroleid from tbl_test a,tbl_test b
WHERE a.uidroleid = b.uidparentid and  b.uidroleid = a.uidparentid;

删除重复数据

delete from tbl_test
where recordid in (select recordid from tbl_testgroup by recordid having count(recordid ) > 1)
and ctid not in (select min(ctid) from tbl_testgroup by recordid having count(recordid ) > 1);
或 
delete from tbl_test2 where ctid not in (select min(ctid) from tbl_test2  group by (recordid,strvalue));

表大小

select pg_size_pretty(pg_table_size('tbl_test'));

2、配置文件

（1）postgresql.conf
         该文件包含一些通用的设置，比如内存分配、新建database的默认存储位置、Postgresql服务的IP地址、日志位置以及许多其他位置。
（2）pg_hba.conf
         该文件用于控制Postgresql服务器的访问权限，具体包括：允许哪些用户连接到那个数据库，允许哪些IP地址连接到本地服务器，以及定制连接时使用的身份验证模式。

（3）pg_ident.conf
         如果该文件存在，则系统会基于文件内容将当前登录的操作系统用户映射为一个Postgresql数据库内部的身份来登录。有些人会把操作系统的root用户映射为Postgresql的postgres超级用户账号。

3、数据库日志

日志种类
（1）PostgreSQL有3种日志
         pg_log（数据库运行日志） 内容可读 默认关闭的，需要设置参数启动
         pg_xlog（WAL 日志，即重做日志） 内容一般不具有可读性 强制开启
         pg_clog（事务提交日志，记录的是事务的元数据） 内容一般不具有可读性 强制开启
（2）日志所在路径
         pg_xlog和pg_clog一般是在$PGDATA下面的文件夹下
         pg_log默认路径是$PGDATA/pg_log，实际路径可以在$PGDATA/postgresql.conf文件中设置

         pg_log记录各种Error信息，以及服务器与DB的状态信息，可由用户随意更新删除
         pg_xlog与pg_clog记录数据库的事务信息，不得随意删除更新，做物理备份时要记得备份着两个日志。
（3）配置文件中与日志相关的配置

	logging_collector = on/off

         是否将日志重定向至文件，默认是off

	log_directory = 'pg_log'

         日志文件目录，默认是PGDATA的相对路径，即PGDATA的相对路径，即{PGDATA}/pg_log，也可以改为绝对路径，默认为${PGDATA}/pg_log，即集群目录下，但是日志文件可能会非常多，建议将日志重定向到其他目录或分区。将此配置修改为${PGDATA}/pg_log下，必须先创建此目录，并修改权限，chown postgres:postgres ${PGDATA}/pg_log

	log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

         日志文件命名形式，使用默认即可

	log_rotation_age = 1d

         单个日志文件的生存期，默认1天，在日志文件大小没有达到log_rotation_size时，一天只生成一个日志文件

	log_rotation_size = 10MB

         单个日志文件的大小，如果时间没有超过log_rotation_age，一个日志文件最大只能到10M，否则将新生成一个日志文件

	log_truncate_on_rotation = off

         当日志文件已存在时，该配置如果为off，新生成的日志将在文件尾部追加，如果为on，则会覆盖原来的日志。

	log_lock_waits = off

         控制当一个会话等待时间超过deadlock_timeout而被锁时是否产生一个日志信息。在判断一个锁等待是否会影响性能时是有用的，缺省是off。

	log_statement = ‘none’ # none, ddl, mod, all

         控制记录哪些SQL语句。none不记录，ddl记录所有数据定义命令，比如CREATE,ALTER,和DROP 语句。mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE等,all记录所有执行的语句，将此配置设置为all可跟踪整个数据库执行的SQL语句。

	log_duration = off

         记录每条SQL语句执行完成消耗的时间，将此配置设置为on,用于统计哪些SQL语句耗时较长。

	log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements and their durations, > 0 logs only statements running at least this number of milliseconds

         -1表示不可用，0将记录所有SQL语句和它们的耗时，>0只记录那些耗时超过（或等于）这个值（ms）的SQL语句。个人更喜欢使用该配置来跟踪那些耗时较长，可能存在性能问题的SQL语句。虽然使用log_statement和log_duration也能够统计SQL语句及耗时，但是SQL语句和耗时统计结果可能相差很多行，或在不同的文件中，但是log_min_duration_statement会将SQL语句和耗时在同一行记录，更方便阅读。

	log_connections = off 

         是否记录连接日志

	log_disconnections = off 

         是否记录连接断开日志

	log_line_prefix = '%m %p %u %d %r '

         日志输出格式（%m,%p实际意义配置文件中有解释）,可根据自己需要设置（能够记录时间，用户名称，数据库名称，客户端IP和端口，方便定位问题）

	log_timezone = 'Asia/Shanghai'

         日志时区，最好和服务器设置同一个时区，方便问题定位