Python Fabric perform remote database task

Question

I need to ssh to a remote Ubuntu server to do some routine job, in following steps:

ssh in as userA
sudo su - userB
run daliy_python.py script with use psycopg2 to read some info from the database (via local connection (non-TCP/IP))
scp readings to my local machine

The question is: How to do that automatically? I've try to use Fabric, but I run into a problem with psycopg2, after I run the Fabric script below, I received error from my daliy_python.py

psycopg2.OperationalError: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/xxx/.s.xxxx"?

My fabfile.py code is as below:

from fabric.api import *

import os
import socket
import pwd

# Target machine setting
srv = 'server.hostname.com'
env.hosts = [srv]
env.user = 'userA'
env.key_filename = '/location/to/my/key'

env.timeout = 2
# Force fabric abort at timeout
env.skip_bad_hosts = False

def run_remote():
    user = 'userB'    
    with settings(warn_only=True):
        run('whoami')
        with cd('/home/%s/script/script_folder' % user):
            sudo('whoami')
            sudo('pwd', user=user)
            sudo('ls', user=user)
            sudo('python daliy_python.py', user=user)

Any suggestions? My database can only be access via userB locally, but only userA can ssh to the server. That might be a limitation. Both local and remote machine is running Ubuntu 14.04.

That's a database connection error which is really a separate concern from running remote processes with fabric. See some of these possible solutions: https://stackoverflow.com/questions/5500332/cant-connect-the-postgresql-with-psycopg2 https://stackoverflow.com/questions/26034092/connection-error-while-connecting-to-postgresql-as-postgres-user — chishaku, Jun 09 '16 at 17:08
@chishaku Thanks, I don't know where this error comes from, If I am ssh to the server and perform the job, it works fine. It just happened when I use fabric script, that makes me wonder if I did something wrong with the fabric setting/or I should use other tool? — cityzz, Jun 09 '16 at 17:31

C14L · Answer 1 · 2016-06-09T17:49:00.247

This is what I do to read my root accessible logfiles without extra login

ssh usera@12.34.56.78 "echo hunter2 | sudo -S tail -f /var/log/nginx/access.log"

That is: ssh usera@12.34.56.78 "..run this code on the remote.."

Then on the remote, you pipe the sudo password into sudo -S echo hunter2 | sudo -S

Add a -u userb to sudo to switch to a particular user, I am using root in my case. So then as the sudo'ed user, run your script. In my case tail -f /var/log/nginx/access.log.

But, reading your post, I would probably simply set up a cronjob on the remote, so it runs automatically. I actually do that for all my databases. A cronjob dumps them once a day to a certain directory, with the date as filename. Then I download them to my local PC with rsync an hour later.

Thanks, possibly it is the better option. To do it in to two steps, one, cron job running on server, dump new info to a file, second, ssh to the server and download the file. — cityzz, Jun 09 '16 at 17:36

score 0 · Accepted Answer · edited May 23 '17 at 11:58

I finally find out where my problem is. Thanks @chishake and @C14L, I look at the problem in another way. After inspired by this posts link1 link2, I start to think this problem is related to environmental variables. Thus I add a with statement to alter $HOME and it worked.

fabfile.py is as below:

from fabric.api import *

import os
import socket
import pwd

# Target machine setting
srv = 'server.hostname.com'
env.hosts = [srv]
env.user = 'userA'
env.key_filename = '/location/to/my/key'

env.timeout = 2
# Force fabric abort at timeout
env.skip_bad_hosts = False

def run_remote():
    user = 'userB'    
    with settings(warn_only=True):
        run('whoami')
        with shell_env(HOME='/home/%s' % user):
            sudo('echo $HOME', user=user)
            with cd('/home/%s/script/script_folder' % user):
                sudo('whoami')
                sudo('pwd', user=user)
                sudo('ls', user=user)
                sudo('python daliy_python.py', user=user)

Python Fabric perform remote database task

2 Answers2