[3.x]: craft db/restore seems to keep certain tables or table contents in its previous state or mixes them (Postgres)

[hiasl]

What happened?

Description

We are using the following workflow:

• System A: craft db/backup
• Transfer dump to System B
• craft db/restore on System B

in 2 different setups:

• manually (between local DDEV instances / multiple developers)
• automatically (triggered by Jenkins in always the same way between staging zones at our hoster)

Important:

• We are using Postgres.
• We are using a schema named craftcms instead of public due to some hosting restrictions, which requires us to change the default Postgres search_path to craftcms by executing ALTER DATABASE db SET search_path = craftcms; before being able to use the database with DDEV for the first time (because the DDEV's user db would use the search_path db and public by default).

Further info about this Postgres' schema topic can be found here: https://craftcms.stackexchange.com/questions/38405/why-do-postgres-schemas-different-from-public-sometimes-not-work-with-craft

Steps to reproduce

see above

Expected behavior

System B should be exactly the same as System A

Actual behavior

The problem appears on System B:

• Entries are mixed up (e.g. a single section contained the same entry multiple times with different IDs)
• Tables still contain rows from before craft db/restore (e.g. the sites table), which are actually not included in the SQL dump.
• We experienced this both with DDEV locally and in our hosting environment

2 more things

• When we delete the DDEV instance, recreate it and import the same dump again, everything is fine.
• I know that this issue is special, that there could be many different reasons, even repo-external ones, and I do not expect an (immediate) fix for this, but I wanted to report it especially if others have the same problem and maybe can contribute something to it getting it fixed

Craft CMS version

Craft Pro 3.7.38

PHP version

7.4.19

Operating system and version

RHEL: Linux 4.18.0-372.16.1.el8_6.x86_64

Database type and version

PostgreSQL 12.10

Image driver and version

No response

Installed plugins and versions

[brandonkelly]

Have you set a custom restoreCommand config setting value?

[hiasl]

No, should be default. I checked my config and my module.

[brandonkelly]

Did the restore get pulled into the wrong schema?

[hiasl]

I will have this checked.

[hiasl]

According to the database team all tables are in the correct schema (the same the dump should go into)

I would now like to compare the database when we manually import it and when we do it through the craft db/restore.

Is there a place where I can easily get the exact postgres command that is executed by the craft db/restore command or do I need to dump it from the source code? I am asking because I was looking into the code and I did not find it and it also does not appear in the logs...

[angrybrad]

@hiasl you can see the logic for the default postgres backup command Craft uses here: https://github.com/craftcms/cms/blob/develop/src/db/pgsql/Schema.php#L117-L160

[hiasl]

OK, we found the problem but do not have a solution yet.

• System A (Source) is a QS System, System B (Target) is DEV
• System A (Source) is on an older branch than System B (Target)
• System B (Target) already has additional project-config applied which only exists in the newer/DEV branch and which causes the creation of certain matrix fields and therefore tables.
• When we use the dump from System A it does not contain the DROP statements needed for those newer tables and constraints. But those tables have FKs to the sites table, which causes an error and also causes the sites tables not to be dropped and get reimported correctly.
• This is the error we get (sorry I did not post this earlier, it was somehow swallowed by our Jenkins):

ALTER TABLE IF EXISTS ONLY craftcms.sites DROP CONSTRAINT IF EXISTS sites_pkey;

psql:2022-08-01_0914-V3.7.38-S3.7.33_db_image:373: ERROR:  cannot drop constraint sites_pkey on table craftcms.sites because other objects depend on it

DETAIL:  constraint fk_odkequwuigzfcklzvpeelprkbwnerbmaeaue on table craftcms.matrixcontent_location depends on index craftcms.sites_pkey

constraint fk_fwpnqbhzwjchclgpnterjeqmfgswtumvfnnm on table craftcms.matrixcontent_tagsnew depends on index craftcms.sites_pkey

HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Maybe the pgdump command could use DROP ... CASCADE as suggested above in the error. But even if it worked, I do not think it was a good idea, because e.g. matrix tables would still not get deleted and would stay in the target database and therefore it would not be clean (means it contained additional tables not needed yet which might also cause problems when project config of newer branches get reapplied).

Our problem: We are in managed database environment, where we can not drop and recreate the database first. In this context, when transferring from one system to the other, the craft db/backup and craft db/restore commands are not helpful to us (and I guess others).

Idea/FR: Could you give the db/restore command and option to first clear the target database? (maybe even default).

[angrybrad]

Even better, you can completely override the default backup/restore commands for your specific needs and the craft db/backup and craft db/restore commands will respect them via:

https://craftcms.com/docs/4.x/config/config-settings.html#backupcommand

https://craftcms.com/docs/4.x/config/config-settings.html#restorecommand

[hiasl]

@angrybrad Thanks for the docs, but I do not think I can solve this via modifying the commands, as there is no "clean complete database" option for the MySQL/Postgres' command line tools. Postgres has the -clean option, but this does not affect tables which are not in the dump. And I have no idea how to modify the dump itself (backup command) to dump tables which the source system does not know of.

I think the question is, if you/P&T want a Craft user to get an exact clone of the source db when using the craft db/restore command. In my situation it does not achieve this because there are tables in the target database which are not removed when using restore.

It's totally ok if you believe this is an edge case and something I should take care of myself (e.g. via EVENT_BEFORE_RESTORE_BACKUP).

Let me know what you think but maybe a craft db/empty command or an addition to the restore command would still be a good idea since it's a rather generic problem. If you agree, I can either file an FR or provide a PR if I find the time.

[angrybrad]

Let me know what you think but maybe a craft db/empty command or an addition to the restore command would still be a good idea since it's a rather generic problem.

The complication is that there could be other non-Craft-related tables and schema objects in the same database that Craft is installed in. We've seen people with Wordpress database tables installed on the same database as Craft is.

So you can't blindly nuke everything in the database without the potential for adversely affecting external systems.

[hiasl]

OK thanks, I understand that.

I posted something on Stackexchange in case other people have the same problem and find this issue here. Here is our solution: https://craftcms.stackexchange.com/questions/39812/crafts-db-restore-fails-to-import-a-dump/39813

[angrybrad]

Thanks for sharing!