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]