Skip to main content

How to create database link to remote EPAS Postgres in EDB Postgres AI

Shanshan Zhao
  • Updated

Problem 1, ERROR: permission denied to create database link

edb_admin=> CREATE DATABASE LINK epas_link
edb_admin-> CONNECT TO enterprisedb IDENTIFIED BY '<password>'
edb_admin-> USING postgres_fdw 'host=172.174.248.8 port=5444 dbname=postgres';
ERROR: permission denied to create database link

Superuser privilege is required.

 

Problem 2, ERROR: foreign-data wrapper "postgres_fdw" does not exist

edb_admin=> CREATE DATABASE LINK epas_link
CONNECT TO enterprisedb IDENTIFIED BY 'biganimal1234'
USING postgres_fdw 'host=172.174.248.8 port=5444 dbname=postgres';
ERROR: foreign-data wrapper "postgres_fdw" does not exist

edb_admin=> create extension postgres_fdw;
CREATE EXTENSION

edb_admin=> CREATE DATABASE LINK epas_link
CONNECT TO enterprisedb IDENTIFIED BY '<password>'
USING postgres_fdw 'host=172.174.248.8 port=5444 dbname=postgres';
CREATE DATABASE LINK

It's documented at CREATE PUBLIC DATABASE LINK, and install extension with the below command

create extension postgres_fdw;

mceclip0.png

EDB Postgres AI Cloud Service allows outbound ports 5444 and 5432(for both private and public setting).  Please ensure the remote EPAS or PostgreSQL can access the cluster.

edb_admin=> CREATE DATABASE LINK pg_link
edb_admin-> CONNECT TO enterprisedb IDENTIFIED BY '<password>'
edb_admin-> USING postgres_fdw 'host=p-7m89c6pg7h.kjs1ac8czj9ebuwb.s.edbcloud.io port=5432 dbname=edb_admin';
CREATE DATABASE LINK