Am 21.02.2017 um 15:12 schrieb Adrian Klaver:
On 02/21/2017 12:53 AM, Thomas Güttler wrote:
I want to move table rows from one database to an central database.
You actually talking about moving from ~100 databases to the central database, correct?
Both run PostgreSQL.
Are all the Postgres instances the same version and what is the version or versions?
Yes, all run postgres, but the version can be different (but not much).
Satellite-DB 9.5 and 9.6 and central 9.6.
My use case looks like this:
There are N satellite databases in different data centers. N is about
100 at the moment.
There is one central database.
I need a way to reliably move rows from the satellite databases to the
central one
Two ways I can think of:
https://www.postgresql.org/docs/9.6/static/dblink.html
https://www.postgresql.org/docs/9.6/static/postgres-fdw.html
Since postgres_fdw is newer, I would focus on it, right?
If I understood it correctly, then there would be N (about 100) tables in the central DB.
What happens if there is a network outage (for about 3 minutes) during accessing a remote table?
Is there a Primary Key on the satellite tables or some way of determining unique rows?
The concrete schema is not specified up to now. But I guess UUID as primary key would be the best fit.
Or am I wrong?
Is there any existing overlap between the data in the central database and the satellite databases?
No, there won't be overlaps. Every satellite system creates its own rows.
How much data are you talking about moving from each database?
> How active are the satellite databases?
100k rows per day per satellite. Each row has only few bytes.
Moving of rows should happen every ten minutes.
- inserts can happen during syncing.
Can UPDATEs happen?
No, rows get created and moved and later deleted.
Thank you Adrian for your questions. It helped me to narrow down my problem.
Regards,
Thomas
--
Thomas Guettler http://www.thomas-guettler.de/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
