Export data from a query ran by pgagent

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Export data from a query ran by pgagent

Gonzalo Rubio Casas
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


Reply | Threaded
Open this post in threaded view
|

Re: Export data from a query ran by pgagent

Dave Page-7
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

Reply | Threaded
Open this post in threaded view
|

Re: Export data from a query ran by pgagent

Gonzalo Rubio Casas
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

Reply | Threaded
Open this post in threaded view
|

Re: Export data from a query ran by pgagent

Dave Page-7


On Thu, Nov 26, 2020 at 3:46 PM Gonzalo Rubio Casas <[hidden email]> 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 (<[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



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

EDB: http://www.enterprisedb.com