Thread: Migration struggles

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.

Any other ideas I have not found out yet? And any other comments what
could possibly go wrong?

thanks a lot
chris

-- 
Christian Lehmann

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.

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.github@gmail.com>:
>
> On Tue, 3 Aug 2021 at 20:01, Christian Lehmann <info@chlehmann.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