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@xxxxxxxxx>: > > On Tue, 3 Aug 2021 at 20:01, Christian Lehmann <info@xxxxxxxxxxxx> 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
