0

I need to run a huge migration script on multiple schemas on PostgreSQL 9.3 server. I want output to be in a .txt format if this failed on any schema. Would you be able to help me on this?

Details: All schemas are of similar structure and I have the list of schemas on which this script needs to be executed.

Michael Green
  • 24,839
  • 13
  • 51
  • 96
Vinod
  • 1
  • 1
  • Do you know how to use python and foreign data wrapper (FDW)? And those schemas are within same database, same cluster but different DBS, various clasters? – Mladen Uzelac Apr 16 '16 at 13:36

1 Answers1

1

You could run the following bash script. It will expect the list of schemas in the schemas.txt file, and your script in myscript.sql. It will run the script on each schema (as a default schema) in mydatabase, and will produce, for each schema, a file named myscript-output-<the_schema_name>.txt.

For Unix, the script is:

cat schemas.txt | while read schema; do
  (
    echo "SET search_path TO $schema,public;"
    cat myscript.sql
  ) | psql -f - -o myscript-output-${schema}.txt mydatabase
done

For windows, the script is (use %%S instead of %S when used in a batch file):

for /F %S in (schemas.txt) do @(
  echo SET search_path TO %S,public;
  type myscript.sql
) | "C:\Program Files\PostgreSQL\9.3\bin\psql" -f - -o  myscript-output-%S.txt mydatabase
Ezequiel Tolnay
  • 5,008
  • 1
  • 16
  • 23