- 浏览: 243503 次
最新评论
1)小型监控:
1.在pg库主机上部署,每5分钟执行一次,插入到我的测试pg库内
[root@mysqltest tina_shell]# cat jk_pg.sh
#!/bin/bash
#适用于中转库192.168.12.8和12.2
running_port=`netstat -nat|grep "LISTEN"|grep "5432"|sed -n 2p|awk -F : '{print $4}'`
jk_host=`ifconfig |grep "inet addr:192.168"|awk '{print $2}'|awk -F : '{print $2}'`
record_time=`date +"%Y-%m-%d %H:%M:%S"`
waiting_count=`ps -ef|grep postgres|grep -v startup |grep waiting|wc -l`
streaming=`ps -ef|grep wal|grep streaming |awk '{print $15}'`
#tbjk=`ps -ef|grep postgres|grep startup|grep waiting|wc -l`
cipan=`df -ah |grep % |grep -v tmpfs|grep -v boot`
usersum=`ps -ef|grep postgres |grep -E "engine|fenxi|sqluser" |wc -l`
#echo $jk_host $record_time $waiting_count $streaming $tbjk >>/tmp/pg_check_state.log
psql -h 192.168.12.31 -U postgres -p 1922 -d tina -c "insert into jk_pg(jk_host,record_time,waiting_count,streaming,running_port,cipan,usersum) values('$jk_host','$record_time','$waiting_count','$streaming','$running_port','$cipan','$usersum');"
2.部署crontab
cat /etc/crontab
0 20 * * * root sh /tina_shell/backup.sh
4 * * * * root sh /tina_shell/pg_delete_archivelog.sh
*/5 * * * * root sh /tina_shell/jk_pg.sh
3.建表
CREATE TABLE jk_pg
(
id serial NOT NULL,
jk_host character varying, -- 监控主机的ip地址
record_time timestamp without time zone, -- 监控的时间
waiting_count integer, -- 发生waiting等待的进程数ps -ef|grep postgres|grep -v startup |grep waiting|wc -l
streaming character varying, -- 正在进行同步的日志ps -ef|grep wal|grep streaming |awk '{print $13}'
usersum integer, -- 当前连接用户总数(sqluser、engine、fenxi)
tbjk integer, -- ps -ef|grep postgres|grep startup|grep waiting|wc -l
running_port integer, -- 检测pg运行是否正常,如果没有显示5432端口,那pg就挂了
cipan character varying, -- 磁盘情况
locks character varying, -- 锁表情况
beizhu character varying -- 填写一些异常的备注
)
WITH (
OIDS=FALSE
);
COMMENT ON TABLE jk_pg IS '自制监控表-tina';
查看监控数据
tina=# select * from jk_pg order by record_time desc,jk_host desc limit 4;
id | jk_host | record_time | waiting_count | streaming | usersum | tbjk | running_port | cipan | locks | beizhu
------+----------------+---------------------+---------------+--------------+---------+------+--------------+------------------------------------------------------+-------+--------
7654 | 192.168.12.2 | 2016-01-13 11:00:01 | 0 | F2B/CE5349B0 | 161 | | 5432 | Filesystem Size Used Avail Use% Mounted on +| |
| | | | | | | | /dev/sda2 104G 21G 78G 22% / +| |
| | | | | | | | /dev/sdc1 917G 540G 331G 63% /opt/db_backup+| |
| | | | | | | | /dev/sdb 939G 370G 522G 42% /home/pgsql | |
7655 | 192.168.12.1 | 2016-01-13 11:00:01 | 0 | F2B/CEE173E8 | 26 | 0 | 5432 | Filesystem Size Used Avail Use% Mounted on +| |
| | | | | | | | /dev/sda3 103G 6.1G 92G 7% / +| |
| | | | | | | | /dev/sdb1 939G 285G 606G 32% /home/pgsql | |
7653 | 192.168.12.8 | 2016-01-13 11:00:01 | 0 | | 30 | | 5432 | Filesystem Size Used Avail Use% Mounted on +| |
| | | | | | | | /dev/sda3 27G 1.9G 24G 8% / +| |
| | | | | | | | /dev/sda2 29G 4.1G 24G 15% /var +| |
| | | | | | | | /dev/sdb1 252G 118G 122G 50% /home | |
2)pg统计库所有表的行数
[root@pg-ro tmp]# cat tinadb.sh
#!/bin/bash
#2015-11-3 tina
date=`date +"%Y-%m-%d %H:%M:%S"`
echo "begin time is: $date" >>/tmp/tongji.log
tables=$(psql -U postgres -d tinadb -c "select tablename from pg_tables where schemaname='public' order by tablename;"|grep -v "tablename" |grep -v "rows"|grep -v "\-")
#echo $tables >>/tmp/tongji.log
for table in $tables
do
echo $table >>/tmp/tongji.log
psql -U postgres -d tinadb -c "select count(*) from $table;" |grep -v "count" |grep -v "row"|grep -v "\-">>/tmp/tongji.log
done
#echo "ok!" >>/tmp/tongji.log
查看--并直接粘贴到execl表格中
[root@pg-ro tmp]# cat /tmp/tongji.log |awk 'NF==1{printf "%s ", $1;next}1'
begin time is: 2015-11-03 14:12:12
t1 11024
t2 8267537
t3 1684
t4 2
统计其他库,直接用vi替换功能替换db名即可:
替换 :%s/tinadb/dbname/g
3)pg 定期vacuum和reindex脚本
[root@pg tina_shell]# cat pg_tinadb_vacuum.sh
#!/bin/bash
#2014-10-22 tina
date=`date +"%Y-%m-%d %H:%M:%S"`
echo "begin time is: $date" >>/tmp/pg_tinadb_vacuum.log
tables=$(psql -U postgres -d tinadb -c "select tablename from pg_tables where schemaname='public';" |grep -v "tablename" |grep -v "rows"|grep -v "\-")
echo $tables >>/tmp/pg_tinadb_vacuum.log
indexes=$(psql -U postgres -d tinadb -c "select indexname from pg_indexes where schemaname='public' and indexname not like '%pkey';"|grep -v "indexname"|grep -v "\-" |grep -v "row")
for table in $tables
do
psql -U postgres -d tinadb -c "vacuum full $table;">>/tmp/pg_tinadb_vacuum.log
echo "table $table has finished vacuum.">>/tmp/pg_tinadb_vacuum.log
done
for index in $indexes
do
psql -U postgres -d tinadb -c "reindex index $index;">>/tmp/pg_tinadb_vacuum.log
echo "index $index has finished reindex.">>/tmp/pg_tinadb_vacuum.log
done
查看后台日志:
[root@pg tmp]# tail -f pg_tinadb_vacuum.log
begin time is: 2016-01-13 11:38:26
VACUUM
table t1 has finished vacuum.
VACUUM
table t2 has finished vacuum.
VACUUM
table t3 has finished vacuum.
VACUUM
table t4 has finished vacuum.
REINDEX
index t1_rin_idx has finished reindex.
建议:如果库中存在大表,就单独手动操作,不然可能会导致执行时长时间锁表,影响其他业务。
4)pg日常备份脚本
[root@mysqltest tina_shell]# cat backup.sh
#!/bin/bash
#本地备份保存目录
bkdir=/home/bk_pg
day=`date +"%Y%m%d"`
#直接指定备份哪些,也可以通过pg_database查询所有非模板和系统db进行自动备份
DB="tinadb testdb"
cd $bkdir
#result=0
if [ -f $bkdir/pg.md5 ]
then
rm -f $bkdir/pg.md5
fi
for db in $DB
do
pg_dump --host localhost --port 5432 --username "postgres" --format custom --blobs --encoding UTF8 --verbose $db --file $bkdir/$db.$day.backup &> $bkdir/bk.log
pgret=$?
if [ "$pgret" -ne "0" ]
then
echo "$pgtime $db backup fail" >> $bkdir/pg.md5
exit 1
else
md5sum $bkdir/$db.$day.backup >> $bkdir/pg.md5
fi
done
#上传ftp,异地保存一份备份
lftp backup.work <<END
user username userpasswd
lcd $bkdir
cd 12.8_pg
put tinadb.$day.backup
put testdb.$day.backup
put pg.md5
exit
END
#删除两天前的备份
find $bkdir/ -type f -mtime +2 -exec rm -f {} \;
5)简易的pg主从同步检测脚本1
[root@mysqltest tina_shell]# cat pg_check_sync.sh
#!/bin/bash
#check pg database whether is running
pg_port=`netstat -nat|grep "LISTEN"|grep "5432"|sed -n 2p|awk -F : '{print $4}'|awk '{gsub(/ /,"")}1'`
host_ip=`ifconfig |grep "inet addr:192.168"|awk '{print $2}'|awk -F : '{print $2}'`
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/tmp/pg_check_state.log
if [ "$pg_port" = "5432" ]
then
echo "$host_ip postgresql is running" >> /tmp/pg_check_state.log
else
echo "Warnning -$host_ip postgresql is not running!" >>/tmp/pg_check_state.log
fi
#check the role of the host
pg_role1=`ps -ef |grep wal| awk '{print $10}'|grep "sender"`
pg_role2=`ps -ef |grep wal| awk '{print $10}'|grep "receiver"`
pg_slave_ip=`ps -ef|grep wal|grep sender|awk '{print $13}'|awk -F "(" '{print $1}'`
if [ "$pg_role1" == "sender" -a "$pg_role2" == "" ]
then
echo "$host_ip is master host and $pg_slave_ip is slave host" >>/tmp/pg_check_state.log
else if [ "$pg_role1" == "" -a "$pg_role2" == "receiver" ]
then echo "$host_ip is postgresql slave host.Please execute the shell in the master host!" >>/tmp/pg_check_state.log
else
echo "check whether the database has slave host" >>/tmp/pg_check_state.log
fi
fi
#check whether the slave is synchronous
pg_sync_status=$(su - postgres -c "psql -c 'select state from pg_stat_replication;'|sed -n 3p")
if [ "$pg_sync_status" = " streaming" ]
then echo "the slave is synchronous" >>/tmp/pg_check_state.log
else
echo "warnning - please check the sync status of slave database " >>/tmp/pg_check_state.log
fi
执行结果:
1.单节点
[root@mysqltest tina_shell]# cat /tmp/pg_check_state.log
2016-01-13 15:04:53
192.168.12.8 postgresql is running
check whether the database has slave host ----请检查该pg库是否有从库
2.主节点
[root@pg tina_shell]# cat /tmp/pg_check_state.log
2016-01-13 15:03:31
192.168.12.2 postgresql is running
192.168.12.2 is master host and 192.168.12.1 is slave host
the slave is synchronous ----主从同步
3.从节点
[root@pg tina_shell]# cat /tmp/pg_check_state.log
2016-01-13 15:00:44
192.168.12.1 postgresql is running
192.168.12.1 is postgresql slave host.Please execute the shell in the master host! ---此ip上pg是从库,请在主库上执行脚本
6)简易的pg主从同步检测脚本2
root@pg /usr/lib64/nagios/plugins]#cat check_pgsync.sh
#!/bin/bash
# nrpe command: check pg sql and sync state.
# customer config
pgport=
pgdbname=
pgdbuser=
# default value.
pgport=${pgport:-5432}
pgdbname=${pgdbname:-postgres}
pgdbuser=${pgdbuser:-postgres}
if [ -z "$pgport" ]; then
echo "error: pgport no defined"
exit 4
fi
msg_ok="OK - pg is running and slave is synchronous."
msg_warn="WARNING - pg is running but slave synchronous fail."
msg_crit="CRITIAL - pg is not running on port: $pgport"
# check pg running
if netstat -ntple | grep -q "[:]$pgport"; then
# check slave db host.
if ps -ef | grep -q "[w]al receiver process"; then
echo "error: it seems you are running me in slave db host."
fi
# check slave synchronous
if psql -d "$pgdbname" -U "$pgdbuser" \
-c 'select state from pg_stat_replication;' \
| grep -q "[s]treaming"
then
echo "$msg_ok"
exit 0
else
echo "$msg_warn"
exit 1
fi
else
echo "$msg_crit"
exit 2
fi
exit 5
1.单节点
[root@mysqltest tina_shell]# ./check_pgsync.sh
WARNING - pg is running but slave synchronous fail.
2.主节点
[root@pg tina_shell]# ./check_pgsync.sh
OK - pg is running and slave is synchronous.
3.从节点
[root@pg-ro tina_shell]# ./check_pgsync.sh
error: it seems you are running me in slave db host.
WARNING - pg is running but slave synchronous fail.
7)pg主从切换shell脚本(闲来无事写的,不建议部署生产)
主库:192.168.10.232
从库:192.168.10.233
环境:主从同步,主库突然挂掉
脚本都部署好之后,只需要在主从执行第一个脚本,就会触发后面脚本的操作,一步到位。
(部分参数需要提前设置好)
1、检测主库是否正常启动,如果不是正常启动,就去执行从库的切换脚本
[postgres@localhost tmp]$ cat pg_check_master.sh
#!/bin/bash
#check the master pg whether is running
pg_port=`netstat -nat|grep "LISTEN"|grep "5432"|sed -n 2p|awk -F : '{print $4}'|awk '{gsub(/ /,"")}1'`
host_ip=`ifconfig |grep "inet addr:192.168"|awk '{print $2}'|awk -F : '{print $2}'`
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/tmp/pg_check_master.log
if [ "$pg_port" = "5432" ]
then
echo "$host_ip postgresql is running" >> /tmp/pg_check_master.log
else
echo "Warnning -$host_ip postgresql is not running!" >>/tmp/pg_check_master.log
echo "the slave is switching to the master ...please waiting" >>/tmp/pg_check_master.log
ssh 192.168.10.233 "sh /tmp/pg_switch.sh"
fi
2、创建从库的触发文件,将从库启动成主库(触发文件,主库和从库的名字最好不要设置成一样的,以免不好区分)
[postgres@localhost tmp]$ cat pg_switch.sh
#!/bin/bash
#swtch slave to master
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/tmp/pg_switch.log
cd /pg/data
rm -fr recovery.done
touch /tmp/pg.trigger.456
sleep 20s
if [ -f '/pg/data/recovery.done' ]
then echo "the slave has switched to the master successful!" >> /tmp/pg_switch.log
echo "the old master is going to switch to the new slave!">>/tmp/pg_switch.log
his_file=`ls -lt /pg/data/pg_xlog/0000000*.history |sed -n 1p|awk '{print $9}'`
scp $his_file root@192.168.10.232:/pg/data/pg_xlog
ssh 192.168.10.232 "sh /tmp/start_new_slave.sh"
else
echo "warnning:the slave has switched fail!">>/tmp/pg_switch.log
fi
3、注意recovery.conf会随着主从的变化而消失,因此我们可以先将内容写好的文件备份到上一级目录
内容包含如下:
vi /pg/recovery.conf.bak
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.10.233 port=5432 user=postgres password=tina'
trigger_file = '/tmp/pg.trigger.456'
4、有了时间线文件、有了recovery.conf,检查一下pg_hba.conf,就可以直接启动pg新从库了,并做一个主从同步的检查。
[root@localhost tmp]# cat start_new_slave.sh
#!/bin/bash
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/tmp/start_new_slave.log
chown postgres.postgres /pg/data/pg_xlog/*.history
cp /pg/recovery.conf.bak /pg/data/recovery.conf
chown postgres.postgres recovery.conf
su - postgres -c "pg_ctl -D /pg/data start" >>/tmp/start_new_slave.log 2&>1
pg_slave_status=`ps -ef |grep wal| awk '{print $10}'|grep "receiver"`
if [ "$pg_slave_status" = "receiver" ]
then
echo "the slave sync is ok!" >>/tmp/start_new_slave.log
else
echo "error:please check the slave whether is running or not!" >>/tmp/start_new_slave.log
fi
8)pg删除归档日志
[root@pg tina_shell]# cat pg_delete_archivedlog.sh
#!/bin/bash
find /home/pgsql/backup_new/archived_log/ -type f -mtime +2 -exec rm {} \;
9)常用拼接sql
select 'select count(*) from '||tablename||';' from pg_tables where schemaname='public';
select 'alter table '||tablename||' add constraint u_'||tablename||' unique(sample_h);' from pg_tables where tablename like 't_wh20%';
1.在pg库主机上部署,每5分钟执行一次,插入到我的测试pg库内
[root@mysqltest tina_shell]# cat jk_pg.sh
#!/bin/bash
#适用于中转库192.168.12.8和12.2
running_port=`netstat -nat|grep "LISTEN"|grep "5432"|sed -n 2p|awk -F : '{print $4}'`
jk_host=`ifconfig |grep "inet addr:192.168"|awk '{print $2}'|awk -F : '{print $2}'`
record_time=`date +"%Y-%m-%d %H:%M:%S"`
waiting_count=`ps -ef|grep postgres|grep -v startup |grep waiting|wc -l`
streaming=`ps -ef|grep wal|grep streaming |awk '{print $15}'`
#tbjk=`ps -ef|grep postgres|grep startup|grep waiting|wc -l`
cipan=`df -ah |grep % |grep -v tmpfs|grep -v boot`
usersum=`ps -ef|grep postgres |grep -E "engine|fenxi|sqluser" |wc -l`
#echo $jk_host $record_time $waiting_count $streaming $tbjk >>/tmp/pg_check_state.log
psql -h 192.168.12.31 -U postgres -p 1922 -d tina -c "insert into jk_pg(jk_host,record_time,waiting_count,streaming,running_port,cipan,usersum) values('$jk_host','$record_time','$waiting_count','$streaming','$running_port','$cipan','$usersum');"
2.部署crontab
cat /etc/crontab
0 20 * * * root sh /tina_shell/backup.sh
4 * * * * root sh /tina_shell/pg_delete_archivelog.sh
*/5 * * * * root sh /tina_shell/jk_pg.sh
3.建表
CREATE TABLE jk_pg
(
id serial NOT NULL,
jk_host character varying, -- 监控主机的ip地址
record_time timestamp without time zone, -- 监控的时间
waiting_count integer, -- 发生waiting等待的进程数ps -ef|grep postgres|grep -v startup |grep waiting|wc -l
streaming character varying, -- 正在进行同步的日志ps -ef|grep wal|grep streaming |awk '{print $13}'
usersum integer, -- 当前连接用户总数(sqluser、engine、fenxi)
tbjk integer, -- ps -ef|grep postgres|grep startup|grep waiting|wc -l
running_port integer, -- 检测pg运行是否正常,如果没有显示5432端口,那pg就挂了
cipan character varying, -- 磁盘情况
locks character varying, -- 锁表情况
beizhu character varying -- 填写一些异常的备注
)
WITH (
OIDS=FALSE
);
COMMENT ON TABLE jk_pg IS '自制监控表-tina';
查看监控数据
tina=# select * from jk_pg order by record_time desc,jk_host desc limit 4;
id | jk_host | record_time | waiting_count | streaming | usersum | tbjk | running_port | cipan | locks | beizhu
------+----------------+---------------------+---------------+--------------+---------+------+--------------+------------------------------------------------------+-------+--------
7654 | 192.168.12.2 | 2016-01-13 11:00:01 | 0 | F2B/CE5349B0 | 161 | | 5432 | Filesystem Size Used Avail Use% Mounted on +| |
| | | | | | | | /dev/sda2 104G 21G 78G 22% / +| |
| | | | | | | | /dev/sdc1 917G 540G 331G 63% /opt/db_backup+| |
| | | | | | | | /dev/sdb 939G 370G 522G 42% /home/pgsql | |
7655 | 192.168.12.1 | 2016-01-13 11:00:01 | 0 | F2B/CEE173E8 | 26 | 0 | 5432 | Filesystem Size Used Avail Use% Mounted on +| |
| | | | | | | | /dev/sda3 103G 6.1G 92G 7% / +| |
| | | | | | | | /dev/sdb1 939G 285G 606G 32% /home/pgsql | |
7653 | 192.168.12.8 | 2016-01-13 11:00:01 | 0 | | 30 | | 5432 | Filesystem Size Used Avail Use% Mounted on +| |
| | | | | | | | /dev/sda3 27G 1.9G 24G 8% / +| |
| | | | | | | | /dev/sda2 29G 4.1G 24G 15% /var +| |
| | | | | | | | /dev/sdb1 252G 118G 122G 50% /home | |
2)pg统计库所有表的行数
[root@pg-ro tmp]# cat tinadb.sh
#!/bin/bash
#2015-11-3 tina
date=`date +"%Y-%m-%d %H:%M:%S"`
echo "begin time is: $date" >>/tmp/tongji.log
tables=$(psql -U postgres -d tinadb -c "select tablename from pg_tables where schemaname='public' order by tablename;"|grep -v "tablename" |grep -v "rows"|grep -v "\-")
#echo $tables >>/tmp/tongji.log
for table in $tables
do
echo $table >>/tmp/tongji.log
psql -U postgres -d tinadb -c "select count(*) from $table;" |grep -v "count" |grep -v "row"|grep -v "\-">>/tmp/tongji.log
done
#echo "ok!" >>/tmp/tongji.log
查看--并直接粘贴到execl表格中
[root@pg-ro tmp]# cat /tmp/tongji.log |awk 'NF==1{printf "%s ", $1;next}1'
begin time is: 2015-11-03 14:12:12
t1 11024
t2 8267537
t3 1684
t4 2
统计其他库,直接用vi替换功能替换db名即可:
替换 :%s/tinadb/dbname/g
3)pg 定期vacuum和reindex脚本
[root@pg tina_shell]# cat pg_tinadb_vacuum.sh
#!/bin/bash
#2014-10-22 tina
date=`date +"%Y-%m-%d %H:%M:%S"`
echo "begin time is: $date" >>/tmp/pg_tinadb_vacuum.log
tables=$(psql -U postgres -d tinadb -c "select tablename from pg_tables where schemaname='public';" |grep -v "tablename" |grep -v "rows"|grep -v "\-")
echo $tables >>/tmp/pg_tinadb_vacuum.log
indexes=$(psql -U postgres -d tinadb -c "select indexname from pg_indexes where schemaname='public' and indexname not like '%pkey';"|grep -v "indexname"|grep -v "\-" |grep -v "row")
for table in $tables
do
psql -U postgres -d tinadb -c "vacuum full $table;">>/tmp/pg_tinadb_vacuum.log
echo "table $table has finished vacuum.">>/tmp/pg_tinadb_vacuum.log
done
for index in $indexes
do
psql -U postgres -d tinadb -c "reindex index $index;">>/tmp/pg_tinadb_vacuum.log
echo "index $index has finished reindex.">>/tmp/pg_tinadb_vacuum.log
done
查看后台日志:
[root@pg tmp]# tail -f pg_tinadb_vacuum.log
begin time is: 2016-01-13 11:38:26
VACUUM
table t1 has finished vacuum.
VACUUM
table t2 has finished vacuum.
VACUUM
table t3 has finished vacuum.
VACUUM
table t4 has finished vacuum.
REINDEX
index t1_rin_idx has finished reindex.
建议:如果库中存在大表,就单独手动操作,不然可能会导致执行时长时间锁表,影响其他业务。
4)pg日常备份脚本
[root@mysqltest tina_shell]# cat backup.sh
#!/bin/bash
#本地备份保存目录
bkdir=/home/bk_pg
day=`date +"%Y%m%d"`
#直接指定备份哪些,也可以通过pg_database查询所有非模板和系统db进行自动备份
DB="tinadb testdb"
cd $bkdir
#result=0
if [ -f $bkdir/pg.md5 ]
then
rm -f $bkdir/pg.md5
fi
for db in $DB
do
pg_dump --host localhost --port 5432 --username "postgres" --format custom --blobs --encoding UTF8 --verbose $db --file $bkdir/$db.$day.backup &> $bkdir/bk.log
pgret=$?
if [ "$pgret" -ne "0" ]
then
echo "$pgtime $db backup fail" >> $bkdir/pg.md5
exit 1
else
md5sum $bkdir/$db.$day.backup >> $bkdir/pg.md5
fi
done
#上传ftp,异地保存一份备份
lftp backup.work <<END
user username userpasswd
lcd $bkdir
cd 12.8_pg
put tinadb.$day.backup
put testdb.$day.backup
put pg.md5
exit
END
#删除两天前的备份
find $bkdir/ -type f -mtime +2 -exec rm -f {} \;
5)简易的pg主从同步检测脚本1
[root@mysqltest tina_shell]# cat pg_check_sync.sh
#!/bin/bash
#check pg database whether is running
pg_port=`netstat -nat|grep "LISTEN"|grep "5432"|sed -n 2p|awk -F : '{print $4}'|awk '{gsub(/ /,"")}1'`
host_ip=`ifconfig |grep "inet addr:192.168"|awk '{print $2}'|awk -F : '{print $2}'`
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/tmp/pg_check_state.log
if [ "$pg_port" = "5432" ]
then
echo "$host_ip postgresql is running" >> /tmp/pg_check_state.log
else
echo "Warnning -$host_ip postgresql is not running!" >>/tmp/pg_check_state.log
fi
#check the role of the host
pg_role1=`ps -ef |grep wal| awk '{print $10}'|grep "sender"`
pg_role2=`ps -ef |grep wal| awk '{print $10}'|grep "receiver"`
pg_slave_ip=`ps -ef|grep wal|grep sender|awk '{print $13}'|awk -F "(" '{print $1}'`
if [ "$pg_role1" == "sender" -a "$pg_role2" == "" ]
then
echo "$host_ip is master host and $pg_slave_ip is slave host" >>/tmp/pg_check_state.log
else if [ "$pg_role1" == "" -a "$pg_role2" == "receiver" ]
then echo "$host_ip is postgresql slave host.Please execute the shell in the master host!" >>/tmp/pg_check_state.log
else
echo "check whether the database has slave host" >>/tmp/pg_check_state.log
fi
fi
#check whether the slave is synchronous
pg_sync_status=$(su - postgres -c "psql -c 'select state from pg_stat_replication;'|sed -n 3p")
if [ "$pg_sync_status" = " streaming" ]
then echo "the slave is synchronous" >>/tmp/pg_check_state.log
else
echo "warnning - please check the sync status of slave database " >>/tmp/pg_check_state.log
fi
执行结果:
1.单节点
[root@mysqltest tina_shell]# cat /tmp/pg_check_state.log
2016-01-13 15:04:53
192.168.12.8 postgresql is running
check whether the database has slave host ----请检查该pg库是否有从库
2.主节点
[root@pg tina_shell]# cat /tmp/pg_check_state.log
2016-01-13 15:03:31
192.168.12.2 postgresql is running
192.168.12.2 is master host and 192.168.12.1 is slave host
the slave is synchronous ----主从同步
3.从节点
[root@pg tina_shell]# cat /tmp/pg_check_state.log
2016-01-13 15:00:44
192.168.12.1 postgresql is running
192.168.12.1 is postgresql slave host.Please execute the shell in the master host! ---此ip上pg是从库,请在主库上执行脚本
6)简易的pg主从同步检测脚本2
root@pg /usr/lib64/nagios/plugins]#cat check_pgsync.sh
#!/bin/bash
# nrpe command: check pg sql and sync state.
# customer config
pgport=
pgdbname=
pgdbuser=
# default value.
pgport=${pgport:-5432}
pgdbname=${pgdbname:-postgres}
pgdbuser=${pgdbuser:-postgres}
if [ -z "$pgport" ]; then
echo "error: pgport no defined"
exit 4
fi
msg_ok="OK - pg is running and slave is synchronous."
msg_warn="WARNING - pg is running but slave synchronous fail."
msg_crit="CRITIAL - pg is not running on port: $pgport"
# check pg running
if netstat -ntple | grep -q "[:]$pgport"; then
# check slave db host.
if ps -ef | grep -q "[w]al receiver process"; then
echo "error: it seems you are running me in slave db host."
fi
# check slave synchronous
if psql -d "$pgdbname" -U "$pgdbuser" \
-c 'select state from pg_stat_replication;' \
| grep -q "[s]treaming"
then
echo "$msg_ok"
exit 0
else
echo "$msg_warn"
exit 1
fi
else
echo "$msg_crit"
exit 2
fi
exit 5
1.单节点
[root@mysqltest tina_shell]# ./check_pgsync.sh
WARNING - pg is running but slave synchronous fail.
2.主节点
[root@pg tina_shell]# ./check_pgsync.sh
OK - pg is running and slave is synchronous.
3.从节点
[root@pg-ro tina_shell]# ./check_pgsync.sh
error: it seems you are running me in slave db host.
WARNING - pg is running but slave synchronous fail.
7)pg主从切换shell脚本(闲来无事写的,不建议部署生产)
主库:192.168.10.232
从库:192.168.10.233
环境:主从同步,主库突然挂掉
脚本都部署好之后,只需要在主从执行第一个脚本,就会触发后面脚本的操作,一步到位。
(部分参数需要提前设置好)
1、检测主库是否正常启动,如果不是正常启动,就去执行从库的切换脚本
[postgres@localhost tmp]$ cat pg_check_master.sh
#!/bin/bash
#check the master pg whether is running
pg_port=`netstat -nat|grep "LISTEN"|grep "5432"|sed -n 2p|awk -F : '{print $4}'|awk '{gsub(/ /,"")}1'`
host_ip=`ifconfig |grep "inet addr:192.168"|awk '{print $2}'|awk -F : '{print $2}'`
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/tmp/pg_check_master.log
if [ "$pg_port" = "5432" ]
then
echo "$host_ip postgresql is running" >> /tmp/pg_check_master.log
else
echo "Warnning -$host_ip postgresql is not running!" >>/tmp/pg_check_master.log
echo "the slave is switching to the master ...please waiting" >>/tmp/pg_check_master.log
ssh 192.168.10.233 "sh /tmp/pg_switch.sh"
fi
2、创建从库的触发文件,将从库启动成主库(触发文件,主库和从库的名字最好不要设置成一样的,以免不好区分)
[postgres@localhost tmp]$ cat pg_switch.sh
#!/bin/bash
#swtch slave to master
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/tmp/pg_switch.log
cd /pg/data
rm -fr recovery.done
touch /tmp/pg.trigger.456
sleep 20s
if [ -f '/pg/data/recovery.done' ]
then echo "the slave has switched to the master successful!" >> /tmp/pg_switch.log
echo "the old master is going to switch to the new slave!">>/tmp/pg_switch.log
his_file=`ls -lt /pg/data/pg_xlog/0000000*.history |sed -n 1p|awk '{print $9}'`
scp $his_file root@192.168.10.232:/pg/data/pg_xlog
ssh 192.168.10.232 "sh /tmp/start_new_slave.sh"
else
echo "warnning:the slave has switched fail!">>/tmp/pg_switch.log
fi
3、注意recovery.conf会随着主从的变化而消失,因此我们可以先将内容写好的文件备份到上一级目录
内容包含如下:
vi /pg/recovery.conf.bak
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.10.233 port=5432 user=postgres password=tina'
trigger_file = '/tmp/pg.trigger.456'
4、有了时间线文件、有了recovery.conf,检查一下pg_hba.conf,就可以直接启动pg新从库了,并做一个主从同步的检查。
[root@localhost tmp]# cat start_new_slave.sh
#!/bin/bash
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/tmp/start_new_slave.log
chown postgres.postgres /pg/data/pg_xlog/*.history
cp /pg/recovery.conf.bak /pg/data/recovery.conf
chown postgres.postgres recovery.conf
su - postgres -c "pg_ctl -D /pg/data start" >>/tmp/start_new_slave.log 2&>1
pg_slave_status=`ps -ef |grep wal| awk '{print $10}'|grep "receiver"`
if [ "$pg_slave_status" = "receiver" ]
then
echo "the slave sync is ok!" >>/tmp/start_new_slave.log
else
echo "error:please check the slave whether is running or not!" >>/tmp/start_new_slave.log
fi
8)pg删除归档日志
[root@pg tina_shell]# cat pg_delete_archivedlog.sh
#!/bin/bash
find /home/pgsql/backup_new/archived_log/ -type f -mtime +2 -exec rm {} \;
9)常用拼接sql
select 'select count(*) from '||tablename||';' from pg_tables where schemaname='public';
select 'alter table '||tablename||' add constraint u_'||tablename||' unique(sample_h);' from pg_tables where tablename like 't_wh20%';
发表评论
-
pg 锁
2016-01-14 16:26 0pg 锁 ... -
postgresql 的三类日志
2016-01-14 15:59 18527一、PostgreSQL有3种日志: 1)pg_log(数据 ... -
pg存储过程--创建分区表
2016-01-13 15:46 01)将普通表改成按时间字段分区表 调用select fun_c ... -
postgresql 时间类型和相关函数
2016-01-13 10:41 5457今天来好好学习一下postgresql涉及时间的字段类型和一些 ... -
pg 表空间
2016-01-07 16:28 3124一、说明 在数据库运维工作中,经常会有数据目录使用率较高 ... -
pg 定期vacuum和reindex
2016-01-07 14:56 8615定期vacuum和reindex: 一 ... -
pg 序列
2016-01-06 16:58 1621一、简介 一个序列对象通常用于为行或者表生成唯一的标识符。 ... -
pg 简单备份和恢复
2016-01-06 15:53 3768pg的备份和恢复 pg_dump ... -
ERROR: invalid page header in block 27073 of relation base/21078/45300926
2016-01-06 15:12 2143突然断网,检查后通知我们UPS断电,db所在主机重启 1、连上 ... -
pg_cancel_backend()和pg_terminate_backend()
2016-01-05 17:42 3555pg_cancel_backend()和pg_terminat ... -
canceling statement due to conflict with recovery
2016-01-05 17:12 1681报错: canceling statement due to ... -
postgresql dblink 使用
2015-12-31 14:33 2042dblink的使用 pg的跨库查询工具 select dbli ... -
root用户不能使用psql或者pg_dump等pg命令
2015-12-24 14:40 7030root用户不能使用psql或者pg_dump等pg命令 [ ... -
postgresql新建库2个常见报错
2015-12-22 16:43 6261今天使用pg建库发现两个报错: ERROR: new c ... -
安装postgresql 9.1.1
2015-12-22 16:25 642安装postgresql 9.1.1 ---版本自选,步骤相同 ... -
pgbadger监控安装和使用
2015-12-21 10:01 2033pgbadger监控安装和使用 https://github ... -
oracle,postgresql,mysql一些使用上的区别记录
2015-12-16 11:38 01.限制行数: select * from ta where ... -
postgresql存储过程实例:已审核证书存入临时表
2015-12-14 16:44 652存储过程实例: 需求: 思路:建立存储过程 代码逻辑: 1 ... -
pg 函数sfa_tmp_sleep()执行越来越慢-sql分析
2015-12-11 09:48 678pg 函数sfa_tmp_sleep()执行越来越慢 ... -
pgpool 主从流复制模式下的安装使用
2015-12-11 09:50 4127pgpool-II 是一个位于 PostgreSQL 服务器和 ...
相关推荐
《中文翻译pg054-7series-pcie-en-us-3.3中文翻译》是对Xilinx 7系列FPGA在PCI Express(PCIe)接口应用的详细技术文档的中文版,旨在帮助中国用户更好地理解和应用这项技术。这篇文档涵盖了7系列FPGA在PCIe接口设计...
这份名为“pg054-7series-pcie.pdf”的文件是一个关于Xilinx公司7系列FPGA的PCI Express(PCIe)集成块(Integrated Block for PCIe)的技术手册,版本号为1.7。该手册详细介绍了如何在Xilinx 7系列FPGA平台上使用...
windows下的安装包,pgAdmin 是一个非常流行、功能强大并且开源的 PostgreSQL 管理与开发平台。pgAdmin 支持 Linux、Unix、Mac OS X 以及 Windows 操作系统,可以管理 PostgreSQL 9.2 以及更高版本。
标题中的“pg141-dds-compiler_Xilinx_pg141-dds_pg141-dds-compiler_”暗示了这是一个与Xilinx公司的DDS(Direct Digital Synthesis,直接数字频率合成)编译器相关的资源,版本号可能是14.1。DDS是一种广泛应用于...
blockram的手册,适合开发者使用是xilinx的
pgpool-II-pg11-debuginfo-4.0.9-1pgdg.rhel6.x86_64.rpm
根据给定文件内容,以下是对Xilinx关于JESD204B IP core设计应用的详细知识点说明: JESD204B是一个串行接口标准,最初由JEDEC制定,旨在提高模拟和数字数据转换器与数字处理器之间的数据传输速度。...
pgadmin4-1.4-x86.exe 官网下载的,是英文的。 pgadmin4-1.4-x86.exe
postgis-bundle-pg15x64-setup-3.3.2-2.exe是一个安装程序,用于在64位操作系统上安装PostGIS和PostgreSQL扩展。PostGIS是一个开源的空间数据库扩展,它允许用户在PostgreSQL数据库中存储和查询地理空间数据。该扩展...
开源项目-go-pg-pg#why-go-pg.zip,ORM for Postgres with SQL joins that are 2x-10x faster than GORM (sharding included)
pgadmin4-4.29-x86(官网)32位下载
在标题"pg202-mipi-dphy-v4.2_FPGAmipi_fpga_Xilinx_dphy_mipi_"中,我们可以推断出这是关于MIPI D-PHY协议的第四版2(v4.2)实现,特别关注的是在Xilinx FPGA上的应用。标签"FPGAmipi fpga Xilinx dphy mipi"进一步...
汇川***N系列MCTC-PG-A4 PG卡是专为ME320LN系列变频器配置异步电机增量式AB编码器而设计的,同时具备分频输出功能。该PG卡主要用于应对编码器输入信号质量问题,特别是解决编码器反馈信号在电机加减速过程中的边沿...
pgadmin4-4.6-x86.exe 客户端 web页面执行 比pgAdmin3好用
它能在各种平台的Windows,Linux,FreeBSD,Mac和Solaris服务器上使用。 特性包括: ...带有升级脚本生成功能的版本跟踪。 Microsoft MSysConf 表的配置。 数据输入和输出向导。 数据库的CRUD操作。
文档“pg071-v-smpte-sdi.pdf”遵循了一个标准的产品指南结构,包括概述、产品规格、设计指南、定制和生成核心、约束指南、仿真、综合和实施、示例设计、测试台演示、验证、合规性与互操作性、迁移与升级、调试、...
postgis-bundle-pg10x64-setup-3.2.1-1.exe安装包,对应postgres10
python库。 资源全名:pg_es_fdw-0.5.1-py2.py3-none-any.whl