Re: How to restore roles without changing postgres password

From: Justin <zzzzz(dot)graf(at)gmail(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>, "pgsql general (pgsql-general(at)postgresql(dot)org)" <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to restore roles without changing postgres password
Date: 2020-02-11 22:37:55
Message-ID: CALL-XeNbJ8w_edXjqTuPL0DJv1OdS7WU4z9hMzo71DfAnwMVWg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs pgsql-general

pg_dumpall creates an SQL file which is just a simple text file

you can then edit sql removing postgres user from the file

This can be automated in a script that searches the generated sql file for
the postgres user replacing it with a blank/empty line or adds -- to the
bringing of the line which comments it out.

On Tue, Feb 11, 2020 at 5:27 PM Andrus <kobruleht2(at)hot(dot)ee> wrote:

> Hi!
>
> How to create backup script which restores all roles and role memberships
> from other server without changing postgres user password.
>
> I tried shell script
>
> PGHOST=example.com
> PGUSER=postgres
> PGPASSWORD=mypass
> export PGHOST PGPASSWORD PGUSER
> pg_dumpall --roles-only --file=globals.sql
> psql -f globals.sql postgres
>
> but this changes user postgres password also.
> How to restore roles so that postgres user password is not changed on
> restore.
>
> Script runs on Debian 10 with Postgres 12
> Server from where it reads users runs on Debian Squeeze with Postgres 9.1
>
> Andrus
>
>
>
>

In response to

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Tom Lane 2020-02-11 22:46:20 Re: How to restore roles without changing postgres password
Previous Message Andrus 2020-02-11 22:26:50 How to restore roles without changing postgres password

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2020-02-11 22:46:20 Re: How to restore roles without changing postgres password
Previous Message Andrus 2020-02-11 22:26:50 How to restore roles without changing postgres password