#!/bin/sh -e

echo "=== create empty databases ==="
dropdb -q old 2>/dev/null || true
dropdb -q new 2>/dev/null || true
createdb -q old
createdb -q new

echo "=== populating old database ==="
psql -q old <<EOF
CREATE TABLE userdata (x int);
INSERT INTO userdata VALUES (42);
INSERT INTO userdata VALUES (256);
CREATE TABLE auxdata (x int);
INSERT INTO auxdata VALUES (1);
INSERT INTO auxdata VALUES (2);
EOF

echo "=== pre-creating auxdata in new database ==="
psql -q new <<EOF
CREATE TABLE auxdata (x int);
INSERT INTO auxdata VALUES (-1);
INSERT INTO auxdata VALUES (-2);
EOF

echo "=== restoring old to new ==="
pg_dump -Fc old | pg_restore -vd new || echo "pg_restore failed with $?"

echo "=== new/userdata: ==="
psql -Atc 'SELECT * FROM userdata' new

echo "=== new/auxdata: ==="
psql -Atc 'SELECT * FROM auxdata' new