Re: postgres hit url?

From: Julie Goldberg <julie(at)empowerengine(dot)com>
To: Brian Rice <brice(at)bigloops(dot)com>
Cc: Tony Alderman <tonyalderman(at)gmail(dot)com>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, "seapug(at)postgresql(dot)org" <seapug(at)postgresql(dot)org>
Subject: Re: postgres hit url?
Date: 2014-04-26 01:10:44
Message-ID: 164A07D6-5287-4AE6-9E55-1E4A2A93CD0B@empowerengine.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: seapug

Hi Brian,

Thanks for the RDS suggestion.

I definitely don't want to take a full snapshot every time data gets loaded. We're getting nightly data dumps from another server, but it's transient data that we're going to show for a day and then replace again the next night. We may be getting data a half dozen times in the night, each set of which will go into a different table to be processed slightly differently, and we won't necessarily know what is the last set of data.

That said, if I can use postgres triggers to kick off an Amazon event notification, this solution might work very well. Is that feasible?

Thanks

Julie

--
Julie Goldberg
Principal
Empower Engine

(206) 632-7647
julie(at)empowerengine(dot)com

On Apr 25, 2014, at 5:52 PM, Brian Rice <brice(at)bigloops(dot)com> wrote:

> Hey, Julie, I'm glad you're giving RDS/Postgres a try. I hope it works out for you. Do share your experiences.
>
> Anyway, here's an RDS-specific idea for how to do what you want to do. Caution: I haven't tested this. After your data load is complete, that would be a great time to take a snapshot backup, wouldn't it? You could use boto to launch a database snapshot operation after the load completes. Now, there is a DB Event Notification available from RDS that indicates, "Hey, your backup is complete." As the target of this notification, you can register a URL. You could register your arbitrary URL as the recipient of that notification.
>
> Just an idea. Here's more info on RDS DB Event Notifications:
>
> http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Events.html#
>
> Good luck!
>
> --
> Brian Rice
> Product Marketing Manager, Amazon RDS
> briarice(at)amazon(dot)com
>
>
> On Fri, Apr 25, 2014 at 5:25 PM, Julie Goldberg <julie(at)empowerengine(dot)com> wrote:
> Thanks all.
>
> Tony's github link looks great. We recently migrated to Amazon RDS for our database server, so we can dynamically resize and use their backup procedures. Unfortunately, Amazon doesn't give you shell access to the db server, so we can't install stuff like that.
>
> Joshua's point that I should try pl/perl or pl/python was helpful too. Unfortunately, again, I can't import any of the standard perl libraries used for grabbing a URL. I tried HTTP::Request, NET::LDAP and NET::LDAPS.
>
> Any other ideas? If not, I'm going to see if we can get the folks sending us data to make an API call to our web server after the db dump. If that fails too, I'll have to use a cron job that checks if my data has been updated.
>
> Thanks
>
> Julie
>
> --
> Julie Goldberg
> Principal
> Empower Engine
>
> (206) 632-7647
> julie(at)empowerengine(dot)com
>
>
>
>
> On Apr 25, 2014, at 4:00 PM, Tony Alderman <tonyalderman(at)gmail(dot)com> wrote:
>
>> Here is an extension that someone wrote to do exactly that, but I've never used it and can't vouch for it...
>>
>> https://github.com/pramsey/pgsql-http
>>
>> On Apr 25, 2014, at 3:45 PM, Julie Goldberg <julie(at)empowerengine(dot)com> wrote:
>>
>>> Hi there,
>>>
>>> I haven't made it to a SeaPug meeting in a few months, but I have a postgres challenge, so I'm hoping someone on this list can help me.
>>>
>>> How do I write a pl/pgsql function that will have postgres hit an arbitrary url?
>>>
>>> I feel like there's got to be a way to do that, but it's very hard to Google for.
>>>
>>> My basic problem is this. I have an external db server that is adding data to my db, and whenever that happens, I want to trigger a job in my django app to process it. Redoing the job in sql would be harder to maintain. I could write a cron job that checks every hour if there's new data and processes it, but a trigger is the more appropriate way to handle this and gets the updated data sooner. I found postgres' notifications system, but I would have to set up a specific server that listens to postgres. Ultimately I want a hidden page or api call built into my django app that the db calls out to.
>>>
>>> Thanks in advance
>>>
>>> Julie
>>> --
>>> Julie Goldberg
>>> Principal
>>> Empower Engine
>>>
>>> (206) 632-7647
>>> julie(at)empowerengine(dot)com
>>>
>>>
>>>
>>>
>
>

In response to

Browse seapug by date

  From Date Subject
Next Message Ralph Smith 2014-05-21 22:01:32 Re: TEKsystems - PostgreSQL Question
Previous Message Julie Goldberg 2014-04-26 01:00:32 Re: postgres hit url?