Re: horizontal sharding

Hey guys
what is a good way to horizontal shard in postgresql
1. pgpool 2
2. gridsql

which is a better way to use sharding

also is it possible to paritition without changing client code
thanks

--
Bidegg worlds best auction site
http://bidegg.com

On Mon, Jun 15, 2009 at 12:32:15AM -0700, mobiledreamers(at)gmail(dot)com wrote:
> Hey guys
> what is a good way to horizontal shard in postgresql

"Sharding" is not really a technical term, so it's not really possible
to answer this question meaningfully as posed.

What is it that you actually want to accomplish here exactly? What
are your goals, and what are your priorities on those goals? What
will you trade off to achieve them?

> 1. pgpool 2
> 2. gridsql
>
> which is a better way to use sharding
>
> also is it possible to paritition without changing client code

Almost certainly not. This would be one of the trade-offs mentioned
above.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

what is a good way to horizontal shard in postgresql

> 1. pgpool 2
> 2. gridsql
>
> which is a better way to use sharding
>

Both are good methods of sharding, but it depends on your goals. GridSQL is
better in reporting applications where as PG Pool2 is better in
transactional situations.

>
> also is it possible to paritition without changing client code

Yes, but it depends on the SQL in your client code. If you are just using
simple SQL with no stored functions, you should be able to slip in either
solution without changing the client code.

--
Jim Mlodgenski
EnterpriseDB (http://www.enterprisedb.com)

Jim Mlodgenski wrote:
>
>
> also is it possible to paritition without changing client code
>
> Yes, but it depends on the SQL in your client code. If you are just
> using simple SQL with no stored functions, you should be able to slip
> in either solution without changing the client code.
>

what about queries that need to do joins or aggregate reporting across
the partitions?!? I can't see how that could be done transparently
short of something like Oracle RAC.

what about queries that need to do joins or aggregate reporting across the
partitions?!? I can't see how that could be done transparently short of
something like Oracle RAC.
GridSQL actually does a nice job of breaking up the query and optimizing it
appropriately to handle cross node joins. There are agents running on each
of the nodes that handles the inter-node communication allowing it to be
transparent to the calling application.

--
Jim Mlodgenski
EnterpriseDB (http://www.enterprisedb.com)

On Mon, Jun 15, 2009 at 11:36 AM, John R Pierce<pierce(at)hogranch(dot)com> wrote:
> Jim Mlodgenski wrote:
>>
>>
>>    also is it possible to paritition without changing client code
>>
>> Yes, but it depends on the SQL in your client code. If you are just using
>> simple SQL with no stored functions, you should be able to slip in either
>> solution without changing the client code.
>
> what about queries that need to do joins or aggregate reporting across the
> partitions?!?  I can't see how that could be done transparently short of
> something like Oracle RAC.

If you use inheritance and therefore put the child tables on the same
server, it's invisible to the client that the table is partitioned.
Make sure you've got constraint_exclusion turned on.

Jim thanks

Can you please show how to setup partitining in gridsql and pgpool 2

thanks a lot

On Mon, Jun 15, 2009 at 10:28 AM, Jim Mlodgenski <jimmy76(at)gmail(dot)com> wrote:

> what is a good way to horizontal shard in postgresql
>
>> 1. pgpool 2
>> 2. gridsql
>>
>> which is a better way to use sharding
>>
>
> Both are good methods of sharding, but it depends on your goals. GridSQL is
> better in reporting applications where as PG Pool2 is better in
> transactional situations.
>
>
>>
>> also is it possible to paritition without changing client code
>
> Yes, but it depends on the SQL in your client code. If you are just using
> simple SQL with no stored functions, you should be able to slip in either
> solution without changing the client code.
>
> --
> Jim Mlodgenski
> EnterpriseDB (http://www.enterprisedb.com)
>
>

--
Bidegg worlds best auction site
http://bidegg.com

GOals are to scale our game for 10000 simultaneous connection to db
right now db crashing at 500 or 1000 sim connections

On Mon, Jun 15, 2009 at 8:07 AM, David Fetter <david(at)fetter(dot)org> wrote:

> On Mon, Jun 15, 2009 at 12:32:15AM -0700, mobiledreamers(at)gmail(dot)com wrote:
> > Hey guys
> > what is a good way to horizontal shard in postgresql
>
> "Sharding" is not really a technical term, so it's not really possible
> to answer this question meaningfully as posed.
>
> What is it that you actually want to accomplish here exactly? What
> are your goals, and what are your priorities on those goals? What
> will you trade off to achieve them?
>
> > 1. pgpool 2
> > 2. gridsql
> >
> > which is a better way to use sharding
> >
> > also is it possible to paritition without changing client code
>
> Almost certainly not. This would be one of the trade-offs mentioned
> above.
>
> Cheers,
> David.
> --
> David Fetter <david(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>

--
Bidegg worlds best auction site
http://bidegg.com

On Wed, Jun 24, 2009 at 11:03:07PM -0700, mobiledreamers(at)gmail(dot)com wrote:
> GOals are to scale our game for 10000 simultaneous connection to db
> right now db crashing at 500 or 1000 sim connections

Consider using pgbouncer to multiplex your database connections.

If that doesn't work, consider hiring one of the PostgreSQL consulting
outfits like Command Prompt, Endpoint, OmniTI, or the one I work for,
PostgreSQL Experts <http://www.pgexperts.com/>

Cheers,
David.
> On Mon, Jun 15, 2009 at 8:07 AM, David Fetter <david(at)fetter(dot)org>
> wrote:
>
> > On Mon, Jun 15, 2009 at 12:32:15AM -0700, mobiledreamers(at)gmail(dot)com
> > wrote:
> > > Hey guys what is a good way to horizontal shard in postgresql
> >
> > "Sharding" is not really a technical term, so it's not really
> > possible to answer this question meaningfully as posed.
> >
> > What is it that you actually want to accomplish here exactly?
> > What are your goals, and what are your priorities on those goals?
> > What will you trade off to achieve them?
> >
> > > 1. pgpool 2 2. gridsql
> > >
> > > which is a better way to use sharding
> > >
> > > also is it possible to paritition without changing client code
> >
> > Almost certainly not. This would be one of the trade-offs
> > mentioned above.

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Thu, Jun 25, 2009 at 12:03 AM, <mobiledreamers(at)gmail(dot)com> wrote:
> GOals are to scale our game for 10000 simultaneous connection to db
> right now db crashing at 500 or 1000 sim connections

Definitely look into pooling. I've got a pretty hefty database server
(16 15k5 drives, HW RAID, 32Gig RAM and 8 cores) and I wouldn't DREAM
of trying to handle 10,000 simo connections with it.

can someone show how to setup pgpool 2 in parallel replication modethanks a
lot

On Thu, Jun 25, 2009 at 10:28 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Thu, Jun 25, 2009 at 12:03 AM, <mobiledreamers(at)gmail(dot)com> wrote:
> > GOals are to scale our game for 10000 simultaneous connection to db
> > right now db crashing at 500 or 1000 sim connections
>
> Definitely look into pooling. I've got a pretty hefty database server
> (16 15k5 drives, HW RAID, 32Gig RAM and 8 cores) and I wouldn't DREAM
> of trying to handle 10,000 simo connections with it.
>

--
Bidegg worlds best auction site
http://bidegg.com

can someone show how to setup pgpool 2 in parallel *query* mode ie
horizontal partitioningthanks a lot

On Fri, Jun 26, 2009 at 5:35 PM, <mobiledreamers(at)gmail(dot)com> wrote:

> can someone show how to setup pgpool 2 in parallel replication modethanks
> a lot
>
>
> On Thu, Jun 25, 2009 at 10:28 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:
>
>> On Thu, Jun 25, 2009 at 12:03 AM, <mobiledreamers(at)gmail(dot)com> wrote:
>> > GOals are to scale our game for 10000 simultaneous connection to db
>> > right now db crashing at 500 or 1000 sim connections
>>
>> Definitely look into pooling. I've got a pretty hefty database server
>> (16 15k5 drives, HW RAID, 32Gig RAM and 8 cores) and I wouldn't DREAM
>> of trying to handle 10,000 simo connections with it.
>>
>
>
>
> --
> Bidegg worlds best auction site
> http://bidegg.com
>

--
Bidegg worlds best auction site
http://bidegg.com

mobiledreamers(at)gmail(dot)com wrote:
> can someone show how to setup pgpool 2 in parallel *query* mode ie
> horizontal partitioning

did you look at
http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#parallel
?

I'd pay close attention to the restrictions here, too...
http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction

Hey johnthanks

i did read both extensively, but finishing all the steps parallel mode or
horizontal partitioning mode doesnot work!

but this is my conf file

# backend_hostname, backend_port, backend_weight
# here are examples
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/mnt/work/database'

backend_hostname1 = 'marc.somehost.com'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/mnt/work/database'

*this is the problem*
* pgpool-II-2.2.2]$ createdb -p 9999*
*createdb: could not connect to database postgres: server closed the
connection unexpectedly*
* This probably means the server terminated abnormally*
* before or while processing the request.*
------------------------------------------------------------------------------------------------------------------------------------
Error in log
*tail -f /tmp/pgpool.log
2009-06-26 17:24:07 LOG: pid 5561: pgpool successfully started
2009-06-26 18:48:34 ERROR: pid 5563: pool_read_int: data does not match
between between master(0) slot[1] (50331648)
2009-06-26 18:48:34 ERROR: pid 5563: pool_do_auth: read auth kind failed*
*
*
*tail -f /tmp/pgpool.log
2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: backend key data received
2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: transaction state: I
2009-06-26 18:49:40 DEBUG: pid 23466: I am 23466 accept fd 7
2009-06-26 18:49:40 DEBUG: pid 23466: Protocol Major: 3 Minor: 0 database:
postgres user: mark
2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 0 backend
2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 1 backend
2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 0
length: 8
2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 1
length: 8
2009-06-26 18:49:40 ERROR: pid 23466: pool_read_int: data does not match
between between master(0) slot[1] (50331648)
2009-06-26 18:49:40 ERROR: pid 23466: pool_do_auth: read auth kind failed

*
*
*On Fri, Jun 26, 2009 at 5:56 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> mobiledreamers(at)gmail(dot)com wrote:
>
>> can someone show how to setup pgpool 2 in parallel *query* mode ie
>> horizontal partitioning
>>
>
> did you look at
> http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#parallel?
>
> I'd pay close attention to the restrictions here, too...
> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction
>
>
>

--
Bidegg worlds best auction site
http://bidegg.com

I thought pgool II was abandonware...

On Fri, Jun 26, 2009 at 7:50 PM, <mobiledreamers(at)gmail(dot)com> wrote:
> Hey john
> thanks
> i did read both extensively, but finishing all the steps parallel mode or
> horizontal partitioning mode doesnot work!
> but this is my conf file
>
> # backend_hostname, backend_port, backend_weight
> # here are examples
> backend_hostname0 = 'localhost'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/mnt/work/database'
> backend_hostname1 = 'marc.somehost.com'
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = '/mnt/work/database'
> this is the problem
>  pgpool-II-2.2.2]$ createdb -p 9999
> createdb: could not connect to database postgres: server closed the
> connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> ------------------------------------------------------------------------------------------------------------------------------------
> Error in log
> tail -f /tmp/pgpool.log
> 2009-06-26 17:24:07 LOG: pid 5561: pgpool successfully started
> 2009-06-26 18:48:34 ERROR: pid 5563: pool_read_int: data does not match
> between between master(0) slot[1] (50331648)
> 2009-06-26 18:48:34 ERROR: pid 5563: pool_do_auth: read auth kind failed
> tail -f /tmp/pgpool.log
> 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: backend key data received
> 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: transaction state: I
> 2009-06-26 18:49:40 DEBUG: pid 23466: I am 23466 accept fd 7
> 2009-06-26 18:49:40 DEBUG: pid 23466: Protocol Major: 3 Minor: 0 database:
> postgres user: mark
> 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 0 backend
> 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 1 backend
> 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 0
> length: 8
> 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 1
> length: 8
> 2009-06-26 18:49:40 ERROR: pid 23466: pool_read_int: data does not match
> between between master(0) slot[1] (50331648)
> 2009-06-26 18:49:40 ERROR: pid 23466: pool_do_auth: read auth kind failed
>
> On Fri, Jun 26, 2009 at 5:56 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
>>
>> mobiledreamers(at)gmail(dot)com wrote:
>>>
>>> can someone show how to setup pgpool 2 in parallel *query* mode ie
>>> horizontal partitioning
>>
>> did you look at
>> http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#parallel
>> ?
>>
>> I'd pay close attention to the restrictions here, too...
>> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction
>>
>>
>
>
>
> --
> Bidegg worlds best auction site
> http://bidegg.com
>

--
When fascism comes to America, it will be intolerance sold as diversity.

On Mon, Jun 15, 2009 at 11:36 AM, John R Pierce<pierce(at)hogranch(dot)com> wrote:
> Jim Mlodgenski wrote:
>>
>>
>>    also is it possible to paritition without changing client code
>>
>> Yes, but it depends on the SQL in your client code. If you are just using
>> simple SQL with no stored functions, you should be able to slip in either
>> solution without changing the client code.
>
> what about queries that need to do joins or aggregate reporting across the
> partitions?!?  I can't see how that could be done transparently short of
> something like Oracle RAC.

You could make something reasonably fast and partitioned with pl/proxy
and views.

no it is very good and well designed the whole app code doesnt need to
change

we jus need to configure the pgpool insertion logic

and it has a cool pgpooladmin in php like phppgadmin

if only i can get it to work

On Fri, Jun 26, 2009 at 10:53 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> I thought pgool II was abandonware...
>
> On Fri, Jun 26, 2009 at 7:50 PM, <mobiledreamers(at)gmail(dot)com> wrote:
> > Hey john
> > thanks
> > i did read both extensively, but finishing all the steps parallel mode or
> > horizontal partitioning mode doesnot work!
> > but this is my conf file
> >
> > # backend_hostname, backend_port, backend_weight
> > # here are examples
> > backend_hostname0 = 'localhost'
> > backend_port0 = 5432
> > backend_weight0 = 1
> > backend_data_directory0 = '/mnt/work/database'
> > backend_hostname1 = 'marc.somehost.com'
> > backend_port1 = 5432
> > backend_weight1 = 1
> > backend_data_directory1 = '/mnt/work/database'
> > this is the problem
> > pgpool-II-2.2.2]$ createdb -p 9999
> > createdb: could not connect to database postgres: server closed the
> > connection unexpectedly
> > This probably means the server terminated abnormally
> > before or while processing the request.
> >
> ------------------------------------------------------------------------------------------------------------------------------------
> > Error in log
> > tail -f /tmp/pgpool.log
> > 2009-06-26 17:24:07 LOG: pid 5561: pgpool successfully started
> > 2009-06-26 18:48:34 ERROR: pid 5563: pool_read_int: data does not match
> > between between master(0) slot[1] (50331648)
> > 2009-06-26 18:48:34 ERROR: pid 5563: pool_do_auth: read auth kind failed
> > tail -f /tmp/pgpool.log
> > 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: backend key data
> received
> > 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: transaction state: I
> > 2009-06-26 18:49:40 DEBUG: pid 23466: I am 23466 accept fd 7
> > 2009-06-26 18:49:40 DEBUG: pid 23466: Protocol Major: 3 Minor: 0
> database:
> > postgres user: mark
> > 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 0
> backend
> > 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 1
> backend
> > 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 0
> > length: 8
> > 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 1
> > length: 8
> > 2009-06-26 18:49:40 ERROR: pid 23466: pool_read_int: data does not match
> > between between master(0) slot[1] (50331648)
> > 2009-06-26 18:49:40 ERROR: pid 23466: pool_do_auth: read auth kind failed
> >
> > On Fri, Jun 26, 2009 at 5:56 PM, John R Pierce <pierce(at)hogranch(dot)com>
> wrote:
> >>
> >> mobiledreamers(at)gmail(dot)com wrote:
> >>>
> >>> can someone show how to setup pgpool 2 in parallel *query* mode ie
> >>> horizontal partitioning
> >>
> >> did you look at
> >>
> http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#parallel
> >> ?
> >>
> >> I'd pay close attention to the restrictions here, too...
> >>
> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction
> >>
> >>
> >
> >
> >
> > --
> > Bidegg worlds best auction site
> > http://bidegg.com
> >
>
>
>
> --
> When fascism comes to America, it will be intolerance sold as diversity.
>

--
Bidegg worlds best auction site
http://bidegg.com

Oh crap I think I mixed it up in my head with slony II. nevermind. :)

On Sat, Jun 27, 2009 at 1:17 AM, <mobiledreamers(at)gmail(dot)com> wrote:
> no it is very good and well designed the whole app code doesnt need to
> change
>
> we jus need to configure the pgpool insertion logic
>
> and it has a cool pgpooladmin in php like phppgadmin
>
> if only i can get it to work
>
> On Fri, Jun 26, 2009 at 10:53 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> wrote:
>>
>> I thought pgool II was abandonware...
>>
>> On Fri, Jun 26, 2009 at 7:50 PM, <mobiledreamers(at)gmail(dot)com> wrote:
>> > Hey john
>> > thanks
>> > i did read both extensively, but finishing all the steps parallel mode
>> > or
>> > horizontal partitioning mode doesnot work!
>> > but this is my conf file
>> >
>> > # backend_hostname, backend_port, backend_weight
>> > # here are examples
>> > backend_hostname0 = 'localhost'
>> > backend_port0 = 5432
>> > backend_weight0 = 1
>> > backend_data_directory0 = '/mnt/work/database'
>> > backend_hostname1 = 'marc.somehost.com'
>> > backend_port1 = 5432
>> > backend_weight1 = 1
>> > backend_data_directory1 = '/mnt/work/database'
>> > this is the problem
>> >  pgpool-II-2.2.2]$ createdb -p 9999
>> > createdb: could not connect to database postgres: server closed the
>> > connection unexpectedly
>> >         This probably means the server terminated abnormally
>> >         before or while processing the request.
>> >
>> > ------------------------------------------------------------------------------------------------------------------------------------
>> > Error in log
>> > tail -f /tmp/pgpool.log
>> > 2009-06-26 17:24:07 LOG: pid 5561: pgpool successfully started
>> > 2009-06-26 18:48:34 ERROR: pid 5563: pool_read_int: data does not match
>> > between between master(0) slot[1] (50331648)
>> > 2009-06-26 18:48:34 ERROR: pid 5563: pool_do_auth: read auth kind failed
>> > tail -f /tmp/pgpool.log
>> > 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: backend key data
>> > received
>> > 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: transaction state: I
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: I am 23466 accept fd 7
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: Protocol Major: 3 Minor: 0
>> > database:
>> > postgres user: mark
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 0
>> > backend
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 1
>> > backend
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 0
>> > length: 8
>> > 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot: 1
>> > length: 8
>> > 2009-06-26 18:49:40 ERROR: pid 23466: pool_read_int: data does not match
>> > between between master(0) slot[1] (50331648)
>> > 2009-06-26 18:49:40 ERROR: pid 23466: pool_do_auth: read auth kind
>> > failed
>> >
>> > On Fri, Jun 26, 2009 at 5:56 PM, John R Pierce <pierce(at)hogranch(dot)com>
>> > wrote:
>> >>
>> >> mobiledreamers(at)gmail(dot)com wrote:
>> >>>
>> >>> can someone show how to setup pgpool 2 in parallel *query* mode ie
>> >>> horizontal partitioning
>> >>
>> >> did you look at
>> >>
>> >> http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#parallel
>> >> ?
>> >>
>> >> I'd pay close attention to the restrictions here, too...
>> >>
>> >> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction
>> >>
>> >>
>> >
>> >
>> >
>> > --
>> > Bidegg worlds best auction site
>> > http://bidegg.com
>> >
>>
>>
>>
>> --
>> When fascism comes to America, it will be intolerance sold as diversity.
>
>
>
> --
> Bidegg worlds best auction site
> http://bidegg.com
>

--
When fascism comes to America, it will be intolerance sold as diversity.

yeah i have never tried slony
and i didnt try plproxy as it is a new language and need to modify all
client code

not modifiying client code in pgpool 2 rocks

On Sat, Jun 27, 2009 at 12:52 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> Oh crap I think I mixed it up in my head with slony II. nevermind. :)
>
> On Sat, Jun 27, 2009 at 1:17 AM, <mobiledreamers(at)gmail(dot)com> wrote:
> > no it is very good and well designed the whole app code doesnt need to
> > change
> >
> > we jus need to configure the pgpool insertion logic
> >
> > and it has a cool pgpooladmin in php like phppgadmin
> >
> > if only i can get it to work
> >
> > On Fri, Jun 26, 2009 at 10:53 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com
> >
> > wrote:
> >>
> >> I thought pgool II was abandonware...
> >>
> >> On Fri, Jun 26, 2009 at 7:50 PM, <mobiledreamers(at)gmail(dot)com> wrote:
> >> > Hey john
> >> > thanks
> >> > i did read both extensively, but finishing all the steps parallel mode
> >> > or
> >> > horizontal partitioning mode doesnot work!
> >> > but this is my conf file
> >> >
> >> > # backend_hostname, backend_port, backend_weight
> >> > # here are examples
> >> > backend_hostname0 = 'localhost'
> >> > backend_port0 = 5432
> >> > backend_weight0 = 1
> >> > backend_data_directory0 = '/mnt/work/database'
> >> > backend_hostname1 = 'marc.somehost.com'
> >> > backend_port1 = 5432
> >> > backend_weight1 = 1
> >> > backend_data_directory1 = '/mnt/work/database'
> >> > this is the problem
> >> > pgpool-II-2.2.2]$ createdb -p 9999
> >> > createdb: could not connect to database postgres: server closed the
> >> > connection unexpectedly
> >> > This probably means the server terminated abnormally
> >> > before or while processing the request.
> >> >
> >> >
> ------------------------------------------------------------------------------------------------------------------------------------
> >> > Error in log
> >> > tail -f /tmp/pgpool.log
> >> > 2009-06-26 17:24:07 LOG: pid 5561: pgpool successfully started
> >> > 2009-06-26 18:48:34 ERROR: pid 5563: pool_read_int: data does not
> match
> >> > between between master(0) slot[1] (50331648)
> >> > 2009-06-26 18:48:34 ERROR: pid 5563: pool_do_auth: read auth kind
> failed
> >> > tail -f /tmp/pgpool.log
> >> > 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: backend key data
> >> > received
> >> > 2009-06-26 18:49:37 DEBUG: pid 23506: s_do_auth: transaction state: I
> >> > 2009-06-26 18:49:40 DEBUG: pid 23466: I am 23466 accept fd 7
> >> > 2009-06-26 18:49:40 DEBUG: pid 23466: Protocol Major: 3 Minor: 0
> >> > database:
> >> > postgres user: mark
> >> > 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 0
> >> > backend
> >> > 2009-06-26 18:49:40 DEBUG: pid 23466: new_connection: connecting 1
> >> > backend
> >> > 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot:
> 0
> >> > length: 8
> >> > 2009-06-26 18:49:40 DEBUG: pid 23466: pool_read_message_length: slot:
> 1
> >> > length: 8
> >> > 2009-06-26 18:49:40 ERROR: pid 23466: pool_read_int: data does not
> match
> >> > between between master(0) slot[1] (50331648)
> >> > 2009-06-26 18:49:40 ERROR: pid 23466: pool_do_auth: read auth kind
> >> > failed
> >> >
> >> > On Fri, Jun 26, 2009 at 5:56 PM, John R Pierce <pierce(at)hogranch(dot)com>
> >> > wrote:
> >> >>
> >> >> mobiledreamers(at)gmail(dot)com wrote:
> >> >>>
> >> >>> can someone show how to setup pgpool 2 in parallel *query* mode ie
> >> >>> horizontal partitioning
> >> >>
> >> >> did you look at
> >> >>
> >> >>
> http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html#parallel
> >> >> ?
> >> >>
> >> >> I'd pay close attention to the restrictions here, too...
> >> >>
> >> >>
> http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction
> >> >>
> >> >>
> >> >
> >> >
> >> >
> >> > --
> >> > Bidegg worlds best auction site
> >> > http://bidegg.com
> >> >
> >>
> >>
> >>
> >> --
> >> When fascism comes to America, it will be intolerance sold as diversity.
> >
> >
> >
> > --
> > Bidegg worlds best auction site
> > http://bidegg.com
> >
>
>
>
> --
> When fascism comes to America, it will be intolerance sold as diversity.
>

--
Bidegg worlds best auction site
http://bidegg.com

On Fri, 26 Jun 2009, mobiledreamers(at)gmail(dot)com wrote:

> 2009-06-26 18:48:34 ERROR: pid 5563: pool_read_int: data does not match between between master(0) slot[1] (50331648)
> 2009-06-26 18:48:34 ERROR: pid 5563: pool_do_auth: read auth kind failed

My guess is that you have different types of connections being made
between the local database and the remote one. See
http://pgfoundry.org/pipermail/pgpool-general/2007-September/000708.html
for some comments about the restrictions here, and
http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html for
background on the file you'll need to edit in order to change this
situation.

If you still can't resolve your problem, you should ask about it on the
pgpool-general mailing list rather than this one:
http://lists.pgfoundry.org/pipermail/pgpool-general/
--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Thanks a lot greg trying it out
If it does not work i ll post to pgpool groups
thanks again

but i think what you have pointed out is the right solution

On Sat, Jun 27, 2009 at 10:59 PM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:

> On Fri, 26 Jun 2009, mobiledreamers(at)gmail(dot)com wrote:
>
> 2009-06-26 18:48:34 ERROR: pid 5563: pool_read_int: data does not match
>> between between master(0) slot[1] (50331648)
>> 2009-06-26 18:48:34 ERROR: pid 5563: pool_do_auth: read auth kind failed
>>
>
> My guess is that you have different types of connections being made between
> the local database and the remote one. See
> http://pgfoundry.org/pipermail/pgpool-general/2007-September/000708.htmlfor some comments about the restrictions here, and
> http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html for
> background on the file you'll need to edit in order to change this
> situation.
>
> If you still can't resolve your problem, you should ask about it on the
> pgpool-general mailing list rather than this one:
> http://lists.pgfoundry.org/pipermail/pgpool-general/
> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>

--
Bidegg worlds best auction site
http://bidegg.com