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

Sam_2

Hi there,

We have a leased physical box I need to get off of. It runs Vertica 6.1.1.

We will be migrating to another 1 noder - and then eventually a 3 node cluster.

The developers don't have any schemas, user ids, permission, etc backed up in files. I'm hoping to reverse engineer the system tables to grab table definitions, schema and load it on to the new box.

I figure script kind of sweeping through v_catalog.views + tables, and doing export_objects.

But I'm not sure entirely what to do with the users+grants+roles table...especially on the password side of things.

Has anybody done this kind of "dump/extraction" before? Is there an existing script out there? Is this something "vbr.py" can do in some form or another?

Any thoughts/advise would be appreciated.
Thanks

Sam_2

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!

[Deleted User]

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

Sam_2

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.

[Deleted User]

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.

Sam_2

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

[Deleted User]

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!

Sam_2

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

zxcvmnb

Hi aseering, suggested fix to use export_objects does not give sql statements to grant all privileges for given table.

Is there a way to do that ?

Adrian_Oprea_1

The export function will not bring the privs with it! , for this use Doug Harmon script to extract the privs  see article at http://datadug.com/how-to-script-out-users-roles-and-permissions-in-vertica/