Spool/Capture output of COPY command in postgres/Redshift in shell script

Question

I have shell script which will insert data into Redshift/Postgres database using psql command and i am using > for capturing script output. It captures everything but not how many rows inserted. When i run manually script on screen i can see rows inserted but not when i append output to file.

Currently i am getting -

TRUNCATE TABLE and COMMIT TRANSACTION
COPY

Expected output-

TRUNCATE TABLE and COMMIT TRANSACTION
INFO:  Load into table 'tablename' completed, 568 record(s) loaded successfully.
COPY

Command for insert from my script -

psql -w -U uname -h dbhost db --port 1234 -c "COPY tablename FROM 's3://bucket1/tablename.json' credentials 'aws_access_key_id=' format as json 'auto';"

That message is probably going to `stderr` and not `stdout`. Try adding `2>&1` at the end of your command. — Panki, Oct 14 '20 at 12:42

score 1 · Accepted Answer · answered Oct 14 '20 at 14:30

You are only redirecting stdout, but that message is being printed to stderr.

To solve this, redirect the output of stderr (2) to stdout (1) by appending 2>&1.

psql -w -U uname -h dbhost db --port 1234 \
-c "COPY tablename FROM 's3://bucket1/tablename.json' credentials 'aws_access_key_id=' format as json 'auto';" \
 2>&1 > /path/to/log

Spool/Capture output of COPY command in postgres/Redshift in shell script

1 Answers1