Copy database with python..

[Erik Jones]

On Nov 2, 2007, at 11:49 AM, Diez B. Roggisch wrote:

> Abandoned wrote:
>
>> On Nov 2, 4:19 pm, Paul McNett <p... at ulmcnett.com> wrote:
>>> Abandoned wrote:
>>>> Hi.
>>>> I want to copy my database but python give me error when i use this
>>>> command.
>>>> cursor.execute("pg_dump mydata > old.dump")
>>>> What is the problem ? And how can i copy the database with python ?
>>>
>>> You are just going to have to give us more to go on. Please post the
>>> entire traceback of the error that you see (copy/paste it from your
>>> terminal).
>>>
>>> You can't issue system commands using the cursor's execute()  
>>> method and
>>> expect that to work. execute() is for executing SQL, DML, or DDL,  
>>> not
>>> for doing shell stuff.
>>>
>>> Try:
>>>
>>> import os
>>> os.system("pg_dump mydata > /tmp/old.dump")
>>>
>>> but I'm not versed in postgressql, so this probably won't work  
>>> exactly
>>> as written. You'd need to run that code from the server hosting the
>>> postgresql database.

Not true, you can run the dump from any server that can connect to  
the database server (based on network connectivity and the connection  
permissions in your pg_hba.conf file).  Just be sure to use the -h  
(host) -p (port) and -U (username) flags when execute pg_dump.

>>>
>>>> Note: The database's size is 200 GB
>>>
>>> Well, then you may want to make sure you have enough room on the  
>>> target
>>> volume before trying to dump the file! Note that the dump file could
>>> conceivably be much larger (or much smaller) than the database  
>>> itself.

If you use the -F c (pg_dump -F -c -f your_dmp.file your_db) option  
you'll get a compressed dump file which can be as little as 10% (or  
less) the size of your database.
>>>
>>> --
>>> pkm ~http://paulmcnett.com
>>
>> Are there any way to copy database without dump or any temp files ?
>> (If there is a temp my harddisk not enough for this operation :( )

If you can stop the database then you can just do a manual copy  
(using cp, rsync, or whatever) of the entire pg data directory but  
that will require the same amount of space as the original database.   
If you're goal is to make a backup, pg_dump is the way to go.  Make  
note, though, pg_dump will only dump one database so if you have more  
than one database in your postgres "cluster" then you'll need to use  
pg_dumpall in which case you don't have the compression (-F c)  
option.  There are other caveats between the two as well.  Just be  
sure to read the documentation.  Also, for other postgres questions,  
you should join one of the postgres mailing lists.

Erik Jones

Software Developer | Emma®
erik at myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com