On Thu, Nov 26, 2020 at 3:46 PM Gonzalo Rubio Casas < gonzalo.rubio.ca...@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-Casas > > El jue, 26 nov 2020 a las 15:55, Dave Page (<dp...@pgadmin.org>) escribió: > >> Hi >> >> On Thu, Nov 26, 2020 at 2:42 PM Gonzalo Rubio Casas < >> gonzalo.rubio.ca...@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. >> >> -- >> Dave Page >> Blog: http://pgsnake.blogspot.com >> Twitter: @pgsnake >> >> EDB: http://www.enterprisedb.com >> >> -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EDB: http://www.enterprisedb.com
