Обсуждение: Trigger Function and backup
Hello every one, I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in few days) on windows platform. I had tried using Slony-I for replication and was not able to create a cluster. After struggling for some time i decide to implement a way around to take differential backup. As the tables getting changed were very less. Here is what i intend to do: - Write a trigger for each of the tables in concern - Some how write a function which can copy / execute the same query in another temp Db on the same physical system (I have no idea how to do that) - Take a backup of temp DB which will be the differential backup of DB (We need to clear temp db after backup) Am i going in the right direction? Is there any way i can implement it. Any help will be really of great help Regards Nishkarsh%-| -- View this message in context: http://www.nabble.com/Trigger-Function-and-backup-tp24030638p24030638.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi,
I have found the following strangeness on Windows:
create table round_test (id int primary key, value double precision);
insert into round_test(id, value) values(1, 1.5);
insert into round_test(id, value) values(2, -1.5);
insert into round_test(id, value) values(3, 3.5);
select round(value) from round_test;
psql 8.2.13 returns
2
-2
4
But psql 8.3.3 returns
1
-1
3
What does cause this?
How can I avoid this incompatibility or perhaps bug?
Thanks,
Otto
I have found the following strangeness on Windows:
create table round_test (id int primary key, value double precision);
insert into round_test(id, value) values(1, 1.5);
insert into round_test(id, value) values(2, -1.5);
insert into round_test(id, value) values(3, 3.5);
select round(value) from round_test;
psql 8.2.13 returns
2
-2
4
But psql 8.3.3 returns
1
-1
3
What does cause this?
How can I avoid this incompatibility or perhaps bug?
Thanks,
Otto
On Mon, Jun 15, 2009 at 5:50 AM, Havasvölgyi Ottó<havasvolgyi.otto@gmail.com> wrote: > Hi, > > I have found the following strangeness on Windows: > > create table round_test (id int primary key, value double precision); > insert into round_test(id, value) values(1, 1.5); > insert into round_test(id, value) values(2, -1.5); > insert into round_test(id, value) values(3, 3.5); Firstly, I'm not sure what this message has to do with the thread "Trigger Function and backup". Secondly, please show your table definition. And Finally, the round() mathematical function is ill-defined. People have been arguing over this for decades. Choose either ceil() or floor() to get a more precise result with appropriate addition or subtraction of 0.5 to get the "rounding" effect you seem to desire.
On Mon, Jun 15, 2009 at 4:29 AM, Nishkarsh<nishkarsh_k@rediffmail.com> wrote: > Hello every one, > > I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in few > days) on windows platform. > > I had tried using Slony-I for replication and was not able to create a > cluster. > > After struggling for some time i decide to implement a way around to take > differential backup. As the tables getting changed were very less. > > Here is what i intend to do: > > - Write a trigger for each of the tables in concern > - Some how write a function which can copy / execute the same query in > another temp Db on the same physical system (I have no idea how to do that) > - Take a backup of temp DB which will be the differential backup of DB (We > need to clear temp db after backup) > > Am i going in the right direction? > Is there any way i can implement it. > Any help will be really of great help Generating a full trigger based replication system on your own is IMNSHO crazy. Slony is the best solution to this problem (trigger replication with postgres) that I know of, and is probably better than any one person to come up with in a reasonable amount of time. Probably, your best course of action if you need to get things running right now is to give slony another go (why did you not succeed?). Hand written trigger replication is ok if you need to copy, say, a couple of tables or you have some other very specific requirement. In particular, copying an insert to a mirror database with trigger function wrapping dblink is a snap (updates are more problematic, but doable). Of course, you need to figure out how to deal with schema updates and other issues that plague replication systems such as volatile data in cascading triggers (just to name one). General purpose trigger replication is a huge project... It sounds to me that what you really want is the 'hot standby' feature that unfortunately missed the cut for 8.4. Hot standby is probably the easiest way to mirror a database for purposes of read only querying. There are no triggers to worry about, just a few .conf settings and some other setup to get going (more or less, it isn't finalized yet). So maybe, waiting for hot standby (or even, digging up a hot standby patch and trying to apply it vs. 8.4 if your adventurous) is the answer. Another possibility is to look at statement level replication, like pgpool. merlin
Hi Merlin, thanks for the detailed input. As per ur suggestion i will try to implement Slony-I. I think i will need some help to do it. I am useing Postgres 8.3.7, on Windows. I was following the Slony-I example in the help for pgAdmin III. I am able to perform the steps from 1-7. Step 8 : create Slony-I cluster i am getting a msg in the interface "Slony-I creation script no available; only join possible" On doing some research i found some scripts to be copied (I was not able to find very clear instruction) or give slony-I path. i tried all that but was not able to move ahead. Can u plz guide me through &-(%-| Regards Nishkarsh Merlin Moncure-2 wrote: > > On Mon, Jun 15, 2009 at 4:29 AM, Nishkarsh<nishkarsh_k@rediffmail.com> > wrote: >> Hello every one, >> >> I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in few >> days) on windows platform. >> >> I had tried using Slony-I for replication and was not able to create a >> cluster. >> >> After struggling for some time i decide to implement a way around to take >> differential backup. As the tables getting changed were very less. >> >> Here is what i intend to do: >> >> - Write a trigger for each of the tables in concern >> - Some how write a function which can copy / execute the same query in >> another temp Db on the same physical system (I have no idea how to do >> that) >> - Take a backup of temp DB which will be the differential backup of DB >> (We >> need to clear temp db after backup) >> >> Am i going in the right direction? >> Is there any way i can implement it. >> Any help will be really of great help > > Generating a full trigger based replication system on your own is > IMNSHO crazy. Slony is the best solution to this problem (trigger > replication with postgres) that I know of, and is probably better than > any one person to come up with in a reasonable amount of time. > Probably, your best course of action if you need to get things running > right now is to give slony another go (why did you not succeed?). > > Hand written trigger replication is ok if you need to copy, say, a > couple of tables or you have some other very specific requirement. In > particular, copying an insert to a mirror database with trigger > function wrapping dblink is a snap (updates are more problematic, but > doable). Of course, you need to figure out how to deal with schema > updates and other issues that plague replication systems such as > volatile data in cascading triggers (just to name one). General > purpose trigger replication is a huge project... > > It sounds to me that what you really want is the 'hot standby' feature > that unfortunately missed the cut for 8.4. Hot standby is probably > the easiest way to mirror a database for purposes of read only > querying. There are no triggers to worry about, just a few .conf > settings and some other setup to get going (more or less, it isn't > finalized yet). So maybe, waiting for hot standby (or even, digging > up a hot standby patch and trying to apply it vs. 8.4 if your > adventurous) is the answer. > > Another possibility is to look at statement level replication, like > pgpool. > > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/Trigger-Function-and-backup-tp24030638p24051851.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, Jun 16, 2009 at 6:48 AM, Nishkarsh<nishkarsh_k@rediffmail.com> wrote: > > Hi Merlin, thanks for the detailed input. > > As per ur suggestion i will try to implement Slony-I. > > I think i will need some help to do it. > > I am useing Postgres 8.3.7, on Windows. > > I was following the Slony-I example in the help for pgAdmin III. I am able > to perform the steps from 1-7. Step 8 : create Slony-I cluster i am getting > a msg in the interface > > "Slony-I creation script no available; only join possible" > > On doing some research i found some scripts to be copied (I was not able to > find very clear instruction) or give slony-I path. i tried all that but was > not able to move ahead. > > Can u plz guide me through &-(%-| did you ask on the slony mailing list? I haven't looked at slony for a good while so you'll get better help there (unless somebody else wants to chime in). you also might want to check out irc. merlin
On Jun 16, 7:48 pm, nishkars...@rediffmail.com (Nishkarsh) wrote: > Hi Merlin, thanks for the detailed input. > > As per ur suggestion i will try to implement Slony-I. > > I think i will need some help to do it. > > I am useing Postgres 8.3.7, on Windows. > > I was following the Slony-I example in the help for pgAdmin III. I am able > to perform the steps from 1-7. Step 8 : create Slony-I cluster i am getting > a msg in the interface > > "Slony-I creation script no available; only join possible" > > On doing some research i found some scripts to be copied (I was not able to > find very clear instruction) or give slony-I path. i tried all that but was > not able to move ahead. > > Can u plz guide me through &-(%-| > > Regards > Nishkarsh > > > > Merlin Moncure-2 wrote: > > > On Mon, Jun 15, 2009 at 4:29 AM, Nishkarsh<nishkars...@rediffmail.com> > > wrote: > >> Hello every one, > > >> I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in few > >> days) on windows platform. > > >> I had tried using Slony-I for replication and was not able to create a > >> cluster. > > >> After struggling for some time i decide to implement a way around to take > >> differential backup. As the tables getting changed were very less. > > >> Here is what i intend to do: > > >> - Write a trigger for each of the tables in concern > >> - Some how write a function which can copy / execute the same query in > >> another temp Db on the same physical system (I have no idea how to do > >> that) > >> - Take a backup of temp DB which will be the differential backup of DB > >> (We > >> need to clear temp db after backup) > > >> Am i going in the right direction? > >> Is there any way i can implement it. > >> Any help will be really of great help > > > Generating a full trigger based replication system on your own is > > IMNSHO crazy. Slony is the best solution to this problem (trigger > > replication with postgres) that I know of, and is probably better than > > any one person to come up with in a reasonable amount of time. > > Probably, your best course of action if you need to get things running > > right now is to give slony another go (why did you not succeed?). > > > Hand written trigger replication is ok if you need to copy, say, a > > couple of tables or you have some other very specific requirement. In > > particular, copying an insert to a mirror database with trigger > > function wrapping dblink is a snap (updates are more problematic, but > > doable). Of course, you need to figure out how to deal with schema > > updates and other issues that plague replication systems such as > > volatile data in cascading triggers (just to name one). General > > purpose trigger replication is a huge project... > > > It sounds to me that what you really want is the 'hot standby' feature > > that unfortunately missed the cut for 8.4. Hot standby is probably > > the easiest way to mirror a database for purposes of read only > > querying. There are no triggers to worry about, just a few .conf > > settings and some other setup to get going (more or less, it isn't > > finalized yet). So maybe, waiting for hot standby (or even, digging > > up a hot standby patch and trying to apply it vs. 8.4 if your > > adventurous) is the answer. > > > Another possibility is to look at statement level replication, like > > pgpool. > > > merlin > > > -- > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > To make changes to your subscription: > >http://www.postgresql.org/mailpref/pgsql-general > > -- > View this message in context:http://www.nabble.com/Trigger-Function-and-backup-tp24030638p24051851... > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Hi Merlin, you could also consider giving "rubyrep" a try. Like Slony it is also an open source, trigger based, asynchronous replication solution. Focus of rubyrep is easy setup. Tutorial, screencast and other information are available on the project website: http://www.rubyrep.org Best Regards, Arndt Lehmann (Disclosure: I wrote rubyrep)
Arndt,
Your website says rubyrep runs on Linux and Windows - am I going to have difficulties if I want to try it on Solaris 10?
Andrew
Your website says rubyrep runs on Linux and Windows - am I going to have difficulties if I want to try it on Solaris 10?
Andrew
2009/6/23 Arndt Lehmann <arndt.lehmann@gmail.com>
On Jun 16, 7:48 pm, nishkars...@rediffmail.com (Nishkarsh) wrote:> > On Mon, Jun 15, 2009 at 4:29 AM, Nishkarsh<nishkars...@rediffmail.com>
> Hi Merlin, thanks for the detailed input.
>
> As per ur suggestion i will try to implement Slony-I.
>
> I think i will need some help to do it.
>
> I am useing Postgres 8.3.7, on Windows.
>
> I was following the Slony-I example in the help for pgAdmin III. I am able
> to perform the steps from 1-7. Step 8 : create Slony-I cluster i am getting
> a msg in the interface
>
> "Slony-I creation script no available; only join possible"
>
> On doing some research i found some scripts to be copied (I was not able to
> find very clear instruction) or give slony-I path. i tried all that but was
> not able to move ahead.
>
> Can u plz guide me through &-(%-|
>
> Regards
> Nishkarsh
>
>
>
> Merlin Moncure-2 wrote:
>> > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)> > wrote:
> >> Hello every one,
>
> >> I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in few
> >> days) on windows platform.
>
> >> I had tried using Slony-I for replication and was not able to create a
> >> cluster.
>
> >> After struggling for some time i decide to implement a way around to take
> >> differential backup. As the tables getting changed were very less.
>
> >> Here is what i intend to do:
>
> >> - Write a trigger for each of the tables in concern
> >> - Some how write a function which can copy / execute the same query in
> >> another temp Db on the same physical system (I have no idea how to do
> >> that)
> >> - Take a backup of temp DB which will be the differential backup of DB
> >> (We
> >> need to clear temp db after backup)
>
> >> Am i going in the right direction?
> >> Is there any way i can implement it.
> >> Any help will be really of great help
>
> > Generating a full trigger based replication system on your own is
> > IMNSHO crazy. Slony is the best solution to this problem (trigger
> > replication with postgres) that I know of, and is probably better than
> > any one person to come up with in a reasonable amount of time.
> > Probably, your best course of action if you need to get things running
> > right now is to give slony another go (why did you not succeed?).
>
> > Hand written trigger replication is ok if you need to copy, say, a
> > couple of tables or you have some other very specific requirement. In
> > particular, copying an insert to a mirror database with trigger
> > function wrapping dblink is a snap (updates are more problematic, but
> > doable). Of course, you need to figure out how to deal with schema
> > updates and other issues that plague replication systems such as
> > volatile data in cascading triggers (just to name one). General
> > purpose trigger replication is a huge project...
>
> > It sounds to me that what you really want is the 'hot standby' feature
> > that unfortunately missed the cut for 8.4. Hot standby is probably
> > the easiest way to mirror a database for purposes of read only
> > querying. There are no triggers to worry about, just a few .conf
> > settings and some other setup to get going (more or less, it isn't
> > finalized yet). So maybe, waiting for hot standby (or even, digging
> > up a hot standby patch and trying to apply it vs. 8.4 if your
> > adventurous) is the answer.
>
> > Another possibility is to look at statement level replication, like
> > pgpool.
>
> > merlin
>
> > --> > To make changes to your subscription:> View this message in context:http://www.nabble.com/Trigger-Function-and-backup-tp24030638p24051851...
> >http://www.postgresql.org/mailpref/pgsql-general
>
> --> Sent from the PostgreSQL - general mailing list archive at Nabble.com.> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
>
> --
Hi Merlin,
you could also consider giving "rubyrep" a try.
Like Slony it is also an open source, trigger based, asynchronous
replication solution.
Focus of rubyrep is easy setup.
Tutorial, screencast and other information are available on the
project website:
http://www.rubyrep.org
Best Regards,
Arndt Lehmann
(Disclosure: I wrote rubyrep)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Jun 23, 10:20 am, laconi...@gmail.com (Andrew Smith) wrote: > Arndt, > > Your website says rubyrep runs on Linux and Windows - am I going to have > difficulties if I want to try it on Solaris 10? > > Andrew > > 2009/6/23 Arndt Lehmann <arndt.lehm...@gmail.com> > > > On Jun 16, 7:48 pm, nishkars...@rediffmail.com (Nishkarsh) wrote: > > > Hi Merlin, thanks for the detailed input. > > > > As per ur suggestion i will try to implement Slony-I. > > > > I think i will need some help to do it. > > > > I am useing Postgres 8.3.7, on Windows. > > > > I was following the Slony-I example in the help for pgAdmin III. I am > > able > > > to perform the steps from 1-7. Step 8 : create Slony-I cluster i am > > getting > > > a msg in the interface > > > > "Slony-I creation script no available; only join possible" > > > > On doing some research i found some scripts to be copied (I was not able > > to > > > find very clear instruction) or give slony-I path. i tried all that but > > was > > > not able to move ahead. > > > > Can u plz guide me through &-(%-| > > > > Regards > > > Nishkarsh > > > > Merlin Moncure-2 wrote: > > > > > On Mon, Jun 15, 2009 at 4:29 AM, Nishkarsh<nishkars...@rediffmail.com> > > > > wrote: > > > >> Hello every one, > > > > >> I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in > > few > > > >> days) on windows platform. > > > > >> I had tried using Slony-I for replication and was not able to create a > > > >> cluster. > > > > >> After struggling for some time i decide to implement a way around to > > take > > > >> differential backup. As the tables getting changed were very less. > > > > >> Here is what i intend to do: > > > > >> - Write a trigger for each of the tables in concern > > > >> - Some how write a function which can copy / execute the same query in > > > >> another temp Db on the same physical system (I have no idea how to do > > > >> that) > > > >> - Take a backup of temp DB which will be the differential backup of DB > > > >> (We > > > >> need to clear temp db after backup) > > > > >> Am i going in the right direction? > > > >> Is there any way i can implement it. > > > >> Any help will be really of great help > > > > > Generating a full trigger based replication system on your own is > > > > IMNSHO crazy. Slony is the best solution to this problem (trigger > > > > replication with postgres) that I know of, and is probably better than > > > > any one person to come up with in a reasonable amount of time. > > > > Probably, your best course of action if you need to get things running > > > > right now is to give slony another go (why did you not succeed?). > > > > > Hand written trigger replication is ok if you need to copy, say, a > > > > couple of tables or you have some other very specific requirement. In > > > > particular, copying an insert to a mirror database with trigger > > > > function wrapping dblink is a snap (updates are more problematic, but > > > > doable). Of course, you need to figure out how to deal with schema > > > > updates and other issues that plague replication systems such as > > > > volatile data in cascading triggers (just to name one). General > > > > purpose trigger replication is a huge project... > > > > > It sounds to me that what you really want is the 'hot standby' feature > > > > that unfortunately missed the cut for 8.4. Hot standby is probably > > > > the easiest way to mirror a database for purposes of read only > > > > querying. There are no triggers to worry about, just a few .conf > > > > settings and some other setup to get going (more or less, it isn't > > > > finalized yet). So maybe, waiting for hot standby (or even, digging > > > > up a hot standby patch and trying to apply it vs. 8.4 if your > > > > adventurous) is the answer. > > > > > Another possibility is to look at statement level replication, like > > > > pgpool. > > > > > merlin > > > > > -- > > > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > > > To make changes to your subscription: > > > >http://www.postgresql.org/mailpref/pgsql-general > > > > -- > > > View this message in context: > >http://www.nabble.com/Trigger-Function-and-backup-tp24030638p24051851... > > > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > > -- > > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > > To make changes to your subscription: > >http://www.postgresql.org/mailpref/pgsql-general > > > Hi Merlin, > > > you could also consider giving "rubyrep" a try. > > Like Slony it is also an open source, trigger based, asynchronous > > replication solution. > > > Focus of rubyrep is easy setup. > > Tutorial, screencast and other information are available on the > > project website: > > http://www.rubyrep.org > > > Best Regards, > > Arndt Lehmann > > > (Disclosure: I wrote rubyrep) > > > -- > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > To make changes to your subscription: > >http://www.postgresql.org/mailpref/pgsql-general > > Hi Andrew, I never tried on Solaris. But as (the JRuby) version is based on Java (1.6), I cannot think of any reason why it wouldn't work. I would appreciate if you could give it a try and let me know how it goes. Best Regards, Arndt
hello Arndt, Thanks for the input. I was successfully able to implement "rubyrep", on a windows system (I used windows 7). Is there any way i can implement Master Slave replication by rubyrep. Regards Nishkarsh Arndt Lehmann-2 wrote: > > Hi Merlin, > > you could also consider giving "rubyrep" a try. > Like Slony it is also an open source, trigger based, asynchronous > replication solution. > > Focus of rubyrep is easy setup. > Tutorial, screencast and other information are available on the > project website: > http://www.rubyrep.org > > Best Regards, > Arndt Lehmann > > (Disclosure: I wrote rubyrep) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/Trigger-Function-and-backup-tp24030638p24217919.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.