Re: Migration struggles

Hi there
Well, migration itself won't be triggered by ansible (maybe in the
future, but then -c for sure will be used!). But I honestly did not
try --if-exists because there is no "create role abc if not exists".
But maybe it handles role-creation differently, I will try that.

Thanks so far!

Am Di., 3. Aug. 2021 um 16:51 Uhr schrieb Vijaykumar Jain
<vijaykumarjain(dot)github(at)gmail(dot)com>:
>
> On Tue, 3 Aug 2021 at 20:01, Christian Lehmann <info(at)chlehmann(dot)ch> 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
>> - 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.
>
>
> I have not done a migration using config management,
> but ansible like other config management tools should be idempotent iirc.
> you can run as many times as you want, the runs are mostly noops if the desired state is already attained imho.
>
>
> so, in that case when you run a dump all using
> -c, --clean clean (drop) databases before recreating
> --if-exists use IF EXISTS when dropping objects
>
> this would drop the objects before recreating, so restore should run fine.
> and later ansible runs, should restore anything that is not a part of db restoration just fine.
>
> but i have not done a migration using cfg mgmt tools, so just my opinion.
>
>
>
>
> pg_dumpall -c --if-exists > dump.sql
>
>
>
> --
>
> Thanks,
> Vijay
> Mumbai, India

--
Christian Lehmann
Bündackerstrasse 148
3047 Bremgarten
078 696 90 90