Technologies informatiques,
Recherche et développement

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== 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