title: pg_dump & pg_restore password using python module subprocess
body: <p><strong>Problem:</strong> Use the PSQL <code>pg_dump</code> and <code>pg_restore</code> in a Python script and using the <code>subprocess</code> module.</p>
  
  <p><strong>Background:</strong> I am using the following <code>python 2.7</code> script from the localhost (i.e. <code>Ubuntu 14.04.5 LTS</code>) to create a backup of a table in a PSQL server (i.e. <code>PostgreSQL 9.4.11</code>) and restore it into the remote host (i.e. <code>Ubuntu 16.04.2 LTS</code>) in a newer version of PSQL server (i.e. <code>PostgreSQL 9.6.2</code>).</p>
  
  <pre><code>#!/usr/bin/python
  
  from subprocess import PIPE,Popen
  
  def dump_table(host_name,database_name,user_name,database_password,table_name):
  
      command = 'pg_dump -h {0} -d {1} -U {2} -p 5432 -t public.{3} -Fc -f /tmp/table.dmp'\
      .format(host_name,database_name,user_name,table_name)
  
      p = Popen(command,shell=True,stdin=PIPE)
  
      return p.communicate('{}\n'.format(database_password))
  
  def restore_table(host_name,database_name,user_name,database_password):
  
      command = 'pg_restore -h {0} -d {1} -U {2} &lt; /tmp/table.dmp'\
      .format(host_name,database_name,user_name)
  
      p = Popen(command,shell=True,stdin=PIPE)
  
      return p.communicate('{}\n'.format(database_password))
  
  def main():
      dump_table('localhost','testdb','user_name','passwd','test_tbl')
      restore_table('remotehost','new_db','user_name','passwd')
  
  if __name__ == "__main__":
      main()
  </code></pre>
  
  <p>When I use the functions sequentially as above the <code>dump_table()</code> function finishes successfully and creates the <code>/tmp/table.sql</code> file but the <code>restore_table()</code> function returns the following error:</p>
  
  <blockquote>
    <p>('', 'Password: \npg_restore: [archiver (db)] connection to database
    "database_name" failed: FATAL: password authentication failed for user
    "username"\nFATAL: password authentication failed for user
    "username"\n')*</p>
  </blockquote>
  
  <p>I have checked the credentials &amp; outputs by executing the commands for <code>pg_restore</code> in the shell and I have also included the credentials to .pgpass (although not relevant since I am passing the password in <code>p.communicate()</code>)</p>
  
  <p>Anyone had similar experience? I am pretty much stuck!</p>
  
  <p>Regards,
  D.</p>