PostgreSQL数据库培训

PostgreSQL数据库培训
PostgreSQL使用常见的客户端/服务器的模式。

一次PostgreSQL 会话由下列相关的进程(程序)组成：
服务器进程
它管理数据库文件，接受来自客户端应用与数据库的连接，并且代表客户端在数据库上执行操作。

数据库服务器程序叫postgres。

客户端应用
客户端应用可能本身就是多种多样的：它们可以是一个字符界面的工具，也可以是一个图形界面的应用，或者是一个通过访问数据库来显示网页的web 服务器，或者是一个特殊的数据库管理工具。

一些客户端应用是和PostgreSQL发布一起提供的，但绝大部分是用户开发的。

PostgreSQL服务器可以处理来自客户端的多个并发连接。

因此，它为每个连接启动(“forks”)一个新的进程。

从这个时候开始，客户端和新服务器进程就不再经过最初的postgres进程进行通讯。

因此，主服务器总是在运行，等待客户端连接，而客户端及其相关联的服务器进程则是起起停停。

一、数据库连接
例子：psql -h 192.168.100.160 -p 10000 -d bcstream_data-U stream -W Usage:
psql[OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "rdbdba")
-f, --file=FILENAME execute commands from file, then exit -l, --list list available databases, then exit
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "rdbdba")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
二、对象创建
1、创建用户及数据库
CREATE USER streamWITH PASSWORD '1qaz!QAZ';
CREATE DATABASE bcstream_data WITH OWNER=stream TEMPLATE=template0 ENCODING='UTF8';
说明：
●用户与角色说明
在PG数据库中用户与角色为同一概念。

区别在于：使用create role语句创建角色时没有login权限，需单独赋权
●配置远程连接访问：
postgresql默认情况下，不能远程访问不能，如果需允许远程访问，需修改两个配置文件，说明如下：
----- postgresql.conf
将该文件中的listen_addresses项值设定为“*”，在9.0 Windows版中，该项配置已经是“*”无需修改。

----- pg_hba.conf
在该配置文件的host all all 127.0.0.1/32 md5行下添加以下配置（表示允许网段
192.168.1.0上的所有主机使用所有合法的数据库用户名访问数据库，并提供加密的密码验证。

其中，数字24是子网掩码，表示允许192.168.1.0--
192.168.1.255的计算机访问！）host all all 192.168.1.0/24 md5 ，或者直接将这一行修改为以下配置
host all all 0.0.0.0/0 md5
如果不希望允许所有IP远程访问，则可以将上述配置项中的0.0.0.0设定为特定的IP值。

2、创建SCHEMA
CREATE SCHEMA IF NOT EXISTS bcstream_data AUTHORIZATION stream; ALTER ROLE stream SET search_path TO bcstream_data;
设定用户stream的默认schema为bcstream_data
3、建表
例子：
CREATE TABLE rpt_web.tb_dt_module_config (
models_idvarchar(40) NOT NULL,
models_nmvarchar(255) NOT NULL,
upload_bgn_time timestamp(6),
trsfm_txt_flag char(1) ,
PRIMARY KEY (models_id)
)distribute by hash(models_id);
Commen on table rpt_web.tb_dt_module_config is ‘XXXXXX’;
Commen on columnrpt_web.tb_dt_module_config.models_id is ‘XXXXXX’;
其中SERIAL代表自增，默认从1开始增加，每次自增1。

说明：
表创建时影响数据分布的两种方式：
distribute by hash(字段名称) 分布表，字段名称尽量选用主键或唯一键
distribute by replication复制表，如果是不常变更的字典表，用该方式。

A.Replication or Distribution 解释
-- Replication
表的每一行存在所有数据节点( datanode )中，即每个数据节点都有完整的表数据。

1 replication 表查询时只需要读任一个数据节点；
2 replication 表更改数据时，需要同时对所有数据节点进行，代价较大；
3 replication 适用于读比较繁忙的静态数据表。

-- Distribution
表的每一行仅存在一个数据节点( datanode )中，即每个数据节点仅保留表的部分数据。

Distribute 表数据分片方式有多种，包括ROUND ROBIN，HASH ，MODULO 1 单独查询或者写一条记录时，如果根据分区键查询，只需要扫描一个数据节点( Round Robin 分片方式除外)；
2 单独查询或者写一条记录时，如果根据非分区键，需要扫描所有数据节点。

3 如果查询需要扫描多个数据节点，性能会有所降低；
数据库常用字段类型
数值类型：
字符类型：
日期时间类型：
布尔类型：
PostgreSQL支持标准的SQL boolean数据类型。

boolean只能有两个状态之一：真(True)或假(False)。

该类型占用1个字节数组类型：
1). 创建字段含有数组类型的表。

CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[] --还可以定义为integer[4]或integer ARRAY[4] );
2). 插入数组数据：
MyTest=# INSERT INTO sal_emp VALUES ('Bill', '{11000, 12000, 13000, 14000}'); 符合类型：
PostgreSQL中复合类型有些类似于C语言中的结构体，也可以被视为Oracle中的记录类型，
例子：
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);
三、常用操作
●\db[+] [PATTERN] list tablespaces
●\dg[+] [PATTERN] list roles
●\dn[S+] [PATTERN] list schemas、
●\l[+] [PATTERN] list databases
●\du 列出所有角色/用户
●\q 退出数据库
●\d 列出当前数据库里的所有表
●alter sequence ci_approve_history_history_id_seq start with 100; ----从100开始●alter sequence ci_approve_history_history_id_seq restart with 100; ----插入数据时从100重新开始
●select setval('ci_approve_history_history_id_seq', 100, true); ----设定当前值
为100，再次插入数据时从101开始
●\c[onnect] [DBNAME|- USER|- HOST|- PORT|-] 切换数据库连接
●COPY 命令可以快速的导入数据到PostgreSQL 中，文件格式类似CVS之类；适合
批量导入数据。

也可快速的导出数据。

\copypgSchema.tableNm from dataFile with DELIMITER '|' NULL AS ''
【\】的意思是从本地的dataFile导入数据，否则从服务器端dataFile导入数据四、主要系统表功能及依赖关系、
●pg_namespace系统表pg_namespace用于存储命名空间
●pg_tablespace存储表空间信息，将表放置在不同的表空间有助于实施磁盘文件布局●pg_database 存放了当前数据集簇中数据库的信息
●pg_class 存储表及与表类似结构的数据库对象信息，包含索引、序列、视图、复合数
据类型、TOAST表等.每一个对象都在pg_class中表示为一个元组，并且每一个对象都会被分配一个OID作为唯一标识，该OID作为该元组的一个隐藏属性存储
●pg_type 存储数据类型信息。

基本数据类型和枚举类型由CREATE TYPE创建，域类
型由CREATE DOMAIN创建，复合数据类型在表创建时自动创建
●pg_attribute 存储表的属性信息
●pg_index存储索引的具体信五、系统视图。