ab520025520025 发表于 2016-11-20 06:27:28

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
       templatetemplate0;

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

使用createdb创建数据库
$ 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 TABLEtbname IS'说明表 ';
COMMENT ON COLUMN tbname.id IS '编号';
COMMENT ON COLUMN tbname.name IS '姓名';
COMMENT ON COLUMN tbname.sexIS'性别';
COMMENT ON COLUMN tbname.log_inIS'登录时间';
COMMENT ON COLUMN tbname.scoreIS'分数';

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 -9pid   --优先使用kill,kill -9的权限很高,可能引起故障

3、创建用户
现有库,如无必要,不创建新用户;
若创建了新库,需要另建用户,操作如下:
Create database tb1 with owner postgre;
Create user user1 encryptedpassword '***'nosuperuser nocreatedbnocreaterole 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=# copytbnameto ’/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 * frompg_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
页: [1]
查看完整版本: Postgresql数据库运维笔记