Thread: 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.
Kind regards,
Gonzalo
Hi
On Thu, Nov 26, 2020 at 2:42 PM Gonzalo Rubio Casas <gonzalo.rubio.casas@gmail.com> 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.
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 (<dpage@pgadmin.org>) escribió:
Hi--On Thu, Nov 26, 2020 at 2:42 PM Gonzalo Rubio Casas <gonzalo.rubio.casas@gmail.com> 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.
On Thu, Nov 26, 2020 at 3:46 PM Gonzalo Rubio Casas <gonzalo.rubio.casas@gmail.com> wrote:
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.
No, shell scripts can only be executed on the machine pgAgent runs on. Only SQL steps support remote execution (because they can be done using the PostgreSQL wire protocol).
Best,Gonzalo Rubio-CasasEl jue, 26 nov 2020 a las 15:55, Dave Page (<dpage@pgadmin.org>) escribió:Hi--On Thu, Nov 26, 2020 at 2:42 PM Gonzalo Rubio Casas <gonzalo.rubio.casas@gmail.com> 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.