Subject: Re: Migration struggles
To: pgsql-admin@lists.postgresql.org
References: 
 <CAEh-Kgt2VHahRGvBmG=EHGPQ56OCk_TG8KXB8SgLQWdU9fQCOA@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <ecba315b-aa4e-854e-2093-3d17c4b84f27@gmail.com>
Date: Tue, 3 Aug 2021 09:50:03 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: 
 <CAEh-Kgt2VHahRGvBmG=EHGPQ56OCk_TG8KXB8SgLQWdU9fQCOA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/ecba315b-aa4e-854e-2093-3d17c4b84f27%40gmail.com>
Precedence: bulk

On 8/3/21 9:31 AM, Christian Lehmann wrote:
> Hi all
> i am currently building a ansible-managed pg13-cluster. Ansible not
> only configures postgres pgbouncer etc but also creates roles and
> manages pg_hba.
>
> Now I am planning and documenting the upcoming migration. Do you have
> a good idea, how i could proceed without a lot of sed'ing or similar
> hacky things?
>
> - if i use pg_dumpall, i have the "create role"-statements which will fail
> - if i use pg_dump i have to backup all db's one by one

In bash, I'd use psql to generate a list of user databases, and then loop 
through that list.

> - pg_dumpall -a only gives me the data, -s is only the schema. This
> might be the most promising approach, backing up schema, then data and
> importing it on the new server. But still it would be more convenient
> to just run one command.
>
> Any other ideas I have not found out yet? And any other comments what
> could possibly go wrong?
>
> thanks a lot
> chris
>

-- 
Angular momentum makes the world go 'round.