一、高可用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://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'