Best way to migrate schema, user ids, and permissions for Vertica

OK - spotted something suggesting using the vbr.py code.

Even with that said - i kind of want to keep the user ids/schema (and specific schema maybe) in synch between what will be prod and dev. Running vbr.py everything time may be an overkill.

Open to suggestions/different implementations. I kind of dont need the data as much as I need everything else (i.e schema permissions. user ids, views, etc.)

Thanks!

Hi Sam,

One thought -- have you looked at the meta-function "select export_objects(...)"?

https://my.vertica.com/docs/5.1.6/HTML/index.htm#12721.htm

This won't get you data.  (Though you can get data using COPY FROM VERTICA.)  But it will get most catalog metadata.

Adam

As an update - i wrote a simple python script, which did export_catalog for the entire db, and migrated that to the new box. What was interesting was how some of the DDLs didn't come out exactly the way expected. For example, some of the ddls we had bound a default to a column as "current_timestamp". When we extracted the ddl out using export_catalog, the "current_timestamp" became  " DEFAULT ('now()')::timestamptz"  instead of  "DEFAULT current_timestamp".

Luckily we caught it before it could wreak havoc, but that was interesting. We made a note to  use "default now()" for the future.

Transporting data with id fields were ugly and we had to customize stuff. No matter how much I hate identity fields, developers will continue to use it so I figure I'll have get comfortable with "vbr.py".


Never figured out how to successfully grant schema permissions + user ids + passwords - we recreated this from scratch with some minor hiccups based off how we saved this data into a file. Once again assume vbr.py is the way to go but would have been nice to run a SQL statement and grab this stuff like the relational DBs provide.

For what it's worth -- Vertica remembers logical SQL statements, not exact strings.  "current_timestamp" and "now()::timestamptz" are, as it turns out, logically equivalent.  So Vertica will use the two interchangeably in its export format.

Interesting. But the behavior is different - when I default to "current_timestamp", any insert defaults the column to the current time stamp of when the data was inserted. The  ('now()')::timestamptz, on the otherhand, is defaulting the column to the time the table was created. That's the behavior we saw on 6.1.3

Hm, you're right; there's an interesting typo in our translation there.  I'll forward this along to relevant people here.  Thanks for pointing it out!

Thanks. Yeah - figure I'd share it. I also think I can't be the only knucklehead who ran into this. Share people, share!