[SQL] Database synchronization
Hi, I have two postgres databases "Db1" and "Db2" on different linux servers. "DB1" is my master server and its data updates very frequently. My application access DB2 database, which in turn requires data from Db1 database. The same can be achieved in either of the following ways: 1. I can have the same tables from Db1 in the Db2 database and use some replication process to update these tables say using Slony-I. And then our application accesses the data from the single database. 2. Or I can use dblink to fetch the data from the master database only. My problem is my master database changes frequently and I always require the latest information. Please suggest which is the right approach in this situation. Thanks, Jyoti Seth ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Database synchronization
Try not to start a new thread (question) by replying to an old one - it can mean some people don't see your question. Jyoti Seth wrote: Hi, I have two postgres databases "Db1" and "Db2" on different linux servers. "DB1" is my master server and its data updates very frequently. My application access DB2 database, which in turn requires data from Db1 database. OK The same can be achieved in either of the following ways: 1. I can have the same tables from Db1 in the Db2 database and use some replication process to update these tables say using Slony-I. And then our application accesses the data from the single database. Yes 2. Or I can use dblink to fetch the data from the master database only. Yes also: 3. Write a layer in your application / between the application & database which routes queries to the correct database. My problem is my master database changes frequently and I always require the latest information. Well, if by "latest" you mean you can't have any delay at all you'll have to use #2 or #3. However, that could be slow if you need to join a lot of data from DB1 to DB2. Can you provide more details of what each contains? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Database synchronization
My master database contains prices of some products that vary after few hours and many other details that are also reqd in Db2 which doesn't change that frequently. My slave database contains the details of queries sent by the customers. These queries are to find the price of the product and other details. (Some part of the info reqd from Db1 varies frequently where some information is a kind of master data that doesn't vary frequently). So should I use all the ways i.e #2 or #3 to get price info and #1 (slony) for other details or there is some other better option Thanks, Jyoti -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 25, 2007 3:54 PM To: Jyoti Seth Cc: [email protected] Subject: Re: [SQL] Database synchronization Try not to start a new thread (question) by replying to an old one - it can mean some people don't see your question. I will take care in future. Jyoti Seth wrote: > Hi, > > I have two postgres databases "Db1" and "Db2" on different linux servers. > "DB1" is my master server and its data updates very frequently. > > My application access DB2 database, which in turn requires data from Db1 > database. OK > The same can be achieved in either of the following ways: > 1. I can have the same tables from Db1 in the Db2 database and use some > replication process to update these tables say using Slony-I. And then our > application accesses the data from the single database. Yes > 2. Or I can use dblink to fetch the data from the master database only. Yes also: 3. Write a layer in your application / between the application & database which routes queries to the correct database. > My problem is my master database changes frequently and I always require the > latest information. Well, if by "latest" you mean you can't have any delay at all you'll have to use #2 or #3. However, that could be slow if you need to join a lot of data from DB1 to DB2. Can you provide more details of what each contains? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Database synchronization
Jyoti Seth wrote: My master database contains prices of some products that vary after few hours and many other details that are also reqd in Db2 which doesn't change that frequently. My slave database contains the details of queries sent by the customers. These queries are to find the price of the product and other details. (Some part of the info reqd from Db1 varies frequently where some information is a kind of master data that doesn't vary frequently). So should I use all the ways i.e #2 or #3 to get price info and #1 (slony) for other details or there is some other better option If you can live with a time-lag measured in seconds, then slony will work just fine. If you can't then you'll want dblink. One other thing you might consider - if you attach "valid_from" time to each price then you can put them in place in advance, and make sure they are replicated before needed. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Assistance with a trigger
--- Paul Lambert <[EMAIL PROTECTED]> wrote: > I have some triggers in an MS SQL server database which I need to > copy > across to a PG database. I've not yet done triggers in PG so I was > hoping to get a little bit of a pointer on the first one as a place > to > start and work my way through from there. http://www.postgresql.org/docs/8.2/interactive/plpgsql.html I found the doc to be quite good. I'd start there, and then, if you have problems, email again. Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.yahoo.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Assistance with a trigger
chester c young wrote: http://www.postgresql.org/docs/8.2/interactive/plpgsql.html I found the doc to be quite good. I'd start there, and then, if you have problems, email again. I'd read that before... but another re-read triggered a thought pattern and I've got it working now - it's also a lot cleaner than the SQL Server implementation: CREATE OR REPLACE function fn_update_so_tran() RETURNS TRIGGER AS $trg_update_so_tran$ DECLARE BEGIN IF (NEW.tran_status = 'U') OR (NEW.tran_status = 'D') THEN UPDATE parts_purchasing SET qty_received=qty_received + NEW.qty_received, qty_invoiced = qty_invoiced + NEW.qty_invoiced, amt_invoiced = amt_invoiced + NEW.amt_invoiced, amt_received = amt_received + NEW.amt_received WHERE dealer_id = NEW.dealer_id AND so_tran_address = NEW.so_tran_address AND this_tran_address = so_tran_address; END IF; RETURN NULL; END; $trg_update_so_tran$ LANGUAGE plpgsql; ALTER FUNCTION fn_update_so_tran() OWNER TO "AutoDRS"; CREATE TRIGGER trg_update_so_tran AFTER INSERT OR UPDATE on parts_purchasing FOR EACH ROW EXECUTE PROCEDURE fn_update_so_tran(); I'm liking PostgreSQL more and more with each new thing I try :) Thanks muchly. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Database synchronization
Thanks a lot for your suggestion. It will help me in taking the decision. -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 25, 2007 6:10 PM To: Jyoti Seth Cc: [email protected] Subject: Re: [SQL] Database synchronization Jyoti Seth wrote: > My master database contains prices of some products that vary after few > hours and many other details that are also reqd in Db2 which doesn't change > that frequently. My slave database contains the details of queries sent by > the customers. > These queries are to find the price of the product and other details. (Some > part of the info reqd from Db1 varies frequently where some information is a > kind of master data that doesn't vary frequently). > > So should I use all the ways i.e #2 or #3 to get price info and #1 (slony) > for other details or there is some other better option If you can live with a time-lag measured in seconds, then slony will work just fine. If you can't then you'll want dblink. One other thing you might consider - if you attach "valid_from" time to each price then you can put them in place in advance, and make sure they are replicated before needed. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Assistance with a trigger
I have some triggers in an MS SQL server database which I need to copy across to a PG database. I've not yet done triggers in PG so I was hoping to get a little bit of a pointer on the first one as a place to start and work my way through from there. The trigger looks like thus: CREATE TRIGGER update_so_tran ON parts_purchasing FOR INSERT, UPDATE AS begin declare @found int declare @update varchar(10) declare @dealerid varchar(6) declare @trx_address varchar(10) declare @quan_received numeric(14,4) declare @amn_received numeric(14,4) declare @quan_invoiced numeric(14,4) declare @amn_invoiced numeric(14,4) declare @line_no int select @update = tran_status from inserted if @update = "U" or @update = "D" select @dealerid = dealer_id from inserted select @trx_address = so_tran_address from inserted select @quan_received = qty_received from inserted select @amn_received = amt_received from inserted select @quan_invoiced = qty_invoiced from inserted select @amn_invoiced = amt_invoiced from inserted select @line_no = line_number from inserted update parts_purchasing set qty_received=qty_received + @quan_received, qty_invoiced = qty_invoiced + @quan_invoiced, amt_invoiced = amt_invoiced + @amn_invoiced, amt_received = amt_received + @amn_received where dealer_id = @dealerid AND so_tran_address = @trx_address; end if end Basically in words, I have a parts_purchasing table which contains master order records as well as sub-records for the actual receival and invoicing of the orders. whenever a receival and invoice transaction comes through I need to update the qty_received, qty_invoiced, amt_received and amt_invoiced on the master record. It's pretty straight forward in SQL server but I'm having a little bit of difficulty in wrapping my head around it in PG. I know I have to create a function and call that from the trigger I'm just having a small bit of trouble in working out how to write this function. If anyone could give me a starting point that would be highly appreciated. Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
