From: Tim Underwood <tunderwood2@wsutech.edu>
To: "'pgsql-admin@lists.postgresql.org'" <pgsql-admin@lists.postgresql.org>
Subject: Questions regarding pgAgent and ODBC drivers
Thread-Topic: Questions regarding pgAgent and ODBC drivers
Thread-Index: AdV5JHWSDdu3JUWlQ3CMO3DYsACCsw==
Date: Wed, 2 Oct 2019 13:27:37 +0000
Message-ID: <f88547e0e92e4c1a9dfe2854343dd900@wsutech.edu>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk

All,

I have some general questions that aren't (so far as I can tell), specifica=
lly answered by docs/wikis.

Background - one of our main systems will be moving "into the cloud", and w=
ill 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 f=
ew 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 n=
o Stored Procedures).  We have an a couple Oracle databases, and a SQL Serv=
er database.

Here are my questions:

pgAgent - I understand pgAgent is available for creating job schedules.  Wh=
at I've read seems to indicate that the pgagent schema MUST reside on the d=
atabase in question (the cloud, SaaS database).  Is this true?  If so, we c=
annot 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 th=
e database itself.  Read only.  So, I've thought of setting up a "linked" s=
erver using either Oracle or MS SQL Server, but understand the only real me=
thod of linking the two would be to use the postgresql ODBC drivers?   If t=
hat is the case, unfortunately this probably isn't an option.  The vendor h=
as 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 upgra=
de 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 al=
ternatives - we have a few stored procedures that run throughout the day, e=
very day, on our on-premise database, but once it moves into the cloud - we=
 no longer have that access.=20