Unable to run psql commands on remote server from local python script
<p>I'm writing a script that is called locally to run psql queries on a remote server. I'm writing the script in Python 2.7 using the subprocess module. I'm using subprocess.Popen to ssh into the remote server and directly run a psql command. My local machine is osx and I think the server is CentOS.</p>

<p>When I call my script locally, I get back an error saying <code>psql: command not found</code>. If I run the same exact psql command on the remote server, then I get back the correct query result.</p>

<p>I suspected there might be something wrong with my ssh code, so instead of sending over psql commands to the remote server, I tried sending over simple commands like <code>ls</code>, <code>cd</code>, <code>mkdir</code>, and even <code>scp</code>. All of those worked fine, so I don't think there's a problem with my code that ssh's over the commands to the remote server.</p>

<p><strong>Does anybody understand what is going wrong with my code and why I'm getting back <code>psql: command not found</code>?</strong> I researched and found this earlier SO question, but psql is installed on the remote server as evidenced by the fact that I can run psql commands manually on there.
<br>
<a href="http://stackoverflow.com/questions/6790088/postgresql-bash-psql-command-not-found">Postgresql -bash: psql: command not found</a>
<br>
<br>
<br>
From an external class file:</p>

<pre><code>def db_cmd(self, query):
    # check that query ends in semicolon
    if query[-1] != ';':
        query = query + ';'

    formatted_query = 'psql ' + self.db_string + \
        ' -c "begin; ' + query + ' ' + self.db_mode + ';"'
    print formatted_query

    ssh = subprocess.Popen(['ssh', self.host, formatted_query],
                           shell=False,
                           stdout=subprocess.PIPE,
                           stderr=subprocess.PIPE)
    result = ssh.stdout.readlines()
    if not result:
        error = ssh.stderr.readlines()
        print &gt;&gt;sys.stderr
        for e in error:
            print 'ERROR: %s' % e,
    else:
        for r in result:
            print r,
</code></pre>

<p><br>
<br>
Excerpt from a local script that imports above class and sends out the psql command to remote server:</p>

<pre><code>s = Jump(env, db_mode)
s.db_cmd('select * from student.students limit 5;')
</code></pre>

<p><br>
<br>
Running my script locally. 
Note that the script prints out the psql command for debugging. If I copy and paste that same psql command and run it on the remote server, I get back the correct query result.</p>

<pre><code>$ ./script.py 1 PROD  ROLLBACK
psql -h &lt;server_host&gt; -d &lt;db_name&gt; -U &lt;db_user&gt; -p &lt;port&gt; -c "begin; select * from student.students limit 5; ROLLBACK;"

ERROR: bash: psql: command not found
</code></pre>

<p>Thanks</p>

<p>When you run ssh for an interactive session on a remote system, ssh requests a PTY (pseudo TTY) for the remote system. When you run ssh to run a command on a remote system, ssh doesn't allocate a PTY by default.</p>

<p>Having a TTY/PTY changes the way your shell initializes itself. Your actual problem is you need to do any or all of the following to run psql on the remote system:</p>

<ul>
<li>Add one or more directories to your command path.</li>
<li>Add one or more environment variables to your environment.</li>
<li>define one or more aliases.</li>
</ul>

<p>You're doing these things in a shell startup file (.bash_profile, for example) and it's only happening for interactive sessions. When you use ssh to run psql remotely, your shell is skipping the initialization which permits psql to run.</p>

<p>There are two simple approaches to fixing this:</p>

<ol>
<li>Run ssh with the "-t" or "-tt" option, which causes ssh to allocate a TTY on the remote system for psql.</li>
<li>Change your shell startup files on the remote system to perform the necessary initialization on non-interactive sessions.</li>
</ol>

python python-2.7 ssh subprocess psql