Thread: Newly Created Source DB Table Not Reflecting into Destination Foreign Tables

On 12/11/18 1:44 AM, ramsiddu007 wrote:
> Dear Professionals,
>                             I hope you are doing well. I am looking 
> *Foreign Data Wrappers*. In this way I have created a data wrapper in 
> destination database. But at the time of creating wrapper, server and 
> user mappings, source database having only 1 table. After creating 
> wrapper.. those things. I have fired a select query which is extract all 
> the data from table in source database table, in destination database.
>                         This time I got whole data from source database 
> with the help of FDW. But this time I have created one more table in 
> source database, but this table not came into foreign table in 
> destination database. For this is there any other steps. Please let me know.

Not enough information to make a guess. Need:

1) Postgres version

2) The FDW you are using and to what destination database e.g. 
www.postgresql.org/docs/11/postgres-fdw.html to Postgres.

3) The configuration settings for the wrapper and the server and user 
mapping.

> 
> For the Kind request to all, please share any notes or pdf's or links 
> related to FDW, it will help to gain in depth knowledge for me.
> 
> I hope you will.
> 
> 
> -- 
> _*Best Regards:*_
> Ramanna Gunde
> 
> *Don't complain about the HEAT,*
> 
> ***PLANT A .*
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 12/11/18 7:28 AM, ramsiddu007 wrote:

Please reply to list also.
Ccing list.

> Thanks for giving reply.
> 1. Postgres Version: 11
> 
> 2. Below Databases are in Single Server:
>   Source Database (TestDB1):
> ---------------------------------------
> create table dept(deptno smallint, dname character varying(50), location 
> character varying(50));
>       insert into dept values (10, 'Product - Development', 
> 'Hyderabad'), (20, 'Product - Sales', 'Pune'), (30, 'Product - 
> Marketing', 'Bangalore');
> 
> Destination Database (TestDB2):
> ---------------------------------------------
> create extension postgres_fdw;
> 
> CREATE SERVER fdw_hr
>   FOREIGN DATA WRAPPER postgres_fdw
>   OPTIONS (dbname 'TestDB1', host '192.168.52.25', port '5432');
> 
> CREATE USER MAPPING for postgres
> SERVER fdw_hr
> OPTIONS (user 'postgres', password 'Qazwsx@12');
> 
> IMPORT FOREIGN SCHEMA "public" FROM SERVER fdw_hr INTO public;
> 
> Now dept table in foreign tables tree view in destination database.
> 
> I have ran below query in destination database
> select * from dept;
> 
> Good. Above query getting data.
> 
> After that i have created emp table in Destination Database like below
> 
> CREATE TABLE employee (empid int, eame character varying(20), deptno 
> smallint);
> 
> insert into employee values (101, 'Einstein', 10), (102, 'Saleem Ali', 
> 20), (103, 'Adison', 30);
> 
> after that emp table not came in foreign table tree view in destination 
> database.

https://www.postgresql.org/docs/11/sql-importforeignschema.html

"By default, all tables and views existing in a particular schema on the 
foreign server are imported"

The emp table did not exist when you did the initial IMPORT FOREIGN 
SCHEMA. You will need to import it using either CREATE FOREIGN TABLE or
IMPORT FOREIGN SCHEMA. NOTE: For IMPORT FOREIGN SCHEMA you can exclude 
the existing table using:

EXCEPT ( table_name [, ...] )

> 
> I have done those things only, no configurations done.
> 

> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com