pg_dump password in python

Question

I have a ubuntu remote server say 172.123.342.12. I want to take backup of a postgresql database on my local machine via a python script.

My Script is:

def backUp(self):
        Pass = 'fb2024d4'
        os.putenv("PGPASSWORD",Pass)
        dt = datetime.now()
        format = "%Y_%b_%d"
        cur_time = dt.now()
        form_time = cur_time.strftime(format)
        backup_str = "C:\\Bitnami\\odoo-8.0-7\\postgresql\\bin\\pg_dump.exe --format=c -h 172.123.342.12 -p 5432 -d new_db -U bn_openerp > C:\\Users\\n\\Desktop\\Odoo_Backups\\%s.dump" %form_time
        os.system(backup_str)
        print ("Backup Created in Desktop")
        box.showinfo("Information", "Backup Created")

backup()

It does nothing. Some help will be appreciated.

EDIT: The Script works on a database on windows as i am using admin account. So it does not asks for password. But When i try to backup a database from remote ubuntu server. It asks for password. I have tried following solutions:

1.) SET PGPASSPASSWORD = C:\foo\bar..\pgpass.conf.
2.) os.putenv("PGPASSWORD","password")
3.) PGPASSWORD='password' pg_dump.exe -h localhost.....

No one worked for me.

you can't run " backup_str = "C:\\Bitnami\\odoo-8.0-7\\postgresql\\bin\\pg_dump.exe" on ubuntu... — Vao Tsun, May 27 '15 at 09:22
i am running it from my local windows machine. i have postgresql installed. so i just need to pass on parameters -h -p -d -U and it will do the rest. thats why i asked how to pass postgres password in windows — Manish Gupta, May 27 '15 at 09:43
save apassword to pgpass file - read http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html "On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile)." (c) — Vao Tsun, May 27 '15 at 10:06
can you elaborate a little more. file pgpass.conf was there in directory you said. i edited it and saved a password — Manish Gupta, May 27 '15 at 10:08
if you saved it in correct format, when you connect to a database it will be automatically consumed — Vao Tsun, May 27 '15 at 10:15
when you call pg_dump, it will pickup the file itself. first tries PGPASSWORD var, then PGPASS, then tries default pgpass. automatically — Vao Tsun, May 27 '15 at 10:25
Giving me thid error. pg_dump: [archiver (db)] connection to database "new_db" failed: FATAL: passwor d authentication failed for user "bn_openerp" — Manish Gupta, May 27 '15 at 10:30
172.123.342.12:5432:*:bn_openerp:fb2024d4 i am using this string. — Manish Gupta, May 27 '15 at 10:39
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78884/discussion-between-manish-gupta-and-vao-tsun). — Manish Gupta, May 27 '15 at 10:44

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

I was able to use a python script to create a dump file using pg_dump.exe:

filename = 'C:/Path/To/File/mydb_dump.sql'
pgDump = 'C:/Program Files/PostgeSQL/9.5/bin/pg_dump'

subprocess.Popen('"{}" -h 127.0.0.1 dbname > "{}"'.format(pgDump, filename), shell=True)

A few things to note:

I STRONGLY CAUTION AGAINST USING shell=True !!!

There is a huge security hazard with possible shell injections as per the documentation.

I'm not sure if will work with a remote Ubuntu server, but I couldn't see why not if all permissions and sharing is setup properly.

I know this is pretty old, but I hope it helps.

pg_dump password in python

1 Answers1

A few things to note: