Script to import a table to another schema in PostgreSQL

Tested with Centos 5 and PostgreSQL 8.4.

Sadly PostgreSQL does not have remap_schema parameter in its data import tool pg_restore, so importing tables to another schema in destination database is a little bit tricky.

Here is a Bash script I use to connect to the source database over SSH tunnel, export two tables (srv.stat_export and disp.trip) and then import them to the destination database under schema etl.

Download the script: import_table_to_another_schema.sh

#!/bin/bash -l

# Open SSH tunnel to remote database
# Make sure, that passwordless authentication is enabled
ssh -C -N -L 15432:livedb2:5432 username@live.site.com &
sleep 10s

# Set source PostgreSQL connection parameters
# Make sure that PostgreSQL tools (psql, pg_dump) can connect with these parameters without password
# if necessary use ~/.pgpass file
export PGDATABASE=db_live
export PGHOST=localhost
export PGPORT=15432
export PGUSER=exportuser

# Generate temporary filename
SCHEMAFILE=`mktemp`

# Dump the data
pg_dump -t srv.stat_export -t disp.trip --file=$SCHEMAFILE -O --no-acl
RESULT=$?

# Kill SSH tunnel
kill %+

# If export was successfull, then start importing
if [ $RESULT -eq 0 ]; then
  # Replace source schema name with destination schema name
  sed -i '/^SET search_path = /s/disp,\|srv,/etl,/g' $SCHEMAFILE

  # Set destination PostgreSQL connection parameters
  # Make sure that PostgreSQL tools (psql, pg_dump) can connect with these parameters without password
  export PGDATABASE=db_dev
  export PGHOST=localhost
  export PGPORT=5432
  export PGUSER=postgres

  # Drop old tables
  echo 'drop table etl.stat_export; drop table etl.trip; ' | psql -q
  # Load new tables
  psql -q < $SCHEMAFILE
  # Fix access privileges
  echo 'alter table etl.stat_export owner to etl; alter table etl.trip owner to etl;' | psql -q
fi

# Remove temporary file
rm $SCHEMAFILE