Re: Problems migrating ReviewBoard from MySQL to PostgreSQL

[Christian Hammond]

Hi David,

How did you attempt the migration? Converting raw SQL, or using the dumpdb/loaddb management commands?

Migrating from MySQL to PostgreSQL is not something I've ever tried. I know some people have had success going from sqlite to MySQL using dumpdb/loaddb.

Christian

-- 
Christian Hammond - chi...@chipx86.com
Review Board - http://www.reviewboard.org

Beanbag, Inc. - http://www.beanbaginc.com

On Apr 24, 2013, at 11:54 AM, David Lee <dl...@digium.com> wrote:

We have an existing ReviewBoard setup that we're trying to migrate from an old MySQL infrastructure to a shiny new PostgreSQL setup.

We've gotten it to the point where the migration seems to work fine for reading the data out of the PostgreSQL database, and for some simple changes. But when we try to create new content, we get a 500 error from reviewboard. It looks like the sequences for generating primary keys aren't being updated when we run dbimport, which is causing unique constraint violations.

I can't find a related issue in the issue tracker, or much discussion about this on the Internet at large.

1. Does anyone have patch for fixing this?

2. How about a script for fixing up the sequences in PostgreSQL?

3. Anyone else have experiences migrating ReviewBoard to PostgreSQL that might help us out?

Here are the details, if it helps:

We're getting errors in the logs that look like:

2013-04-24 18:32:51,325 - ERROR - Exception thrown for user dlee at http://rbtest.digium.internal/r/new/

duplicate key value violates unique constraint "diffviewer_diffsethistory_pkey"

DETAIL:  Key (id)=(7) already exists.

It looks like what's happening is that the data in the database is being migrated successfully, but the sequences for generating primary keys are left unchanged.

reviewboard=# select max(id) from diffviewer_diffsethistory;

 max  

------

 2466

(1 row)

reviewboard=# \d diffviewer_diffsethistory_id_seq 

     Sequence "public.diffviewer_diffsethistory_id_seq"

    Column     |  Type   |              Value               

---------------+---------+----------------------------------

 sequence_name | name    | diffviewer_diffsethistory_id_seq

 last_value    | bigint  | 7

 start_value   | bigint  | 1

 increment_by  | bigint  | 1

 max_value     | bigint  | 9223372036854775807

 min_value     | bigint  | 1

 cache_value   | bigint  | 1

 log_cnt       | bigint  | 29

 is_cycled     | boolean | f

 is_called     | boolean | t

Thanks!

dave

<><

--
Want to help the Review Board project? Donate today at http://www.reviewboard.org/donate/
Happy user? Let us know at http://www.reviewboard.org/users/
-~----------~----~----~----~------~----~------~--~---
To unsubscribe from this group, send email to reviewboard...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/reviewboard?hl=en
---
You received this message because you are subscribed to the Google Groups "reviewboard" group.
To unsubscribe from this group and stop receiving emails from it, send an email to reviewboard...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[David Lee]

On Wednesday, April 24, 2013 3:05:05 PM UTC-5, Christian Hammond wrote:

Hi David,

How did you attempt the migration? Converting raw SQL, or using the dumpdb/loaddb management commands?

dumpdb/loaddb.

Here's the process we're using:

root@old-machine# rb-site manage /var/www/reviewboard dumpdb > dumpdb.json

root@old-machine# scp dumpdb.json new-machine:

root@new-machine# rb-site install /var/www/reviewboard

root@new-machine# rb-site manage /var/www/reviewboard syncdb

root@new-machine# rb-site manage /var/www/reviewboard dbshell

> TRUNCATE django_content_type CASCADE

> TRUNCATE scmtools_tool CASCADE

root@new-machine# rb-site manage /var/www/reviewboard loaddb dumpdb.json

[David Lee]

I've put together a quick script to fixup sequences in PostgreSQL. It seems to work fine.

psql -t ${DB} -c "SELECT E'select setval(\'' || c.relname || E'\',  (select max(id)+1 from ' || replace(c.relname, '_id_seq', '') || '), false);' FROM pg_class c WHERE c.relkind = 'S';" | psql ${DB}

dave