0

I'm not massively familiar with Postgres so apologies if this is a silly question.

I'd like to create a crontab job on a server that will run a query and export the results to CSV in a directory on a different server.

The reason for this is that the resulting file needs to be web accessible so needs to be copied to a folder on an http server. However, the http server I have access to does not have postgres installed (so no \copy or pgsql commands locally) and the database server doesn't have directories exposed to http.

So, is it possible to create a batch script, presumably on the server with posgres installed that will copy the query results to a csv on a different server?

Improve this question

1 Answer 1

Reset to default
1

If you are able to ssh from the database server to the web server, then here is one possibility:

psql -d dbname -t -A -F"," -c "SELECT whatever FROM whatever" | ssh user@webhost 'cat > /destination/path/file.csv'

Before doing this, you would want to set up SSH keys to allow the ssh connection to go through without entering a password.

You could also do it the other way around, put the cron job on the web server and do this:

ssh user@webhost 'psql -d dbname -t -A -F"," -c "SELECT whatever FROM whatever"' > /destination/path/file.csv
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Why not \copy as a -c for psql? Seems to be more appropriate.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.