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;