Thread: PostgreSQL clustering with DRBD

Hi,

Serge Fonville wrote:
> Can PostgreSQL use DRBD as its storage?

Yes, many deployments use this.

> Since the in-memory database would be synchronized with the on-disk
> database.
> If this would be done with every query, this would greatly impact
> performance.

Performance with DRBD is usually acceptable, but you should test this
yourself in your setup.

> Since the cluster will be multi-master/dual-primary, do I need to have a
> separate block device for each PostgreSQL instance or can it use the
> DRBD device?

I don't understand how you want to have a multimaster PostgreSQL setup
with DRBD.  DRBD can only be used for an inactive standby.

> I read mostly about MySQL clustering with DRBD and there the query cache
> should be disabled to make sure data is in-sync.
> To me it seems something similar would apply to PostgreSQL.

no

> I believe cybercluster is the most active and complete PostgreSQL
> clustering solution.
> My endgoal is a two node cluster with load sharing and fail over where
> both nodes can perform reads and writes.

That's not going to happen very easily, if at all.

Why not?

Dual primary support for use with GFS/OCFS2

In many cases asynchronous replication is just not enough to model a certain business case. 

Therefore Cybertec Schönig & Schönig GmbH offers a synchronous multimaster replication solution for PostgreSQL called Cybercluster.

Serge Fonville wrote:
> So, DRBD dual primary and Cybercluster multimaster, cannot be combined?

If you have questions on Cybercluster, you should perhaps ask there for
details.  For normal PostgreSQL, using DRBD dual primary is not possible.

>         My endgoal is a two node cluster with load sharing and fail over
>         where both nodes can perform reads and writes.
>
>
>     That's not going to happen very easily, if at all.
>
>
> Why not?
> Cybercluster is a multimaster clustering solution for PostgreSQL
> DRBD supports dual primary
>  From the feature list:

I don't know.  Give it a try.

After reading your post I decided to check out cybercluster.   In PgFoundry there is a cybercluster project http://pgfoundry.org/projects/cybercluster/ but it hasn't been updated since 2007.

Is that the one you are talking about or is there another cybercluster I should be looking at.

Also....


Is there an article or something that compares the different HA solutions for postgres? What are the differences between pgpool, pgcluster, cybercluster etc?


Any HOWTOs anywhere?

Thanks.

Hello,

the pgfoundry project seems to be the initial start of cybercluster
offered by CyberTec from Austria (German page:
http://www.postgresql-support.de/pr_cybercluster.html).
As far as I know this is a modified/adapted pgcluster solution.

We're very happy with pgpool-II for load-balancing and multi-master
usage of PostgreSQL (keep in mind to enable HA for pgpool-II itself to
avoid a SPOF, e.g. with heartbeat).

regards...GERD...

Tim Uckun schrieb:
>
>
> On Wed, Feb 11, 2009 at 11:24 PM, Serge Fonville
> <serge.fonville@gmail.com <mailto:serge.fonville@gmail.com>> wrote:
>
>     Hi,
>
>     I am in the process of setting up a two node cluster.
>     Can PostgreSQL use DRBD as its storage?
>     Since the in-memory database would be synchronized with the on-disk
>     database.
>     If this would be done with every query, this would greatly impact
>     performance.
>     Since the cluster will be multi-master/dual-primary, do I need to
>     have a separate block device for each PostgreSQL instance or can it
>     use the DRBD device?
>     I read mostly about MySQL clustering with DRBD and there the query
>     cache should be disabled to make sure data is in-sync.
>     To me it seems something similar would apply to PostgreSQL.
>     I believe cybercluster is the most active and complete PostgreSQL
>     clustering solution.
>     My endgoal is a two node cluster with load sharing and fail over
>     where both nodes can perform reads and writes.
>
>
>
> After reading your post I decided to check out cybercluster.   In
> PgFoundry there is a cybercluster project
> http://pgfoundry.org/projects/cybercluster/ but it hasn't been updated
> since 2007.
>
> Is that the one you are talking about or is there another cybercluster I
> should be looking at.
>
> Also....
>
>
> Is there an article or something that compares the different HA
> solutions for postgres? What are the differences between pgpool,
> pgcluster, cybercluster etc?
>
>
> Any HOWTOs anywhere?
>
> Thanks.
>
>

--
/===============================\
| Gerd König
| - Infrastruktur -
|
| TRANSPOREON GmbH
| Pfarrer-Weiss-Weg 12
| DE - 89077 Ulm
|
|
| Tel: +49 [0]731 16906 16
| Fax: +49 [0]731 16906 99
| Web: www.transporeon.com
|
\===============================/



Bleiben Sie auf dem Laufenden.
Jetzt den Transporeon Newsletter abonnieren!
http://www.transporeon.com/unternehmen_newsletter.shtml


TRANSPOREON GmbH, Amtsgericht Ulm, HRB 722056
Geschäftsf.: Axel Busch, Peter Förster, Roland Hötzl, Marc-Oliver Simon

Thanks.

I am going to see which one has better documentation and try that one first.

Hey Serge.

Any update on this?

I can't seem to find any information about cybercluster anywhere.  Is anybody using it? Is there a community? A mailing list? 

I found one howto at the web site and it looked pretty simple to set up but it was really sparse on how to monitor, troubleshoot etc.

Is anybody on the postgres list using it?

Cheers.

On Mon, Feb 23, 2009 at 2:27 AM, Serge Fonville
<serge.fonville@gmail.com> wrote:
> Thanks all for the responses,
>>
>> We're very happy with pgpool-II for load-balancing and multi-master
>>
>> usage of PostgreSQL (keep in mind to enable HA for pgpool-II itself to
>>
>> avoid a SPOF, e.g. with heartbeat).
>
> I could not determine whether pgpool-II is suitable for what I want.
> It does not seem to support multimaster in the fashion I had in mind, based
> on the information on the website it looks like it does not support full
> CRUD on any node.

So, what design criteria are you using that says multi-master is a
better choice than master / slave?  I know that multi-master is
buzzword compliant, but often the actual product you get with it isn't
any better, and in some cases worse, than a master / slave setup.

> The most suitable solution seems to be LVS for a shared IP, ldirectord for
> load balancing and cybercluster for the database
> replication/synchronization.

Again, this is a lot of work to avoid master / slave with failover.
Are you sure it's really needed for your situation?

On Tue, Mar 3, 2009 at 3:57 AM, Tim Uckun <timuckun@gmail.com> wrote:
>
>>
>>
>> Again, this is a lot of work to avoid master / slave with failover.
>> Are you sure it's really needed for your situation?
>>
>
> What is the most straightforward and simple way to achieve master slave with
> failover?

We use Slony at work, which has a pretty easily run command to
failover.  We initiate failover at the application level when a
majority of the servers agree that the primary is no longer
responding.

> Preferably  a solution that would have decent monitoring, alerting and
> failback capacity.

We had to write out own alerting and such back in the day.  Nagios can
definitely keep you apprised of things happening.  So can a collection
of shell scripts.  I'm afraid I know of no pre-made pre-wrapped
packages to do what you want.  Then again, each solution needs to fit
the needs of the user, so it's hard to just have one size fit all
here.

> It would be also nice if you could use the standby as a read only database
> for reporting or something.

That works wonderfully well with Slony.  We actually have one master,
one failover slave that would take over in the case of the master
going down, and x read slaves that read from that machine for the web
app to read.  Allows pretty good scalability and redundancy.

The problem with multi-master is that you can either have good
performance or good redundancy, but it's hard to get both.  And don't
hold up RAC as an example of great multi-master.  It's overly complex,
tends to fail individual nodes a lot and costs an arm and a leg.  And
performance wise it's definitely a meh grade solution for most
applications.

Hi all, executing the following command inside pgAdmin on my Windows
Vista (please avoid comment, I pray you) :

copy anagrafica_import from 'c:\\temp\\anagraficaANIDIs.csv' WITH CSV


I get the following error:

WARNING:  nonstandard use of \\ in a string literal
LINE 1: copy anagrafica_import from 'C:\\temp\\anagraficaANIDIs.csv'...
                                    ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
ERROR:  could not open file "C:\temp\anagraficaANIDIs.csv" for reading:
No such file or directory

********** Errore **********

ERROR: could not open file "C:\temp\anagraficaANIDIs.csv" for reading:
No such file or directory
Stato SQL: 58P01


The problem is the file C:\temp\anagraficaANIDIs.csv is there and I have
granted reading rights to everyone .
Any suyggestion?

On 17/03/2009 14:45, Ivano Luberti wrote:
> Hi all, executing the following command inside pgAdmin on my Windows
> Vista (please avoid comment, I pray you) :
>
> copy anagrafica_import from 'c:\\temp\\anagraficaANIDIs.csv' WITH CSV

Try putting an 'E' in front of the path, like this:

  ....from E'c:\\temp\\anagraficaANIDIs.csv' with csv;

Also, remember that the file needs to be on the same machine as the
server; if you're running pgAdmin on a different machine, this won't work.

HTH,

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Thanks but it keeps on not finding the file: the warning has disappeared


ERROR:  could not open file "c:\temp\anagraficaANIDIs.csv" for reading:
No such file or directory

********** Errore **********

ERROR: could not open file "c:\temp\anagraficaANIDIs.csv" for reading:
No such file or directory
Stato SQL: 58P01

I have also tried uppercasing C without success.
I'm sure about the path because I have copied and pasted from the
properties window.








Raymond O'Donnell ha scritto:
> On 17/03/2009 14:45, Ivano Luberti wrote:
>
>> Hi all, executing the following command inside pgAdmin on my Windows
>> Vista (please avoid comment, I pray you) :
>>
>> copy anagrafica_import from 'c:\\temp\\anagraficaANIDIs.csv' WITH CSV
>>
>
> Try putting an 'E' in front of the path, like this:
>
>   ....from E'c:\\temp\\anagraficaANIDIs.csv' with csv;
>
> Also, remember that the file needs to be on the same machine as the
> server; if you're running pgAdmin on a different machine, this won't work.
>
> HTH,
>
> Ray.
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
>

--
==================================================
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
web: www.archicoop.it
==================================================

On 17/03/2009 15:04, Ivano Luberti wrote:
> Thanks but it keeps on not finding the file: the warning has disappeared
>
>
> ERROR:  could not open file "c:\temp\anagraficaANIDIs.csv" for reading:
> No such file or directory

You haven't said whether the file is on the same machine as the server -
is this the case?

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

I'm sorry, you are right that is the problem
I had interpreted that as the file should reside on the same machine
where pgAdmin (or another client) runs , not the server.

Thank you again


Raymond O'Donnell ha scritto:
> On 17/03/2009 15:04, Ivano Luberti wrote:
>
>> Thanks but it keeps on not finding the file: the warning has disappeared
>>
>>
>> ERROR:  could not open file "c:\temp\anagraficaANIDIs.csv" for reading:
>> No such file or directory
>>
>
> You haven't said whether the file is on the same machine as the server -
> is this the case?
>
> Ray.
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
>

--
==================================================
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
web: www.archicoop.it
==================================================

On 17/03/2009 15:28, Ivano Luberti wrote:
> I'm sorry, you are right that is the problem
> I had interpreted that as the file should reside on the same machine
> where pgAdmin (or another client) runs , not the server.
>
> Thank you again

You're welcome! That actually cost me a half-hour or so of frustration
not long ago....so I was feeling your pain. :-)

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------