PostgreSQL 15—安装配置

官网

https://www.postgresql.org/download/linux/redhat/

yum 安装 Version 15：

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:
yum install postgresql15 postgresql15-server

安装完数据库后，默认会创建一个postgres用户。

初始化PostgreSQL

主要设定 PostgreSQL的数据库文件目录，超级用户密码

#创建库文件目录
mkdir -p /data/db/pgsql
#改变库文件目录属主
chown -R postgres:postgres /data/db/pgsql
#初始 主要设定 PostgreSQL的数据库文件目录，超级用户密码
su - postgres #切换用户，再执行下面的命令
/usr/pgsql-15/bin/initdb -D /data/db/pgsql -W  #根据提示输出超级用户密码
#启动
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15
#重启
systemctl restart postgresql-15

#Success. You can now start the database server using:
/usr/pgsql-15/bin/pg_ctl -D /data/db/pgsql -l logfile start

PostgreSQL配置

经过initdb命令初始化后，重要的配置文件如pg_hba.conf、pg_ident.conf、postgresql.conf已经生成到上面指定的库文件目录/data/db/pgsql之下

#编辑文件，不知道是否要修改，没测试过
vi /usr/lib/systemd/system/postgresql-15.service
#库文件目录
Environment=PGDATA=/data/db/pgsql/

1、允许远程连接

#修改 vi pg_hba.conf，设置
# type:local：本地连接 host：使用TCP/IP连接 | 库名 | 用户 | 远程ip规则 |鉴权方式/机制
host    all             all             0.0.0.0/0                 trust
#修改 vi postgresql.conf，设置
listen_addresses='*'

鉴权方式/机制

METHOD: 鉴权方式
trust：无条件允许连接
reject：无条件拒绝
password：要求客户端提供未加密的密码进行身份验证，密码在网络上以明文形式传输，如果使用SSL，可以加密传输
scram-sha-256：执行 SCRAM-SHA-256 身份验证，这是一种质询响应机制，这是目前最安全的方式，不过有的数据库客户端可能不支持。
md5：也是一种质询响应机制
peer：获取客户端操作系统的用户名，如果和请求连接的用户名一样。这只有在本地连接时才有用。

PostgreSQL 命令

1、psql登录

#执行如下命令，进入命令行模式
psql -U postgres #未指定密码
psql -U postgres -p 5432 #指定密码
#psql命令参数
-d dbname
-h hostname
-p port
-U username
-W 强制psql在连接到数据库之前提示输入密码。

#导入建表sql
psql -d testdb -U postgres -f  /home/sql/create.sql

2、createdb 创建库

#创建数据库
createdb [option] [dbname[description]]
-E 指定编码
-T 创建数据库的模板 默认模板库为 template1
-h localhost 是为了确定登录的主机
-U postgres 是确定登录数据库的用户名
-p 5432 是确定端口号，默认的端口号是5432
-O owner 指定将拥有新数据库的数据库用户
#创建用户，并授权库权限

3、createdb 创建sequence序列

PostgreSQL表自增主键，需要依赖sequence序列

CREATE SEQUENCE IF NOT EXISTS public.record_id_seq
    INCREMENT 1 #增量
    START 1 #起始值
    MINVALUE 1 #最小值
    MAXVALUE 9223372036854775807 #最大值
    CACHE 1;
ALTER SEQUENCE public.record_id_seq
    OWNER TO postgres;

4、create table 创建表

#创建表
CREATE TABLE IF NOT EXISTS public.record
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    stock_id integer NOT NULL,
    stock_code character varying(10) COLLATE pg_catalog."default",
    stock_name character varying(50) COLLATE pg_catalog."default",
    trade_date integer NOT NULL,
    trade_seconds integer NOT NULL,
    trade_price numeric(10,2),
    trade_volume bigint,
    trade_value numeric(18,2),
    create_time timestamp without time zone,
    update_time timestamp without time zone,
    CONSTRAINT record_pkey PRIMARY KEY (id),
    CONSTRAINT idx_unique UNIQUE (stock_id, trade_date, trade_seconds)
)
TABLESPACE pg_default;
#表授权
ALTER TABLE IF EXISTS public.record
    OWNER to postgres;
#修正字表、字段注释
COMMENT ON TABLE public.record IS '行情-成交记录表';
COMMENT ON COLUMN public.record.stock_id IS '股票id';

5、表索引维护

#创建主键
ALTER TABLE public.tablename      #-- 添加主键的表名
    ADD CONSTRAINT tablename_pkey #-- 主键的名称
        PRIMARY KEY (id           #-- 主键的列名
    ); 
#修改表主键
ALTER TABLE IF EXISTS public.record ADD CONSTRAINT record_pkey PRIMARY KEY (id);
#修改索引
ALTER TABLE IF EXISTS public.record ADD CONSTRAINT idx_unique UNIQUE (stock_id, trade_date, trade_seconds);
#创建索引
CREATE INDEX idx_stock_id ON public.yn_quote_trade_record (stock_id);
#创建唯一索引
CREATE UNIQUE INDEX name ON table ( column [, ...]);

6、表达式索引

对字段进行某种运算之后的结果创建索引

#c创建一个基于lower(col1)函数结果之上的索引
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
#创建一个这样的索引
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

7、Query查询

太多，慢慢完善。

#返回从第 5 个记录开始的行数长度为3的记录
select * from fruits limit 3 offset 4;
#inner join 同MySQL，仍可使用on
#自连接

8、用户及授权

postgresql中有角色和用户两个概念，其中角色可以没有 LOGIN 权限。

#创建角色 
create role bizman PASSWORD '1234'; #业务员
CREATE ROLE name LOGIN; #创建角色带登录权限  SUPERUSER=超级用户角色权限 ，CREATEDB=创建数据库角色权限，CREATEROLE=创建角色权限

#创建用户
CREATE USER quote_user WITH PASSWORD '123456'; #用户
#-- 修改密码
alter user quote_user with password 'password';

#授权
#授予quote_user 用户访问 quote_realtime 数据库的权限
#GRANT some_privileges ON database_object_type object_name TO role_name;
#some_privileges 表示在该数据库对象中的权限，例如 INSERT``UPDATE``DELETE 诸如此类。
#database_object_type** **表示数据库的对象类型，如 TABLE SEQUENCE``SCHEMA等。

grant all privileges on database quote_realtime to quote_user ;
grant all privileges on all tables in database quote_realtime to quote_user ;
grant all privileges on all sequences in database quote_realtime to quote_user ;
grant select,insert,update,delete,truncate,references,create on all tables in database quote_realtime to quote_user ;

#收回用户在 kangll_schema 下所有表的所有权限
#REVOKE some_privileges ON database_object_type object_name TO role_name;
revoke all privileges on all tables in database quote_realtime from quote_user ;

#修改表owner
grant all on database yn_stock_realtime to yueniu;
alter table yn_quote_trade_record owner to yueniu;

9、性能分析explain analyze

explain analyze SELECT * FROM public.record where stock_id = 200000002
	order by id desc ;

pgAdmin 下载安装

wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v8.3/windows/pgadmin4-8.3-x64.exe