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
四、索引
--查询索引 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年数据库方向大佬