Migrating ownCloud from MySQL to PostgreSQL

This article describes how you can migrate you ownCloud installation from MySQL DB to PostgreSQL. My setup is as follows

  • ownCloud 9.1.1
  • PHP 7.1
  • MySQL 5.6
  • PostgreSQL 9.6
  • Ubuntu 14.04 LTS

Migrating is quite easy thanks to ownCloud builtin db:convert-type command and takes no longer than 30 minutes.

Installing PostgreSQL 9.6 on Ubuntu 14.04

To get started you first of all need PostgreSQL and you really don’t want to use the outdated stuff from Ubuntu repositories but (from point-of-time of writing the article) PostgreSQL Version 9.6 because it has awesome features such as Parallel Queries and more.

Doing so is quite easy just add the official PostgreSQL repository for Ubuntu. Import the needed key and install PostgreSQL via apt-get. Don’t forget to install php7.0-pgsql extension for your PHP version.

[bash]
sudo su
echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" >> /etc/apt/sources.list.d/postgres.list
wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
sudo apt-get update
sudo apt-get install postgresql-9.6 php7.0-pgsql
[/bash]

You can check this link for detailed information of you Linux version.

Setup PostgreSQL User & DB

You should really check PostgreSQL documentation to tweak settings tune performance but for getting started right away it is sufficient to keep the default settings.

[bash]
sudo -i -u postgres
createuser –interactive
createdb foo_owncloud

# Open PostgreSQL Client prompt
psql
GRANT ALL PRIVILEGES ON DATABASE foo_owncloud to foo_oc;
ALTER USER foo_oc WITH PASSWORD ‘!SOME-REALLY-SECURE-PASSWORD!’;
\q
exit
[/bash]

Migrating the ownCloud Database

Last but not least you need to tell ownCloud to migrate the DB by running the db:convert-type command from console script which takes some time.

[bash]
# cd to wherever your ownCloud installation is
cd /var/www/owncloud

# Run migration command
php console.php db:convert-type –all-apps pgsql foo_oc localhost foo_owncloud
[/bash]

I had some trouble with the contacts app which contained some invalid data so I just deleted the contact causing problem and then restart converting db and added contact later again. You may experience similiar issues. You can rerun the above command then with

[bash]
php console.php db:convert-type –clear-schema –all-apps pgsql foo_oc localhost foo_own
[/bash]

Leave a Reply

Your email address will not be published. Required fields are marked *