[草稿]pg入门篇

[TOC]

安装与卸载

• 快速安装 PostgreSQL 10.12

#.开始安装
cd /opt/
wget -c http://iso.sqlfans.cn/postgresql/postgresql-10.12-1-linux-x64-binaries.tar.gz
wget -c http://iso.sqlfans.cn/postgresql/install_pgsql_1012.sh
wget -c http://iso.sqlfans.cn/postgresql/my_pgsql.conf
bash install_pgsql_1012.sh /opt /data 1921

#.登陆测试
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "SELECT * FROM pg_shadow;"

• 快速卸载

ps aux | fgrep 'postgres' | fgrep "bin" | awk '{print $2}' | xargs kill -9
rm -rf /data/pgsql*
rm -rf /opt/pgsql
userdel -r postgres

基本操作

登入与登出

#.登陆测试
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "SELECT * FROM pg_shadow;"

#.登录数据库
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres

--.使用 user02 登录 testdb 数据库
\c testdb user02

--.退出交互式界面
\quit
\q

数据库的管理

• 与 MySQL 不同，PostgreSQL 不支持 create if not exists 语法

--.建议先创建账号,再创建数据库
CREATE USER user_test WITH PASSWORD 'Aa_123456';
CREATE DATABASE db1 WITH OWNER=user_test ENCODING='UTF-8';

--.创建数据库,分配owner为postgres
CREATE DATABASE db2 WITH OWNER=postgres ENCODING='UTF-8';

--.查看数据库清单
SELECT * from pg_database;
\list
\l

--.切换上下文数据库
\connect db1
\c db1

--.查看所有数据库的大小
select datname, pg_database_size(datname) AS size_byte, pg_size_pretty(pg_database_size(datname)) as size_kb from pg_database order by size_byte desc;

--.查看指定数据库的大小
select pg_size_pretty(pg_database_size('db1'));

--.删除数据库
DROP DATABASE db2;

数据表的管理

--.切换上下文数据库
\c db1

--.创建数据表
CREATE TABLE aaa (id int NOT NULL, name character(32), number char(5), CONSTRAINT aaa_pk PRIMARY KEY (id)) with(fillfactor=85);
INSERT INTO aaa (id, name, number) VALUES (1, '张三', '1023');
SELECT * FROM aaa;

--.查看当前数据库下所有的表名
SELECT * FROM pg_tables where schemaname = 'public';
\d

--.查看特定表的所有字段情况
\d aaa;

--.查看当前数据库下所有表的大小
select relname, pg_total_relation_size(relid) as size_byte, pg_size_pretty(pg_total_relation_size(relid)) as size_kb 
from pg_stat_user_tables where schemaname='public' 
order by pg_relation_size (relid) desc;

--.通过系统数据字典查询表结构
select col.table_schema,col.table_name,col.ordinal_position,col.column_name,col.data_type,col.character_maximum_length
  ,col.numeric_precision,col.numeric_scale,col.is_nullable,col.column_default,des.description
from information_schema.columns col 
left join pg_description des on col.table_name::regclass = des.objoid and col.ordinal_position = des.objsubid
where table_schema = 'public' and table_name = 'aaa'
order by ordinal_position;

--.通过系统数据字典查询表结构
select table_catalog,table_schema,table_name,column_name,ordinal_position,data_type,character_maximum_length,is_nullable 
from information_schema.columns where table_schema='public';

--.通过系统数据字典查询索引信息
select A.SCHEMANAME,A.TABLENAME,A.INDEXNAME,A.TABLESPACE,A.INDEXDEF,B.AMNAME,C.INDEXRELID
  ,C.INDNATTS,C.INDISUNIQUE,C.INDISPRIMARY,C.INDISCLUSTERED,D.DESCRIPTION
from PG_AM B 
left join PG_CLASS F on B.OID = F.RELAM 
left join PG_STAT_ALL_INDEXES E on F.OID = E.INDEXRELID 
left join PG_INDEX C on E.INDEXRELID = C.INDEXRELID 
left outer join PG_DESCRIPTION D on C.INDEXRELID = D.OBJOID,PG_INDEXES A
where A.SCHEMANAME = E.SCHEMANAME and A.TABLENAME = E.RELNAME and A.INDEXNAME = E.INDEXRELNAME 
and E.SCHEMANAME = 'public' and E.RELNAME = 'aaa';

--.删除数据表
drop table if exists aaa;

增删改查

--.创建测试表
\c db1
CREATE TABLE IF NOT EXISTS aaa (id int NOT NULL, name character(32), number char(5), CONSTRAINT aaa_pk PRIMARY KEY (id)) with(fillfactor=85);

#.增删改查
INSERT INTO aaa (id, name, number) VALUES (1, '张三', '1023');
INSERT INTO aaa (id, name, number) VALUES (2, '李四', '1024');
INSERT INTO aaa (id, name, number) VALUES (3, '王五', '1025');
UPDATE aaa SET name='麻子' WHERE id=3;
SELECT * FROM aaa WHERE id>0;

账号的管理

• 创建超级用户

--.创建超级用户
CREATE ROLE root SUPERUSER PASSWORD 'rIFz_1Se4B7pBK6Z' LOGIN;
CREATE ROLE dba_admin SUPERUSER PASSWORD 'Admin_147' LOGIN;
SELECT * FROM pg_shadow;

• 创建业务账号

--.创建数据库，与MySQL不同，PostgreSQL不支持create if not exists语法
CREATE DATABASE xxx_pay_gateway WITH OWNER=postgres ENCODING='UTF-8';
\l

--.创建业务账号（先授权数据库，再进入数据库授权表权限）
CREATE USER user_pay_gateway WITH PASSWORD 'Aa_123456';
GRANT ALL PRIVILEGES ON DATABASE xxx_pay_gateway TO user_pay_gateway;
\c xxx_pay_gateway
GRANT ALL PRIVILEGES ON all tables in schema public TO user_pay_gateway;

• 创建只读账号

--.允许访问特定库的所有表
CREATE USER user_bigdata_readonly WITH PASSWORD 'Aa_123456';
\c xxx_pay_gateway
GRANT SELECT ON all tables in schema public TO user_bigdata_readonly;

--.只允许访问特定库的特定表，比如 aaa
CREATE USER user_bigdata_readonly WITH PASSWORD 'Aa_123456';
\c db1
GRANT SELECT ON TABLE aaa TO user_bigdata_readonly;

• 查询与修改账号

--.查看账号
SELECT * FROM pg_shadow;
select * from pg_user;
SELECT * FROM pg_roles;

--.查看数据库用户
\du

--.查看当前连接的用户名
select user;

--.设置用户密码过期时间
alter user user_bigdata_readonly valid until '2024-12-31';

--.密码永不过期
alter user user_bigdata_readonly valid until 'infinity';

--.禁用与启用，疑似低版本(比如 pg 10.12)不支持
alter user user_bigdata_readonly lock;
alter user user_bigdata_readonly unlock;

--.限制用户的连接数
ALTER USER user_bigdata_readonly WITH CONNECTION LIMIT 200;

--.修改密码
ALTER USER user_bigdata_readonly WITH PASSWORD 'Q8yWuLJ1q_Sth9fA';

• 角色和用户的区别：角色默认没有登录权限，而用户有登录权限

--.创建角色
create role role01 with password 'Aa_1234456';

--.创建用户继承角色的权限
create user user01 with password 'Aa_1234456' inherit;

--.创建用户不继承角色的权限
create user user02 with password 'Aa_1234456' noinherit;

--.将角色赋给用户
grant role01 to user01, user02;

--.赋权给角色
--.此时 user01 用该表的查询权限，但 user02 没有 (noinherit)
grant select on table tb01 to role01;

--.使用 user02 登录数据库
\c testdb user02

• 授权与撤销

--.授权：先授权数据库，再授权表权限(需要管理员进入数据库进行授权)
GRANT ALL PRIVILEGES ON DATABASE xxx_pay_gateway TO user_pay_gateway;
\c xxx_pay_gateway
GRANT ALL PRIVILEGES ON all tables in schema public TO user_pay_gateway;

--.仅赋予读写权限
-- grant select,insert,update,delete ON ALL TABLES IN SCHEMA public to user_pay_gateway;

--.撤销：先撤销数据库权限，再撤销表权限
REVOKE ALL PRIVILEGES ON DATABASE xxx_pay_gateway FROM user_pay_gateway;
REVOKE ALL PRIVILEGES ON all tables in schema public FROM user_pay_gateway;

• 删除账号，当用户有表和数据库依赖关系时，需要先删除或转移相关的表和数据库

--.删除账号
DROP USER user_pay_gateway;

索引与调优

索引的创建与删除

• 准备测试数据

--.创建测试表
\c db1
CREATE TABLE IF NOT EXISTS xxx (id int NOT NULL, name character(32), nickname character(32), number char(5));

INSERT INTO xxx (id, name, nickname, number) VALUES (1, '曹操', '孟德', '1023');
INSERT INTO xxx (id, name, nickname, number) VALUES (2, '刘备', '玄德', '1024');
INSERT INTO xxx (id, name, nickname, number) VALUES (3, '孙权', '仲谋', '1025');

• 索引的创建

--.切换上下文数据库
\c db1

--.创建索引
CREATE INDEX idx_1 ON xxx USING btree(name) WITH (fillfactor='90');

--.创建函数索引
CREATE INDEX idx_2 ON xxx USING btree(upper(nickname) varchar_pattern_ops);

--.创建复合索引
CREATE INDEX idx_3 ON xxx USING btree(id, upper(name) varchar_pattern_ops);

• 索引的确认与删除

--.切换上下文数据库
\c db1

--.删除索引
DROP INDEX idx_3;

--.查看索引
select * from pg_indexes;
select * from pg_statio_all_indexes;

--.查看索引使用记录
select * from pg_stat_all_indexes;
select * from pg_stat_user_indexes;

--.如果不通过 analyze 来更新统计信息，得到的执行计划是不准确的
analyze xxx;

--.分析执行计划
explain analyze SELECT * FROM xxx WHERE id=2;
explain analyze SELECT name FROM xxx where id>1 ORDER BY 1 LIMIT 8;

• 索引的高级用法

--.切换上下文数据库
\c db1

--.创建部分索引，对于固定条件的查询（示例 id=1 为固定的条件）使用部分索引可以减少索引的大小，同时提升查询效率
select * from xxx where id=1 and name=?;
create index idx_name_id on xxx(name) where id=1;

--.创建索引时加 CONCURRENTLY 关键字，可以并行创建，且不会堵塞DML操作，否则会堵塞DML操作
create index CONCURRENTLY idx_id on xxx(id);

--.如何加快创建索引的速度，调大maintenance_work_mem，可以提升创建索引的速度，但是需要考虑实际的可用内存
begin;
set local maintenance_work_mem='2GB';
create index idx on xxx(id);
end;