As other have pointed out, you can take a pg_dump at anytime. You can provide arguments to pg_dump to only dump a subset of the database (like one table). Also mentioned is using a foreign data wrapper (FDW). yet another approach is to use the "copy to/from program" command to stream the table from one db to the other using netcat (nc):
On destination server:
\copy table_name to program "nc origin_server 9999";
On origin server:
\copy table_name from program ''nc -l 9999";
If your network is slower than your cpu you can compress the data by piping it through lz4 (or gzip/bzip whatever, but lz4 is fast both directions for streaming).
-Michel
Hi Team,
We have a requirement to copy a table from one database server to another database server. We are looking for a solution to achieve this with lesser downtime on Prod. Can you help us with this?
Table Size: 160GB
Postgresql Server Version: 9.5
