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.
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
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.
sudo -i -u postgres
# Open PostgreSQL Client prompt
GRANT ALL PRIVILEGES ON DATABASE foo_owncloud to foo_oc;
ALTER USER foo_oc WITH PASSWORD ‘!SOME-REALLY-SECURE-PASSWORD!’;
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.
# cd to wherever your ownCloud installation is
# Run migration command
php console.php db:convert-type –all-apps pgsql foo_oc localhost foo_owncloud
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
php console.php db:convert-type –clear-schema –all-apps pgsql foo_oc localhost foo_own