Copy Table from One Database to another database in the same cluster?

Question

Is there a way to copy table of one database to table of another database within the same cluster?

I have a cluster with multiple database. I have a master Database and multiple slave databases .

I will change one table in master and i want to copy to remaining database.

Is there a way to do it via postgresql console?

I use the following from the bash.

pg_dump -h localhost -U myuser -C -t my_table -d first_db>/tmp/table_dump 
psql -U myuser -d second_db</tmp/table_dump

I have to create a temporary dump file.

I use psycopg adapter to manage databases. Here I am looking forward for some solution.

• user1822: Use a foreign data wrapper: postgresql.org/docs/current/static/postgres-fdw.html

Answer 1

Inspired by https://stackoverflow.com/a/19697098/450812, you can create a pipe.

pg_dump -h localhost -U myuser -C -t my_table -d first_db | psql -U myuser -d second_db

If you need to enter passwords, you can:

PGPASSWORD=password1 pg_dump -h localhost -U myuser -C -t my_table -d first_db | PGPASSWORD=password2 psql -U myuser -d second_db