pg数据库学习

一、高可用stolon基础概念

https://github.com/sorintlab/stolon  项目地址

Stolon是一个cloud native的PostgreSQL高可用管理工具

stolon is a cloud native PostgreSQL manager for PostgreSQL high availability.

Stolon 是由3个部分组成的：

• keeper：负责管理PostgreSQL的实例汇聚到由sentinel(s)提供的clusterview。

• sentinel：负责发现并且监控keeper，并且计算最理想的clusterview。

• proxy：客户端的接入点。它强制连接到右边PostgreSQL的master并且强制关闭连接到由非选举产生的master。

Stolon 用etcd或者Consul作为主要的集群状态存储。

二、pg数据库备份

pg_dump(SQL转储备份，属于逻辑备份工具 )

pg_dump是一个普通的客户端工具，如果不指定主机IP和端口，那么默认备份的是本地服务器上的数据库。一般来说，这个命令由超级管理员来运行，这样可以备份到整个数据库的所有对象。由数据库创建的对象是一致的，即在运行pg_dump那一刻存储了该时刻的数据库快照，这个命令在运行过程中数据库的更新不会被转储。同时，pg_dump不会阻塞其他对数据库的操作。

备份：
pg_dump  -h 主机地址  -U username   数据库   >   备份数据库.dmp   
恢复：
createdb -h localhost -U username  -E unicode   新数据库名字
psql  -h localhost -U username    -d  新数据库名字   <  备份数据库.dmp

上述两个命令都是在postgres用户下运行的，outfile和infile都是sql文件。

恢复

pg_dump生成的sql文本可以有psql程序读取，但是注意的是恢复数据库的时候dbname是需要存在的，也就是说，转储文件中并不包含创建数据库的语句。可以在恢复之前创建一个数据库，用如下命令来创建一个数据库。

createdb -T template0 dbname

表明这个数据库dbname是基于模板template0来创建的，然后再执行如下恢复语句：

psql dbname < infile

pg_dumpall

pg_dump工具转储的是一个数据库dbname中所有的信息，不会转储角色和表空间等信息，进行单个数据库的备份，如果需要完整转储整个数据库中所有的数据库实例，PostgreSQL提供了一个工具pg_dumpall，该工具能够转储一个数据库集簇中所有的内容，同时还确保保留象用户和组这样的全局数据状态，包含了前面提到的角色和表空间。使用的方法是：

备份：pg_dumpall > outfile

docker exec -t pg_container bash -c " 
export PGPASSWORD=$POSTGRES_PASS; 
pg_dumpall \ 
-h $POSTGRES_HOST \ 
-p $POSTGRES_PORT \ 
-U $POSTGRES_USER \ 
| gzip > $BACK_DIR/$POSTGRES_DB_NAME; "

恢复：psql -f infile postgres

从pg_dump和pg_dumpall两个工具的作用来看，前者偏向于重建一个数据库实例，这个实例中包含了基本的对象和数据信息，适用于角色等信息比较简单等，常用于定期备份使用；后者偏向于重建一个数据库集簇，适用于在一个新的环境中部署一套与原来一致的数据库环境，包括表空间和角色，尤其是在角色较多的复杂环境下，减少了手动干预的工作量。

pg_basebackup(物理备份)

Usage:
  pg_basebackup [OPTION]...

-h 指定连接的数据库的主机名或IP地址，这里就是主库的ip。

-U 指定连接的用户名，专门负责流复制的repl用户。

-F, --format=p|t output format (plain (default), tar)

指定输出格式：p原样输出，即把主数据库中的各个数据文件，配置文件、目录结构都完全一样的写到备份目录；t 把输出的备份文件打包到一个tar文件中。

-x 表示备份开始后，启动另一个流复制连接从主库接收WAL日志。

-R 表示会在备份结束后自动生成recovery.conf文件，这样也就避免了手动创建。(12.0有差异)

-D, --pgdata=DIRECTORY receive base backup into directory

指定把备份写到那个目录，如果这个目录或这个目录路径中的各级父目录不存在，则pg_basebackup就会自动创建这个目录，如果目录存在，但目录不为空，则会导致pg_basebackup执行失败。

-l 表示指定一个备份的标识

-r, --max-rate=RATE maximum transfer rate to transfer data directory  (in kB/s, or use suffix "k" or "M")

-T, --tablespace-mapping=OLDDIR=NEWDIR   relocate tablespace in OLDDIR to NEWDIR

-x, --xlog include required WAL files in backup (fetch mode)

备份时会把备份中产生的xlog文件也自动备份出来，这样才能在恢复数据库时，应用这些xlog文件把数据库推到一个一致点，然后真正打开这个备份的数据库，这个选项与

-X fetch是完全一样的。使用这个选项，需要设置“wal_keep_segments"参数，以保证在备份过程中，需要的WAL日志文件不会被覆盖。

-X, --xlog-method=fetch|stream

include required WAL files with specified method

--xlogdir=XLOGDIR location for the transaction log directory

-z, --gzip compress tar output  使用gzip压缩，仅能能与tar输出模式配合使用。

-Z, --compress=0-9 compress tar output with given compression level   指定压缩级别

-P, --progress show progress information    在备份过程中实时打印备份进度

-v, --verbose output verbose messages    详细模式，使用了-P后，还会打印出正在备份的具体文件的信息。

-p, --port=PORT database server port number

docker exec -t pg_container bash -c "
export PGPASSWORD=xxxx;
pg_basebackup \
-h stolon_node \
-p stolon-proxy-port \
-U replication \
-Ft -Pv -z \
-D XXX"

选项含义

选项	含义
-f<文件名>	指定输出文件
-s	只转储模式，不包括数据
-x	不要转储权限
-a	只转储数据，不包括模式
-t	只转储表空间，而不转储数据库或角色
-r	只转储角色，不包括数据库或表空间
-g	只转储全局对象，不包括数据库
-c	在重新创建之前，先删除数据库对象
-O	不恢复对象所属者
--disable-triggers	在只恢复数据的过程中禁用触发器
-S<用户名>	在转储中，指定的超级用户名
-h<主机名>	数据库服务器的主机名
-l<数据库名>	另一个默认数据库
-p<端口号>	数据库服务器端口号
-U<名字>	以指定的数据库用户连接
-w	永远不提示输入口令
-W	强制口令提示
--inserts	以INSERT命令，而不是COPY命令的形式转储数据
--column-inserts	以带有列名的INSERT命令形式转储数据
--no-tablespaces	不转储表空间分配信息

备份postgresql服务器上的所有数据库

# su - postgres
$ pg_dumpall >db.sql

备份postgresql服务器上的所有数据库，只转储模式，不包括数据

$ pg_dumpall -s >db2.sql

备份postgresql服务器上的所有数据库，只转储数据，不包括模式

$ pg_dumpall -a >db3.sql 

案例

#!/bin/bash

POSTGRES_HOST='node8'
POSTGRES_PORT=5432
POSTGRES_USER='xxxxx'
POSTGRES_PASS='xxxxx'

# 数据库备份文件夹名
POSTGRES_DB_NAME="node_basebackup_$(date '+%Y%m%d_%H%M%S')"

# 数据库备份路径名
BACK_FOLDER=/mnt/hdd2/pg_data_back

saturday=$(date '+%w') #确定今天是周几
if [[ $saturday -eq 6 ]]; then
    folder="weeks";
else
    folder="days";
fi

BACK_DIR=$BACK_FOLDER/$folder/$(date '+%Y%m%d')
mkdir -p $BACK_DIR

# 生成备份
docker exec -t pg_tools bash -c "
export PGPASSWORD=$POSTGRES_PASS;
pg_basebackup \
-h $POSTGRES_HOST \
-p $POSTGRES_PORT \
-U $POSTGRES_USER \
-Ft  -Pv -z \
-D $BACK_DIR/$POSTGRES_DB_NAME \
"

## 删除days目录下7天前的文件
## 删除weeks目录下365天前的文件
## 每周六运行一次删除
if [[ $saturday -eq 6 ]]; then
    DEL_DIR=$BACK_FOLDER/days
    if [[ -d $DEL_DIR ]]; then
        find $DEL_DIR -mindepth 1 -type d -mtime +7 | xargs rm -rf
    else
        echo "Folder $DEL_DIR is not exist";
    fi
    DEL_DIR=$BACK_FOLDER/weeks
    if [[ -d $DEL_DIR ]]; then
        find $DEL_DIR -mindepth 1 -type d -mtime +365 | xargs rm -rf
    else
        echo "Folder $DEL_DIR is not exist";
    fi
fi

# 同步到 storage
rsync -aP --delete ${BACK_FOLDER}/  node5:/mnt/hdd1/pg_data_back/

# 重启pg_tools，防止卡死导致备份失败
docker restart pg_tools

三、客户端可视化工具pgadmin4

docker pull dpage/pgadmin4
docker run -p 80:80 \
    -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \
    -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \
    -d dpage/pgadmin4

https://blog.csdn.net/qq_28289405/article/details/80243476

https://www.yiibai.com/postgresql/postgresql-syntax.html  不错的教程

四、导出csv文件

查询stolon从节点

方法一：执行sql语句查询，顺便导出csv文件

文件名
file_name="slave.csv"

# sql语句
sql1="select client_addr from pg_stat_replication"

# 导出csv文件
docker exec -it stolon_proxy sh -c "export PGPASSWORD=xxxxx;  psql -h 127.0.0.1 -d dbname -U db_user -c \"COPY (${sql1}) to stdout (FORMAT CSV, HEADER);\" | tee /tmp/${file_name} " && docker cp stolon_proxy:/tmp/$file_name ~/xxx/xxx/xx/

执行脚本

bash sql-import.sh
client_addr
192.20.223.3
192.20.223.13

方法二：查看stolon的log

stolon-proxy 的日志，会显示 proxying to master address XXXXXX ，这个地址就是master，剩下的就是slave

四、索引

--查询索引
select * from pg_indexes where tablename='tab1';   

--创建索引  tab1_bill_code_index  为索引名，
create index  tab1_bill_code_index  on  "db1".tab1(bill_code);

--删除索引
drop index tab1_bill_code_index  ;

统计创建索引耗时、索引占用空间大小

#!bin/bash

sql1='CREATE INDEX fingerprint_citizen_f436dd_hash  ON public.fingerprint_personinfo USING hash (citizen_id_number COLLATE pg_catalog."default") TABLESPACE pg_default'

sql2="select pg_size_pretty(pg_relation_size('fingerprint_citizen_f436dd_hash'))"

start=$(date +%s)

docker exec -it  postgres sh -c "export PGPASSWORD=moqi#233@fingerprint;   psql -h 127.0.0.1 -d fingerprint -U moqi_user -c \"${sql1};\""

end=$(date +%s)

take=$((end - start)) 

echo $take

docker exec -it  postgres sh -c "export PGPASSWORD=moqi#233@fingerprint;   psql -h 127.0.0.1 -d fingerprint -U moqi_user -c \"${sql2};\""

执行后在创建索引期间会阻塞 dml，特别是比较繁忙的系统或者大表上执行

因此使用 concurrently 选项不阻塞事务创建索引

create index concurrently idx_table_name_x1 on table_name(col_name); 

五、解锁

查询正在运行的进程

//datname为数据库名称
select * from pg_stat_activity WHERE datname='aaa' 

查看等待中的进程

//wait_event_type = 'Lock' 表示锁表线程
select * from pg_stat_activity WHERE datname='aaa' and wait_event_type = 'Lock'

释放锁定

//多个同时执行，返回结果为f
select pg_cancel_backend('上面查到的pid');
select pg_cancel_backend('上面查到的pid');
select pg_cancel_backend('上面查到的pid');
select pg_cancel_backend('上面查到的pid');
select pg_cancel_backend('上面查到的pid');

六、补充：

1、目前主流的数据库访问技术(驱动)

2、postgresql 列出某个数据库下的某个schema下面所有的表

select * from pg_tables where schemaname = 'schema_name'