1

I have a remote postgresql server running with views in one of the schema. I need to copy the data from a view in the remote database server and put it in a new table in my local database server

Assume

remote

remote host: hostA

remote port: 5432

remote db: dbA

remote schema: schemaA

remote user: userA

remote password: passA

local

local host: hostB

local port: 5432

local db: dbB

local schema: schemaB

local user: userB

local password: passB

Vinit Khandelwal
  • 490
  • 8
  • 20
  • 1
    Hi - as views don’t contain data, do you mean copy the tables that the view uses or create a brand new table in the target that contains the data that the view displays? – NickW Dec 28 '22 at 10:38
  • Yes, I need to take the data the view is showing and create a new table in my local database and put it there – Vinit Khandelwal Dec 28 '22 at 10:41
  • from hostB you can access the data of hostA as a foreign table, see the [manual](https://www.postgresql.org/docs/current/ddl-foreign-data.html) – Edouard Dec 28 '22 at 13:16
  • Besides using [postgres_fdw](https://www.postgresql.org/docs/current/postgres-fdw.html) and using a remote connection per @Edouard comment you could just dump the underlying tables using [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html): `pg_dump -h -U -d -t ` . You can repeat `-t` to get multiple tables. If the tables already exist on the other database then add `-a` to get only the data. – Adrian Klaver Dec 28 '22 at 16:46

0 Answers0