Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id DB20F1014388 for ; Thu, 1 Mar 2012 03:51:49 -0400 (AST) Received: from eu1sys200aog113.obsmtp.com ([207.126.144.135]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1S30ni-0003DA-0U for pgsql-admin@postgresql.org; Thu, 01 Mar 2012 07:51:49 +0000 Received: from mail-tul01m020-f175.google.com ([209.85.214.175]) (using TLSv1) by eu1sys200aob113.postini.com ([207.126.147.11]) with SMTP ID DSNKT08qg07DffzPXEiOvPgKFSmQM6XPTycl@postini.com; Thu, 01 Mar 2012 07:51:45 UTC Received: by mail-tul01m020-f175.google.com with SMTP id v19so499328obq.34 for ; Wed, 29 Feb 2012 23:51:31 -0800 (PST) Received-SPF: pass (google.com: domain of kgama@argility.com designates 10.60.26.133 as permitted sender) client-ip=10.60.26.133; Authentication-Results: mr.google.com; spf=pass (google.com: domain of kgama@argility.com designates 10.60.26.133 as permitted sender) smtp.mail=kgama@argility.com Received: from mr.google.com ([10.60.26.133]) by 10.60.26.133 with SMTP id l5mr1480289oeg.22.1330588291090 (num_hops = 1); Wed, 29 Feb 2012 23:51:31 -0800 (PST) Received: by 10.60.26.133 with SMTP id l5mr1291673oeg.22.1330588291003; Wed, 29 Feb 2012 23:51:31 -0800 (PST) From: Khangelani Gama MIME-Version: 1.0 X-Mailer: Microsoft Outlook 14.0 Thread-Index: Acz3gC0yyOTTQ5+xTl+Dh1bcWfrpyQ== Date: Thu, 1 Mar 2012 09:51:57 +0200 Message-ID: <539e85fb02376a0adb1ab5eb6e833cb3@mail.gmail.com> Subject: Can't recover data from a .tar.gz file which was backed up using COPY BINARY command To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=e89a8fb206b66d74ec04ba29bad2 X-Gm-Message-State: ALoCoQkr7ZPCHv5j4foqintTr7izakRqunurt/USxCKeCAZnCjWIU6c0JYhJj7DxQ2SNzCM+ebH4 X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201203/1 X-Sequence-Number: 39189 --e89a8fb206b66d74ec04ba29bad2 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Someone in our company wrote a script that backs up data from a certain table, that person left the company and no one is able to figure it out. Part of this script has something like, look at the COPY BINARY command just below: EXPORTFILE=3D${EXPORTFILE}.${AUD_UP_TO}_${MAX_AUDIT} *$PSQL -c "COPY BINARY dailybackup TO '$EF_PATH/$EXPORTFILE';"* if [ $? -eq 0 ]; then cd $EF_PATH tar cvzf ${EXPORTFILE}.tar.gz ${EXPORTFILE} if [ $? -ne 0 ]; then echo -e "Error while compressing tar file : " > /tmp/backup.trickle.state echo `date` >> /tmp/backup.trickle.state fi echo "Backup to backup disk successful" echo "Update trickle checkpoint" ${ANT} run -Dclass=3Ducs.instore.ejb.system.SetTrickleCheckpoint -Darg1=3D${MAX_AUDIT} I tried to untar the file which went through fine, but now inside the file there is funny characters, see below: PGBCOPY =FF^M ^@^D^C^B^A^@^@^@^@^@^@^@^@^R^@^D^@~UIS^@=FF=FF ^@^@^@1~1~1^D^@^=C9^@^@^D^@^@^@^@^@=FF=FF^K^@^@^@day_end=FF=FF^K^@^@^@centr= al^H^@wI=F4=F0=E5=CF=B6A=FF=FF=B6^B^@^@^M I am aware of the following format, but can=92t figure out how many characters should I specify and this backup system doesn=92t use pg_dump command? COPY [ BINARY ] table_name [ WITH OIDS ] FROM { 'filename' | STDIN } [ [USING] DELIMITERS 'delimiter' ] [ WITH NULL AS 'null string' ] Thanks in advanced CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addre= ssee only, and may contain the confidential information of Argility (Prop= rietary) Limited and/or its subsidiaries. Any review, use or disseminatio= n thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediatel= y and destroy the e-mail. Argility (Proprietary) Limited and its subsidia= ries distance themselves from and accept no liability for unauthorised us= e of their e-mail facilities or e-mails sent other than strictly for busi= ness purposes. =0D --e89a8fb206b66d74ec04ba29bad2 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable

Someone in our company wrote a script that backs up data from a certain ta= ble, that person left the company and no one is able to figure it out. Part= of this script has something like, look at the COPY BINARY command just be= low:

=A0

EXPORTFILE=3D${EXPORTFILE}.${AUD_UP_TO}_${MAX_AUDIT}

=A0=A0 $PSQL -c "COP= Y BINARY dailybackup TO '$EF_PATH/$EXPORTFILE';"

=A0

=A0=A0 if [ $? -eq 0 ]; then

=A0=A0=A0=A0=A0 cd $EF_PATH

<= p class=3D"MsoNormal">=A0=A0=A0=A0=A0 tar cvzf ${EXPORTFILE}.tar.gz ${EXPOR= TFILE}

=A0=A0=A0=A0=A0 if [ $? -ne 0 ]; then

=A0=A0=A0=A0=A0=A0=A0=A0 echo -e &q= uot;Error while compressing tar file :=A0 " > /tmp/backup.trickle.s= tate

=A0=A0=A0=A0=A0=A0=A0=A0 echo `date` >> /tmp/b= ackup.trickle.state

=

=A0

fi

=A0

=A0=A0 echo "Backup to backup disk successful"

=A0

=A0=A0 echo "Update trickle checkpoint"

=A0=A0 =A0

=A0=A0 ${ANT} run -Dclass=3Ducs.instore.ejb.system.SetTr= ickleCheckpoint -Darg1=3D${MAX_AUDIT}

=A0

=A0

=A0

I tried to untar the f= ile which went through fine, but now inside the file there is funny charact= ers, see below:

=A0

PGBCOPY

=FF^= M

^@^D^C^B^A^@^@^@^@^@^@^@^@^R^@^D^@~UIS^@=FF=FF=A0=A0=A0=A0= =A0 ^@^@^@1~1~1^D^@^=C9^@^@^D^@^@^@^@^@=FF=FF^K^@^@^@day_end=FF=FF^K^@^@^@c= entral^H^@wI=F4=F0=E5=CF=B6A=FF=FF=B6^B^@^@<?xml version=3D"1.0&quo= t; encoding=3D"UTF-8"?>^M

=A0

=A0

I am aware of the following format, but can=92t figure out how many c= haracters should I specify and this backup system doesn=92t use pg_dump com= mand?

=A0

= =A0

COPY [ BINARY ] = table_name [ WITH OIDS ]

=A0=A0=A0 FROM { 'filename' | STDIN }

=A0=A0=A0 [ [USING] DELIMITERS &= #39;delimiter' ]

=A0=A0=A0 [ WITH NULL AS 'null string' ]

=A0

=A0

=A0

Thanks in advanced

=A0

=A0

=A0




CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addre=
ssee only, and may contain the confidential information of Argility (Prop=
rietary) Limited and/or its subsidiaries. Any review, use or disseminatio=
n thereof by anyone other than the intended addressee is prohibited.
If you are not the intended addressee please notify the writer immediatel=
y and destroy the e-mail. Argility (Proprietary) Limited and its subsidia=
ries distance themselves from and accept no liability for unauthorised us=
e of their e-mail facilities or e-mails sent other than strictly for busi=
ness purposes.
=0D
--e89a8fb206b66d74ec04ba29bad2--