Postgresql数据库运维笔记

1、 对象创建
研发、测试无权创建、删除数据库和表，也无权修改表结构，都由DBA统一操作
a)创建数据库：
CREATE DATABASE dbsample           --数据库名不能与现有库重复，pg严格区分大小写，因此请统一小写命名，不能使用特殊字符（@ # &等），不能以数字开头，可以以字母和下划线开头，不能超过63个字符
WITH OWNER = postgres                    --指定数据库的属主为postgres      
ENCODING = 'UTF8'                            --一般情况下生产都使用的UTF8的字符集
TABLESPACE = pg_default;                 --一般情况下使用默认表空间
COMMENT ON DATABASE dbsample       --添加数据库备注
IS '模板库';

CREATE DATABASE tinadb
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'zh_CN.UTF-8'
       LC_CTYPE = 'zh_CN.UTF-8'
       CONNECTION LIMIT = -1
       template  template0;

COMMENT ON DATABASE tinadb IS 'tina的测试库';

使用createdb创建数据库
[postgres@localhost bin]$ createdb --encoding=UTF8 --owner=postgresql -U postgres testdb
--encoding=UTF8 设置字符集
--owner=postgres 设置数据库的所有者
--tmplate=tmplate0   设置建库的模板，该模板支持空间数据操作
--U postgres  用postgres身份建立数据库

b)删除数据库
drop database dbname; 

c)创建表
语法：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
{ column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

范例：
CREATE TABLE tbname                                     --表名不能与现有表重复，统一小写命名，不能使用特殊字符（@ # &等），不能以数字开头，可以以字母和下划线开头，不能超过63个字符
(id serial primary key,                                          --每个表都指定一个主键
name varchar(20) not null,                                  --varchar类型的尽可能的不要设置太长，增加不必要的开销  
sex char(2) default 'F' check (sex in ('F', 'M')),    
log_in timestamp without time zone,                   --时间类型的选择，优先使用timestamp，占的字节更少表更小    
score numeric check(score >0 and score<100));

尽量给表和字段都添加上备注说明，方便其他人查看
COMMENT ON TABLE  tbname IS  '说明表 ';
COMMENT ON COLUMN tbname.id IS '编号';
COMMENT ON COLUMN tbname.name IS '姓名';
COMMENT ON COLUMN tbname.sex  IS  '性别';
COMMENT ON COLUMN tbname.log_in  IS  '登录时间';
COMMENT ON COLUMN tbname.score  IS  '分数';

d）表授权
表创建后schema和owner与现有表保持一致：
yunwei=# \dt
                         关联列表
架构模式 |            名称            |  型别  |  拥有者
----------+----------------------------+--------+----------
public   | andriod_1mobile            | 资料表 | postgres

给业务用户授权：
grant select,insert,update,delete on table tbname to sqluser;  --授权给sqluser
由创建表而自动生成的序列也需要授权
grant select,update on sequence tbname_id_seq to sqluser; 
grant select on table tbname to fenxi;                     --授权给fenxi

e）删除表
drop table tbname;       

f）修改表结构
新增字段：
alter table tbname add column telephone bigint not null;

删除字段：
alter table tbname drop column telephone;

修改表结构：
alter table tbname alter column sex set not null;                   --设置非空
alter table tbname alter column score type decimal;             --修改字段类型，并不能修改成任意类型
alter table tbname drop constraint key_md5;                        --删除约束
alter table tbname add column id serial primary key;            --新增自增主键(一个表只能有一个主键)
alter table tbname alter column sample drop not null;          --删除非空约束（非空约束是没有约束名的，因此不能像第一条那么删）

2、 停掉或者kill掉卡住的会话
a)优先在数据库操作
查询活跃的后台会话：
select p.datname,p.usename,p.application_name,p.client_addr,p.query_start,p.current_query,p.waiting,p.procpid from pg_stat_activity p ;
命令：
select pg_cancel_backend('procpid');              --取消session
select pg_terminate_backend('procpid');         --结束session
pg_cancel_backend()操作后，session还在，事物回退;
pg_terminate_backend()操作后，session消失，事物回退。
如果在某些时候pg_terminate_backend()不能杀死session，那么可以在os层面，使用kill命令

b）在操作系统kill
ps –ef|grep postgresql  第二个字段pid，找到需要kill的那个进程
kill pid
kill -9  pid   --优先使用kill，kill -9的权限很高，可能引起故障

3、创建用户
现有库，如无必要，不创建新用户；
若创建了新库，需要另建用户，操作如下：
Create database tb1 with owner postgre;
Create user user1 encrypted  password '***'  nosuperuser nocreatedb  nocreaterole noreplication noinherit;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;  --必须做这一步

4、重新加载数据库参数
部分参数可以不需要重启，reload就能生效
修改参数：
vi /home/pgsql/9.1/data/postgresql.conf
重新加载参数：
/usr/bin/pg_ctl reload -D /home/pgsql/9.1/data

5、修改管理员密码
忘记管理员密码：
vi /home/pgsql/9.1/data/pg_hba.conf
local   all         all                                       trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128                   trust
重新加载：
/usr/bin/pg_ctl reload -D /home/pgsql/9.1/data
改后无需密码认证，就可以直接psql连上数据库
修改密码：
alter user postgres with password  '*****'；

6、alter database命令
数据库的重命名
命令:     ALTER DATABASE
描述:     改变一个数据库
语法:
ALTER DATABASE 名字 SET 参数 { TO | = } { 值 | DEFAULT }
ALTER DATABASE 名字 RESET 参数
ALTER DATABASE 名字 RENAME TO 新名字
ALTER DATABASE 名字 OWNER TO 新属主

7、数据库常用简写命令
\df  列出函数
\di  只列出索引
\do  只列出操作符
\ds  只列出序列
\dS  列出系统表和索引
\dt  只列出非系统表
\dT  列出数据类型 (加 "+" 获取更多的信息)
\db  列出表空间 (加 "+" 获取更多的信息)
\dg  列出组
\dn  列出模式 (加 "+" 获取更多的信息)
\do  列出操作符
\dl  列出大对象, 和 \lo_list 一样
\dp  列出表, 视图, 序列的访问权限
\du  列出用户
\l   列出所有数据库 (加 "+" 获取更多的信息)
\q   退出 psql 程序

8、copy命令
copy命令必须是管理员才能执行
这个命令导出的都是文本格式的，可以用符号隔开，也可以是纯文本的。
语法
COPY 表名 [ ( 字段 [, ...] ) ]
FROM { '文件名' | STDIN }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]

COPY 表名 [ ( 字段 [, ...] ) ]
TO { '文件名' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]
范例：
导出全表数据
postgres=# copy  tbname  to ’/tmp/tbname.txt’;  

导出部分字段，并以;间隔开

导入数据     ---注意copy命令只会在原表数据上附加，而不会覆盖
postgres=# copy tbname from '/tmp/tbname.txt';
COPY 4
导入部分字段
t_url=# copy t_source_url(export_id,source,export_time,key_word) from '/home/hrburl/1.txt';
COPY 134312

9、常用命令
a） 查看大小
SELECT pg_size_pretty(pg_database_size('tm_samples'));    --数据库大小
SELECT pg_size_pretty(pg_relation_size('white_list'));    --表大小
SELECT pg_size_pretty(pg_relation_size('white_list_pkey')); --索引大小
SELECT pg_size_pretty(pg_tablespace_size('pg_default'));    --表空间使用大小

b) 查找对象
查表
select * from pg_tables where tablename='white_list';

查表字段
select table_catalog,table_name,column_name,ordinal_position,column_default,is_nullable,data_type,character_maxinum_length from information_schema.columns where table_name='white_list' order by ordinal_position;

查索引定义
select b.indexrelid from pg_class a,pg_index b where a.oid=b.indrelid and a.relname='white_list';

查序列
select * from information_schema.sequences where sequence_name='t_white_id_seq';

查约束
select oid,conname,connamespace,contype from pg_constraint where conname like '%white%';

查function定义
select oid from pg_proc where procname='zhprs_start';
select * from pg_get_functiondef('oid');


同样的，可以通过系统表信息函数，来获取对象的创建语句
pg_get_viewdef(view_oid)
pg_get_ruledef(rule_oid)
pg_get_indexdef(index_oid)
pg_get_triggerdef(trigger_oid)
pg_get_constraintdef(constraint_oid)

查活动会话
SELECT * from  pg_stat_activity where datname='yunwei';

c）常用类型转换
select round(1::numeric/4::numeric，2);                           --结果0.25
select round( cast ( 1 as numeric )/ cast( 4 as numeric),2);       --结果0.25
select substr(cast(1234 as text),3,1);                             --换成文本，从第三个字符开始，取一个字符出来。
select to_char(current_timestamp, 'HH12:MI:SS');                    --结果16:03:29
select to_date('05 Dec 2000', 'DD Mon YYYY');                        --结果2000-12-05
select to_number('12,454.8-', '99G999D9S');                            --结果-12454.8
select to_timestamp('2014-10-10 10:40:10','yyyy-MM-dd HH24:MI:ss');         --结果2014-10-10 10:40:10+08

10、如何利用pg生产库每日的定期备份紧急恢复数据库？
a)查看pg的备份脚本

备份命令：
pg_dump --host localhost --port 5432 --username "postgres" --format custom --blobs --encoding UTF8 --verbose yunwei --file ./yunwei.backup


b)利用最近的一个备份片恢复单个库

恢复命令：
pg_restore
pg_restore -U postgres -d yunwei /opt/db_backup/20140922/yunwei.backup >/tmp/yunwei.log 2>&1