Skip to main content

How to create database link to remote EPAS Postgres in BigAnimal

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 '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;

mceclip0.png

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