====== How to upgrade nextcloud mysql to postgresql ======

===== Upgrade the server =====

This is optional of course.

<code bash>
apt update
# apt remove --purge -y postgresql*   # if you want to delete all postgresql db & versions first. YMMV!
apt upgrade -y && apt autoremove -y && apt dist-upgrade -y
do-release-upgrade  # yes, reboot when asked
</code>

Login again on your server, and:

<code>
apt autoremove -y
apt install postgresql postgresql-client postgresql-client-common
</code>

====== Using the mattermost migration script ======

You will see that it is more or less broken though :-/

<code bash>
cd /root
mkdir mm_migration
cd mm_migration
systemctl stop mattermost

wget https://github.com/mattermost/migration-assist/releases/download/v0.3/migration-assist-Linux-x86_64.tar.gz
tar xvzf migration-assist-Linux-x86_64.tar.gz && rm migration-assist-Linux-x86_64.tar.gz
</code>

Set these two according to your setup in your sessions. They will be used more than once. Eg:

<code bash>
MYSQL_DSN="matteruser:mysqlpassword@tcp(127.0.0.1:3306)/mattermost"
POSTGRES_DSN="postgres://matteruser:postgresqlpassword@127.0.0.1:5432/mattermost"
</code>

====== Create database and user ======

<code>
su - postgres
dropdb -f mattermost
psql
</code>

<code sql>
DROP DATABASE mattermost;
CREATE DATABASE mattermost;
\connect mattermost
</code>

<code sql>
CREATE USER matteruser WITH PASSWORD 'LJFHzzERLhf56';
ALTER ROLE "matteruser" WITH LOGIN;
GRANT ALL PRIVILEGES ON DATABASE mattermost to matteruser;
ALTER DATABASE mattermost OWNER TO matteruser;
GRANT USAGE, CREATE ON SCHEMA PUBLIC TO matteruser;
ALTER SCHEMA public OWNER TO matteruser;
GRANT ALL ON SCHEMA public to matteruser;
ALTER user matteruser in database mattermost set search_path to 'public';
\q
</code>

<code bash>
# Exit user postgres
[[ $USER = 'root' ]] || exit

vi /etc/postgresql/{version}/main/pg_hba.conf
# Add this line BEFORE the "local all all peer":
local   mattermost      matteruser                              md5

# restart postgresql
systemctl reload postgresql
</code>

You can test it with ''psql %%--%%dbname=mattermost %%--%%username=matteruser %%--%%password'' and:

<code sql>
\connect mattermost
-- You are now connected to database "mattermost" as user "matteruser" --
\q
</code>

Then:

  mysql -u root mattermost

<code sql>
  -- you may need this --
  -- ALTER USER 'matteruser'@'%' IDENTIFIED WITH mysql_native_password BY 'mysqlpassword'; --

  -- Fix also some data that does not fit in the pg target --
  UPDATE LinkMetadata SET URL = left(URL, 2000) WHERE length(URL) > 2000;
</code>

<code bash>
# For this error:
#    ERROR mysql: Failed to connect to mysql at "127.0.0.1" (port 3306) as user "matteruser": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
# We do this (once!)
echo -e "\n[mysqld]\ndefault-authentication-plugin=mysql_native_password\n" >> /etc/mysql/mysql.cnf

systemctl restart mysql
</code>

====== pgloader hell ======

Build pgloader from source (other ways sometimes fails):

<code bash>
apt install libssl-dev freetds-dev
cd /root/mm_migration/
mkdir pgloader_from_source
cd pgloader_from_source
wget https://github.com/dimitri/pgloader/releases/download/v3.6.9/pgloader-bundle-3.6.9.tgz
tar xvzf pgloader-bundle-3.6.9.tgz && rm pgloader-bundle-3.6.9.tgz
cd pgloader-bundle-3.6.9/
apt install sbcl make
make DYNSIZE=32000 pgloader
mv bin/pgloader /root/mm_migration/
cd /root/mm_migration/
/root/mm_migration/pgloader --version   # pgloader version "3.6.9"
rm -r pgloader_from_source
</code>

====== Do the migration ======

<code bash> 
cd ~/mm_migration
  
./migration-assist mysql "$MYSQL_DSN"
./migration-assist postgres "$POSTGRES_DSN" --run-migrations --mattermost-version="10.4.2"  # see "/opt/mattermost/bin/mattermost version"
./migration-assist pgloader --mysql="$MYSQL_DSN" --postgres="$POSTGRES_DSN" > migration.load
  
sed -i "s/_mem to '[^']*'/_mem to '12000MB'/" migration.load
sed -i "s/workers = [0-9]*/workers = 2/" migration.load
sed -i "s/prefetch rows = [0-9]*/prefetch rows = 2000/" migration.load
./pgloader migration.load | tee migration.log
</code>

Small fixes:

  # This may fail because of "tsvector" maximum size:
  #    ./migration-assist postgres post-migrate "$POSTGRES_DSN"
  # So we do it manually: use 1000000 or shorter values (like 500000)
  su - postgres
  psql
  
</code sql>
\connect mattermost
CREATE INDEX IF NOT EXISTS idx_posts_message_txt ON public.posts USING gin(to_tsvector('english', substring(message,500000)));
CREATE INDEX IF NOT EXISTS idx_fileinfo_content_txt ON public.fileinfo USING gin(to_tsvector('english', substring(content,500000)));
\q
</code>

<code bash>
[[ $USER = 'root' ]] || exit
./migration-assist postgres post-migrate "$POSTGRES_DSN"  # terminates the job

# Extra stuff to migrate:
for job in boards playbooks calls; do
  ./migration-assist pgloader $job --mysql="$MYSQL_DSN" --postgres="$POSTGRES_DSN" > $job.load
  ./pgloader $job.load | tee $job.log
done
</code>

====== Enable postgresql database in mattermost config ======

<code bash>
cp /opt/mattermost/config/config.json /opt/mattermost/config/config.json-$(date +'%Y%m%d-%H%M%S')
sed -i 's/"DriverName": "mysql"/"DriverName": "postgres"/' /opt/mattermost/config/config.json
sed -i '/"DataSource"/r"DataSource": "'$POSTGRES_DSN'"?sslmode=disable&connect_timeout=10",' /opt/mattermost/config/config.json
</code>

And start the service:

<code bash>
  cd /opt/mattermost/
  sudo -u mattermost ./bin/mattermost
</code>

If all is OK, re-enable mattermost:

<code bash>
  systemctl start mattermost
  tail -F /opt/mattermost/logs/mattermost.log
</code>

======  Cleanup ======

  rm -r /root/mm_migration/
  # drop mysql database if and only when you are sure the migration is OK