Thread: 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
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
-----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-----
Hi,
I want to alter the size of a column, say from char(40) to char(80),
but it seem that
the ALTER does not support such operation, nor does it support column
removing.
How can I do for this ?
Thanks
> I want to alter the size of a column, say from char(40) to char(80), > but it seem that > the ALTER does not support such operation, nor does it support column > removing. > > How can I do for this ? I would also like to know how to do both of these things.
Michael Talbot-Wilson wrote: > > > I want to alter the size of a column, say from char(40) to char(80), > > but it seem that > > the ALTER does not support such operation, nor does it support column > > removing. > > > > How can I do for this ? > > I would also like to know how to do both of these things. I'm not aware of an easy way of doing it. But you can dump your schema and data separately. Manually edit your schema. Reload the schema then reload the data.
Right now the best way is probably: create table newtable ( ... new column info ... ) insert into newtable select * from oldtable; alter table oldtable rename to old_oldtable; alter table newtable rename to oldtable; In the second line, you may not be able to get away with a * if you're doing more complicated changes of types that can't automatically converted. And once you're done and sure everything is working, you can delete the backup of the old table. Stephan Szabo sszabo@bigpanda.com On Tue, 1 Aug 2000, Cheng Kai wrote: > Hi, > > I want to alter the size of a column, say from char(40) to char(80), > but it seem that > the ALTER does not support such operation, nor does it support column > removing. > > How can I do for this ? > > > Thanks >