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.
