Unclarities regarding the migration from MariaDB to PostgreSQL

Infos:

  • Used Zammad version: 5.4.0-1680178137.55dcec12.centos7
  • Used Zammad installation type: RPM from repository
  • Operating system: CentOS 7.9
  • Browser + version: not relevant

Questions

@MrGeneration I refer to your recommendation to migrate from MariaDB to PostgreSQL (Zammad web UI slow / detailed documentation missing: How to troubleshoot? - #20 by MrGeneration). But having a more detailed look at the documentation at Migrate to PostgreSQL server — Zammad documentation, two questions came up:

  • Is there a reason, why for CentOS 7 it is recommended to install PostgreSQL 14 and not 15 or is PG15 just newer than the documentation and it is ok to just install the newest available version?
  • the documentation instructs to install PG14, but then run postgresql13-setup initdb. Can I assume, that this is a typo or is there a specific reason to run an older setup tool?

Sorry if the questions are trivial, but I never worked with PG and I am completely unaware of possible quirks and required workaround :wink:

Any clarifications from any side? That would be extremely helpful, since we’d like to get rid of MariaDB on the Zammad server as soon as possible

Thanks for the hint.
That part actually originates from the source code installation and is a typo - should be 14. Will be fixed within the next 15 minutes.

I tested while I was fixing the command and thought to raise the commands… but…

[root@centos ~]# yum install postgresql15-server postgresql15-contrib
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.mirror.iphh.net
 * extras: centos.mirror.iphh.net
 * updates: mirror.speedkom.de
Resolving Dependencies
--> Running transaction check
---> Package postgresql15-contrib.x86_64 0:15.2-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql15-libs(x86-64) = 15.2-1PGDG.rhel7 for package: postgresql15-contrib-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql15(x86-64) = 15.2-1PGDG.rhel7 for package: postgresql15-contrib-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql15-contrib-15.2-1PGDG.rhel7.x86_64
---> Package postgresql15-server.x86_64 0:15.2-1PGDG.rhel7 will be installed
--> Processing Dependency: libzstd.so.1()(64bit) for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql15.x86_64 0:15.2-1PGDG.rhel7 will be installed
--> Processing Dependency: libzstd >= 1.4.0 for package: postgresql15-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: libzstd.so.1()(64bit) for package: postgresql15-15.2-1PGDG.rhel7.x86_64
---> Package postgresql15-libs.x86_64 0:15.2-1PGDG.rhel7 will be installed
---> Package postgresql15-server.x86_64 0:15.2-1PGDG.rhel7 will be installed
--> Processing Dependency: libzstd.so.1()(64bit) for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Finished Dependency Resolution
Error: Package: postgresql15-15.2-1PGDG.rhel7.x86_64 (pgdg15)
           Requires: libzstd >= 1.4.0
Error: Package: postgresql15-15.2-1PGDG.rhel7.x86_64 (pgdg15)
           Requires: libzstd.so.1()(64bit)
Error: Package: postgresql15-server-15.2-1PGDG.rhel7.x86_64 (pgdg15)
           Requires: libzstd.so.1()(64bit)
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

So that’s the reason, it’s not “just installable” and requires fixing. I didn’t bother with that and honestly most likely never will for CentOS 7 due to its age. Of course you can try to overcome that issue (if you have it) and choose a more current version as long as it’s supported by Zammad!

Thanks a lot @MrGeneration

Hi @MrGeneration, still some additional comments, because I wonder, if anyone at Zammad has ever fully done a database migration on CentOS 7 according to https://docs.zammad.org/en/latest/appendix/migrate-to-postgresql.html: The postgresql15 installation worked just fine on my side (in your installation some packages seem to be outdated), but some commands in the instructions have typos (it’s postgresql-14-setup initdb, not postgresql14-setup initdb, same with “15”, while up to “13” there was no dash yet, and the service name is postgresql-14, not postgresql14, again also for “15”).

But more important: pgloader doesn’t seem to be compatible with the scram-sha-256 password encryption method used since PG 13.5 (see [FEATURE] Add support for postgres scram-sha-256 authentication · Issue #1207 · dimitri/pgloader · GitHub). Anyone trying this method with a pristine PG installation will most probably fail. I finally followed these instructions to temporarily change the encryption method and now it seems to be working for me. But you might want to add a warning regarding this issue to the migration instructions.

Wow… just wow.
I generally verify everything I commit. On all supported platforms.

I might have wrongly copied it. Feel free to create a merge request on that issue.

No.
Because:

Change to MD5 and you should be good to go.

somehow agreed, but since you know now, that there are these pgloader problems it would imho be a nice gesture to issue a concrete warning or a link to the mentioned pgloader issue. It could save others with little PG experience the several hours of frustrating work.

I get that point, however, we are not the documentation for the third party tools used.
If we’d start with that it would need much more other places as well which we intentionally don’t want to document.

If you create the PR you could accidentally add such a note if it’s short enough I might not even complain.

:thumbsup: I’ll see, if I can wrap my head around rst.

1 Like

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