Thread: exporting data from one DB to another asynchronously
I have three problems that I need to solve that are closely related There are tasks: Task 1) Salesman access an application to submit information on a sales they made. This information includes data about the customer (name, address etc) data about the product (color, model, part number etc) and a picture. This is private information and I want to remove the information daily. 2) an email needs to be sent to the customer 3 days later that takes a form letter template and merges with it information about the transaction (customer name, product, salesman picture etc.). There will be a link on the email that directs the customer to a form where they can refer another potential customer to the salesman. For each referral they will be put in the draw for the monthly prize. 3) The customer will get login information to a website. Once the customer logs in they will be asked to complete a survey, they will be asked to referr others that may be interested in the product. For each referral the will be put in the draw for a monthly price. I would like to make these three separate applications for simplicity and reliability. The question is that I don't want to keep the customers information online in (Task 1) so I will download and clear each order every day. Every day I would like to move the daily transactions from Task 1 application to the Task 2 application so that email can be sent 3 days later. I would also like to move the data from Task 1 to Task 3 application. Is this possible with postgresql? Thanks
On Sun, 5 Mar 2006 13:24:04 -0800
"Nagita Karunaratne" <nagita.k@gmail.com> wrote:
> I have three problems that I need to solve that are closely related
>
> There are tasks:
>
> Task 1) Salesman access an application to submit information on a
> sales they made.
>
> This information includes data about the customer (name, address etc)
> data about the product (color, model, part number etc) and a picture.
>
> This is private information and I want to remove the information
> daily.
>
> 2) an email needs to be sent to the customer 3 days later that takes a
> form letter template and merges with it information about the
> transaction (customer name, product, salesman picture etc.). There
> will be a link on the email that directs the customer to a form where
> they can refer another potential customer to the salesman. For each
> referral they will be put in the draw for the monthly prize.
>
> 3) The customer will get login information to a website. Once the
> customer logs in they will be asked to complete a survey, they will be
> asked to referr others that may be interested in the product. For each
> referral the will be put in the draw for a monthly price.
>
> I would like to make these three separate applications for simplicity
> and reliability.
>
> The question is that I don't want to keep the customers information
> online in (Task 1) so I will download and clear each order every day.
>
> Every day I would like to move the daily transactions from Task 1
> application to the Task 2 application so that email can be sent 3 days
> later.
>
> I would also like to move the data from Task 1 to Task 3 application.
>
>
> Is this possible with postgresql?
>
> Thanks
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 1: if posting/reading
> through Usenet, please send an appropriate subscribe-nomail command
> to majordomo@postgresql.org so that your message can get through to
> the mailing list cleanly
Nagita,
You are asking the right questions of the wrong group. Postgresql is a
database. Or more completely and Object-Relational Database Management
System. Postgresql keeps track of data. Your "problems" or tasks are
more appropriately in the area of application. So if you want it to
postgresql can store your data that your salesman collects, that gets
e-mailed to customers, and that comes in via the web. But in and of
itself it won't collect, send, or provide a web interface.
Good luck.
John
--
Well, anyway, I was reading this James Bond book, and right away I
realized that like most books, it had too many words. The plot was the
same one that all James Bond books have: An evil person tries to blow
up the world, but James Bond kills him and his henchmen and makes love
to several attractive women. There, that's it: 24 words. But the guy
who wrote the book took *thousands* of words to say it.
Or consider "The Brothers Karamazov", by the famous Russian
alcoholic Fyodor Dostoyevsky. It's about these two brothers who kill
their father. Or maybe only one of them kills the father. It's
impossible to tell because what they mostly do is talk for nearly a
thousand pages. If all Russians talk as much as the Karamazovs did, I
don't see how they found time to become a major world power.
I'm told that Dostoyevsky wrote "The Brothers Karamazov" to
raise the question of whether there is a God. So why didn't he just
come right out and say: "Is there a God? It sure beats the heck out of
me." Other famous works could easily have been summarized in a few
words:
* "Moby Dick" -- Don't mess around with large whales because they
symbolize nature and will kill you.
* "A Tale of Two Cities" -- French people are crazy.
-- Dave Barry
> Nagita, > > You are asking the right questions of the wrong group. Postgresql is a > database. Or more completely and Object-Relational Database Management > System. Postgresql keeps track of data. Your "problems" or tasks are > more appropriately in the area of application. So if you want it to > postgresql can store your data that your salesman collects, that gets > e-mailed to customers, and that comes in via the web. But in and of > itself it won't collect, send, or provide a web interface. Hi John, I think I over-elaborated. Basically I would like to know if postgresql can move information from one instance to another periodically (daily). So if the source database has customer name, address, phone, model purchased, date of purchase, color etc. Can it move just the date of purchase, model, color to a second database in another application so it is ready when they login to that application to take a after sales survey? Thanks, NK
On Sun, 5 Mar 2006 14:20:09 -0800 "Nagita Karunaratne" <nagita.k@gmail.com> wrote: > Hi John, > > I think I over-elaborated. > > Basically I would like to know if postgresql can move information from > one instance to another periodically (daily). > > So if the source database has customer name, address, phone, model > purchased, date of purchase, color etc. > > Can it move just the date of purchase, model, color to a second > database in another application so it is ready when they login to that > application to take a after sales survey? > > Thanks, > NK > > ---------------------------(end of > broadcast)--------------------------- TIP 1: if posting/reading > through Usenet, please send an appropriate subscribe-nomail command > to majordomo@postgresql.org so that your message can get through to > the mailing list cleanly Nagita, I say this without mailice or spite. You seem to be a person who knows nothing of dance or carpentry but you're asking me if this dance floor can do the jitterbug. Postgresql, the database (or the floor) holds your data. The End. A database APPLICATION can move data back and forth from one database to another (or more likely I think in this case) present the information in a variety of ways by asking the database for it. Now postgres has tools built into it which can do a lot of things but it takes a dancer to use it. Think of it as having the steps to a variety of dances painted into the dance floor. I hope this terribly strained metaphor makes it a bit clearer. And honestly I am not attempting to ridicule you! Some full time computer programmers (and an ungodly number of wanna be's) have no clearer idea of what a database does. John -- You've been leading a dog's life. Stay off the furniture.
It's relatively straightforward (ie well documented) to implement a web application with a database on the same machine or even on a different machine. But I think if you have two or three applications that access subsets of the same data and it is private information (with penalties for any unwanted data disclosures) and you want it to be as secure as possible then it is much less simple. Someone suggested that I have a single Postgres instance with three tablespaces. If I have a separate machine running the Postgres DB and three applications accessing only the tables in their respective tablespace that may be easier and more secure to implement. It seems there is a way to migrate data between tablespaces with stored procedures and triggers and not have the application themselves be aware of the requirements of the others. This way I only have to worry about one database but any failure in that will bring down all three apps. Thanks, NK On 3/5/06, John Purser <jmpurser@gmail.com> wrote: > On Sun, 5 Mar 2006 14:20:09 -0800 > "Nagita Karunaratne" <nagita.k@gmail.com> wrote: > > > Hi John, > > > > I think I over-elaborated. > > > > Basically I would like to know if postgresql can move information from > > one instance to another periodically (daily). > > > > So if the source database has customer name, address, phone, model > > purchased, date of purchase, color etc. > > > > Can it move just the date of purchase, model, color to a second > > database in another application so it is ready when they login to that > > application to take a after sales survey? > > > > Thanks, > > NK > > > > ---------------------------(end of > > broadcast)--------------------------- TIP 1: if posting/reading > > through Usenet, please send an appropriate subscribe-nomail command > > to majordomo@postgresql.org so that your message can get through to > > the mailing list cleanly > > Nagita, > > I say this without mailice or spite. You seem to be a person who knows > nothing of dance or carpentry but you're asking me if this dance floor > can do the jitterbug. > > Postgresql, the database (or the floor) holds your data. The End. A > database APPLICATION can move data back and forth from one database to > another (or more likely I think in this case) present the information > in a variety of ways by asking the database for it. Now postgres has > tools built into it which can do a lot of things but it takes a dancer > to use it. Think of it as having the steps to a variety of dances > painted into the dance floor. > > I hope this terribly strained metaphor makes it a bit clearer. And > honestly I am not attempting to ridicule you! Some full time > computer programmers (and an ungodly number of wanna be's) have no > clearer idea of what a database does. > > John > -- > You've been leading a dog's life. Stay off the furniture. >
On Mar 5, 2006, at 9:56 PM, Nagita Karunaratne wrote: > Someone suggested that I have a single Postgres instance with three > tablespaces. If I have a separate machine running the Postgres DB and > three applications accessing only the tables in their respective > tablespace that may be easier and more secure to implement. This does not make sense. A tablespace is a way to setup storage for a single database. It does not buy you anything in terms of security or redundancy. If you really want separate databases and want to use only PostgreSQL to shuttle data between them, look at the contrib module called dblink (see FAQ 4.17 below). The more typical way to do this would be to setup your three databases and use a client-side utility to merge them. By client-side I just mean some program (e.g. in Perl, PHP or any other language that can access PostgreSQL) which is setup to access all three databases and merge information between them. I you have not yet built the web application for this, I highly recommend Drupal. You can setup multiple web sites easily, each with their own database. There is a cron facility you can invoke to sync your databases as needed. It would also be easy to setup each structure in a separate schema in the same database. You could setup permissions to provide the security you need, but have everything in one database making it easier to synchronize. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ===== 4.17) How do I perform queries using multiple databases? There is no way to query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave. contrib/dblink allows cross-database queries using function calls. Of course, a client can also make simultaneous connections to different databases and merge the results on the client side.
> > Nagita, > > > > You are asking the right questions of the wrong > group. Postgresql is a > > database. Or more completely and Object-Relational > Database Management > > System. Postgresql keeps track of data. Your > "problems" or tasks are > > more appropriately in the area of application. So > if you want it to > > postgresql can store your data that your salesman > collects, that gets > > e-mailed to customers, and that comes in via the > web. But in and of > > itself it won't collect, send, or provide a web > interface. > > Hi John, > > I think I over-elaborated. > > Basically I would like to know if postgresql can > move information from > one instance to another periodically (daily). > > So if the source database has customer name, > address, phone, model > purchased, date of purchase, color etc. > > Can it move just the date of purchase, model, color > to a second > database in another application so it is ready when > they login to that > application to take a after sales survey? > > Thanks, > NK Nagita, why do you want two databases? under normal circumstances, you'd just use one db to store your data and your application would run a SQL statement against it in order to pull the relevant data. if you *really* want to keep the data separate (security reasons, for example), it is best to design the schemas (or dbs) that way and use your application to enter the data into both dbs (real time, not transfer stuff all over the place), but only make the "public" data available to the dbuser defined in the app to run the sales survey. i've never done this, so don't assume what i just said necessarily makes sense, though. ;-) come to think of it, you could probably manage prmissions on tables to achieve the same result, should that be a requirement. good luck. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> > Nagita, why do you want two databases? under normal > circumstances, you'd just use one db to store your > data and your application would run a SQL statement > against it in order to pull the relevant data. I want to keep my three applications as separate as possible so one application can't possibly access information in another. I am leaning towards putting everything on one database using different schemas to keep the data separate. > if you *really* want to keep the data separate > (security reasons, for example), it is best to design > the schemas (or dbs) that way and use your application > to enter the data into both dbs (real time, not > transfer stuff all over the place), but only make the > "public" data available to the dbuser defined in the > app to run the sales survey. > That seems like a good idea. Possibly simpler too. > i've never done this, so don't assume what i just said > necessarily makes sense, though. ;-) > > come to think of it, you could probably manage > prmissions on tables to achieve the same result, > should that be a requirement. Like giving permissions to each app to specific tables. I guess that would be good as well. > good luck. > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com >