PostgreSQL how to find any changes in the last n-m

问题:

I am writing a program that syncs PostgreSQL and MS SQL server databases (and adds some changes in this transition). With multi million records, it takes a long time, and loads server pretty bad with select *; it also takes more resources to parse unchanged records and validate them against MS SQL server.

Are there any logs in PostgreSQL that I can parse to find out the changes that took place in the last n-minutes? This would allow me to select only those records that I need to work; improving performance.

回答1:

Postgresql, find changes in the last n minutes:

Postgresql does not automatically store the time that rows were added/updated/deleted (it would really slow things down for postgresql to handle timestamps like this if you didn't want it to).

You'll have to do it yourself: Add a timestamp column to the table. When you insert a row into the table, have it update the timestamp column to the current_timestamp. When you are selecting the row, use a select statement that filters down where timestamp is greater than N minutes ago as follows:

Get rows where a timestamp is greater than a date:

SELECT * from yourtable 
WHERE your_timestamp_field > to_date('05 Dec 2000', 'DD Mon YYYY');

Get rows that have been changed in the last n minutes:

SELECT * from yourtable 
WHERE your_timestamp_field > current_timestamp - interval '5 minutes'

回答2:

You can use a trigger based approach described here:

http://wiki.postgresql.org/wiki/Audit_trigger

Essentially every table change fires a trigger which can write some info in a log table.