PostgreSQL copy table differences between two databases

postgres_copy_table_differences.sh

#!/bin/bash

POSTGRES_HOST=your_db_host
POSTGRES_PORT=your_db_port
POSTGRES_USER=your_db_username
POSTGRES_PASS=your_db_password

POSTGRES_DBNAME1=your_db_1_name
POSTGRES_DBNAME2=your_db_2_name

declare -a tables=("your_table_1" "your_table_2" "your_table_x")

copy_table() {
  local TABLE="$1"

  local PG_URI1="postgresql://$POSTGRES_USER:$POSTGRES_PASS@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DBNAME1"
  local PG_URI2="postgresql://$POSTGRES_USER:$POSTGRES_PASS@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DBNAME2"

  echo "Dumping $TABLE"
  pg_dump "$PG_URI1" -t "$TABLE" > "$TABLE".1.sql
  pg_dump "$PG_URI2" -t "$TABLE" > "$TABLE".2.sql

  cat "$TABLE".1.sql | sed -n '/COPY/,/\\\./p' > 1.tmp && mv 1.tmp "$TABLE".1.sql
  cat "$TABLE".2.sql | sed -n '/COPY/,/\\\./p' > 2.tmp && mv 2.tmp "$TABLE".2.sql

  cat "$TABLE".1.sql | grep COPY > "$TABLE".3.sql
  awk 'NR==FNR{c[$2]++;next};c[$2] == 0' "$TABLE".2.sql "$TABLE".1.sql >> "$TABLE".3.sql
  echo '\.' >> "$TABLE".3.sql

  echo "Copying $TABLE"
  cat "$TABLE".3.sql | psql "$PG_URI2"

  rm -f "$TABLE".{1,2,3}.sql
}

for i in "${tables[@]}"
do
   copy_table "$i"
done

echo "Done"