Python Subprocess Module | Postgres pg_dump with password

Question

I have a database that I want to back up with my a python code.

I tried to base my code from the code in this discussion that uses the subprocess module and pg_dump. My problem now is that I have to manually type in the password to get the backup file. I read somewhere that doing a .pgpass but I do want to see if it is possible to do it within the subprocess module.

My code follows below:

from subprocess import Popen, PIPE
from pathlib import Path, PureWindowsPath

def backup():
    version = 11
    postgresDir = Path("C:/Program Files/PostgreSQL/{}/bin/".format(version))
    directory = PureWindowsPath(postgresDir)
    filename = 'myBackUp2'  # output filename here
    saveDir = Path("D:/Desktop/{}.tar".format(filename))  # output directory here
    file = PureWindowsPath(saveDir)

    host = 'localhost'
    user = 'postgres'
    port = '5434'
    dbname = 'database_name'  # database name here
    proc = Popen(['pg_dump', '-h', host, '-U', user, '-W', '-p', port,
                   '-F', 't', '-f', str(file), '-d', dbname],
                    cwd=directory, shell=True, stdin=PIPE)
    proc.wait()

backup()

The code above works and the backup is created is I type in the password. I tried to replace proc.wait() with the code below to remove the need of typing the password manually:

return proc.communicate('{}\n'.format(database_password))

But I would receive this error:

TypeError: a bytes-like object is required, not 'str'

Is this possible to do within subprocess? If so, how?

Answer 1

Use a password file.

On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

and the -w or --no-password command line option (instead of -W)

-w

--no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.