Search code examples
pythonpostgresqlshellbackupos.system

Backup Postgres from Python on Win10

I'm trying to backup Postgres from Python on Win10.

I'm working on Anaconda python 3.8, Win10 machine with Postgres12 local. On path environment variable I have postgres (lib and bin), no anaconda, and python 3.8 (no the anaconda one).

I'm able to correctly backup the database using on window's command shell:

pg_dump --dbname=postgresql://postgres:[email protected]:5432/test > C:\backup\dumpfile3.dump

but when I run it on anaconda:

os.system("pg_dump --dbname=postgresql://postgres:[email protected]:5432/test > C:\backup\dumpfile3.dump"  )

I get as output 1 , witch is error code. It creates the file, but it's empty.

Using:

import subprocess
stk= 'pg_dump --dbname=postgresql://postgres:[email protected]:5432/test > C:\backup\dumpfile3.dump'

try:
    subprocess.check_output(stk, shell=True, stderr=subprocess.STDOUT)
except subprocess.CalledProcessError as e:
    raise RuntimeError("command '{}' return with error (code {}): {}".format(e.cmd, e.returncode, e.output))

I get :

RuntimeError: command 'pg_dump --dbname=postgresql://postgres:[email protected]:5432/test > C:\backup\dumpfile3.dump' return with error (code 1): b"'pg_dump' is not recognized as an internal or external command,\r\noperable program or batch file.\r\n"

If I use: subprocess.run or subprocess.call I doesn't produce error, but the created file it's empty.

It seems that neither os.system or subprocess on the anaconda interpreter, got access to the environment variables on the command shell. How is this possible? and how I can overcome it?. It is different user invoking the shell?

Thanks in advance.

Solution

  • The Computer was restarted, and it solves the issue... . There was no change in the paths, I believe that from the moment things (python ,postgres, ...) were installed, the machine hasn"t been restarted.

    import os
os.system("pg_dump --dbname=postgresql://postgres:[email protected]:5432/test > C:\backup\dumpfile3.dump" )

    worked!, and

    import subprocess
subprocess.call(r"C:\some\path\backup.bat")

    also worked!. Inside backup.bat is:

    pg_dump pg_dump --dbname=postgresql://postgres:[email protected]:5432/test > C:\backup\dumpfile3.dump

    I imagine that the issue was that the anaconda interpreter need a system restart to get access to the environment variables (where the postgres variable was), witch make very little sense as return with error (code 1): b"'pg_dump' is not recognized as an internal or external command,\r\noperable program or batch file.\r\n" seen like a console message.

    If anyone have a better explanation, is welcome.