Thread: Syncing Data to Production DB Server
Dear All, I want some views on the below requirements : 1. I have a Postgres DB server with 25 GB database. It has more than 110 tables. I am using Postgresql 8.3 on a CentOs. 2. I have another system laptop that contains the same database but it is for testing purposes. What I want ? If someone inserts some data in the laptop ( same database and tables as Postgres DB server ) , that data would synk to my Production Database server. I thought the replication would help but it is not necessary that the laptop is connected to LAN always and if by mistake issue drop command, all goes in vain . PLease guide me some ways or solutions . Thanks
On Aug 18, 2011, at 5:36 AM, Adarsh Sharma wrote: > Dear All, > > I want some views on the below requirements : > > 1. I have a Postgres DB server with 25 GB database. It has more than 110 tables. > I am using Postgresql 8.3 on a CentOs. > 2. I have another system laptop that contains the same database but it is for testing purposes. > > What I want ? If someone inserts some data in the laptop ( same database and tables as Postgres DB server ) , that datawould synk to my Production Database server. > > I thought the replication would help but it is not necessary that the laptop is connected to LAN always and if by mistakeissue drop command, all goes in vain . > > PLease guide me some ways or solutions . So.... to rephrase (and simplify): - you have a main database - you have another database which is a superset of the main database - you want both databases to be able to accept inserts, deletes, etc. - you want to replicate inserts (only?) on the overlapping tables of the second database back to the main database (or doyou want bi-directional replication?) - these databases will often not be able to talk to each other It sounds like Buccardo *might* be a solution for you. It allows for multi-master operation, which is what you seem to belooking for. But if you want, say, inserts only to be replicated, and not deletes, you probably need to look into writing your own replicationsystem in your application. If you keep the requirements strict enough it's really not that hard.
Ben Chobot wrote:
Now, I take complete backup of the database from demo machine & restore it in production server, which is very unusual way.
Thanks
On Aug 18, 2011, at 5:36 AM, Adarsh Sharma wrote:Dear All, I want some views on the below requirements : 1. I have a Postgres DB server with 25 GB database. It has more than 110 tables. I am using Postgresql 8.3 on a CentOs. 2. I have another system laptop that contains the same database but it is for testing purposes. What I want ? If someone inserts some data in the laptop ( same database and tables as Postgres DB server ) , that data would synk to my Production Database server. I thought the replication would help but it is not necessary that the laptop is connected to LAN always and if by mistake issue drop command, all goes in vain . PLease guide me some ways or solutions .Thanks Ben,
No, both databases are same but on different systems.So.... to rephrase (and simplify): - you have a main database - you have another database which is a superset of the main database
Fore.g: One is Production Server and the other is simple demo machine. If someone inserts some data in demo machine, I want that data to be sync to my production server.- you want both databases to be able to accept inserts, deletes, etc.
Now, I take complete backup of the database from demo machine & restore it in production server, which is very unusual way.
I want a simple technique through which I update my production server easily.- you want to replicate inserts (only?) on the overlapping tables of the second database back to the main database (or do you want bi-directional replication?) - these databases will often not be able to talk to each other It sounds like Buccardo *might* be a solution for you. It allows for multi-master operation, which is what you seem to be looking for. But if you want, say, inserts only to be replicated, and not deletes, you probably need to look into writing your own replication system in your application. If you keep the requirements strict enough it's really not that hard.
Thanks
On 08/18/11 9:03 PM, Adarsh Sharma wrote: > I want a simple technique through which I update my production server > easily. what if there's been data changes on the production server and different changes on the demo laptop? how do you plan on reconciling those differences? -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Hi Michael,
I think you misunderstood my problem.
I have a demo system and the data is inserted in this system.
Simply I want this newly inserted data to be synk to my production server.
Taking pg_dump of cumbersome daily.
I reserached & find some proprietary solution but I think there may be other solutions too.
Thanks
Michael Nolan wrote:
I think you misunderstood my problem.
I have a demo system and the data is inserted in this system.
Simply I want this newly inserted data to be synk to my production server.
Taking pg_dump of cumbersome daily.
I reserached & find some proprietary solution but I think there may be other solutions too.
Thanks
Michael Nolan wrote:
On Thu, Aug 18, 2011 at 11:03 PM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:I want a simple technique through which I update my production server easily.
What I do with a similar sized database is do a pg_dumpall on the production server and
restore it to the laptop. Because the production server is around 950 miles from my office, it usually takes me longer to copy the dumpall file across the Internet than it does to restore it on the laptop.
I do this about twice a month.
I find having a test database that is a week or two out of date doesn't affect most development work. In fact, being able to restore the test database to a known state repeatedly has come in handy for testing some scenarios. Your situation may be different.
--
Mike Nolan
You can use Talend or Navicat for syncing the data as per your needs without much complexity in writing a data sync application.
You have to purchase license for navicat but you can talend for free and it also supports many other database systems.
Chaitanya Kulkarni
You have to purchase license for navicat but you can talend for free and it also supports many other database systems.
Chaitanya Kulkarni
On Fri, Aug 19, 2011 at 10:18 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
Hi Michael,
I think you misunderstood my problem.
I have a demo system and the data is inserted in this system.
Simply I want this newly inserted data to be synk to my production server.
Taking pg_dump of cumbersome daily.
I reserached & find some proprietary solution but I think there may be other solutions too.
Thanks
Michael Nolan wrote:On Thu, Aug 18, 2011 at 11:03 PM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:I want a simple technique through which I update my production server easily.
What I do with a similar sized database is do a pg_dumpall on the production server and
restore it to the laptop. Because the production server is around 950 miles from my office, it usually takes me longer to copy the dumpall file across the Internet than it does to restore it on the laptop.
I do this about twice a month.
I find having a test database that is a week or two out of date doesn't affect most development work. In fact, being able to restore the test database to a known state repeatedly has come in handy for testing some scenarios. Your situation may be different.
--
Mike Nolan
I used Navicat free version many times. As you rightly said, we have to purchase license for Data Synchroniztion.Also , I cannot able to find Talend for Linux.
Is it works only for Windows. I find one component Talend MDM for linux.
Can it satisfy my requirements ?
Thanks
c k wrote:
You can use Talend or Navicat for syncing the data as per your needs without much complexity in writing a data sync application.
You have to purchase license for navicat but you can talend for free and it also supports many other database systems.
Chaitanya KulkarniOn Fri, Aug 19, 2011 at 10:18 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:Hi Michael,
I think you misunderstood my problem.
I have a demo system and the data is inserted in this system.
Simply I want this newly inserted data to be synk to my production server.
Taking pg_dump of cumbersome daily.
I reserached & find some proprietary solution but I think there may be other solutions too.
Thanks
Michael Nolan wrote:On Thu, Aug 18, 2011 at 11:03 PM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:I want a simple technique through which I update my production server easily.
What I do with a similar sized database is do a pg_dumpall on the production server and
restore it to the laptop. Because the production server is around 950 miles from my office, it usually takes me longer to copy the dumpall file across the Internet than it does to restore it on the laptop.
I do this about twice a month.
I find having a test database that is a week or two out of date doesn't affect most development work. In fact, being able to restore the test database to a known state repeatedly has come in handy for testing some scenarios. Your situation may be different.
--
Mike Nolan
Yes, sure.
I have already used it. You have to create database connections to both of your databases. Then create a job to sync data per table as per your needs. It should not be too hard. Once you job is created then test it for any errors. If required you have to use data transformations. Once your test succeed, you can deploy it to java based application server. You can get more details about it from Talend's documentation. You can also run your job manually whenever you add data to your demo database. It will be not too hard.
You can also use stored functions on you demo database to send the updated data to your main database by using dblink. When ever you update data in demo db, you can execute these function(s) to update data in main db.
Hope that will be helpful.
Regards,
Chaitany Kulkarni
I have already used it. You have to create database connections to both of your databases. Then create a job to sync data per table as per your needs. It should not be too hard. Once you job is created then test it for any errors. If required you have to use data transformations. Once your test succeed, you can deploy it to java based application server. You can get more details about it from Talend's documentation. You can also run your job manually whenever you add data to your demo database. It will be not too hard.
You can also use stored functions on you demo database to send the updated data to your main database by using dblink. When ever you update data in demo db, you can execute these function(s) to update data in main db.
Hope that will be helpful.
Regards,
Chaitany Kulkarni
On Fri, Aug 19, 2011 at 12:37 PM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
Thanks CK , I configured Talend but I m not able to understand how we can get data synk to our DB servers.
Any views on that.
c k wrote:You can use Talend or Navicat for syncing the data as per your needs without much complexity in writing a data sync application.
You have to purchase license for navicat but you can talend for free and it also supports many other database systems.
Chaitanya KulkarniOn Fri, Aug 19, 2011 at 10:18 AM, Adarsh Sharma <adarsh.sharma@orkash.com <mailto:adarsh.sharma@orkash.com>> wrote:
Hi Michael,
I think you misunderstood my problem.
I have a demo system and the data is inserted in this system.
Simply I want this newly inserted data to be synk to my production
server.
Taking pg_dump of cumbersome daily.
I reserached & find some proprietary solution but I think there
may be other solutions too.
Thanks
Michael Nolan wrote:
On Thu, Aug 18, 2011 at 11:03 PM, Adarsh Sharma<adarsh.sharma@orkash.com <mailto:adarsh.sharma@orkash.com>> wrote:
I want a simple technique through which I update my
production server easily.
What I do with a similar sized database is do a pg_dumpall on the
production server and
restore it to the laptop. Because the production server is
around 950 miles from my office, it usually takes me longer to
copy the dumpall file across the Internet than it does to restore
it on the laptop.
I do this about twice a month.
I find having a test database that is a week or two out of date
doesn't affect most development work. In fact, being able to
restore the test database to a known state repeatedly has come in
handy for testing some scenarios. Your situation may be
different. --
Mike Nolan