官网
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