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