Copy database with python..

[Abandoned]

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 ?
Note: The database's size is 200 GB

 

[Carsten Haese]

Hi.
I want to copy my database but python give me error when i use this
command.
cursor.execute("pg_dump mydata > old.dump")

cursor.execute executes SQL queries. pg_dump is not an SQL query, it is
an operating system command. To execute OS commands, use
os.system("...") or the subprocess module.

Hope this helps,

 

[Martin Sand Christensen]

Abandoned> I want to copy my database but python give me error when i
Abandoned> use this command. cursor.execute("pg_dump mydata > old.dump")
Abandoned> What is the problem ?

cursor.execute() is for executing SQL commands, and this is not an SQL
command, but rather a shell command.

Abandoned> And how can i copy the database with python ? Note: The
Abandoned> database's size is 200 GB

If you want to do this from Python, run it as a separate process.

Martin

 

[Paul McNett]

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.

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.

 

[Abandoned]

Abandoned> I want to copy my database but python give me error when i
Abandoned> use this command. cursor.execute("pg_dump mydata > old.dump")
Abandoned> What is the problem ?

cursor.execute() is for executing SQL commands, and this is not an SQL
command, but rather a shell command.

Abandoned> And how can i copy the database with python ? Note: The
Abandoned> database's size is 200 GB

If you want to do this from Python, run it as a separate process.

Martin

Yes i understand thank you.
Now i find that maybe help the other users.

import os
os.system("su postgres")
....
...

 

[Abandoned]

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.


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.

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 )

 

[Martin Sand Christensen]

Abandoned> Yes i understand thank you. Now i find that maybe help the
Abandoned> other users.

Abandoned> import os
Abandoned> os.system("su postgres")
Abandoned> ...

I get the distinct impression that you're trying to replace simple shell
scripting with Python. While it's possible, you're probably making
things much more complicated than they need to be. Unless you're
actually doing something with all that data of yours, don't use Python
where a simple shell script will be much smaller and cleaner.

Martin

 

[Diez B. Roggisch]

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 )

You can invoke the pg_dump on the remote machine.

Diez

 

[Erik Jones]

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.

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.
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®
(e-mail address removed)
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

 

[Bruno Desthuilliers]

Abandoned a écrit :

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 ?

Could it have to do with the fact that cursor.execute expects a valid
SQL query - not a bash command line ?

And how can i copy the database with python ?

import os
help(os.system)