posgresql postgres_fdw使用

一个业务由于需要跨库访问表，选择使用外部表效率高一些，这里记录一下

这里是avcp_bill库访问avcp_work库里的相关表，这里用户名和数据库名一致

> \c avcp_bill postgresYou are now connected to database "avcp_bill" as user "postgres".
建立extension# create extension postgres_fdw;CREATE EXTENSION# select * from pg_extension ;   extname    | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition --------------+----------+--------------+----------------+------------+-----------+-------------- plpgsql      |       10 |           11 | f              | 1.0        |           |  postgres_fdw |       10 |         2200 | t              | 1.0        |           | (2 rows)# select * from pg_foreign_data_wrapper;   fdwname    | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions --------------+----------+------------+--------------+--------+------------ postgres_fdw |       10 |    3253178 |      3253179 |        | (1 row)# \dx                               List of installed extensions     Name     | Version |   Schema   |                    Description                     --------------+---------+------------+---------------------------------------------------- plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers(2 rows)
赋予avcp_bill使用权限# grant usage on foreign data wrapper postgres_fdw to avcp_bill;
#\c avcp_bill avcp_bill
创建server# create server avcp_work_fdw foreign data wrapper postgres_fdw options(host '127.0.0.1',port '6789',dbname 'avcp_work');
创建user mapping# CREATE USER MAPPING FOR public SERVER avcp_work_fdw OPTIONS (user 'avcp_work', password 'xxxxx');# select * from pg_user_mappings;  umid   |  srvid  |    srvname    | umuser | usename |                   umoptions                    ---------+---------+---------------+--------+---------+------------------------------------------------ 3253183 | 3253181 | avcp_work_fdw |      0 | public  | {user=avcp_work,password=xxxxxx} 创建相关外部表 CREATE FOREIGN TABLE avcp_user_recharge_log_fdw (    id integer DEFAULT nextval('avcp_user_recharge_log_id_seq'::regclass) NOT NULL,    pay_orderid character varying,    paytype integer DEFAULT 0 NOT NULL,    error_message character varying,    pointid integer DEFAULT 0 NOT NULL,    imsi character varying(30),    et character varying(30),    create_time timestamp without time zone DEFAULT now() NOT NULL,    charge_point character varying,    channel_id character varying,    user_price integer DEFAULT 0 NOT NULL,    roomid integer,    seqid bigint,    transationid character varying,    smsc character varying(20),    dexid character varying,    dexver character varying,    card_type integer,    provice_id integer,    user_id character varying(200),    seqid_str character varying(30),    imei character varying(30)) server avcp_work_fdw options (schema_name 'avcp_work',table_name 'avcp_user_recharge_log');相关报错处理：ERROR:  relation "avcp_user_recharge_log_id_seq" does not exist
序列不存在，无法成功建立外部表，那么我们建立序列：
>create sequence avcp_user_recharge_log_id_seq;
> select * from avcp_pay_order_log_fdw limit 1;ERROR:  password is requiredDETAIL:  Non-superuser cannot connect if the server does not request a password.HINT:  Target server's authentication method must be changed
由于默认是trust，但是我们外部表的连接需要密码，所以加入以下MD5验证，reload即可。
# IPv4 local connections:host   avcp_work       avcp_work        127.0.0.1/32             md5host    all             all             127.0.0.1/32            trustpg_ctl -D $PGDATA reload