stolon

一、高可用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作为主要的集群状态存储。

1、查看集群状态

 docker exec stolon-proxy stolonctl status

二、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 \
    --restart=always \
    -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=xx;   psql -h 127.0.0.1 -d databasename -U user -c \"COPY (${sql}) to stdout (FORMAT CSV, HEADER);\" | tee /tmp/zjz " && docker cp stolon_proxy:/tmp/zjz ~

执行后在创建索引期间会阻塞 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'

 

 

 

 

https://blog.csdn.net/horses/category_2677699.html     14年数据库方向大佬

posted @ 2021-01-27 23:09  凡人半睁眼  阅读(1117)  评论(0编辑  收藏  举报