Synchronize a postgres table through bash and csv

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#!/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 in Database, PostgreSQL | Leave a comment

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 in Database, PostgreSQL | Leave a comment

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;

Posted in Uncategorized | Leave a comment

VirtualBox – Imporving usability on guest OS

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

1
yum install gcc kernel-devel kernel-headers dkms make bzip2 perl
Posted in Uncategorized | Leave a comment

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

Posted in Uncategorized | Leave a comment

Postgres SSD optimization

If you’re running your database on an SSD instead of a spinning disk, you might want to optimize postgres table space cost:

1
2
3
4
5
6
7
8
9
10
-- Change the tablespace cost
ALTER TABLESPACE pg_default SET ( seq_page_cost = 20,  random_page_cost = 1 );
-- Verifiy the change
SELECT * FROM pg_tablespace;


-- Undo the tablespace cost
ALTER TABLESPACE pg_default RESET  ( seq_page_cost,  random_page_cost);
-- Verifiy the change
SELECT * FROM pg_tablespace;
Posted in Database, PostgreSQL | Leave a comment

Ant Junit debugging

Sometime we want to debug why ant build failed when executing a certain JUnit
Make sure your ant junit task look like the following

1
2
3
4
<junit printsummary="withOutAndErr" haltonfailure="yes">
:
:
</junit>

and not like

1
2
3
4
<junit printsummary="yes" haltonfailure="yes">
:
:
</junit>
Posted in Uncategorized | Leave a comment

Delete Postgres Cache

1
2
3
4
#!/bin/bash
sync
echo 1 > /proc/sys/vm/drop_caches
service postgresql-9.3 restart
Posted in Database, PostgreSQL | Leave a comment

Print call stack in Java from anywhere

1
 LOG.trace(ExceptionUtils.getStackTrace(new Throwable()));
Posted in java | Leave a comment

Check which database object depends on (has reference to) your table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT R.TABLE_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
    ON U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    AND U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    AND U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE
-- THIS IS WHERE YOUR INTEREST IS (YOUR TABLE)
U.COLUMN_NAME = 'THE_COLUMN_NAME'
AND U.TABLE_CATALOG = 'THE_DB_NAME'
AND U.TABLE_SCHEMA = 'THE_SCHEMA_NAME'
AND U.TABLE_NAME = 'THE_TABLE_NAME'
Posted in Database, PostgreSQL | Leave a comment