Export data from a query ran by pgagent

Hi everyone,

I hope someone has an idea because I have been stuck with this for a couple of days. I'm using pgagent to allow some users of my database to schedule period remote queries that create some reports.

However, I do not find anywhere on the manual or the mailing list how to retrieve the result of the ran queries. Alternatively, I have used COPY statement although that requires the SQL query being run by a superuser which is not an option anymore as this compromises the general security of the system.

Does anyone know or has come with a hack for exporting the information retrieved by a query run by pgagent? It is important to note that pgagent jobs are connected to a remote database so the possibility of a shell script is not possible I'm afraid.

Hi

On Thu, Nov 26, 2020 at 2:42 PM Gonzalo Rubio Casas <[hidden email]> wrote:

Hi everyone,

I hope someone has an idea because I have been stuck with this for a couple of days. I'm using pgagent to allow some users of my database to schedule period remote queries that create some reports.

However, I do not find anywhere on the manual or the mailing list how to retrieve the result of the ran queries. Alternatively, I have used COPY statement although that requires the SQL query being run by a superuser which is not an option anymore as this compromises the general security of the system.

Does anyone know or has come with a hack for exporting the information retrieved by a query run by pgagent? It is important to note that pgagent jobs are connected to a remote database so the possibility of a shell script is not possible I'm afraid.

pgAgent isn't designed to do this sort of thing I'm afraid. Given that you can't use a shell script (or COPY), probably the best you could do is have the query results be dumped into a table from which the users can query the data when they need it.

 

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

Hi Dave,

Thanks for your quick response,

I'm afraid dumping the query results into another table is not possible as the queried database is a read-only copy of our main database.

I assume there's no way to schedule remote shell script executions with pgAgent right? I didn't find anything on the documentation about that specific point.

Best,

Gonzalo Rubio-Casas

El jue, 26 nov 2020 a las 15:55, Dave Page (<[hidden email]>) escribió:

Hi

On Thu, Nov 26, 2020 at 2:42 PM Gonzalo Rubio Casas <[hidden email]> wrote:

Hi everyone,

I hope someone has an idea because I have been stuck with this for a couple of days. I'm using pgagent to allow some users of my database to schedule period remote queries that create some reports.

However, I do not find anywhere on the manual or the mailing list how to retrieve the result of the ran queries. Alternatively, I have used COPY statement although that requires the SQL query being run by a superuser which is not an option anymore as this compromises the general security of the system.

Does anyone know or has come with a hack for exporting the information retrieved by a query run by pgagent? It is important to note that pgagent jobs are connected to a remote database so the possibility of a shell script is not possible I'm afraid.

pgAgent isn't designed to do this sort of thing I'm afraid. Given that you can't use a shell script (or COPY), probably the best you could do is have the query results be dumped into a table from which the users can query the data when they need it.

 

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com