Re: 1 cluster on several servers

Hi,

Is it possible to run one PostgreSQL cluster on more than one (hardware)
server?

WBL

Willy-Bas Loos wrote:
> Hi,
>
> Is it possible to run one PostgreSQL cluster on more than one (hardware)
> server?
>
> WBL
>
You would be looking for replication.
Start with
http://www.postgresql.org/docs/8.2/interactive/high-availability.html
to get some idea on what is available for what you wish to achieve.
Some of the projects that add these features are mentioned.

--

Shane Ambler
pgSQL(at)Sheeky(dot)Biz

Get Sheeky @ http://Sheeky.Biz

I'll take that as a "no".
What i mean is to actually run exactly one cluster (no replicated copy) on
more than one server. Of course, if that were possible, why would people
bother with replication..

I guess it is irrational to suggest that it would be possible, since each
server would at least need to have it's own copy of the DBMS software etc,
or it would cease to be a separate server.

Maybe "Data Partitioning", as in the documentation link Shane sent, possibly
combined with Slony for the other data per server, would be an option for
me.

Is there an implementation for this in PostgreSQL? It would have to be
something like pgPool (middleware), because: How would the query know on
which server to put it's data? And i guess i would need some Kerberos-like
implementation for my authentication and authorization...

cheers,

WBL

On Nov 29, 2007 1:23 PM, Shane Ambler <pgsql(at)sheeky(dot)biz> wrote:

> Willy-Bas Loos wrote:
> > Hi,
> >
> > Is it possible to run one PostgreSQL cluster on more than one (hardware)
> > server?
> >
> > WBL
> >
> You would be looking for replication.
> Start with
> http://www.postgresql.org/docs/8.2/interactive/high-availability.html
> to get some idea on what is available for what you wish to achieve.
> Some of the projects that add these features are mentioned.
>
>
>
>
> --
>
> Shane Ambler
> pgSQL(at)Sheeky(dot)Biz
>
> Get Sheeky @ http://Sheeky.Biz
>

Willy-Bas Loos wrote:
>
> Is there an implementation for this in PostgreSQL? It would have to be
> something like pgPool (middleware), because: How would the query know on
> which server to put it's data? And i guess i would need some Kerberos-like
> implementation for my authentication and authorization...

Try explaining what it is you're trying to do, and maybe someone can
suggest something. The people at skype have their DB partitioned over
more than one machine, for example.

--
Richard Huxton
Archonet Ltd

From within pgAdminIII I get this message in the status bar when I go
to setup slony-I replication.

I have the scripts in pgdir /shared after installing from source,
should they be somewhere else, or should I have some environment
variables set?

Glyn Astill

___________________________________________________________
Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
now.
http://uk.answers.yahoo.com/

Willy-Bas Loos wrote:
> I'll take that as a "no".
> What i mean is to actually run exactly one cluster (no replicated copy) on
> more than one server. Of course, if that were possible, why would people
> bother with replication..
>
> I guess it is irrational to suggest that it would be possible, since each
> server would at least need to have it's own copy of the DBMS software etc,
> or it would cease to be a separate server.

I think you need to better identify what you're trying to do. I can
think of a couple of different solutions based on the limited info
provided. You've already said you don't want replication.

We have a scenario where we have a data silo that is shared between two
servers, so the data exist in one place. To make things simple, if one
server fails, the postmasters running on that server are started on the
other server. This is a hot/hot fail over implementation as we have
multiple postmasters running.

You could implement a hot/warm fail over solution if you're running a
single postmaster.

Finally, you might be thinking of something like a beowulf cluster where
multiple machines function as a single machine. I can't help you with
that scenario as I don't have any experience with it and postgresql.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

Hi.

From: "Glyn Astill" <glynastill(at)yahoo(dot)co(dot)uk>

> From within pgAdminIII I get this message in the status bar when I go
> to setup slony-I replication.
>
> I have the scripts in pgdir /shared after installing from source,
> should they be somewhere else, or should I have some environment
> variables set?

I can't judge in what stage you encountered the problem. Probably , you
can grasp the right place by the installer. and as for an early setting script,
creating to slonik is desirable. Then, the detailed information developed
in large quantities GUI of pgAdminIII will be difficult to set up if it does
not have full knowledge of slony. Moreover, if it is the script of slonik,
Slony-ML(slony1-general(at)lists(dot)slony(dot)info) will be help more.

Regards,
Hiroshi Saito

Sorry Hiroshi, I failed to understand exactly what you're saying
there.

So far I've installed Slony as per the docs, all seems to be well and
the installer ahs put the replication *.sql files in pgsql/shared.

I haven't started the slon daemons yet.

I thought once slony was set up you could then use the scripts from
pgAdminIII to setup slony before starting the slon daemons.

I have the scripts, but pgAdmin is telling me it can't find them.

--- Hiroshi Saito <z-saito(at)guitar(dot)ocn(dot)ne(dot)jp> wrote:

> Hi.
>
> From: "Glyn Astill" <glynastill(at)yahoo(dot)co(dot)uk>
>
>
> > From within pgAdminIII I get this message in the status bar when
> I go
> > to setup slony-I replication.
> >
> > I have the scripts in pgdir /shared after installing from source,
> > should they be somewhere else, or should I have some environment
> > variables set?
>
> I can't judge in what stage you encountered the problem. Probably ,
> you
> can grasp the right place by the installer. and as for an early
> setting script,
> creating to slonik is desirable. Then, the detailed information
> developed
> in large quantities GUI of pgAdminIII will be difficult to set up
> if it does
> not have full knowledge of slony. Moreover, if it is the script of
> slonik,
> Slony-ML(slony1-general(at)lists(dot)slony(dot)info) will be help more.
>
> Regards,
> Hiroshi Saito
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Glyn Astill

___________________________________________________________
Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
now.
http://uk.answers.yahoo.com/

Hi.

From: "Glyn Astill" <glynastill(at)yahoo(dot)co(dot)uk>

> Sorry Hiroshi, I failed to understand exactly what you're saying
> there.

Ahh, Sorry...

>
> So far I've installed Slony as per the docs, all seems to be well and
> the installer ahs put the replication *.sql files in pgsql/shared.
>
> I haven't started the slon daemons yet.
>
> I thought once slony was set up you could then use the scripts from
> pgAdminIII to setup slony before starting the slon daemons.
>
> I have the scripts, but pgAdmin is telling me it can't find them.

Um, Here may be the material which helps you.
http://developer.pgadmin.org/~hiroshi/Slony-I/
Anyhow, the first setup recommends slonik.

Regards,
Hiroshi Saito

>
> --- Hiroshi Saito <z-saito(at)guitar(dot)ocn(dot)ne(dot)jp> wrote:
>
>> Hi.
>>
>> From: "Glyn Astill" <glynastill(at)yahoo(dot)co(dot)uk>
>>
>>
>> > From within pgAdminIII I get this message in the status bar when
>> I go
>> > to setup slony-I replication.
>> >
>> > I have the scripts in pgdir /shared after installing from source,
>> > should they be somewhere else, or should I have some environment
>> > variables set?
>>
>> I can't judge in what stage you encountered the problem. Probably ,
>> you
>> can grasp the right place by the installer. and as for an early
>> setting script,
>> creating to slonik is desirable. Then, the detailed information
>> developed
>> in large quantities GUI of pgAdminIII will be difficult to set up
>> if it does
>> not have full knowledge of slony. Moreover, if it is the script of
>> slonik,
>> Slony-ML(slony1-general(at)lists(dot)slony(dot)info) will be help more.
>>
>> Regards,
>> Hiroshi Saito
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>>
>
>
>
> Glyn Astill
>
>
>
> ___________________________________________________________
> Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
> now.
> http://uk.answers.yahoo.com/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

This is the system i am talking about:
http://people.planetpostgresql.org/xzilla/index.php?/archives/326-Solving-the-big-science-checklist.html

There are many parties involved, and i am trying to figure out what
configuration would be ideal for ours.

On Nov 29, 2007 3:20 PM, Geoffrey <lists(at)serioustechnology(dot)com> wrote:

> Willy-Bas Loos wrote:
> > I'll take that as a "no".
> > What i mean is to actually run exactly one cluster (no replicated copy)
> on
> > more than one server. Of course, if that were possible, why would people
> > bother with replication..
> >
> > I guess it is irrational to suggest that it would be possible, since
> each
> > server would at least need to have it's own copy of the DBMS software
> etc,
> > or it would cease to be a separate server.
>
> I think you need to better identify what you're trying to do. I can
> think of a couple of different solutions based on the limited info
> provided. You've already said you don't want replication.
>
> We have a scenario where we have a data silo that is shared between two
> servers, so the data exist in one place. To make things simple, if one
> server fails, the postmasters running on that server are started on the
> other server. This is a hot/hot fail over implementation as we have
> multiple postmasters running.
>
> You could implement a hot/warm fail over solution if you're running a
> single postmaster.
>
> Finally, you might be thinking of something like a beowulf cluster where
> multiple machines function as a single machine. I can't help you with
> that scenario as I don't have any experience with it and postgresql.
>
> --
> Until later, Geoffrey
>
> Those who would give up essential Liberty, to purchase a little
> temporary Safety, deserve neither Liberty nor Safety.
> - Benjamin Franklin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

On Nov 29, 2007 6:27 PM, Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:

> I'll take that as a "no".
> What i mean is to actually run exactly one cluster (no replicated copy) on
> more than one server. Of course, if that were possible, why would people
> bother with replication..
>
> I guess it is irrational to suggest that it would be possible, since each
> server would at least need to have it's own copy of the DBMS software etc,
> or it would cease to be a separate server.
>
> Maybe "Data Partitioning", as in the documentation link Shane sent,
> possibly combined with Slony for the other data per server, would be an
> option for me.
>
> Is there an implementation for this in PostgreSQL? It would have to be
> something like pgPool (middleware), because: How would the query know on
> which server to put it's data? And i guess i would need some Kerberos-like
> implementation for my authentication and authorization...
>

There used to be parallel server type solution for postgresql called ExtenDB
www.extendb.com , but i think its now acquired by a commercial PostgresSQL
vendor, but i have seen others similar to that.

>
> cheers,
>
> WBL
>
>
>
> On Nov 29, 2007 1:23 PM, Shane Ambler <pgsql(at)sheeky(dot)biz> wrote:
>
> > Willy-Bas Loos wrote:
> > > Hi,
> > >
> > > Is it possible to run one PostgreSQL cluster on more than one
> > (hardware)
> > > server?
> > >
> > > WBL
> > >
> > You would be looking for replication.
> > Start with
> > http://www.postgresql.org/docs/8.2/interactive/high-availability.html
> > to get some idea on what is available for what you wish to achieve.
> > Some of the projects that add these features are mentioned.
> >
> >
> >
> >
> > --
> >
> > Shane Ambler
> > pgSQL(at)Sheeky(dot)Biz
> >
> > Get Sheeky @ http://Sheeky.Biz
> >
>
>

--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Willy-Bas Loos wrote:
> I'll take that as a "no".
> What i mean is to actually run exactly one cluster (no replicated copy) on
> more than one server. Of course, if that were possible, why would people
> bother with replication..

What you may be thinking of is having several machines running postgres
and reading/writing to the same "shared" filesystem?
It's not advisable to try something like that. (as a read only system it
may work fine)

Each machine should run their own copy of postgres and have their own
copy of the data (which also gives you redundancy) to prevent contention
and overwriting other servers changes.
To do that you use replication of some sort. How you achieve that
depends on your needs.

Also think that having ten machines reading and writing to the one hard
drive (or array of drives) will not help your performance needs in any
way. The hard drive is the slowest point of the server, you want to add
more drives to multiply the transfer speeds to reach the performance
required.

Say you have 10,000 clients requesting data. If you had ten machines
reading from the same shared drive you wouldn't get better performance
than if one machine was accessing the drive alone.
If you had ten machines with their own drives and copy of the data then
you would be multiplying the amount of data sent out by ten.

Slony would be setup with one server that receives the insert and
updates and copies them to the other servers that would handle selects
from all your clients. I believe that Slony 2 is suppose to handle
multiple masters but I don't believe it is available yet.

If you want more than one server to respond to insert and updates then
maybe PGCluster may be closer to what you are looking for. This is a
multi-master setup where each server commits any changes before the
transaction is completed. This will give you each machine having
identical copies of data to work with.
Cybercluster appears to be a branch from PGCluster.

Bucardo is a project that has just recently been released to the
community. It supports multi-master replication and was developed by a
busy online store to meet their needs.

There are several commercial options available from many of the
companies that also provide postgres support that may fit your needs
better. EnterpriseDB, Commandprompt, Pervasive, Cybertec, Greenplum are
the first few that come to mind.

The real question is what you want to achieve - supporting an extremely
high number of client connections? Redundancy to prevent disaster?

> I guess it is irrational to suggest that it would be possible, since each
> server would at least need to have it's own copy of the DBMS software etc,
> or it would cease to be a separate server.
>
> Maybe "Data Partitioning", as in the documentation link Shane sent, possibly
> combined with Slony for the other data per server, would be an option for
> me.
>
> Is there an implementation for this in PostgreSQL? It would have to be
> something like pgPool (middleware), because: How would the query know on
> which server to put it's data? And i guess i would need some Kerberos-like
> implementation for my authentication and authorization...
>
> cheers,
>
> WBL
>
>
> On Nov 29, 2007 1:23 PM, Shane Ambler <pgsql(at)sheeky(dot)biz> wrote:
>
>> Willy-Bas Loos wrote:
>>> Hi,
>>>
>>> Is it possible to run one PostgreSQL cluster on more than one (hardware)
>>> server?
>>>
>>> WBL
>>>
>> You would be looking for replication.
>> Start with
>> http://www.postgresql.org/docs/8.2/interactive/high-availability.html
>> to get some idea on what is available for what you wish to achieve.
>> Some of the projects that add these features are mentioned.
>>
>>
>>
>>
>> --
>>
>> Shane Ambler
>> pgSQL(at)Sheeky(dot)Biz
>>
>> Get Sheeky @ http://Sheeky.Biz
>>
>

--

Shane Ambler
pgSQL(at)Sheeky(dot)Biz

Get Sheeky @ http://Sheeky.Biz

Am 2007-11-29 12:50:58, schrieb Willy-Bas Loos:
> Hi,
>
> Is it possible to run one PostgreSQL cluster on more than one (hardware)
> server?

Yes of course... I run at a customer "Monster"
with 42 PostgreSQL servers of each 1,8 TByte.

Thanks, Greetings and nice Day
Michelle Konzack
Tamay Dogan Network
Open Hardware Developer
Debian GNU/Linux Consultant

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
50, rue de Soultz MSN LinuxMichi
0033/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)

On 02/12/2007 13:02, Michelle Konzack wrote:

> with 42 PostgreSQL servers of each 1,8 TByte.

Wow! Who ever said size wasn't everything.... :-)

Would you be willing to tell us a little about your hardware and
software set-up?

Also, are the servers running separate databases? - or is it one
whopping database load-balanced across the servers?

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
---------------------------------------------------------------