Postgresql cross-library synchronization table and usage description of postgres

postgres_fdw module

PostgreSQL 9.3 add postgres_fdw extension for accessing remote tables

PostgreSQL 9.3 has added a postgres_fdw module, which has been integrated into the source code package. It is used to create postgres external tables.

Note: db_des is the target library, and developer_month_orders_data is the table name. It means to establish a FOREIGN association table with the same name from the query library a.

You can query the data in the target library. The following command is executed in the associated library that needs to be established.

The table in the target library must exist, that is, it is built first, otherwise the query will report a table error that cannot be found from the a library.

Alibaba Cloud RDS, database: PostgreSQL 9.4, cross-instance database does not support postgres_fdw to create external tables, which is a pitfall!

Alibaba Cloud technology responds to RDS with 10.0 version of postgresql to support cross-instances. Otherwise, you can only have external tables between different databases under the same instance.

-- Install postgres_fdw Plugin 
CREATE EXTENSION postgres_fdw;
 
-- Create a remote service 
CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw;
 
-- View Remote Services
select * from pg_foreign_server ;
 
-- Modify remote services 
alter server remote_server options (add hostaddr '11.216.10.215', 
       add port '5432', add dbname 'db_des');
 
-- SERVEREmpowerment 
grant usage on foreign server remote_server to postgres;
 
-- Create in the local databaseuser mapping 
CREATE USER MAPPING FOR postgres server remote_server options (user 'postgres', password 'xxxxx');
 
-- Create an enum
CREATE TYPE db_enum AS ENUM ('postgres', 'sqlserver', 'mysql');
 
-- View enum typeoid 
select oid from pg_type where typname='db_enum';
 
-- Create an external tabledeveloper_month_orders_data
CREATE FOREIGN TABLE developer_month_orders_data(
 id integer not null,
 developer_user_id integer,
 package_id integer,
 order_month date,
 create_datetime timestamp
) SERVER remote_server options (schema_name 'public',table_name 'developer_month_orders_data');
 
-- View external tables 
select * from developer_month_orders_data;

ddl maintenance operation:

drop user mapping for postgres server server_remote ; 
drop server server_remote; 
drop extension postgres_fdw ; 
drop foreign table test1;

Supplement: postgresql postgres_fdw cross-library query

1 Install the extension

create extension postgres_fdw;

2 Create a server locally and view the server function is to configure a remote connection information locally. The following configuration is to connect to the remote DB name is the postgres database

create server server_remote_rudy_01 foreign data wrapper postgres_fdw options(host ‘192.168.11.44',port ‘5432',dbname ‘vsphere_info');

Query:

select * from pg_foreign_server ;**

3 Create user matching information and view it locally

Postgres behind for is the username for local login execution, and the option stores the remote user password

create user mapping for postgres server server_remote_rudy_01 options(user ‘vsphere',password ‘viadmin');

4 Create external table locally and specify server

CREATE FOREIGN TABLE v1_cost(sample_time TIMESTAMP,datacenter_id int4,host_id int4 ,cost NUMERIC) server server_remote_rudy_01 options (schema_name ‘public',table_name ‘vi_cost');

5 – Import the specified table, or you can not import the specified table, or you can import the entire table below the schema (an optional step)

IMPORT FOREIGN SCHEMA public FROM SERVER server_remote_rudy_01 INTO public;
IMPORT FOREIGN SCHEMA public limit to(t1) FROM SERVER server_remote_rudy_01 INTO public;

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.

Postgresql cross-library synchronization table and usage description of postgres_fdw

postgres_fdw module