[GENERAL] Replication options in Postgres
I am setting up a system that processes transactions, and it needs to be highly reliable. Once a transaction happens, it can never be lost. This means that there needs to be real-time off-site replication of data. I'm wondering what's the best way to do this. One thing that might simplify this system is that I _never_ use UPDATE or DELETE. The only thing I ever do with the database is INSERT. So this might make replication a little easier. I think I have a few possibilities: 1. In my PHP code, I have functions like inserttransaction(values...). I could just modify inserttransaction() so that it runs the same query (the INSERT) on two or more DB servers. This would probably work ok. 2. I could write triggers for all my tables, so that when there is an INSERT, the trigger does the same INSERT on the other server. Any ideas for an efficient way to do this? 3. Any other tricks? I don't need mirroring. There will be one master and one or more slaves, and the only thing the slaves will do is store backup data. The most important thing is that I can't lose a single transaction. Thanks, e
Re: [GENERAL] Replication options in Postgres
I guess if you don't do deletes then something like selecting all the records with an oid greater than the last replication cycle would find the most recent additions. Erich wrote: I am setting up a system that processes transactions, and it needs to be highly reliable. Once a transaction happens, it can never be lost. This means that there needs to be real-time off-site replication of data. I'm wondering what's the best way to do this. One thing that might simplify this system is that I _never_ use UPDATE or DELETE. The only thing I ever do with the database is INSERT. So this might make replication a little easier. I think I have a few possibilities: 1. In my PHP code, I have functions like inserttransaction(values...). I could just modify inserttransaction() so that it runs the same query (the INSERT) on two or more DB servers. This would probably work ok. 2. I could write triggers for all my tables, so that when there is an INSERT, the trigger does the same INSERT on the other server. Any ideas for an efficient way to do this? 3. Any other tricks? I don't need mirroring. There will be one master and one or more slaves, and the only thing the slaves will do is store backup data. The most important thing is that I can't lose a single transaction. Thanks, e
Re: [GENERAL] Replication options in Postgres
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 1. In my PHP code, I have functions like inserttransaction(values...). I could just modify inserttransaction() so that it runs the same query (the INSERT) on two or more DB servers. This would probably work ok. Why not have a proxy server that your clients talk to, which replicates the transaction across the other (independent) backend servers, and only returns OK if all the backends return OK. Then, theoretically, your databases should always remain concurrant. You could dump diff them periodically to make sure. Ian -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.1 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE5hjM7fn9ub9ZE1xoRAtevAJ9v7Ik/wtasCyTgeCx+zsYvYQWL4QCgubgx PE0m/X6VoY7+ESZS/p3CIlQ= =b6Bv -END PGP SIGNATURE-
