Problem 1, ERROR: permission denied to create database link
edb_admin=> CREATE DATABASE LINK epas_link
edb_admin-> CONNECT TO enterprisedb IDENTIFIED BY 'biganimal1234'
edb_admin-> USING postgres_fdw 'host=172.174.248.8 port=5444 dbname=postgres';
ERROR: permission denied to create database link
It requires superuser which is not provided in BigAnimal by default. Engage BigAnimal Support to get the superuser privilege for edb_admin.
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 'biganimal1234'
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;
Please note, no matter private network or public network is being used, BigAnimal allows outbound ports 5444 and 5432. You need to ensure the remote EPAS or Postgresql can access BigAnimal.
edb_admin=> CREATE DATABASE LINK pg_link
edb_admin-> CONNECT TO enterprisedb IDENTIFIED BY 'biganimal1234'
edb_admin-> USING postgres_fdw 'host=p-7m89c6pg7h.kjs1ac8czj9ebuwb.s.edbcloud.io port=5432 dbname=edb_admin';
CREATE DATABASE LINK