PostgreSQL

开始使用

创建用户和数据库

安装后只有一个 postgres 一个用户（数据库超级用户）。要添加用户，可以使用

sudo -u postgres createuser $USER #交互式添加当前用户

使用 createdb dbname 来创建数据库（省略数据库名将默认使用当前用户名），使用 psql 来交互执行SQL。

psql

psql 接受数据库名作为参数，默认使用当前用户名一致的数据库（如果不存在则出错）。

\l 列出数据库
\dn 列出模式
\dg 列出的角色
\d 列出表（可指定表以描述表）

SQL 示例

自动更新时间戳

^[1]

CREATE OR REPLACE FUNCTION updated_at_trigger()
RETURNS TRIGGER AS $$
BEGIN
   IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
      NEW.updated_at = now(); 
      RETURN NEW;
   ELSE
      RETURN OLD;
   END IF;
   RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER table_updated BEFORE UPDATE
  ON table FOR EACH ROW EXECUTE PROCEDURE updated_at_trigger();

转换列为自增整数

^[2]

CREATE SEQUENCE foo_a_seq;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');
ALTER TABLE foo ALTER COLUMN a SET NOT NULL;
ALTER SEQUENCE foo_a_seq OWNED BY foo.a;

时间间隔转秒数

^[3]

select extract ('epoch' from '5 days 5 hours'::interval);

查询锁等待情况

以下语句可查询正在等待和被等待的进程/操作的信息^[4]。适用于版本 9.1^[5]。

SELECT
   waiting.locktype           AS waiting_locktype,
   waiting.relation::regclass AS waiting_table,
   waiting_stm.current_query  AS waiting_query,
   waiting.mode               AS waiting_mode,
   waiting.pid                AS waiting_pid,
   other.locktype             AS other_locktype,
   other.relation::regclass   AS other_table,
   other_stm.current_query    AS other_query,
   other.mode                 AS other_mode,
   other.pid                  AS other_pid,
   other.granted              AS other_granted
FROM
   pg_catalog.pg_locks AS waiting
JOIN
   pg_catalog.pg_stat_activity AS waiting_stm
   ON (
       waiting_stm.procpid = waiting.pid
   )
JOIN
   pg_catalog.pg_locks AS other
   ON (
       (
           waiting."database" = other."database"
       AND waiting.relation  = other.relation
       )
       OR waiting.transactionid = other.transactionid
   )
JOIN
   pg_catalog.pg_stat_activity AS other_stm
   ON (
       other_stm.procpid = other.pid
   )
WHERE
   NOT waiting.granted
AND
   waiting.pid <> other.pid;

以下代码适用于版本 9.2^[6]及 9.3：

SELECT
   waiting.locktype           AS waiting_locktype,
   waiting.relation::regclass AS waiting_table,
   waiting_stm.query          AS waiting_query,
   waiting.mode               AS waiting_mode,
   waiting.pid                AS waiting_pid,
   other.locktype             AS other_locktype,
   other.relation::regclass   AS other_table,
   other_stm.query            AS other_query,
   other.mode                 AS other_mode,
   other.pid                  AS other_pid,
   other.granted              AS other_granted
FROM
   pg_catalog.pg_locks AS waiting
JOIN
   pg_catalog.pg_stat_activity AS waiting_stm
   ON (
       waiting_stm.pid = waiting.pid
   )
JOIN
   pg_catalog.pg_locks AS other
   ON (
       (
           waiting."database" = other."database"
       AND waiting.relation  = other.relation
       )
       OR waiting.transactionid = other.transactionid
   )
JOIN
   pg_catalog.pg_stat_activity AS other_stm
   ON (
       other_stm.pid = other.pid
   )
WHERE
   NOT waiting.granted
AND
   waiting.pid <> other.pid;

从 other_pid 查询导致此等待的进程的命令行（其中 pid 是自定义命令）：

pid=12345; pid $(sudo netstat -ntp | awk -vport=$(pid $pid | grep -oP '(?<=\()\d+') '{if($4 ~ ":"port){ print $7; }}' | cut -d/ -f1)

另见Lock Monitoring - PostgreSQL wiki。

使用 lateral join 连接最新列

lateral join 可以对每一列数据，执行子查询来获取额外的数据。

select id, a, b, c from table_a left join lateral (
  select d, e from table_b where table_a.id = table_b.a_id order by ts desc limit 1
) as tbl_b on true;

显示索引定义

^[7]

SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

语法

字段

默认字段名是转成小写的，当使用双引号括起来的时候才会保留大小写。据称这是为了符合 SQL 标准。^[8]

序列操作

使用 setval 函数修改一个序列的值：^[9]

select setval('my_id_seq', 4, true);

排序

修改指定列的排序规则：

ALTER TABLE user_profiles ALTER COLUMN uname TYPE text COLLATE "zh_CN";

zh_CN 排序规则会按拼音排序，并且不区分大小写。

注：列级别的排序规则是在 9.1 版本加入的。

事务与并发

并发更新与删除

在默认 ISOLATION LEVEL（Read committed）下，并发删除可以同时进行；在 Repeatable read ISOLATION LEVEL 下，并发删除中，较后执行的一方会报错。如：

insert into t values (1);
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
select * from t; -- see data with id 1
-- in another transaction, delete above data
delete from t where id = 1; -- delete above data on our side
-- 错误:  由于同步更新而无法串行访问
ROLLBACK;

并发更新同一字段时也是如此，冲突方会报错。

配置与管理

给用户授权

授权用户一个数据库的 schema 中的所有权限：^[10]^[11]

GRANT CONNECT ON DATABASE mediawiki TO lilydjwg;
GRANT ALL PRIVILEGES ON SCHEMA public TO lilydjwg;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO lilydjwg;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO lilydjwg;

鉴权配置

Ubuntu 默认对本地用户使用 peer 方法验证。此方法会使用使用者的 Unix 用户身份，导致即使密码正确依旧可能被拒绝。修改 /etc/postgresql/9.1/main/pg_hba.conf ，将本地用户部分的验证方法改为 md5 即可在其他 Unix 用户身份下登录到 PostgreSQL。^[12]

更改用户密码

ALTER USER user_name WITH PASSWORD 'password';

文件配置

在配置文件 /var/lib/postgres/data/postgresql.conf 中设置 stats_temp_directory 为位于 tmpfs 上的目录（比如 /run/postgresql）可以防止无活动时依然写磁盘^[13]。注意 PostgreSQL 并不会自动创建此目录。

日志默认输出到 stderr，会被 systemd 记录到系统日志中，占用大量空间。可在此文件中设置 logging_collector = on 以写入文件中。

给一组用户授权

-- authorize a group of people to select
create role pkg;
grant connect on database lilac_db to pkg;
grant usage on schema lilac to pkg;
grant select on all tables in schema lilac to pkg;

-- create and grant each user
create role newuser login;
grant pkg to newuser;

注意事项

PostgreSQL 不同次版本号创建的数据库不通用，并且，只有使用对应版本号的程序才能够导出和导入。没有用于升级数据库的程序。

PostgreSQL 的数据库文件可直接复制后给相同版本的 PostgreSQL 实例使用，不需要任何修复操作。

参见

SQL

外部链接

第三方模块

参考资料

↑ database - How do I automatically update a timestamp in PostgreSQL - Stack Overflow
↑ postgresql - Adding 'serial' to existing column in Postgres - Stack Overflow
↑ Re: how to convert a time interval to seconds?
↑ Problem running "ALTER TABLE...", ALTER TABLE waiting
↑ PostgreSQL: Documentation: 9.1: The Statistics Collector, 9.1 中 pg_stat_activity 视图的文档
↑ PostgreSQL: Documentation: 9.2: The Statistics Collector, 9.2 中 pg_stat_activity 视图的字段名发生了变化。
↑ PostgreSQL List Indexes
↑ The Site With The LAMP » postgres and case insensitive column names.
↑ postgresql - Postgres manually alter sequence - Stack Overflow
↑ Give all the permissions to a user on a DB - Stack Overflow
↑ https://www.postgresql.org/docs/current/sql-grant.html PostgreSQL: Documentation: 11: GRANT
↑ https://help.ubuntu.com/13.04/serverguide/postgresql.html
↑ https://wiki.archlinux.org/index.php/PostgreSQL#Prevent_disk_writes_when_idle

[1] tabase - How do I automatically update a timestamp in PostgreSQL - Stack Overflow

[2] stgresql - Adding 'serial' to existing column in Postgres - Stack Overflow

[3] Re: how to convert a time interval to seconds?

[4] Problem running "ALTER TABLE...", ALTER TABLE waiting

[5] PostgreSQL: Documentation: 9.1: The Statistics Collector, 9.1 中 pg_stat_activity 视图的文档

[6] PostgreSQL: Documentation: 9.2: The Statistics Collector, 9.2 中 pg_stat_activity 视图的字段名发生了变化。

[7] PostgreSQL List Indexes

[8] The Site With The LAMP » postgres and case insensitive column names.

[9] stgresql - Postgres manually alter sequence - Stack Overflow

[10] Give all the permissions to a user on a DB - Stack Overflow

[11] ttps://www.postgresql.org/docs/current/sql-grant.html PostgreSQL: Documentation: 11: GRANT

[12] ttps://help.ubuntu.com/13.04/serverguide/postgresql.html

[13] ttps://wiki.archlinux.org/index.php/PostgreSQL#Prevent_disk_writes_when_idle

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

[12]

[13]