deepin(或debian系)安装postgresql - 《postgresql笔记》

1.下载
2. 预备locale
3. 编译
4. 安装实例
5. 开启远程访问
6. 安装RUM
7. 权限
8. 实例参数配置
9. 自带压测工具
10. 生成测试数据
11. 备份与还原
12. 执行SQL文件

1.下载

http://www.postgres.cn/v2/download

2. 预备locale

apt-get install -y locales locales-all
localectl list-locales
sudo vi /etc/environment
LANG="zh_CN.UTF-8"
LANGUAGE="zh_CN:zh"
LC_ALL=zh_CN.UTF-8
source /etc/environment
reboot

3. 编译

可能发生的异常解决方法

./configure 出现:
error:readline library not found解决方法
apt-get install libreadline-dev 

这两个包wsl默认没有
sudo apt-get install libreadline6-dev
sudo apt-get install zlib1g-dev

开始编译

#为创建pg实例准备用户和目录
adduser postgres 
export PGDATA=/home/postgres/pg_data
mkdir $PGDATA
chown postgres $PGDATA  # 分配目录权限


su - postgres # 切换用户
export PGDATA=/home/postgres/pg_data
export PGBIN =/home/postgres/pg_bin
mkdir $PGBIN
解压pg压缩包到PGBIN

# 编译
sudo ./configure --bindir ....
这里是可以配置的参数
BINDIR = /mnt/c/development/postgresql-12/bin
DOCDIR = /usr/local/pgsql/share/doc
HTMLDIR = /usr/local/pgsql/share/doc
INCLUDEDIR = /usr/local/pgsql/include
PKGINCLUDEDIR = /usr/local/pgsql/include
INCLUDEDIR-SERVER = /usr/local/pgsql/include/server
LIBDIR = /usr/local/pgsql/lib
PKGLIBDIR = /usr/local/pgsql/lib
LOCALEDIR = /usr/local/pgsql/share/locale
MANDIR = /usr/local/pgsql/share/man
SHAREDIR = /usr/local/pgsql/share
SYSCONFDIR = /usr/local/pgsql/etc

sudo make install

4. 安装实例

# 初始化
$PGBIN/initdb -D $PGDATA -E UTF-8 --locale=zh_CN.UTF8 -A md5 -W -U postgres 
# 启动
$PGBIN/pg_ctl -D $PGDATA start
# 无密登录
psql
# 设置postgres账户密码
ALTER USER postgres PASSWORD 'root';
$PGBIN/psql -d postgres  -U postgres --port=5433 -h 127.0.0.1

5. 开启远程访问

若虚拟机未开启ssh

sudo apt-get update
sudo apt-get install openssh-server  
sudo gedit /etc/ssh/sshd_config  
  port 22 处即为修改端口的地方，默认不修改也可。
service ssh start

    # 修改 $PGDATA/postgresql.conf
    listen_addresses = '*' # what IP address(es) to listen on;
    # 修改 $PGDATA/pg_hba.conf
    # IPv4 local connections:
    #host    all             all             192.168.31.190/32        md5
    host     all             all             0.0.0.0/0               md5
    host    all             all             127.0.0.1/32            md5

6. 安装RUM

git clone https://github.com/postgrespro/rum
cd rum
export PGDATA=/home/postgres/pg_data
export PGHOME=/mnt/c/development/postgresql-12
export PGBIN=$PGHOME/bin
export PATH=$PGBIN:$PATH
make USE_PGXS=1
make USE_PGXS=1 install # 这一步可能需要root
make USE_PGXS=1 installcheck

psql test -c "CREATE EXTENSION rum;"

7. 权限

使用postgres用户登录, 创建 database d1 创建带登录权限的u1
psql -Upostgres -dtest
用户 postgres 的口令：
psql (12.0)
输入 "help" 来获取帮助信息.
test=# create database d1;
CREATE DATABASE
test=# create role u1 login  password 'u1';
CREATE ROLE
test=#

另开窗口登录d1, d1的所有者是postgres, 但u1却可以在d1中建表 删表
psql -Uu1 -d d1
用户 u1 的口令：
psql (12.0)
输入 "help" 来获取帮助信息.
d1=> select 1;
 ?column?
----------
        1
(1 行记录)
d1=> create table t1(id bigint);
CREATE TABLE
d1=> insert into t1 values(1);
INSERT 0 1
d1=> select * from t1;
 id
----
  1
(1 行记录)
d1=> drop table t1;
DROP TABLE
d1=>

public schema对所有用户开放, 这是pg的默认行为, 但这不合适, 接下来有两种做法

1. 回收public schema的所有开放权限, 并将所有权限单独分配给u1

2. 删掉public schema, 重新创建, 将owner设置为u1

如果是第一种, 起owner还是postgres, u1没有权限对schema进行删除, 重命名, 权限分配等操作, 第二种则随心所欲操作public schema

示范第一种
--  回收 schema public的所有权限,
test=# revoke all on schema public from public;
REVOKE
-- 回收 d1的所有权限
test=# revoke all on database d1 from public;
REVOKE
-- 回收 postgers的所有权限
test=# revoke all on database postgres from public;
REVOKE
-- 将 public schema的所有权限赋给u1
test=# grant all on schema public to u1;
GRANT
-- 允许u1登录d1
test=# grant connect on database d1 to u1;
GRANT
-- 使用u1登录d1
d1=> create table t1(id bigint);
CREATE TABLE
d1=> insert into t1 values(1);
INSERT 0 1
d1=> insert into t1 values(2);
INSERT 0 1
d1=> select * from t1;
 id
----
  1
  2
(2 行记录)
d1=> drop table t1;
DROP TABLE
d1=>

示范第二种
--
d1=> drop schema if exists public;
NOTICE:  schema "public" does not exist, skipping
DROP SCHEMA
d1=> create schema public;
CREATE SCHEMA
d1=> alter schema public owner to u1;
ALTER SCHEMA
d1=>

接下来使用postgres会话创建u2 并登录u2
create role u2 login  password 'u2';
-- 分配登录d1的权限给u2
grant connect on database d1 to u2;
psql -Uu2 -d d1
用户 u2 的口令：
psql (12.0)
输入 "help" 来获取帮助信息.
-- public scheama 对 u2 不可见
d1=> \dt
没有找到任何关系.
d1=>
-- 分配schema的使用权给 u2
grant usage on schema public to u2;
-- revoke usage on schema public from u2; 撤销
-- 再次查看
d1=> \dt
             关联列表
 架构模式 | 名称 |  类型  | 拥有者
----------+------+--------+--------
 public   | t1   | 数据表 | u1
(1 行记录)
--只能看到存在, 不能查
-- -s
grant select on table t1 to u2;
-- 创建一个函数, 函数在public schema下, 所以拥有schema usage权限的 u2是可以调用fn1()的
create or replace function fn1() returns void language plpgsql as $$ begin end; $$;
alter function fn1 owner to u1;
-- 撤销 fn1() 的公共权限, 此时只有owner u1可以调用
revoke execute on function   fn1() from public;
-- 分配执行权限给u2, 此时u2可以调用fn1(); 但不能删除 fn1();
grant execute on function   fn1() to u2;

8. 实例参数配置

9. 自带压测工具

pgbench -f 1.sql -U postgres d test -s 8 -c 8 -n
-f 指定要执行的sql文件
-s sql文件内容, 将被执行几次
-c 多少个connection
-j 多少个工作线程
-n 阻止默认执行vacuum
-P 几秒钟打印一次进度

常用
export test_sql="select 1"
export test_sql="select random_phone_cn()"
echo $test_sql > a.txt && pgbench   -M prepared -n -r -P 1 -f a.txt -c 12 -j 12 -T 10 y

10. 生成测试数据

11. 备份与还原

--serializable-deferrable, 如果需要从执行时的绝对一致性
$PGBIN/pg_dump -d y -E utf8 -h 127.0.0.1 -p 5432 -U postgres  > d:\y.dump.sql
$PGHOME/psql  -d y -h 127.0.0.1 -p 5432 -U postgres -f $SHAREHOME/y.dump.sql

12. 执行SQL文件

$PGBIN/psql  -d y -h 127.0.0.1 -p 5432 -U postgres -f $SHAREHOME/y.dump.sql