Обсуждение: 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:

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, 

So.... to rephrase (and simplify):

- you have a main database
- you have another database which is a superset of the main database 

No, both databases are same but on different systems.

- you want both databases to be able to accept inserts, deletes, etc. 

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.
Now, I take complete backup of the database from demo machine & restore it in production server, which is very unusual way.

- 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. 

I want a simple technique through which I update my production server easily.


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:

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

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 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

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