# spool this stuff to a file from your source db, then run the spooled SQL on your destination db
# recreate your groups – the ones you want
select ‘create group ‘||groname||’;’ from pg_group
where groname like ‘sale%’
or groname like ‘mfg%’;
# generate the create user statements
select ‘create user ‘||trim(usename)||’ password ‘||”’Temp123#”;’ from pg_user , pg_group where
pg_user.usesysid = ANY(pg_group.grolist) and
pg_group.groname in (‘sales_group’, ‘mfg_group”);
# now generate the alter group add user commands
select ‘alter group sales_group add user ‘|| trim(usename) || ‘;’ from pg_user , pg_group where
pg_user.usesysid = ANY(pg_group.grolist) and
pg_group.groname = ‘sales_group’;
select ‘alter group mfg_group add user ‘|| trim(usename) || ‘;’ from pg_user , pg_group where
pg_user.usesysid = ANY(pg_group.grolist) and
pg_group.groname = ‘mfg_group’;
# yes there is more to it if you need to generate the grants to the groups…
More from LonzoDB on AWS