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 单独查询或者写一条记录时，如果根据非分区键，需要扫描所有数据节点。