Posted on

RedHat / Centos Firewall

To add an exception to firewall
In RedHat/CentOS 6

iptables --line -vnL
iptables -A INPUT -p tcp --dport 80 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A INPUT -p tcp -s 192.168.0.0/16 -j ACCEPT
iptables -D INPUT -p tcp -s 192.168.0.0/16 -j ACCEPT
service iptables save

In RedHat/CentOS 7

firewall-cmd --list-all
firewall-cmd --permanent --add-port=80/tcp
firewall-cmd --permanent --zone=public --add-source=192.168.0.0/16
firewall-cmd --permanent --zone=public --remove-source=192.168.0.0/16
firewall-cmd --reload
systemctl disable firewalld

Posted on

Bash string comparison


#!/bin/bash

function test(){
echo ""
echo "TEST $1"
echo "VAR_1: $VAR_1 VAR_2: $VAR_2 "
if [ "$VAR_1" = "false" ]; then echo " VAR_1 is false"; fi
if [ "$VAR_2" = "false" ]; then echo " VAR_2 is false"; fi
if [ "$VAR_1" = "false" ] || [ "$VAR_2" = "false" ]; then echo " At least one is false"; fi
}
VAR_1='true';
VAR_2='true';
test 1

VAR_1='true';
VAR_2='false';
test 2

VAR_1='false';
VAR_2='false';
test 3

function test2(){
echo ""
echo "TEST $1"
echo "VAR_3: $VAR_3 VAR_4: $VAR_4 "
[ -n "$VAR_3" ] && echo " VAR_3 is not null"
[ -z "$VAR_3" ] && echo " VAR_3 is null"
[ -n "$VAR_4" ] && echo " VAR_4 is not null"
[ -z "$VAR_4" ] && echo " VAR_4 is null"
}

VAR_3=""
VAR_4=""
test2 4

VAR_3="3"
VAR_4=""
test2 5

Result

$ /c/tmp/bashtest.sh

TEST 1
VAR_1: true VAR_2: true

TEST 2
VAR_1: true VAR_2: false
VAR_2 is false
At least one is false

TEST 3
VAR_1: false VAR_2: false
VAR_1 is false
VAR_2 is false
At least one is false

TEST 4
VAR_3: VAR_4:
VAR_3 is null
VAR_4 is null

TEST 5
VAR_3: 3 VAR_4:
VAR_3 is not null
VAR_4 is null

Posted on

Show postgres lock

https://wiki.postgresql.org/wiki/Lock_Monitoring

SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid

JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Posted on

Check all table size in Postgres

The “tables ” is in information_schema
The “pg_class” is in System Catalogs


SELECT ist.table_name, reltuples AS "entries", pg_size_pretty(relpages::bigint*8*1024) AS size
FROM information_schema.tables ist
INNER JOIN pg_class c ON (ist.table_name = c.relname)
WHERE
ist.table_catalog='ip2_sync_master'
AND ist.table_schema='public'
ORDER BY relpages DESC, ist.table_name ASC;

Posted on

Synchronize a postgres table through bash and csv

Please note that the csv export process does not escape commas.

#!/bin/bash
DIR='/root/sql_dump'
mkdir -p "$DIR"
cd "$DIR"

SIZE=100000
#START=611244350
START=0
END=$((START + SIZE))
STOP=189097000
TABLNAME="schema.tablename"
while [[ $START -lt $STOP ]] && [[ $END -le $STOP ]]; do
export PGPASSWORD="PASSWORD#1"

echo "Read from SOURCE start $START end $END"
FILE="${DIR}/dump${START}.csv"
SQL="SELECT * FROM ${TABLNAME} where id > $START and id <= $END" psql -U postgres -h SOURCEHOST -d DB1 -t -A -F"," -c "$SQL" > "$FILE"

export PGPASSWORD="PASSWORD#1"
SQL="DELETE FROM ${TABLNAME} where id > $START and id <= $END" psql -U postgres -h localhost -d DB2 -t -A -F"," -c "$SQL" psql -U postgres -h localhost -d DB2 -c "\copy ${TABLNAME} FROM '${FILE}' DELIMITER ',' CSV" echo "Injected to DESTINATION" START=$END END=$((START + SIZE)) if [ $END -gt $STOP ]; then END=$STOP fi rm $FILE done

Posted on

Export and Import Postgres query to CSV


# ===========================================================
# Export to CSV
# ===========================================================
# export PGPASSWORD="YOURPASSWORD"
# psql -U YOURUSERNAME -h YOURHOSTNAME -d YOURDBNAME-t -A -F"," -c "select * from YOURTABLENAME limit 2" > output.csv

# ===========================================================
# Import
# ===========================================================
# export PGPASSWORD="YOURPASSWORD"
# psql -U YOURUSERNAME -h YOURHOSTNAME -d YOURDBNAME -c "\copy YOURTABLENAME FROM '/tmp/dump.csv' DELIMITER ',' CSV"

Posted on

Assorted postgres queries

Get table sizes:


SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries",
pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
WHERE relpages >= 8

ORDER BY relpages DESC;