Horton

Thread

  1. Replication options in Postgres

    Erich <hh@cyberpass.net> — 2000-08-01T00:45:06Z

    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

  2. Re: Replication options in Postgres

    Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> — 2000-08-01T01:20:55Z

    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

  3. Re: Replication options in Postgres

    Ian Turner <vectro@pipeline.com> — 2000-08-01T02:17:30Z

    -----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-----

  4. How to alter the size of a column

    Cheng Kai <chengk@isse.kuis.kyoto-u.ac.jp> — 2000-08-01T03:41:32Z

    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

  5. Re: How to alter the size of a column

    Michael Talbot-Wilson <mtw@birdseye.view.net.au> — 2000-08-01T04:14:43Z

    >      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.

  6. Re: How to alter the size of a column

    Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> — 2000-08-01T04:21:31Z

    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.

  7. Re: How to alter the size of a column

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-01T16:51:35Z

    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
>