Postgres Pro
Downloads
Home   >   mailing lists

Re: How to restore roles without changing postgres password - Mailing list pgsql-general

From Justin
Subject Re: How to restore roles without changing postgres password
Date
Msg-id CALL-XeNbJ8w_edXjqTuPL0DJv1OdS7WU4z9hMzo71DfAnwMVWg@mail.gmail.com
Whole thread Raw
In response to How to restore roles without changing postgres password  ("Andrus" <kobruleht2@hot.ee>)
Responses Re: How to restore roles without changing postgres password  ("Andrus" <kobruleht2@hot.ee>)
List pgsql-general
Tree view
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@hot.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



pgsql-general by date:

Previous
From: "Andrus"
Date:
Subject: How to restore roles without changing postgres password
Next
From: Tom Lane
Date:
Subject: Re: How to restore roles without changing postgres password