PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS), 在灵活的BSD许可证下发行。PostgreSQL 开发者把它念作 post-gress-Q-L。PostgreSQL 的 Slogan 是 “世界上最先进的开源关系型数据库”。
1. 简介与安装
1.1 PostgreSQL 特征
- 函数: 通过函数, 可以在数据库服务器端执行指令程序
- 索引: 用户可以自定义索引方法, 或使用内置的 B 树, 哈希表与 GiST 索引
- 触发器: 触发器是由SQL语句查询所触发的事件。如: 一个INSERT语句可能触发一个检查数据完整性的触发器。触发器通常由INSERT或UPDATE语句触发。 多版本并发控制: PostgreSQL使用多版本并发控制(MVCC, Multiversion concurrency control)系统进行并发控制, 该系统向每个用户提供了一个数据库的"快照", 用户在事务内所作的每个修改, 对于其他的用户都不可见, 直到该事务成功提交
- 规则: 规则(RULE)允许一个查询能被重写, 通常用来实现对视图(VIEW)的操作, 如插入(INSERT)、更新(UPDATE)、删除(DELETE)
- 数据类型: 包括文本、任意精度的数值数组、JSON 数据、枚举类型、XML 数据等
- 全文检索: 通过 Tsearch2 或 OpenFTS, 8.3版本中内嵌 Tsearch2
- NoSQL: JSON, JSONB, XML, HStore 原生支持, 至 NoSQL 数据库的外部数据包装器
- 数据仓库: 能平滑迁移至同属 PostgreSQL 生态的 GreenPlum, DeepGreen, HAWK 等, 使用 FDW 进行 ETL。
1.2 PostgreSQL的结构
1.2.1 Potgres(常驻进程)
管理后端的常驻进程, 也称为’postmaster’。其默认监听UNIX Domain Socket和TCP/IP(Windows等, 一部分的平台只监听tcp/ip)的5432端口, 等待来自前端的的连接处理。监听的端口号可以在PostgreSQL的设置文件postgresql.conf里面可以改。
一旦有前端连接过来, postgres会通过fork(2)生成子进程。没有Fork(2)的windows平台的话, 则利用createProcess()生成新的进程。这种情形的话, 和fork(2)不同的是, 父进程的数据不会被继承过来, 所以需要利用共享内存把父进程的数据继承过来。
1.2.2 Postgres(子进程)
子进程根据pg_hba.conf定义的安全策略来判断是否允许进行连接, 根据策略, 会拒绝某些特定的IP及网络, 或者也可以只允许某些特定的用户或者对某些数据库进行连接。
Postgres会接受前端过来的查询, 然后对数据库进行检索, 最好把结果返回, 有时也会对数据库进行更新。更新的数据同时还会记录在事务日志里面(PostgreSQL称为WAL日志), 这个主要是当停电的时候, 服务器当机, 重新启动的时候进行恢复处理的时候使用的。另外, 把日志归档保存起来, 可在需要进行恢复的时候使用。在PostgreSQL 9.0以后, 通过把WAL日志传送其他的postgreSQL, 可以实时得进行数据库复制, 这就是所谓的’数据库复制’功能。
1.2.3 其他的进程
- Writer process
- WAL writer process
- Archive process
- stats collector process
- Logger process
- Autovacuum启动进程
- 自动vacuum进程
- wal sender wal receiver
1.3 安装
sudo apt install postgresql-12
2. 管理
远程访问:
sudo vim /etc/postgresql/12/main/postgresql.conf
listen_addresses = '*'
PostgreSQL数据目录中的pg_hba.conf的作用就是用户认证:
sudo vim /etc/postgresql/12/main/pg_hba.conf
host all all 192.168.0.1/16 md5
2.1 启动pgsl数据库
pg_ctl -D /xx/pgdata start
2.2 命令行登录数据库
sudo -u postgres psql
psql -U username -d dbname -h hostip -p port
# 恢复数据库
pg_restore -d dvdrental -h 192.168.56.102 -U postgres ./dvdrental.tar
2.3 基本管理
--- 修改密码
alter user postgre with password '123456Aa=';
--- 创建用户
create user username with password '****';
--- 创建数据库指定所属者
create database dbtest owner username;
--- 将数据库得权限, 全部赋给某个用户
grant all on database dbtest to username;
3. 基本操作
3.1 常用命令
操作 | 命令 | 说明 |
---|---|---|
查看psql命令列表 | \? |
|
列出所有数据库 | \l |
|
切换数据库 | \c dbname |
|
列出当前数据库的所有表 | \d |
|
列出某一张表格的结构 | \d tablename |
|
查看表 | \dt |
|
查看索引 | \di |
|
查看指定表的基本情况 | \d+ tablename |
|
退出操作 | \q |
|
列出所有用户 | \du |
|
查看SQL命令的解释 | \h |
比如: \h select |
删除表 | drop table tablename; |
|
清空表 | delete from tablename TRUNCATE TABLE tablename |
Truncate table 表名 (注:不带where语句) 速度快,而且效率高.因为DELETE 语句每次删除一行, 并在事务日志中为所删除的每行记录一项。 TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据, 并且只在事务日志中记录页的释放 |
查询结果存储到输出文件 | \o file_path |
|
查询结果存储到输出文件 | \x |
Expanded display |
打开文本编辑器 | \e |
|
列出当前数据库和连接的信息 | \conninfo |
|
列出当前数据库下所有的 schema | \dn |
|
列出当前数据库下所有的 schema 下的所有表 | \dnS |
|
查看当前使用的 schema | show search_path; |
|
切换至目标 schema | set search_path to syncname; |
|
删除目标 schema | DROP SCHEMA myschema; |
|
删除目标 schema及其下表 | DROP SCHEMA myschema CASCADE; |
|
创建目标 schema | CREATE SCHEMA myschema; |
3.2 表操作
3.2.1 新建表
create table TESTCASE(
id INTEGER,
task_class INTEGER,
age TEXT,
PRIMARY KEY(id, task_class)
);
create table CREATETASK_CHKID_N(
id SERIAL PRIMARY KEY,
chk_id TEXT,
n INTEGER
);
其中SERIAL代表自增, 默认从1开始增加, 每次自增1。
3.2.2 alter 操作
--- add
alter table [表名] add column [字段名] [类型];
-- rename
alter table [表名] rename column [旧字段名] to [新字段名];
--- alter
--- 例: 把表table_ex字段col_1限制非空去掉:
ALTER TABLE table_eg ALTER col_1 drop not NULL
--- 更改字段属性, 含空格
--- 如果把字段colname把属性Text转化为int, 原来text里面存在空啥的, 可以
ALTER TABLE tablename ALTER COLUMN colname TYPE int USING (trim(colname)::integer);
--- 更改字段由int4-->int8
alter table test_data alter column task_id type bigint using task_id::bigint
--- 删除字段
alter table [表名] drop column [字段名];
3.2.3 CURD 增删改查
--- 表中插入一行数据
insert into [表名] (字段1,字段2) values (值1,值2);
insert into assist_info (id, maat_id, block_type) values ('F006', 'F7775', 1)
--- 如果表中字段有大写的字段, 则需要对应的加上双引号。例: insert into test (no, "Name") values ('123', 'jihite');
--- 值用单引号引起来(''), 不能用双引号("")
--- 表中删除一行数据
delete from [表名] where [该行特征];
--- 修改表中数据
update [表名] set [目标字段名]=[目标值] where [该行特征]
SERIAL
3.2.5 CASE
SELECT a,
CASE
WHEN a=1 THEN 'One'
ELSE 'Other'
END
FROM test;
3.2.5 其他操作
- 两个查询结果做差 except
(select node_id from node where node_id=1 or node_id=2) except (select node_id from node where node_id=1);
node_id
---------
2
(1 row)
- 复制表
CREATE TABLE test_a_copy AS SELECT * FROM test_a;
- 命令导入sql数据文件
psql -h localhost -d databaseName -U username -f filename
- 数据库的备份&恢复
导出到线下文件
pg_dump --host hostname --port port --username username -t tablename -d dbname >/home/jihite/table.sql
把线下文件导入到数据库
psql -h 10.125.7.68 -p 5432 -d postgres -U postgres -W postgres -f 2.sql
- 从表A中把符合条件的记录拷贝到表B
insert into A select * from B where id in ('a', 'b', 'c');
- 建立索引
--- 单字段索引
CREATE INDEX index_name ON table_name (field1);
--- 多字段索引
CREATE INDEX index_name ON table_name (field1,field2);
--- 查看所有表的索引使用情况
select
relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
from
pg_stat_user_indexes
order by
idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;
--- 查看某个表索引的使用情况
select
relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
from
pg_stat_user_indexes
where
relname = table_name
order by
idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;
- 超找数据库的连接信息
--- 包含: 客户端user、ip、执行语句, 状态、时间
select * from pg_stat_activity