Ronald Pringadi's Technical Notes

Author: ronald

  • SELinux directory permission

    To check SELinux directory permission you need to -z for example

    ls -Z /var/www/html

    If something is incorrect you can re-adjust some of the directory permission:


    chcon -R -t httpd_sys_content_t /var/www/html

  • 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

  • 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

  • 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;

  • 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;

  • 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

  • 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"

  • 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;

  • VirtualBox – Imporving usability on guest OS

    GUEST OS: Centos 6.7 final
    HOST OS: Windows 7
    Virtualbox version: 5.0.4


    yum install gcc kernel-devel kernel-headers dkms make bzip2 perl

  • RedHat or CentOS 6 iptables adding an open port

    iptables –line -vnL
    iptables -I INPUT 5 -p tcp –dport 80 -m state –state NEW,ESTABLISHED -j ACCEPT
    service iptables save