I need to do the following. In a LAN there are 4 postgres boxes each having a small postgres database. These servers are gathering data from the internet and insert them as records in a table in their db. I need to setup a server that will gather the data from each table from those 4 servers and merge the records in one table of the same structure. This table will be then used for centralized processing. The 4 servers fill their table at about 10 records per minute and there is the requirement that at least once a minute each server's new data must be propagated to the "master". Data in the servers must be held for some time (~ one month). The tables have a field of type UUID so uniqueness across all servers is not a problem. But I dont know what would be dest practice in my scenario:
Use dblink on master to pull from servers, with a psql crontab or pgAgent. In this scenario there is the following problem. I need somehow to update servers records that they have been pulled so that the dblink query can have a condition so that it doesnt fetch the whole table each time. There is the advantage that I can fetch all the rows and insert the ones that dont exist, but it is not acceptable that I will have to query for the whole table.Can I somehow do an update with dblink? Is generally this solution recommended for my scenario?
Use COPY TO at the servers, mark the rows, scp the file to the master and then COPY FROM at the master. I dont have any strong objection about this, other than it will be more complicated than the above since it needs separate recurring jobs in the 4 servers and in the master and I would like more experience people to comment on it, before I use it.
The obvious solution of having the servers update the master directly is not an option because it creates a single point of failure for the 4 servers and anyway it is a given condition to me that the servers update their own database instance.
Any comments, other solutions?
(All the servers are fedora 17 32bit / postgres 9.2)
EDIT: I started investigating replication solutions but they seem overkill for my case since I dont need any fancy failover or conflict management, but maybe I am mistaken.