Re: Newly Created Source DB Table Not Reflecting into DestinationForeign Tables - Mailing list pgsql-general

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