Thread: Converting Copy to insert statement in backup file

Converting Copy to insert statement in backup file

From
"Chris Preston"
Date:
<div class="Section1"><p class="MsoNormal"><span class="postbody1"><font face="Verdana" size="1"><span
style="font-size:9.0pt;font-family:Verdana">Hello,</span></font></span><pclass="MsoNormal"><span
class="postbody1"><fontface="Verdana" size="1"><span style="font-size:9.0pt;font-family:Verdana">I have a backup cron
job(shown below) but its too big and there are times that I want to just cut out from the text file certain areas to
restoredata in a specific table… Looking in the file, I notice there is a “copy” command.. Someone told me that there
was a parameter that I could use to convert the copy to insert so I could easily cut it out of the file and run it on
itsown ..Any ideas </span></font></span><p class="MsoNormal"><span class="postbody1"><font face="Verdana"
size="1"><spanstyle="font-size:9.0pt;font-family:Verdana"> </span></font></span><p class="MsoNormal"><span
class="postbody1"><fontface="Verdana" size="1"><span style="font-size:9.0pt;font-family:Verdana">su -c
'/usr/bin/pg_dump-U postgres -o -C ttms |gzip > /home/hrd/ttmsqltrams/sqlbackup.gz'
postgres</span></font></span><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"></span></font></div>

Re: Converting Copy to insert statement in backup file

From
"Scott Marlowe"
Date:
On Thu, Jul 10, 2008 at 9:40 AM, Chris Preston
<chris@thetrafalgartravel.com> wrote:
> Hello,
>
> I have a backup cron job (shown below) but its too big and there are times
> that I want to just cut out from the text file certain areas to restore data
> in a specific table… Looking in the file, I notice there is a "copy"
> command.. Someone told me that there was  a parameter that I could use to
> convert the copy to insert so I could easily cut it out of the file and run
> it on its own ..Any ideas

pg_dump --help says:
 -d, --inserts               dump data as INSERT commands, rather than COPY -D, --column-inserts        dump data as
INSERTcommands with column names 

So one of those two commands will output insert commands instead of
copy commands.  note that insert commands tend to run a bit more
slowly, even if you enclose them in a transaction.