For Arrow itself, you could start with the Cookbook [1] (and feel free to file issues if there are things that are unclear, or that you would like to learn more about [2])
The example there is very long, but much of it is repetitive (that said, it could certainly be improved). But to your specific question, what you would eventually do is write a plugin or proxy for Postgres (or another database) implementing these RPC methods. Then you could submit SQL queries using the Flight SQL client (which calls those RPC methods internally) to get back columnar data. You could wrap this all in a JDBC driver as well (which is being actively worked on [3]), in which case you'd be mostly ignorant of what's going on underneath; the benefit there would (hopefully) be faster data access due to the lower layers being columnar (and zero-copy where possible), and enabling parallelism due to Flight (Postgres might not support this for instance, but other databases could, if they could expose internal parallelism or partitioning). Hope that helps.
[1]: https://arrow.apache.org/cookbook/ [2]: https://github.com/apache/arrow-cookbook/ [3]: https://github.com/apache/arrow/pull/12254