postgresql Backup and Restore Using Copy to export
Example
To Copy table to standard o/p
COPY <tablename> TO STDOUT (DELIMITER '|');
To export table user to Standard ouput:
COPY user TO STDOUT (DELIMITER '|');
To Copy table to file
COPY user FROM '/home/user/user_data' WITH DELIMITER '|';
To Copy the output of SQL statement to file
COPY (sql statement) TO '<filename with path>';
COPY (SELECT * FROM user WHERE user_name LIKE 'A%') TO '/home/user/user_data';
To Copy into a compressed file
COPY user TO PROGRAM 'gzip > /home/user/user_data.gz';
Here program gzip is executed to compress user table data.
