From: Tim Underwood <tunderwood2@wsutech.edu>
To: 'pgsql-admin@lists.postgresql.org' <pgsql-admin@lists.postgresql.org>
Subject: Questions regarding pgAgent and ODBC drivers
Date: Wed, 2 Oct 2019 13:27:37 +0000
Message-ID: <f88547e0e92e4c1a9dfe2854343dd900@wsutech.edu> (raw)
All,
I have some general questions that aren't (so far as I can tell), specifically answered by docs/wikis.
Background - one of our main systems will be moving "into the cloud", and will be managed as a Software as a Service (SaaS). The database will be on PostgreSQL. The vendor will have both a live database, and a backup we can access using pgadmin, and will be replicated from the live database every few minutes or so. The replicated backup is available to us using pgadmin, so queries can be executed, but CANNOT be stored (no write capability, so no Stored Procedures). We have an a couple Oracle databases, and a SQL Server database.
Here are my questions:
pgAgent - I understand pgAgent is available for creating job schedules. What I've read seems to indicate that the pgagent schema MUST reside on the database in question (the cloud, SaaS database). Is this true? If so, we cannot install or use it since we don't have write access to that database.
Stored Procedures - as I said, we cannot save or store any procedures on the database itself. Read only. So, I've thought of setting up a "linked" server using either Oracle or MS SQL Server, but understand the only real method of linking the two would be to use the postgresql ODBC drivers? If that is the case, unfortunately this probably isn't an option. The vendor has stated that they do not and will not support the ODBC interface. While I know it can be setup, and might work initially, my concern is that since they are the admins for the DB - they may change a setting or do some upgrade which would break the ODBC connection. And - just how reliable is this type of setup?
Need to understand possibilities here, and trying to understand possible alternatives - we have a few stored procedures that run throughout the day, every day, on our on-premise database, but once it moves into the cloud - we no longer have that access.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tunderwood2@wsutech.edu, pgsql-admin@lists.postgresql.org
Subject: Re: Questions regarding pgAgent and ODBC drivers
In-Reply-To: <f88547e0e92e4c1a9dfe2854343dd900@wsutech.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox