Call pg_restore from python

I'm trying to automatically download pg_dump files and restore these. I'm having trouble executing the pg_restore command. I'm currently using:

from subprocess import PIPE, Popen
import shlex


command = f'pg_restore ' \
          f'-h {host} ' \
          f'-d {database} ' \
          f'-U {username} ' \
          f'{file_path}'

command = shlex.split(command)
p = Popen(command, shell=False, stdin=PIPE, stdout=PIPE, stderr=PIPE)
p.communicate(str.encode(f'\r{password}\r'))

This however prompts me to fill in my password (when I fill it in, it works just fine).

What I've tried so far:

set shell = True (in which case I get the error [Info] 2019-12-09 14:59:55 - [root] (b'', b'pg_restore: [archiver] input file does not appear to be a valid archive (too short?)\n'))
pass the password using

p.stdin.write(f'{self.push_password}\n')
p.stdin.flush()

Using \n instead of \r
Only passing \r at the end of the p.communicate

I'm out of ideas and don't know what to do. Does anybody know how to automatically pass my password when calling pg_restore from python?

Solution

Two ways.

Set environmental variables:

PGDATABASE, PGHOST, PGPORT and/or PGUSER

Set in file

Password can be put in a file and postgresql picks it up

Documention

When the defaults aren't quite right, you can save yourself some typing by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or PGUSER to appropriate values. (For additional environment variables, see Section 30.12.) It is also convenient to have a ~/.pgpass file to avoid regularly having to type in passwords. See Section 30.13 for more information.

More: https://www.postgresql.org/docs/8.3/app-psql.html

Discussed here: How do I specify a password to psql non-interactively?