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@xxxxxxxxxxx
