Postgres安装和基本权限配置

date
Apr 19, 2022
slug
postgres-install
status
Published
tags
Postgres
summary
postgres初入门
type
Post

介绍与安装

Postgres的基本使用请查看中文文档http://www.postgres.cn/docs/14/index.html 或者查看https://www.w3cschool.cn/qysrc/

1. 安装Postgres

进入官网的下载页面 https://www.postgresql.org/download/
选择操作系统
notion image
依次选择执行
notion image
 
 
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14
 

2. 修改postgres账号密码

PostgreSQL安装成功之后,会默认创建一个名为postgres的Linux用户,初始化数据库后,会有名为postgres的数据库,来存储数据库的基础信息,例如用户信息等等,相当于MySQL中默认的名为mysql数据库。
postgres数据库中会初始化一名超级用户postgres
为了方便我们使用postgres账号进行管理,我们可以修改该账号的密码

2.1、进入PostgreSQL命令行

通过su命令切换linux用户为postgres会自动进入命令行
su postgres

2.2、启动SQL Shell

psql

2.3、修改密码

ALTER USER postgres WITH PASSWORD 'NewPassword';

3. 配置远程访问

3.1、开放端口

sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

3.2、修改IP绑定

#修改配置文件
vi /var/lib/pgsql/14/data/postgresql.conf

#将监听地址修改为*
#默认listen_addresses配置是注释掉的,所以可以直接在配置文件开头加入该行
listen_addresses='*'

3.3、允许所有IP访问

#修改配置文件
vi /var/lib/pgsql/14/data/pg_hba.conf

#在问价尾部加入
host  all  all 0.0.0.0/0 md5

3.4、重启PostgreSQL服务

#重启PostgreSQL服务
sudo systemctl restart postgresql-14
配置完成后即可使用客户端进行连接

4. PostgreSQL shell常用语法示例

启动SQL shell:
su postgres
psql

4.1、数据库相关语法示例

#创建数据库
CREATE DATABASE mydb;

#查看所有数据库
\l

#切换当前数据库
\c mydb

#创建表
CREATE TABLE test(id int,body varchar(100));

#查看当前数据库下所有表
\d

4.2、用户与访问授权语法示例

-- 创建测试库
CREATE DATABASE db1;
-- 创建SCHEMA
CREATE SCHEMA "schema1";
-- 创建TABLE
CREATE TABLE schema1.test(id int);
-- 插入数据
INSERT INTO schema1.test SELECT generate_series ( 1, 10 );

-- 创建角色
CREATE ROLE dev;
CREATE USER rcloud WITH PASSWORD '123456';

-- 创建角色并授予登陆的权限,默认拥有public的部分权限,只能查看数据库名词和表名,其他不能查阅
CREATE USER role1 WITH 
  LOGIN
  NOSUPERUSER
  NOINHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  ENCRYPTED PASSWORD '123456';
	
-- 添加注释 
COMMENT ON ROLE role1 IS '只读用户';
-- 授予数据库的读写权限
GRANT USAGE ON SCHEMA PUBLIC to role;
-- 授权用户使用特定的数据表:
GRANT ALL PRIVILEGES ON DATABASE db1 TO role1;
-- 授权用户使用特定的模式:
GRANT ALL PRIVILEGES ON SCHEMA schema1 TO role1;
-- 授权用户使用指定架构下的全部数据表 
GRANT ALL PRIVILEGES ON ALL tables IN SCHEMA schema1 TO role1;

-- 将前面对象赋权时创建的role_a删除
-- 要想删除用户必须回收所有权限
REVOKE ALL ON ALL tables IN SCHEMA schema1 FROM role1;
REVOKE ALL ON SCHEMA schema1 FROM	role1;
REVOKE ALL ON DATABASE db1 FROM role1;
--删除用户前,需要回收权限
REVOKE CONNECT ON DATABASE postgres FROM	role1;
-- 删除role角色
DROP ROLE role1
-- 删除db
权限代码:SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE

5. 备注

5.1、相关阅读

 

6. 配置自增

Mysql中的主键有自增的方法,但是Postgres中并没有自增主键,需要借助类型
 
  1. 空表的情况下,创建自增id
  1. 表结构是从msql迁移过来的,表结构是int类型,并且是主键,如何更改成自增
 

7. 参数优化

https://pgtune.leopard.in.ua/#/ 在线网站,输入配置和连接数,即可生成配置
notion image
不建议直接去写入conf文件,点击查看ALTER SYSTEM,在SQL中执行一下命令,会在postgresql.auto.conf 中生成配置,然后重启数据库即可
 

空表创建自增健

创建一个新的表,添加id字段,类型选为serial4即可(smallserial,serial,bigserial)分别对应2,4,8
notion image
然后点击主键,点击保存,输入表名,就能看到“默认值”的位置有个nextval('t_test_id_seq'::regclass) 这个是pg自动生成的序列,当然也可以自己去创建序列,自定百度
notion image
 

mysql迁移的表结构,更改自增id

先来看以下mysql迁移完成后的表结构,字段名称和类型基本没啥大问题,默认值是空的,我们就要改这个,
  1. 首先,我们先给当前的主键更改个新的名字,方便生成序列(sequence), 个体dept_id更改为dept_id1
notion image
  1. 新增一个字段叫dept_id 并重复上面空表创建主键的顺序,并且删除dept_id1
notion image
 
 
 

迁移数据

Mysql数据库如何迁移到Postgres,基于Navicat15版本操作,如果是版本问题, 请自行解决
相比大家都会有线考虑Navicat的数据迁移,默认情况下,因为mysql和postgres有差异,同步会出一堆问题,所以以下采取的方案是:先同步结构,后同步数据
  1. 同步结构是用navicat的模型转换
  1. 同步数据用到数据传输(非数据同步,数据同步只能用同种类数据库)

同步表结构

选择数据库,点击右键,选择“逆向数据库到模型”
notion image
选择“文件”-“转换模型为”
notion image
选择数据库为“postgres” 版本号自行选择
notion image
点击确认即可生成表结构,然后选择postgres的表,执行sql即可
 

同步数据

Navicat中的“工具”-“同步传输”
notion image
notion image
notion image
这个位置一定要勾选“遇到错误时继续”,点击开始即可,然后查看日志,可能会报一些错误,但是不影响,去postgres中查看以下表数据
 

常见差异

类型差异

 
mysql中常用int的值来标识开/关 比如1表示开启 0表示关闭,在pg中直接用bool类型来解决
 
 

简介

在postgres里很多时候想要查看数据库、表、字段的大小,做分析。
SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.oid)) size
FROM pg_catalog.pg_database
WHERE datistemplate=false
ORDER BY pg_database_size(pg_database.oid) DESC;
notion image

查看连接的数据里所有用户表的大小

SELECT
relname AS "Table",
pg_size_pretty(pg_total_relation_size(relid)) as "Size",
pg_size_pretty(pg_total_relation_size(relid)-pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
notion image
  1. Size表示整个表的总大小。
  1. External Size表示与表关联的主键、索引的大小。

查看每个表、索引、toast表的大小

SELECT
    relname AS objectname,
    relkind AS objecttype,
    reltuples AS "#entries",
    pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_catalog.pg_class
ORDER BY relpages DESC;
objecttype:
  • r 表示普通表
  • i 表示索引
  • t 表示toast(The Oversized-Attribute Storage Technique)行外存储表
只查看用户表相关的表、索引、toast表大小
WITH user_schema AS (
    SELECT relnamespace FROM pg_catalog.pg_statio_user_tables urtb join pg_catalog.pg_class cls on urtb.relid=cls.oid group by relnamespace
)
SELECT
    relname AS objectname,
    relkind AS objecttype,
    reltuples AS "#entries",
    pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_catalog.pg_class JOIN user_schema ON pg_class.relnamespace=user_schema.relnamespace
ORDER BY relpages DESC;

查询某表某列所有项的大小

SELECT pg_size_pretty(pg_column_size("Values")::bigint) valcolsize,"Id","Values" FROM mdm.concept ORDER BY pg_column_size("Values") DESC;

查询按外键聚合的字段的总大小

WITH concept_group AS(
    SELECT "CodeSystemId",count(*) count,sum(pg_column_size("Values")) sumsize FROM mdm.concept GROUP BY "CodeSystemId" ORDER BY sumsize DESC
) SELECT "CodeSystemId",count "entries", pg_size_pretty(sumsize) FROM concept_group;

json、jsonb类型字段的大小

postgre中text、json、jsonb等类型的字段会在toast表里存储,故通过pg_column_size表查询出来的列数据是经过toast表序列化、压缩之后的大小,这个大小和它们的字符串表示(包括dump文件)会有较大出入,故建议查询大字段的字符串表示时大小,使用octet_length(col):
SELECT pg_size_pretty(pg_column_size("Content")::bigint) columnSize,pg_size_pretty(octet_length("Content"::text)::bigint) octSize,* FROM mdm."ESB_mock" ORDER BY octet_length("Content"::text) DESC;

查询每个表的字段信息

SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen AS length, a.atttypmod AS lengthvar 
    , a.attnotnull AS notnull, b.description AS comment
FROM pg_class c, pg_attribute a
    LEFT JOIN pg_description b
    ON a.attrelid = b.objoid
        AND a.attnum = b.objsubid, pg_type t
WHERE c.relname = 'table_name'
    AND a.attnum > 0
    AND a.attrelid = c.oid
    AND a.atttypid = t.oid
ORDER BY a.attnum;

参考资料

 

复制方案

根据不同的场景选择不同的方案

© WangJiaHao 2022