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

时间： 2024-09-04 14:20:09

Postgresql数据库运维笔记的相关文章

专访平安科技数据库技术专家梁海安：数据库运维未来很大一部分工作会被平台或工具代替

杭州·云栖大会将于2016年10月13-16日在云栖小镇举办,在这场标签为互联网.创新.创业的云计算盛宴上,众多行业精英都将在这几天里分享超过450个演讲主题. 为了帮助大家进一步了解这场全球前言技术共振盛会的内容,采访了各个论坛的大咖,以飨读者. 以下为正文: 梁海安,平安科技数据库管理技术专家.2006年毕业后即加入平安科技数据库团队,十年Oracle运维管理经验,近几年主要负责PostgreSQL运维管理.目前主要负责平安科技PostgreSQL推广,整体数据库监控平台建设,自动化运维,数

数据库运维原则

一.数据库运维工作总原则 1.能不给数据库做的事情不要给数据库,数据库只做数据容器. 2.对于数据库的变更必须有记录,可以回滚. 二.权限相关 总原则,以最低粒度控制权限. SELECT权限:所有开发人员均可拥有自己业务范围内的表权限. INSERT/UPDATE/DELETE权限:所有项目经理可以拥有自己业务范围内的表权限. Structure权限:数据库管理员可以拥有. Administration权限:系统管理员和数据库管理员可以拥有. 程序访问权限:根据IP和系统名建立用户名,只拥有必须

美团数据库运维自动化系统构建之路

美团点评技术沙龙由美团点评技术团队主办,每月一期.每期沙龙邀请美团点评及其它互联网公司的技术专家分享来自一线的实践经验,覆盖各主要技术领域. 目前沙龙会分别在北京.上海和厦门等地举行,要参加下一次最新沙龙活动?赶快关注微信公众号"美团点评技术团队". 本次沙龙主要围绕数据库相关的主题,内容包括美团数据库自动化运维系统构建.点评侧MySQL自动化服务平台RDS.美团数据库中间件.和小米高级DBA带来的Redis Cluster的大规模运维实践. 讲师简介 宁龙,美团网高级DBA,现负责美

从一个简单的约束看规范性的SQL脚本对数据库运维的影响

原文:从一个简单的约束看规范性的SQL脚本对数据库运维的影响   之前提到了约束的一些特点,看起来也没什么大不了的问题,http://www.cnblogs.com/wy123/p/7350265.html以下以实际生产运维中遇到的一个问题来说明规范的重要性. 如下是一个简单的建表脚本,表面上看起来并没有什么问题.其中创建了3个约束,一个主键约束,一个唯一约束,一个默认值约束,该脚本执行起来没有任何问题. USE Test GO if exists(select 1 from sys.table

论数据库运维的全流程管控技术

本文讲的是 论数据库运维的全流程管控技术,"重建设 轻管理"一直是我国各行业信息化发展的主要困境,这个问题在数据库系统的建设.管理工作中同样存在.近年来,这种局面造成的后果已开始明显显现:各类来自内部或第三方外包人员的数据泄露.丢失和被篡改事件频频发生,由此导致的珍贵数据资产损失和相关系统功能瘫痪等情况,如同紧箍咒一般,三不五时地刺激着运维部门本就紧绷的神经. 数据库运维安全现状 数据库运维人员需要承担数据库系统的权限分配.故障处理.性能优化.数据迁移备份等工作任务.这些关键环节中,如

MySQL智能运维与实践，看关系型数据库如何优雅应对云时代

随着互联网场景的导入,非结构化的海量数据给传统数据库的处理能力带来了极大的挑战,作为最受欢迎的开源关系型数据库,MySQL一步步地占领了原有商业数据库市场.如今Google.Facebook.网易.淘宝等大公司都在使用MySQL数据库.而MySQL的发展也从1.0到如今的8.0版本,其功能的完善和稳定性也得到了很好的保证. 本文包含以下三部分: MySQL8.0 的新特性 云时代MySQL的运维实践 金融行业最佳应用场景 今年8.0版本将会带来哪些惊喜呢? MySQL 8.0 新特性一览 1.I

《IT运维之道》——13.3 数据库

13.3 数据库 数据库是一个单位或是一个应用领域的通用数据处理系统,它存储的是属于企业和事业部门.团体和个人的有关的数据集合.数据库中的数据是从全局观点出发建立的,按一定的数据模型进行组织.描述和存储.其结构基于数据间的自然联系,从而可提供一切必要的存取路径,且数据不再针对某一应用,而是面向全组织,具有整体的结构化特征. 数据库中的数据是为众多用户所共享其信息而建立的,已经摆脱了具体程序的限制和制约.不同的用户可以按各自的用法使用数据库中的数据:多个用户可以同时共享数据库中的数据资源,即不同的

《IT运维之道》一13.3 数据库

13.3 数据库 数据库是一个单位或是一个应用领域的通用数据处理系统,它存储的是属于企业和事业部门.团体和个人的有关的数据集合.数据库中的数据是从全局观点出发建立的,按一定的数据模型进行组织.描述和存储.其结构基于数据间的自然联系,从而可提供一切必要的存取路径,且数据不再针对某一应用,而是面向全组织,具有整体的结构化特征. 数据库中的数据是为众多用户所共享其信息而建立的,已经摆脱了具体程序的限制和制约.不同的用户可以按各自的用法使用数据库中的数据:多个用户可以同时共享数据库中的数据资源,即不同的

运维经验：回滚段异常的特殊救急方法

跟着恩墨一起读好书运动开始啦!咱们作为DBA不仅要外部打扮自己,更要从内部武装.近期,小编将分享冷菠老师的<Oracle高性能自动化运维>一部分精选章节分享给大家.如果你对内容很感兴趣,还是要去买一本比较好哦. 购买:https://item.jd.com/12128635.html 冷菠 冷菠,资深DBA,著有<Oracle高性能自动化运维>,有近10年的数据库运维.团队管理以及培训经验.擅长数据库备份恢复.数据库性能诊断优化以及数据库自动化运维等.目前致力于大数据.智能一体化.