Need to Sync postgresql DB to remote

Question

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?

Answer 1

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 :-).

Answer 2

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.