HI Tom Not a bad idea, would want to extend this to all the roles on the server not just postgres
I've edited the global dump many times removing/editing table spaces, comment old users, etc.. On Tue, Feb 11, 2020 at 5:46 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > "Andrus" <kobrule...@hot.ee> writes: > > How to create backup script which restores all roles and role > memberships > > from other server without changing postgres user password. > > [ shrug... ] Edit the command(s) you don't want out of the script. > This seems like a mighty random requirement to expect pg_dump to > support out-of-the-box. > > I wonder though if there's a case for making that easier by breaking > up the output into multiple ALTER commands. Right now you get > something like > > CREATE ROLE postgres; > ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN > REPLICATION BYPASSRLS PASSWORD 'md5128f0d64bfb424d132c3305b3057281c'; > > but perhaps we could make it print > > CREATE ROLE postgres; > ALTER ROLE postgres WITH SUPERUSER; > ALTER ROLE postgres WITH INHERIT; > ALTER ROLE postgres WITH CREATEROLE; > ALTER ROLE postgres WITH CREATEDB; > ALTER ROLE postgres WITH LOGIN; > ALTER ROLE postgres WITH REPLICATION; > ALTER ROLE postgres WITH BYPASSRLS; > ALTER ROLE postgres WITH PASSWORD 'md5128f0d64bfb424d132c3305b3057281c'; > > That would make scripted edits a bit easier, and it'd also make the > output a bit more cross-version portable, eg if you try to load the > latter into a version without BYPASSRLS, the rest of the commands > would still work. > > regards, tom lane > > >
