PostgreSQL – Output psql Results and Errors to a File

You specified #!/bin/sh in your script's shabang line. This gives you a POSIX shell. Under POSIX style shells you can only specify numbers for redirection, as noted in the documentation under section 2.7 Redirection.

&> is a "bashism", shown here in the bash documentation under io-redirection.

This can cause undefined behavior under POSIX compliant shells acting as /bin/sh, and by running shellcheck -s sh on the contents of the script in the above question, as stated under the warning for SC2039, which warns that &> is non-standard, and might fail under different contexts.

You might have better luck, if you specify #!/usr/bin/env bash, or if you change the way that you're doing the redirection, if you want to keep using the #!/bin/sh shell.

As for the $BACKUP_PATH, you might want to double-quote it to prevent any sort of weirdnesses with globbing or word splitting that can happen, which could also possibly explain the zero size tar file.

Hope that helps. =)

There is a massively simpler solution.

SQL code

After removing much of the cruft, it boils down to this:

DO
$do$
DECLARE
   customer_schema text;
BEGIN
   FOR customer_schema IN
      SELECT cname FROM customer
   LOOP 
      EXECUTE format('COPY (
         SELECT p.*, t1.*, t2.* -- etc. Or just: *
         FROM        %1$I.portal         p
         LEFT   JOIN %1$I.p_fb_config    t1 USING (pid)
         LEFT   JOIN %1$I.p_gplus_config t2 USING (pid)
         -- etc.
         ) TO $$/absolute/path/to/my/%1$I_file.csv$$;'
       , customer_schema);
   END LOOP;
 END 
$do$

• Simplify the format() call. Reuse the same parameter many times. Read the documentation here.

• No need to stuff the cnames from your customer table into an array for processing. Just use a FOR loop.

• Standard techniques like table aliases and the USING clause help to simplify SQL code.

Shell function

Adding that back into your shell function:

PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
 $PG_CMD_TP '
DO
$do$
DECLARE
   customer_schema text;
BEGIN
   FOR customer_schema IN
      SELECT cname FROM customer
   LOOP 
      EXECUTE format($f$COPY (
         SELECT *
         FROM        %1$I.portal         p
         LEFT   JOIN %1$I.p_fb_config    t1 USING (pid)
         LEFT   JOIN %1$I.p_gplus_config t2 USING (pid)
         ) TO $p$/absolute/path/to/my/%1$s_file.csv$p$;$f$
       , customer_schema);
   END LOOP;
 END 
$do$';
}

abc

Voilá.

• Enclose the whole query in single quotes for the scope of the shell. Unlike with double quotes the shell does not try any substitutions and the string is passed as is (special characters like $ lose their special meaning, too).

• Switch to dollar-quoting in the plpgsql code to go along with the outer single quotes in the shell (or escape all single quotes properly).

• For a quick solution I named the output files after customer_schema in the example. You may need to sanitize it to be a legal for filename syntax.