Replicating database changes

Question

Hi,

I want to "replicate" a database to an external service. For doing so I could just copy the entire database (SELECT * FROM TABLE).

If some changes are made (INSERT, UPDATE, DELETE), do I need to upload the entire database again or there is a log file describing these operations?

Thanks!

Answer 1

There are many ways to replicate a PostgreSQL database. In the current version 9.0 the PostgreSQL Global Development Group introduced two new rocks features called Hot Standby and Streaming Replication puting to PostgreSQL to a new level and introducing a built-in solution.

On the wiki, there is a completed review of the new PostgreSQL-9.0´s features: http://wiki.postgresql.org/wiki/PostgreSQL_9.0

There are other applications like Bucardo, Slony-I, Londiste (Skytools), etc,which you can use too.

Now, What are you want to do for log processing? What do you want exactly ? regards

Answer 2

It sounds like your "external service" is not just another database, so traditional replication might not work for you. More details on that service would be great so we can customize answers. Depending on how long you have to get data to your external service and performance demands of your application, some main options would be:

• Triggers: add INSERT/ UPDATE/ DELETE triggers that update your external service's data when your data changes (this could be rough on your app's performance but provide near real-time data for your external service)
• Log Processing: you can parse changes from the logs and use some level of ETL to make sure they'll run properly on your external service's data storage. I wouldn't recommend getting into this if you're not familiar with their structure for your particular DBMS.
• Incremental Diffs: you could run diffs on some interval (maybe 3x a day, for example) and have a cron job or scheduled task run a script that moves all the data in a big chunk. This prioritizes your app's performance over the external service.

If you choose triggers, you may be able to tweak an existing trigger-based replication solution to update your external service. I haven't used these so I have no idea how crazy that would be, just an idea. Some examples are Bucardo and Slony (http://www.slony.info).