Used mysqldump for data only; Added 'set backslash_quote = on;' and 'set standard_conforming_strings = off;' in the header of the dump file. After that you need to create the PostgreSQL schema from OTRS scripts, import your data, run OTRS post database script and adjust sequences (you can generate the queries for that with py-mysql2pgsql and some scripting). Solved the article_attachment with otrs.ArticleStorageSwitch.pl. Dropped files to filesystem, moved files to the new server and imported to database again. Thanks. On Thu, Mar 28, 2013 at 12:18 PM, Bogdan Iosif wrote:

Does OTRS have a tool that you used to move the data?

On Thu, Mar 28, 2013 at 5:12 PM, aledr wrote:

...

Good day!

I'm moving my otrs installation from MySQL to PostgreSQL. Almost everything went fine, except article_attachment table.

Is there any available instructions on how to move that data?

Thanks. -- [ ]'s Aledr - Alexandre --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs -- [ ]'s Aledr - Alexandre

It seems faster, but we've made no performance tests at all. We moved fo the sake of simplicity (keeping just one RDBMS to maintain). On Thu, Mar 28, 2013 at 2:12 PM, Bogdan Iosif wrote:

An interesting and ingenious method. I was thinking you're using some ETL tool.

I'm strongly considering doing the same switch as you did but I'm wary of performance issues. Initially, I tried running OTRS on top of SQL Server but Perl's database interface implementation for this RDBMS is very weak (5x slower) compared to MySQL.

Did you run tests that showed PostgreSQL would be better performance-wise or, if not, why did you move?

On Thu, Mar 28, 2013 at 6:55 PM, aledr wrote:

...

Used mysqldump for data only; Added 'set backslash_quote = on;' and 'set standard_conforming_strings = off;' in the header of the dump file. After that you need to create the PostgreSQL schema from OTRS scripts, import your data, run OTRS post database script and adjust sequences (you can generate the queries for that with py-mysql2pgsql and some scripting).

Solved the article_attachment with otrs.ArticleStorageSwitch.pl. Dropped files to filesystem, moved files to the new server and imported to database again.

Thanks.

On Thu, Mar 28, 2013 at 12:18 PM, Bogdan Iosif wrote:

...

Does OTRS have a tool that you used to move the data?

On Thu, Mar 28, 2013 at 5:12 PM, aledr wrote:

...

Good day!

I'm moving my otrs installation from MySQL to PostgreSQL. Almost everything went fine, except article_attachment table.

Is there any available instructions on how to move that data?

Thanks. -- [ ]'s Aledr - Alexandre --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs -- [ ]'s Aledr - Alexandre

---

OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs -- [ ]'s Aledr - Alexandre

I'll see your T&Cs and raise you window functions On Thu, Mar 28, 2013 at 9:22 PM, David Boyes wrote:

** **

** **

Did you run tests that showed PostgreSQL would be better performance-wise or, if not, why did you move?****

** **

Oracle’s new licensing T&Cs for use of MySQL for business tasks in one big reason. The lawyers here think the new language implies that using MySQL with other applications that have a business purpose now requires a MySQL commercial license, which is $$$$. ****

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

http://en.wikipedia.org/wiki/Window_functions Very useful for some queries used mostly with reports (e.g. when you need that Nth occurence in ticket_history) On Thu, Mar 28, 2013 at 9:46 PM, David Boyes wrote:

** **

** **

I'll see your T&Cs and raise you window functions****

** **

?? window functions? Not sure I follow. ****

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

Neat. Hadn’t considered using something like that for reporting. From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Bogdan Iosif Sent: Thursday, March 28, 2013 3:51 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Database Conversion from MySQL to PostgreSQL http://en.wikipedia.org/wiki/Window_functions Very useful for some queries used mostly with reports (e.g. when you need that Nth occurence in ticket_history) On Thu, Mar 28, 2013 at 9:46 PM, David Boyes mailto:dboyes@sinenomine.net> wrote: I'll see your T&Cs and raise you window functions ?? window functions? Not sure I follow. --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

MariaDB very recently entered my radar, mostly because the project is driven by MySQL's inventor. Don't know much about it beyond that. Have you seen it used in a production environment? Is it at the same performance / reliability level as MySQL? On Thu, Mar 28, 2013 at 10:11 PM, Gerald Young wrote:

May I point out MariaDB https://mariadb.org/ while we're talking about MySQL? Just in case you want a different MySQL that isn't encumbered by Oracle, but is a drop-in replacement for MySQL.

On Thu, Mar 28, 2013 at 4:02 PM, David Boyes wrote:

...

Neat. Hadn’t considered using something like that for reporting. ****

** **

** **

*From:* otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] *On Behalf Of *Bogdan Iosif *Sent:* Thursday, March 28, 2013 3:51 PM *To:* User questions and discussions about OTRS. *Subject:* Re: [otrs] Database Conversion from MySQL to PostgreSQL****

** **

http://en.wikipedia.org/wiki/Window_functions****

Very useful for some queries used mostly with reports (e.g. when you need that Nth occurence in ticket_history)****

** **

On Thu, Mar 28, 2013 at 9:46 PM, David Boyes wrote:****

****

****

I'll see your T&Cs and raise you window functions****

****

?? window functions? Not sure I follow. ****

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs****

** **

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

Another good option. I haven't been all that impressed with it yet, but it's improving rapidly as they clean-room reimplement some of the stuff Oracle did on the performance front. It'd just be good for Oracle to get what's coming to them. From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Gerald Young Sent: Thursday, March 28, 2013 4:11 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Database Conversion from MySQL to PostgreSQL May I point out MariaDBhttps://mariadb.org/ while we're talking about MySQL? Just in case you want a different MySQL that isn't encumbered by Oracle, but is a drop-in replacement for MySQL.