Solved

PostgreSQL with master -> multiple slaves. What to use?

Posted on 2007-11-24
6
679 Views
Last Modified: 2010-05-19
Hi People,

We're looking to move our DBMS to PostgreSQL, and I'm looking for a good reliable replication system to use.

The idea is this; a master and slave onsite and then another slave at our second premises down a 10Mb line.

I've looked about and seen mnay different projects about to do this, and I could do with a bit of advice. Has anyone done this, and how good is it?

From what I see slony or pgreplicator do this.

Having tried the innodb replication built into MySQL something similar would be ace. The only reason we're not going with MySQL is I seem to be told everywhere that it's not as fault tollerent as Postgres, however nobody has given me an explanation why.

Thanks

0
Comment
Question by:lorddef
6 Comments
 
LVL 1

Expert Comment

by:rabroersma
ID: 20344939
> The only reason we're not going with MySQL is I seem to be told everywhere that it's not as fault tollerent > as Postgres, however nobody has given me an explanation why.

I am not to sure how innodb compares since most of the Fault tolerant comparisons I've seen were were against MyISAM.

Basically the "pull the power cord" test is given as a test case where your server is un-expectingly halted.  Barring that no hardward fails as a result of the power loss, Postgres ensures that all transactions <including ddl in the transaction> will either be fully committed or fully rolled back.  Some other database brands "may" leave the database in an in-consistent state where some open transactions are partially applied to the database.  Also, postgres has developed the reputation of not crashing under heavy load although it also has/had the reputation of being slower than the composition.  
If mysql ever had a reputation of crashing, I am pretty sure it was only the older versions that had this problem.  Also, if postgresql had a reputation of being slow, the newer versions have certainly improved in the bench marks for speed.
0
 
LVL 19

Expert Comment

by:grant300
ID: 20347160
Take a look at EnterpriseDB.  They have both a supported distribution of PostgreSQL as well as an extended product that boasts both a high degree of Oracle compatibility as well as built in replication.

If your organization both needs and is willing to pay for the level of fault tolerance you are proposing, it is worthwhile to look at commercially supported products to do it.

Regards,
Bill
0
 
LVL 1

Author Comment

by:lorddef
ID: 20347443
Hi grant300,

I have seen enterprise DB and am concidering it, infact was thinking of going with their postgres support, just wondered what different replication methods were about.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Accepted Solution

by:
rabroersma earned 20 total points
ID: 20348048
There is also the Command Prompt replication solution called mammoth replicator.  The basic difference between mammoth replicator and Slony is that Slony uses transaction based replication but mammoth replicator uses WAL log shipping.  Also, if you are looking for support, Command Prompt officially supports both Slony and of couse their own replication solution.
0
 
LVL 1

Expert Comment

by:loquin
ID: 20935221
Slony will certainly do what you're looking for.  I've also looked at EnterpriseDB, and it has more capabilities than does Slony, including multi-master and load sharing capabilities, but these additional features weren't specified. From what I've seen, they do have good PostgreSQL support.

MySQL's reputation has been in the area of raw speed, however this is misleading.  When MySQL is using InnoDB tables (as close as "apples-to-apples" as they can do, in terms of ACID support, there is not a lot of speed difference between the two.  And, as the number of simultaneous connections and workload increase, PostgreSQL's performance soon outshine MySQL.  Take a look at this on-line article by tweakers.net: http://tweakers.net/reviews/657   The article describes testing, intended as a hardware test of various multicore servers.  They used MySQL 4, MySQL 5, and PostgreSQL 8.1 beta.   The results are enightening.
0
 
LVL 1

Author Comment

by:lorddef
ID: 20936667
Thanks loguin.

I settled on slony a week after my initial post anyway.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
join tables 4 55
Sql Join Problem 2 44
multiple application databases same MSSQL instance 5 55
MS SQL Server - Looking to filter rows based on column value 3 37
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question