psql copy command create empty file in the remote server

Question

I use psql copy command to convert PostgreSQL result to CVS file on remote server. I use the below code in python. But it is creating an empty file in the directory.

secretData=json.loads(get_secret(row[31]))
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(hostname=secretData['linux-host'], username=secretData['linux-username'],
            password=secretData['linux-password'], port=secretData['linux-port'])
channel = ssh.get_transport().open_session()
query = 'select * from ' + row[20] + ' where ' + row[25] + ' > ' + row[21] + ' and ' + row[25] + '<=' + str(maxID) + ''
SQL_for_file_output='export PGPASSWORD='+secretData['postgresPassword']+';psql -h "'+secretData['postgreshost']+'" -U "'+secretData['postgresUserName']+'" -c "\copy ('+query+' ) to STDOUT csv header" DB > '+row[17]+row[2]

channel.exec_command(SQL_for_file_output)

exit_code = channel.recv_exit_status()
if exit_code == 0:
    print('File Moved Successfully')
else:
    stdout = channel.makefile().read()
    stderr = channel.makefile_stderr().read()
    print(stdout)
    print(stderr)
    print("Error", exit_code)
channel.close()
ssh.close()

What am missing?

Can anyone please suggest me the solution?

@GiovaniSalazar Thanks for the reply. With headers only, am I missing anything in the command — kaviya .P, Dec 11 '19 at 13:34
then print(query ) and print(SQL_for_file_output) and and execute them remote server..check if you have the same result — GiovaniSalazar, Dec 11 '19 at 13:42
@GiovaniSalazar yes I tried it in remote server. It was throwing error like psql: not found.\n'. What may be the issue here. — kaviya .P, Dec 11 '19 at 15:42
check this https://stackoverflow.com/questions/6790088/postgresql-bash-psql-command-not-found — GiovaniSalazar, Dec 11 '19 at 15:47
are you sure that the secretData['postgreshost'] is installed PostgreSQL ? ..your server is linux or windows ? — GiovaniSalazar, Dec 11 '19 at 15:48
@GiovaniSalazar My server is linux. How can I check that, since I don't have much permission to view the bin directory. — kaviya .P, Dec 11 '19 at 15:50
for linux is more easy if you have acces with root ....have you root? — GiovaniSalazar, Dec 11 '19 at 15:52
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/204067/discussion-between-giovanisalazar-and-kaviya-p). — GiovaniSalazar, Dec 11 '19 at 15:56

psql copy command create empty file in the remote server

0 Answers0