PostgreSQL 新手导引

概览

结构

template -> database -> schema -> table

不同schema中的表名可以相同。对于多个schema中存在相同表表名的情况,可以通过 schemaname.tablename 访问,也可以为每个schema创建一个与之同名的可登录角色并配置默认查找顺序 search_path 为

search_path = "$user", public;

上述情况用于多用户具有相同业务的系统,比如网店

Features

1) 开源
2) 关系性数据库
3) 自定义聚合函数
4) 自定义数据类型
5) 扩展包
6) 多种认证模式

安装

sudo apt-get install postgresql-client postgresql postgresql-contrib  

配置

配置文件位置可以通过在数据库中执行下面语句查看

postgres=# select name, setting from pg_settings where category = 'File Locations';
       name        |                 setting                  
-------------------+------------------------------------------
 config_file       | /etc/postgresql/9.4/main/postgresql.conf
 data_directory    | /var/lib/postgresql/9.4/main
 external_pid_file | /var/run/postgresql/9.4-main.pid
 hba_file          | /etc/postgresql/9.4/main/pg_hba.conf
 ident_file        | /etc/postgresql/9.4/main/pg_ident.conf
(5 行记录)

1) postgresql.conf

基础配置 详细参考https://www.postgresql.org/docs/9.4/static/view-pg-settings.html 2) pg_hba.conf

用户连接配置,如身份认证。 身份认证方面参考https://www.postgresql.org/docs/9.4/static/auth-methods.html

初始设置

刚安装好时无法直接使用 psql 需要通过刚才生成的名为 postgres 的 linux 系统用户

$ sudo su - postgres 
$ psql

进入数据库,添加我们的用户(角色)

CREATE ROLE username LOGIN PASSWORD 'yourpassword';

为这个用户创建数据库

CREATE DATABASE exampledb WITH owner = username  

切换回我们的用户,登陆刚才创建的数据库

psql exampledb  

如果 PostgreSQL 中存在和当前系统用户同名的用户,则可以省略用户名。此外如果 PostgreSQL 内还存在与当前系统用户同名的数据库,则连数据库名都可以省略

用户权限

详细参照 https://www.postgresql.org/docs/9.4/static/sql-grant.html

数据库操作

基本操作和一般 SQL 语言并无太大差异

新建表

exampledb=# CREATE TABLE admin (
username VARCHAR(20),
password VARCHAR(32));

插入数据

exampledb=# INSERT INTO admin (username, password) values ('root', '63a9f0ea7bb98050796b649e85481845');

INSERT 0 1

查询记录

exampledb=# SELECT * FROM admin;
 username |             password             
----------+----------------------------------
 root     | 63a9f0ea7bb98050796b649e85481845
(1 行记录)

更新记录

exampledb=# UPDATE admin set username='admin' WHERE username='root';
UPDATE 1

删除记录

exampledb=# DELETE  FROM admin WHERE username='admin';
DELETE 1

删除表

exampledb=# DROP TABLE IF EXISTS admin;
DROP TABLE

扩展包

安装扩展包需要两步:

1) 安装至服务器中

扩展包下载点 http://pgxn.org/

查看当前可用扩展包

postgres=# SELECT name, installed_version FROM pg_available_extensions ORDER BY name;
        name        | installed_version 
--------------------+-------------------
 adminpack          | 
 autoinc            | 
 btree_gin          | 
 btree_gist         | 
 chkpass            | 

2) 安装至数据库中

CREATE EXTENSION extname;
DROP EXTENSION extname;

查看已经安装的扩展包

postgres=# SELECT name, installed_version FROM pg_available_extensions WHERE installed_version IS NOT NULL ORDER BY name;
  name   | installed_version 
---------+-------------------
 plpgsql | 1.0
(1 行记录)

为需要的数据库单独安装扩展包,如果大量数据库都需要安装此扩展,则应当新建一个模板数据库,然后在此模板数据库中预先安装好扩展,后续的数据库以此模板数据库为基础进行创建

备份与恢复

备份
pgdump
pg
dumpall

恢复
SQL文本备份 psql -U postgres -f backup.sql
tar格式备份 pg_restore

详细参考https://www.postgresql.org/docs/9.4/static/backup.html

管理工具

psql

1) \timing 打开或关闭时间统计
2) \set AUTOCOMIT off 关闭自动提交
默认情况下,psql中每一条命令都会立即提交(一条语句一个事务),关闭自动提交后,通过 ROLLBACK 回滚, COMMIT 提交
3) \! command 执行系统命令
4) \c 连接其他数据库
5) \l 列出所有数据库
6) \d 列出当前数据库的所有表
7) \conninfo 列出当前数据库和连接的信息
8) \watch 固定间隔执行某个语句
如 每10秒钟查询一次所有数据库连接上的活跃负载

SELECT datname, waiting, query FROM pg_stat_activity WHERE state='active' AND pid != pg_backend_pid(); \watch 10
 datname | waiting | query 
---------+---------+-------
(0 行记录)

Watch命令每10s Tue Dec 27 09:19:05 2016
调用一次

详细参考https://www.postgresql.org/docs/9.4/static/app-psql.html

pgAdmin

PostgreSQL 图形化管理工具 官网

Reference

PostgreSQL 即学即用
PostgreSQL 9.4.4 中文手册