Re: how to adjust auto increment id offset? - Mailing list pgsql-general

On 11/15/11 12:56 AM, Bèrto ëd Sèra wrote:
> Hi
>
> On 15 November 2011 11:44, Yan Chunlu <springrider@gmail.com
> <mailto:springrider@gmail.com>> wrote:
>
>     I would like to implement two master db with even-odd id sharding.
>       in mysql it is fairly easy by using the configuration:
>
>     auto_increment_offset = 1
>     auto_increment_increment = 2
>
>
>     but I have searched a lot didn't find anything related to this,
>     some users doing this via trigger like "rubyrep".
>
>     is there an easy way to do this?  thanks!
>
>
> http://www.postgresql.org/docs/8.1/static/sql-createsequence.html
>
>

also see ALTER SEQUENCE.

basically, you'll need to fix up every sequence (these are created
automatically if you have fields of type SERIAL)....   on your 2nd server,

     ALTER SEQUENCE somesequencename INCREMENT BY 2 RESTART WITH 2;

and on your 1st server,

     ALTER SEQUENCE somesequencename INCREMENT BY 2;

do this before inserting any data.

thats a fairly unusual sharding technique, how do you plan on doing
queries across both sets of data?


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast