Mirgrating two databases (2 Zammads into 1)

Technical assistance
1

Infos:

  • Used Zammad version: First Zammad: 3.6.X. The second Zammad: 3.3.0
  • Used Zammad installation source: First Zammad with Package. The second Zammad with Source
  • Operating system: First Zammad on Ubuntu 20.04.1 LTS. The second Zamad on Ubuntu 18.04.4 LTS

Expected behavior:

  • First of all: I have two Zammad installations set up and in use. That means, both Zammads have both databases populated. One database is in MYSQL and the other in POSTGRESQL. I want to migrate the MYSQL database into the POSTGRESQL.
    Now the question: does this work at all? Because when both databases are in use, both databases have different increments etc…
    My goal is to turn two Zammad installations into one. BUT, importantly, the tickets must be migrated!

Actual behavior:

  • I have not tried anything yet. But I found a tutorial to migrate a MYSQL database with PGLOADER to a POSTGRESQL database. But the tutorial probably means to migrate only the single tables and NOT to migrate tables in tables.

Steps to reproduce the behavior:

  • As I said, I have two populated databases (MYSQL and POSTGRESQL) and I want to migrate the MYSQL database with complete content to the POSTGRESQL database.
2

Hi @lukcy

maybe this can be of some help:

IMPORTANT: always take a backup of your databases BEFORE attempting any changes!!

Best,
Martin

3

Okay I’ll have to jump in out of line here to ensure we have no harm here.

Migrating two Zammad instances is not possible and supported.
While it technically would possible, it would require a lot of manual work because you’ll need to remap relations.

Above mentioned merging process would probably lead to “merged” tickets or even worse inconsistent data. I strongly suggest against merging two Zammad databases into the same one.

If you’re talking about two Zammad databases on the same database server then yes, that’s possible, as long as you don’t want to have them in the exact same database. The database.yml within config/ should help you here.

Please note that you’re on your own with the migration from MySQL to PostgreSQL as the translation is not trivial and needs further know how.

4

I think it’s not exactly what I was looking for, but Thanks anyway for your help! :slight_smile:

5

Sad to hear. Maybe I’ll try to make this work in ANY way. But thanks for your answer! :slight_smile:

6

I never had such an scenario :slight_smile:
Anyway, if you manage to achieve your goal, please write a nice how-to and share it with the Zammad community.

Best,
Martin

7

I’ll try my best ^^ But I think I need to move the old Tickets manually… I don’t know exactly how to :smiley:

8

just some general note’s. I think there are 2 routes;

  1. a data route, e.g. moving the data from mysql to pgsql, as @MrGeneration mentioned you might have to fix certain relationships.
    you could check the general postgresql link here; Converting from other Databases to PostgreSQL - PostgreSQL wiki
    Additionally you could setup a mysql to postgres replication using; pg_chameleon MySQL to PostgreSQL replica — pg_chameleon v2.0.16 documentation
    Last clickhouse has in it’s latested release added the postgres client, you could use clickhouse as proxy to both databases. Connect to them as federated source’s, and select out into the other database, possible with some sql transfromations; 2021 | ClickHouse Documentation

  2. the logical route, use the zammad API to read out your data of the mysql instance, and use the zammad API to create the data in your new database.

good luck

3 Likes
9

Second option mentioned by @olafbuitelaar is actually the best approach.
Option one ignores the migration part into the same installation which leaves you to option 2.

When using console or API keep in mind that you cannot use existing IDs but may have to manually lookup new IDs etc etc. You’ve chosen a hard project here.

1 Like
10

@olafbuitelaar Thank you so much for the help, I’ll try the second option asap.

@MrGeneration I’ll collect some information and try the second option asap.

Thanks!

1 Like
11

@MrGeneration @olafbuitelaar
Here is a small update:
we are currently writing a script (PHP) to get the data from the old database, via API, and reassemble the ticket and write it into the new database.
The tests we have done so far have worked quite well.

The only thing we have to adjust in the tickets are the group IDs, which of course works relatively easy in PHP.

When we are done and have migrated all tickets I will report back if everything worked fine ^^

2 Likes
12

@MrGeneration @olafbuitelaar @rsysadmin

Hello again ^^,
our probably last problem with API migration concerns attachments.

The following situation:

Basically, attachments can be downloaded from the source system via API with the following GET:

https://HOSTNAME/api/v1/ticket_attachment/$TICKET_ID/$ARTICLE_ID/$ATTACHMENT_ID

We can get to the $TICKET_ID + $ARTICLE_ID, however we have not found a way to read the $ATTACHMENT_ID from the source system API.
There is no way to get a JSON with the $ATTACHMENT_ID`s belonging to the article, also a search query (e.g. https://HOSTNAME/api/v1/ticket_attachment/search?ticket_article_id=$TICKET_ARTICLE_ID) seems not to be possible.

For the target system it doesn’t matter, here we can simply put the downloaded file into the article and POST it to the target system.

How do we get all $ATTACHMENT_ID’s per $ARTICLE_ID for the source system? How does the front-end match $ATTACHMENT_ID’s and $ARTICLE_ID? When hovering over an attachment the front-end automatically generates the correct link.

Kind regards
Florian

13

I’m confused.
Why aren’t you just fetching your articles by ticket?
https://docs.zammad.org/en/latest/api/ticket-article.html#by-ticket

You’ll retrieve an array with all articles belonging to the ticket in question.
This payload doesn’t just contain the article ids, but also an array with attachment-meta information including the attachment id:

...
        "attachments": [
            {
                "id": 60,
                "filename": "caller-log-dashboard-filled.png",
                "size": "521449",
                "preferences": {
                    "Content-Type": "image/png",
                    "resizable": true,
                    "content_preview": true,
                    "content_inline": true
                }
            }
        ],
...

I believe that’s exactly what you’re looking for…?

14

This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.