Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to write a script to backup a table using COPY.

psql "connection parameters" -c "COPY (SELECT * FROM tbl WHERE insertion_date > 'date') TO STDOUT WITH CSV HEADER;" | bzip2 -c > backup.csv.bz2

Now I want to log how many lines are copied to zip file? I want to calculate it while copying(Not another command).

share|improve this question
    
I improved my solution by using GET DIAGNOSTICS. Should have been in there right away. –  Erwin Brandstetter Mar 11 '12 at 17:12

1 Answer 1

up vote 1 down vote accepted

I recently ran into the same problem and it gave me quite a headache. I tried with a plpgsql function, but ROW_COUNT is not set by COPY. I consider this a bit of a bug or at least a missing feature.

You could just run two queries. First count() and then COPY. With simple queries that's probably the way to go. But with huge / complex queries this is a pain and can double the execution time.

I came up with a solution that uses a temporary table and counts the rows before executing COPY.

I adapted what I have for you so you can COPY TO STDOUT and pipe to bzip2, which is not possible from within a plpgsql function:

1) Create a function that takes an SQL string and creates a temporary table with it:

CREATE OR REPLACE FUNCTION f_copy_prep(_query text)
  RETURNS void AS
$BODY$
DECLARE
   ct integer;
BEGIN

-- If you deal with huge results, set more RAM for temporary tables locally:
-- SET temp_buffers = 512MB';  -- example value

EXECUTE 'CREATE TEMP TABLE cp_tmp ON COMMIT DROP AS (' || _query || ')';

GET DIAGNOSTICS ct = ROW_COUNT;
RAISE LOG 'My text here. rows: %', ct;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION f_copy_prep(text) SET search_path=public,pg_temp;
REVOKE ALL ON FUNCTION f_copy_prep(text) FROM public;
GRANT EXECUTE ON FUNCTION f_copy_prep(text) TO ???;
  • Function executes dynamic SQL, so you can use it for any query.

  • This is inherently unsafe, so run it with the minimum rights necessary, revoke all rights from public and grant EXECUTE exclusively to a trusted user. Follow the instructions in the manual!

  • Create temporary table with ON COMMIT DROP, so it gets dropped automatically at the end of the transaction.

  • Get the row count with GET DIAGNOSTICS - ROW_COUNT is set by the SELECT statement in EXECUTE. Write it to the log - your requirement. No need for a separate count(*).

2) Call from shell-script to pipe output through bzip2

psql "connection parameters" \
-c "SELECT f_copy_prep('SELECT * FROM tbl WHERE insertion_date > ''date'''); \
    COPY cp_tmp TO STDOUT WITH CSV HEADER;" \
| bzip2 -c > backup.csv.bz2
  • Put two SQL commands into your -c argument or put complex queries in a file and use the -f parameter. All is executed in one transaction. Only the output of the last command is returned - fits our need. Careful with the syntax - multiple layers of interpretation (first shell, then Postgres).

  • First command is the above function with your query-string as parameter. Second is the COPY TO STDOUT.

I tested this with PostgreSQL 9.1 on Linux and it worked for me: data in the file, message with row-count in the log.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.