I’ve been considering the missing features of dbtoyaml/yamltodb. Two of those are PG 9.1 features: COLLATIONs and EXTENSIONs. I plan to cover them eventually, but I think I ought to deal first with the remaining pre-9.1 features.
ROLEs (as well as USERs and GROUPs) and TABLESPACEs are not output by pg_dump (the equivalent of dbtoyaml), only by pg_dumpall. I’m thinking that if I were to add support for ROLEs and TABLESPACEs I’d probably do it with a --cluster option to dbtoyaml, and the output would be something like the following:
database postgres: role one: createdb: true login: true role grp: roles: - one tablespace dataspace: location: /data/db
This approach could, in theory, produce output for all databases in a cluster, i.e., the databases would be the top nodes in the YAML spec, rather than the schemas as is normally the case. In other words, it would be the equivalent of pg_dumpall --schema-only. However, I suspect that few persons would be interested in that, at least for version control purposes—since different databases may belong to different projects.
On the other hand, I believe DBAs may want dbtoyaml to include “owner” and privlege (GRANT) information. David Fetter specifically asked for GRANTs saying they would be “handy for deployments.”
Owner and privilege information could be shown as follows:
schema public: table film: owner: jma privileges: admin: - insert - update jma: - all viewer: - select
An open question is whether some list of roles is necessary, aside from the object-level information.
I’d appreciate readers taking a couple of minutes to leave feedback on any of the above points, particularly on how important they think the features are in their day-to-day work.