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
ususing pgadmin, so queries can be executed, but CANNOT be stored (no write capability, so no Stored Procedures). We
havean 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
pgagentschema MUST reside on the database in question (the cloud, SaaS database). Is this true? If so, we cannot
installor 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
thoughtof setting up a "linked" server using either Oracle or MS SQL Server, but understand the only real method of
linkingthe 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,
andmight work initially, my concern is that since they are the admins for the DB - they may change a setting or do some
upgradewhich 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
thatrun throughout the day, every day, on our on-premise database, but once it moves into the cloud - we no longer have
thataccess.