Can i use postgresql instead of sqlite?

Community Support
1

I’m using opennebula-4.12 with sqlite in Ubuntu-14.04. I want to use postgresql instead of sqlite and i need to migrate my db to postgresql. How can i do this? Is it possible?

2

OpenNebula supports sqlite and MySQL but not postgres. You can get more information about the MySQL backend here:

http://docs.opennebula.org/4.12/administration/other_subsystems/mysql.html

Conversion from sqlite to MySQL is not straightforward. Some people use Taps to convert from one database to other but I’ve never tried it.

Recently I had to convert from sqlite to MySQL and these are the steps I did:

  • Stop OpenNebula (this is very important)
  • Make a backup of /var/lib/one/.one
  • Delete all files in /var/lib/one/.one except for one_auth
  • Configure MySQL in OpenNebula following the steps in the previous document
  • Start OpenNebula so it creates the database schema in MySQL
  • Stop OpenNebula again
  • Copy the .one backup done previously again to /var/lib/one/.one and make sure that all the files belong to oneadmin:oneadmin
  • Delete data in MySQL database. You can execute this command with the file I’m linking afterwards:
$ mysql -u root -p < delete.sql
  • Dump sqlite database:
$ sqlite3 database.db3 .dump | grep '^INSERT INTO' > database.sql
  • Convert the dump to mysql format:
$ sqlite3-to-mysql database.sql > database-mysql.sql
  • Load data into MySQL database:
$ mysql -u root -p opennebula < database-mysql.sql

Take care and make backups as I’m typing this from the top of my head. The files delete.sql and sqlite3-to-mysql are in this link:

I’ve found the converstion script in this page:

http://www.redmine.org/boards/2/topics/12793

I hope it helps

1 Like
3

Thank you @jfontan. It is so helpful.

4

What are the reasons for not supporting postgres? There was a ticket and even a patch for that: http://dev.opennebula.org/issues/332

1 Like
5

Hi, there is no specific reason for it, probably we are concentrating on
other issues, so this is not on the high priority list.

The DB access is totally abstracted and is just matter of implementing a
couple of interface methods using the postgres libraries. The SQL used to
interact with the DB is also pretty standard to address sqlite and
mysql/mariadb; so I would not expect to much problems on that side.

Cheers

1 Like
6

Hi))
i am checked )
mysql -u root -p < delete.sql
sqlite3 /var/lib/one/one.db .dump | grep ‘^INSERT INTO’ > database.sql
chmod +x sqlite3-to-mysql
./sqlite3-to-mysql database.sql > database-mysql.sql

7

Hi jfontan. Thanks much for tips. I’ve successfully migrated my Opennebula 4 from sqlite to mysql, but there was a problem, and here is a way to avoid it:
This command:
$ sqlite3 database.db3 .dump | grep ‘^INSERT INTO’ > database.sql
should look like thhis:
$ sqlite3 one.db .dump | grep ‘^INSERT INTO|ssh-rsa|]]><’ > database.sql
because the public keys also break the lines.