0

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.

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

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 ls, cd, mkdir, and even scp. 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.

Does anybody understand what is going wrong with my code and why I'm getting back psql: command not found? 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.
Postgresql -bash: psql: command not found


From an external class file:

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 >>sys.stderr
        for e in error:
            print 'ERROR: %s' % e,
    else:
        for r in result:
            print r,



Excerpt from a local script that imports above class and sends out the psql command to remote server:

s = Jump(env, db_mode)
s.db_cmd('select * from student.students limit 5;')



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.

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

ERROR: bash: psql: command not found

Thanks

Community
  • 1
  • 1
alfonso
  • 884
  • 17
  • 31
  • On the remote server, what directory is the `psql` program installed in? How is that directory added to your command path when you log into the remote system? – Kenster Sep 13 '16 at 20:10
  • I took out some of the filepath, but I can find the location. The directory isn't in my command path at all. Interesting ... sounds like you're suggesting I call psql explicitly listing out the complete filepath. I'll give it a try. It didn't occur to me that it was necessary. `$ whereis psql psql: $ which psql /<...>/bin/psql` – alfonso Sep 13 '16 at 20:18
  • Hmm ... I added the full path and the command that was ssh'd over was `/<...>/bin/psql -h -d -U -p -c "begin; select * from student.students limit 5; ROLLBACK;"`. I got back this error: `ERROR: /<...>/bin/psql: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory` It's progress! I'll go down this rabbit hole and see what I can find out. Thanks. – alfonso Sep 13 '16 at 20:21

1 Answers1

2

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.

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:

  • Add one or more directories to your command path.
  • Add one or more environment variables to your environment.
  • define one or more aliases.

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.

There are two simple approaches to fixing this:

  1. Run ssh with the "-t" or "-tt" option, which causes ssh to allocate a TTY on the remote system for psql.
  2. Change your shell startup files on the remote system to perform the necessary initialization on non-interactive sessions.
Kenster
  • 23,465
  • 21
  • 80
  • 106
  • Thanks! I'll see about getting the flag passed through subprocess.Popen, but the documentation is less than clear. Or maybe the documentation is clear and I'm less than attentive... I might need to use another module. I'll update soon once I get the flag passed and can confirm that it works. I'll accept your answer at that point. Thank you. – alfonso Sep 13 '16 at 20:53
  • I'm not a python coder, but `...Popen(['ssh', '-tt', self.host, formatted_query]...` ought to do it. – Kenster Sep 13 '16 at 21:03
  • Ha! I'm not a Python coder either apparently. It looks like your suggestion did pass the flag, but I'm still getting back the same errors. I'll see what I can do regarding the shell startup files. There's lots of red tape involved with making changes to the server. – alfonso Sep 13 '16 at 21:10
  • Thanks @Kenster, turned out "Add one or more directories to your command path" was the solution for me. I was ssh'ing the full path to psql, but it turned out that there were multiple psql paths, and I was choosing the wrong one. – alfonso Sep 19 '16 at 19:13