Hi all,
I have two servers (production and test) with the same databases. Since lots of people is working on them, from time to time I have to check that there is no difference in the created store procedures and viste.
It’s very time consuming, we have lots of DB, I was therefore thinking if there is a dynamic way to load all SPs and viste in a big txt file, one for test and one for prod, to allow easy comparisons.
many thanks for yoyr help!
There’s usually a command to dump the schemas and procedures from a
database. You could run that, saving to a text file, then diff against
the previous copy.
Cheers,
Cameron Simpson cs@cskk.id.au
Why do you need to check that they are the same?
Instead, just periodically replicate the production database to test, say every night. I expect that a commercial-grade database like Postgres will be able to do that more efficiently than you checking for differences and and then replicating.
https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
In this case, you can take the data in the test database as disposable. You never need to copy test back to production, and can unconditionally copy production back to test as often as you want: weekly, daily, continuously.
Without knowing how Alessandro is currently checking for differences between test and production, how do you know that doing a database dump and text diff is going to be faster that what he is already doing?
Doing a dump of a large database is going to be time consuming, and running diff likewise. Alessandro is asking us to optimise a procedure (determine differences between two databases) without telling us how he is doing it or measuring to see where the bottlenecks are. I think that counts as premature optimization 
Steven, if this was aimed at me, I was talking about dumping the schema.
Not the data.
Cheers,
Cameron Simpson cs@cskk.id.au
My mistake.
But the question still holds: why not just use replication to keep test
and production in sync?
Surely the point of test is to try things not yet in production? It is
inherently an environment which will differ from production.
It sounds like the OP has a dynamic test environment and from time to
time needs to ensure that it is not different from production, or that
if it is that the changes are as expected.
A regular cron job which dumped the schema and diffed it against the
previous dump would be silent when things are unchanged and send email
when the schema changed from the previous run. This, or some similar
automation, would provide some easy to use system for noticing changes.
Even have a script to do that once, and running it by hand at
appropriate times would ease the OP’s problem, since it sounds like it
falls to him to monitor such changes.
Of course, any clarification from allesandro would help.
Cheers,
Cameron Simpson cs@cskk.id.au