PostgreSQL CheatSheet

0x00 前言

本文为 Cheatsheet 类型文章,用于记录我在日常编程中经常使用的 PostgreSQL 相关和命令。

PostGIS 相关的资料参考文章 Geo Processing With Python

  • 安装与基本配置
  • PostgreSQL 配套工具
  • PostgreSQL SQL 常用代码片段
  • Python Driver : psycopg2 , 与两个 ORM ( Django ORM / SQLAlchemy )

不定期更新。

0x01 安装,配置,基本 shell 命令

安装

配置

基本 Shell 命令

1
2
3
4
5
# 开启关闭
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop
pg_ctl -D /usr/local/var/postgres start
pg_ctl -D /usr/local/var/postgres stop -s -m fast

数据的导入导出

1
2
3
4
5
pg_dump -C -Fp -f dump.sql -U twocucao QCS -h 192.168.2.175
pg_dump -C -Fp -f 20160602-150144-dump.sql -U twocucao QCS --column-inserts --data-only --table=users_table -h 192.168.2.175
# 插入数据
psql -U twocucao -d QCS -a -f insert_doc_ids.sql -h 192.168.2.175
pg_restore --verbose --clean --no-acl --no-owner -h localhost example.dump

0x02 PostgreSQL 配套工具

  • JetBrain 的 Datagrip 作为 编写大段 SQL 语句的 IDE
  • 通过网络或者 Dash 查看文档
  • PostgreSQL 官方自带工具

0x03 PostgreSQL SQL 常用代码

3.1 PostgreSQL 相关

1
2
3
4
5
6
7
8
9
10
-- 强行中断连接到此数据库的 session
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'demoweb' ;

3.2 DCL ( Data Control Languge )

1
2
3
4
5
6
7
8
9
10
11
-- 创建只读用户
\c demoweb
CREATE ROLE ro_user WITH LOGIN ENCRYPTED PASSWORD 'xxx123456';
GRANT CONNECT ON DATABASE demoweb TO ro_user;
-- This assumes you're actually connected to mydb..
GRANT USAGE ON SCHEMA public TO ro_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO ro_user;
-- 撤销数据库连接 ()
REVOKE CONNECT ON DATABASE demoweb FROM PUBLIC, demoweb;

3.3 DDL ( Data Definition Language )

CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

3.4 DML ( Data Manipulation Languge )

SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

3.5 TCL ( Transaction Control Languge )

0x04. 常用代码片段

4.1. Tips And Hacks

Recursive Query

1
2
3
4
5
WITH cte_name(
CTE_query_definition -- non-recursive term
UNION [ALL]
CTE_query_definition -- recursive term
) SELECT * FROM cte_name;

4.2. 大数据量运算技巧

4.3 备份还原技巧

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 需要备份的机器
DB_NAME='xxxdb'
DUMP_DB_FILE='latest_dump.sql.gz'
sudo -u postgres pg_dump $DB_NAME | gzip -9 > $DUMP_DB_FILE
TARGET_HOSTNAME='xxx.org'
TARGET_PATH='/webapps/'
scp $DUMP_DB_FILE root@$TARGET_HOSTNAME:/webapps/
# 需要还原的机器
DB_NAME='xxxdb'
DUMP_DB_FILE='latest_dump.sql.gz'
sudo -u postgres dropdb $DB_NAME
sudo -u postgres createdb $DB_NAME
gunzip < $DUMP_DB_FILE | sudo -u postgres psql $DB_NAME

0x05. 并发优化技巧

优化技巧请参考我关于 MySQL 的一片文章。

5.1 ACID

  • Atomicity : 行不行,给个准话
  • Consistency : 完成时候,数据保持一致(多版本并发控制)
  • Isolation : 事务与事务之间是隔离的。即一事务无法查看另一个事务正在修改的数据(默认,如果不默认这玩意,则隔离程度是可以设置的)
  • Durablity : 就是存下来了。

多版本并发控制模型

  • Each query sees only transactions completed before it started
  • On query start, PostgreSQL records:
    • the transaction counter
    • all transaction id’s that are in-process
  • In a multi-statement transaction, a transaction’s own previous queries are also visible
  • The above assumes the default read committed isolation level

使用 MVCC 多版本并发控制比锁定模型的主要优点是在 MVCC 里, 对检索(读)数据的锁要求与写数据的锁要求不冲突, 所以读不会阻塞写,而写也从不阻塞读。
在数据库里也有表和行级别的锁定机制, 用于给那些无法轻松接受 MVCC 行为的应用。 不过,恰当地使用 MVCC 总会提供比锁更好地性能。

5.2 DDL 事务

DDL 可以多条放在一起,然后直接 DDL, 据说可以在 sharding 时候用….

5.3 事务使用

1
2
3
4
5
6
begin;
-- insert_somethings;
savepoint my_savepoint01;
-- wrong ops
rollback to my_savepoint01;
commit;

5.4 事务隔离级别

  • READ UNCOMMITED
  • READ COMMITED
  • REPEATABLE READ
  • SEARLIZABLE

  • 脏读 : 和程序的并发一致 默认是不可能的。

  • 不可重复读 : 一个事物重新读取前面读过的数,但是发现被改过了。能读原来则是可重复读。读新的,则是不可重复读。
  • 幻读 : (举一个为赋新词强说愁的例子)比如,先 count 一下,然后依照 count 值遍历 cursor, 结果发现数量发生变化。

读已提交,是默认。在这里,脏读(不会)、不可重复读(可能)、幻读(可能)。

5.5 锁机制

  • 表级锁模式
  • 行级锁模式

5.6 死锁

死锁的典型案例就是:

  1. 当你找你爸要钱的时候,你爸说,要是你妈给你钱,我就给你钱。
  2. 当你找你妈要钱的时候,你妈说,要是你爸给你钱,我就给你钱。

死锁的四个必要条件:

  • 互斥条件
  • 请求和保持条件
  • 不剥夺条件
  • 环路等待条件

避免死锁的方式,一般是按照顺序来。

当然,数据库可以自动检测出死锁,但是由于捕获死锁需要一定的代价。可能会导致应用程序过久地持有排他锁。

慎用排他锁。

0x07. 踩坑集

  • 序列问题