PostgreSQL 学习笔记

评论

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的 BSD 许可证下发行。

命令

\d           当前数据库的所有表
\dt          只显示匹配的表  
\di          只显示索引
\ds          只显示序列
\dv          只显示视图
\df          只显示函数
\du          列出所有的数据库用户和角色
\dg          列出所有的数据库用户和角色
\encoding    指定客户端的字符编码，如 \encoding UTF8
\x           把表中的每一行的每列数据都拆分为单行展示, 与 MySQL 中的 "\G" 的功能类似

Login

psql -U postgres
psql -h 192.168.0.1 -p 5432 -U repuser -d postgres -W
psql -U postgres -d test
set env
export PGDATABASE=postgres
export PGHOST=192.168.0.1
export PGPORT=5432
export PGUSER=postgres
------------------------------------------------------------------------------

psql

Logout

\q

Create Database

create database test;

Use Database

\c postgres

\c test

Show Tables

\d

\d "Table1"

Create User

create user repuser REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'postgres';

Drop User

drop user repuser;

备份

pg_dump -U postgres test > test.sql 

pg_dump -U postgres -d test -s > test.sql    # -s, --schema-only  dump only the schema, no data

查询

select "Name", "Age" from "Person" where "Sex" = 1 order by "Age" desc limit 3;

 Name|  Age
 ----+-------
 张三 | 20
 李四 | 18
 王五 | 16
(3 rows)

查看数据库、表、索引大小

select pg_size_pretty(pg_database_size('test'));
select pg_size_pretty(pg_table_size('test_table'));   
select pg_size_pretty(pg_indexes_size('test_index'));

查询计划

explain analyze select "Name", "Age" from "Person" where "Sex" = 1 order by "Age";

创建索引

CREATE INDEX person_age
    ON public."Person" USING btree
    ("Age" COLLATE pg_catalog."default")
    TABLESPACE pg_default;

CREATE INDEX person_name
    ON public."Person" USING btree
    ("Name") TABLESPACE pg_default;

查看索引

select * from pg_indexes where tablename='Person';

vacuum

vacuum 操作可以手动和自动。设置自动 vacuum 注意必须设置 track_counts = true 。具体的设置可以参照官方的文档。（8.3 版本以后）

vacuumdb --analyze --verbose -f --dbname=test

好处

  1. 释放，再利用更新或者删除的行所占据的磁盘空间。
  2. 更新 PostgreSQL 查询计划中使用的统计数据。
  3. 防止因事务 ID 的重置而使非常老的数据丢失。

原因

  1. PostgreSQL 数据的插入，更新，删除操作并不是真正放到数据库空间。如果不定期释放空间的话，由于数据太多，查询速度会巨降。
  2. PostgreSQL 在做查询处理的时候，为了是查询速度提高，会根据统计数据来确定执行计划。如果不及时更新的话，查询的效果可能不如预期。
  3. PostgreSQL 中每一个事务都会产生一个事务 ID，但这个数字是有上限的。当事务 ID 达到最大值后，会重新从最小值开始循环。这样如果不及时把以前的数据释放掉的话，原来的老数据会因为事务 ID 的丢失而丢失掉。

参考资料

blazehuCloudNative/SRE/DevOps

个人简介。