Thread: dynamic SQL

On Wed, Sep 10, 2008 at 9:45 AM, davyd <davydky@gmail.com> wrote:
> Hi list,
> I need to create a sql where the name of a tabla is on a variable..
>
> vbuffer        varchar;
>
> SELECT     buffer
> INTO     vbuffer
> FROM     rutas r, unidades u
> WHERE    r.codigo_ruta = u.codigo_ruta AND
> u.codigo_uni = 'B15_AFF666';
>
>
> SELECT within(the_geom,(SELECT geomunion(the_geom) from var vbuffer)) as
> inside

You'll need to do it by building the query as a string in a plpgsql
function and then execute it.

Hi all,

        Can any one tell me how to replicate a db.

Regards
aftab

This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you
arenot the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not
tocopy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This
e-mailmay contain viruses. TSR Darashaw Ltd. has taken every reasonable precaution to minimize this risk, but is not
liablefor any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus
checksbefore opening the e-mail or attachment. TSR Darashaw Ltd. reserves the right to monitor and review the content
ofall messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the
TSRDarashaw Ltd's e-mail system. 

Do you mean Replication? There are already several posts about replication. What Platform are you using and what version of PostgreSQL are you using?

---

> From: AAlam@tsrdarashaw.com
> To: pgsql-admin@postgresql.org
> Date: Thu, 11 Sep 2008 09:39:48 +0530
> Subject: [ADMIN] replica of database
>
> Hi all,
>
> Can any one tell me how to replicate a db.
>
> Regards
> aftab
>
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. TSR Darashaw Ltd. has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. TSR Darashaw Ltd. reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the TSR Darashaw Ltd's e-mail system.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

---

MSN Technology brings you the latest on gadgets, gizmos and the new hits in the gaming market. Try it now!

---

This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. TSR Darashaw Ltd. has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. TSR Darashaw Ltd. reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the TSR Darashaw Ltd's e-mail system.

Aftab Alam wrote:
> Yes ,I want a replica of my db so that I can use it as failover
>
>
>
> Version 7.3 linux AS release 4 update 6

Version 7.3 is end of life and I don't know of *any* of the replication
technologies that will work with it.

Joshua D. Drake

---

From: AAlam@tsrdarashaw.com
To: aroravishal22@hotmail.com; pgsql-admin@postgresql.org
Date: Thu, 11 Sep 2008 10:14:42 +0530
Subject: RE: [ADMIN] replica of database

---

This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. TSR Darashaw Ltd. has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. TSR Darashaw Ltd. reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the TSR Darashaw Ltd's e-mail system.

---

MSN Technology brings you the latest on gadgets, gizmos and the new hits in the gaming market. Try it now!

On Wed, Sep 10, 2008 at 10:52 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> Aftab Alam wrote:
>>
>> Yes ,I want a replica of my db so that I can use it as failover
>>
>>
>> Version 7.3 linux AS release 4 update 6
>
> Version 7.3 is end of life and I don't know of *any* of the replication
> technologies that will work with it.

I think older versions of slony can talk to it, but I'd only use that
to upgrade in place to 8.3.  I'm pretty sure you'd have to make a stop
at 8.0 or 8.1 along the way...

Version 8.3 has pg_standby.  Does anyone know how well that works for failover? --- On Thu, 9/11/08, Scott Marlowe <scott.marlowe@gmail.com> wrote: From: Scott Marlowe <scott.marlowe@gmail.com> Subject: Re: [ADMIN] replica of database To: "Joshua D. Drake" <jd@commandprompt.com> Cc: "Aftab Alam" <AAlam@tsrdarashaw.com>, "Vishal Arora" <aroravishal22@hotmail.com>, "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org> Date: Thursday, September 11, 2008, 12:22 AM On Wed, Sep 10, 2008 at 10:52 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > Aftab Alam wrote: >> >> Yes ,I want a replica of my db so that I can use it as failover >> >> >> Version 7.3 linux AS release 4 update 6 > > Version 7.3 is end of life and I don't know of *any* of the replication > technologies that will work with it. I think older versions of slony can talk to it, but I'd only use that to upgrade in place to 8.3. I'm pretty sure you'd have to make a stop at 8.0 or 8.1 along the way... -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin

On Wed, Sep 10, 2008 at 09:52:41PM -0700, Joshua D. Drake wrote:
> Version 7.3 is end of life and I don't know of *any* of the replication
> technologies that will work with it.

The 1.0.x Slony releases work with 7.3.x, x>2.  I don't recommend
sticking with 7.3, however.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Thu, 2008-09-11 at 10:35 -0400, Andrew Sullivan wrote:
> On Wed, Sep 10, 2008 at 09:52:41PM -0700, Joshua D. Drake wrote:
> > Version 7.3 is end of life and I don't know of *any* of the replication
> > technologies that will work with it.
>
> The 1.0.x Slony releases work with 7.3.x, x>2.  I don't recommend
> sticking with 7.3, however.


If you ignore this very wise advise, and stick with 7.3 and decide to
give Slony 1.0.x a shot, be prepared for lots of late nights rebuilding
replicas and dealing if a whole multitude of other bugs it had then.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

On Thu, Sep 11, 2008 at 7:42 AM, Rex Mabry <rexmabry@yahoo.com> wrote:
> Version 8.3 has pg_standby.  Does anyone know how well that works for
> failover?

pg_standby is part of the Point in time recovery suite, which can be
used to create a cold (recovery on comand) or warm standby (continuous
recovery) server ready to take over with a few commands.  It's quite
different from slony, in that there's not hot read-only standby, so
there's no load balancing, however, it usually produces less load on
the master db, so that's a plus.

PITR is quite well tested and used in a lot of production
environments.  It has very different design objectives than slony or
pgpool or pgbouncer, which all allow you to have multiple live servers
at once.

jd@commandprompt.com ("Joshua D. Drake") writes:
> Aftab Alam wrote:
>> Yes ,I want a replica of my db so that I can use it as failover
>>
>>
>>
>> Version 7.3 linux AS release 4 update 6
>
> Version 7.3 is end of life and I don't know of *any* of the
> replication technologies that will work with it.

Slony-I version 1.1 can still work with 7.3.  We ceased support of 7.3
when we released v1.2, but recommended, at the time, using 1.1 for
7.3-compatibility.

  http://archives.postgresql.org/pgsql-announce/2006-10/msg00012.php
--
output = ("cbbrowne" "@" "linuxfinances.info")
http://linuxfinances.info/info/emacs.html
"A LISP programmer knows the value of everything, but the cost of
nothing." -- Alan J. Perlis

On Thu, Sep 11, 2008 at 8:30 AM, Chris Browne <cbbrowne@acm.org> wrote:
> jd@commandprompt.com ("Joshua D. Drake") writes:
>> Aftab Alam wrote:
>>> Yes ,I want a replica of my db so that I can use it as failover
>>>
>>>
>>>
>>> Version 7.3 linux AS release 4 update 6
>>
>> Version 7.3 is end of life and I don't know of *any* of the
>> replication technologies that will work with it.
>
> Slony-I version 1.1 can still work with 7.3.  We ceased support of 7.3
> when we released v1.2, but recommended, at the time, using 1.1 for
> 7.3-compatibility.
>

And as  buggy as slony 1.0 might have been, I ran it for about 2 years
in production replicating a HUGE amount of data daily with zero
failures. Of course, the servers were fast and reliable, so that might
have helped cover a lot of issues other people had for us.

On Thu, 2008-09-11 at 12:19 -0600, Scott Marlowe wrote:
> On Thu, Sep 11, 2008 at 8:30 AM, Chris Browne <cbbrowne@acm.org> wrote:
> > jd@commandprompt.com ("Joshua D. Drake") writes:
> >> Aftab Alam wrote:
> >>> Yes ,I want a replica of my db so that I can use it as failover
> >>>
> >>>
> >>>
> >>> Version 7.3 linux AS release 4 update 6
> >>
> >> Version 7.3 is end of life and I don't know of *any* of the
> >> replication technologies that will work with it.
> >
> > Slony-I version 1.1 can still work with 7.3.  We ceased support of 7.3
> > when we released v1.2, but recommended, at the time, using 1.1 for
> > 7.3-compatibility.
> >
>
> And as  buggy as slony 1.0 might have been, I ran it for about 2 years
> in production replicating a HUGE amount of data daily with zero
> failures. Of course, the servers were fast and reliable, so that might
> have helped cover a lot of issues other people had for us.

And when we ran it we had replica's getting corrupted due to bugs almost
weekly (based on a particular pattern of activity).  The edges are
there.  It did never lose data on us.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

bnichols@ca.afilias.info (Brad Nicholson) writes:
> On Thu, 2008-09-11 at 12:19 -0600, Scott Marlowe wrote:
>> On Thu, Sep 11, 2008 at 8:30 AM, Chris Browne <cbbrowne@acm.org> wrote:
>> > jd@commandprompt.com ("Joshua D. Drake") writes:
>> >> Aftab Alam wrote:
>> >>> Yes ,I want a replica of my db so that I can use it as failover
>> >>>
>> >>>
>> >>>
>> >>> Version 7.3 linux AS release 4 update 6
>> >>
>> >> Version 7.3 is end of life and I don't know of *any* of the
>> >> replication technologies that will work with it.
>> >
>> > Slony-I version 1.1 can still work with 7.3.  We ceased support of 7.3
>> > when we released v1.2, but recommended, at the time, using 1.1 for
>> > 7.3-compatibility.
>>
>> And as  buggy as slony 1.0 might have been, I ran it for about 2 years
>> in production replicating a HUGE amount of data daily with zero
>> failures. Of course, the servers were fast and reliable, so that might
>> have helped cover a lot of issues other people had for us.
>
> And when we ran it we had replica's getting corrupted due to bugs almost
> weekly (based on a particular pattern of activity).  The edges are
> there.  It did never lose data on us.

Mind you, that was on 7.4, not 7.3.

It's possible that:
 a) 7.4 did new stuff, so that those index corruptions would not have been
    present in 7.3, but also that
 b) 7.3 might have "data-eating problems not present in 7.4."

I *would* suggest using 1.1.[latest], of Slony-I, as that should have
the fewest issues, on the Slony-I side, of any version available to
run against PG 7.3, and should work the most cleanly.

But on the other hand, I'd *also* strongly urge using this to get off
of v7.3 and onto something a LOT newer, ASAP.  I think you can get to
PostgreSQL 8.1 using the 1.1 branch, which is usefully newer :-).
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/unix.html
Economists are still trying to figure out why the girls with the least
principle draw the most interest.