3

I have two PostgreSQL standalone servers A and B. From server A I need to sync only one database and table to server B. How should I proceed?

2
  • 1
    You can use logical replication using pglogical: 2ndquadrant.com/en/resources/pglogical – a_horse_with_no_name Apr 11 '17 at 12:17
  • Can you provide more context for "sync" here? Postgres has a lot of solutions for this. If you want to do some kind of daily backup workflow, perhaps you could use pg_dump or COPY. It's also possible for two Postgres servers to connect to each other live with dblink, which lets you access remote data without having to clone it. – Karew Apr 13 '17 at 4:40
6

As answered before, you have loads of different ways of doing this. Here are a few:

  • External logical replication: projects like Slony, Londiste, Bucardo can replicate the needed objects through triggers;
  • Extensions like pglogical;
  • In-core logical replication which is available since Postgres 10
  • Foreign Data Wrappers: you could use postgres_fdw to make your table readable from a remote server. This means connecting to the other table, not replicating it.
  • Backup and restore: you could cron a pg_dump and restore of your table from server A to B.
  • Physical Replication: you could replicate the whole A cluster on cluster B using streaming replication. This could give you a nice disaster recovery solution. On the B server, you could then issue one of the solutions above to a second instance on the server B.
  • and probably several other ways :-).
0

If you want to sync a single database or table, then you can use Bucardo. This will help you to replicate a single table, single database, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.