PG连接Oracle报错解决

postgres=# select * from t1;

ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle

postgres=# select * from t1;

ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle

DETAIL:

postgres=#

postgres=# SELECT pg_backend_pid();

pg_backend_pid

----------------

97376

epoll_wait(3, [{EPOLLIN, {u32=41936840, u64=41936840}}], 1, -1) = 1

recvfrom(10, "Q\0\0\0\26select * from t1;\0", 8192, 0, NULL, NULL) = 23

openat(AT_FDCWD, "/app/instantclient/oracore/zoneinfo", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = -1 ENOENT (No such file or directory)

openat(AT_FDCWD, "/app/instantclient/oracore/zoneinfo", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = -1 ENOENT (No such file or directory)

get_mempolicy(NULL, NULL, 0, NULL, 0)   = 0

open("/proc/self/status", O_RDONLY)     = 65

fstat(65, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0

mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2acdea8c6000

read(65, "Name:\tpostgres\nUmask:\t0077\nState"..., 1024) = 1024

read(65, ",00000000,00000000,00000000,0000"..., 1024) = 208

close(65)                              = 0

munmap(0x2acdea8c6000, 4096)            = 0

getrlimit(RLIMIT_STACK, {rlim_cur=RLIM64_INFINITY, rlim_max=RLIM64_INFINITY}) = 0

open("/proc/sys/kernel/shmmax", O_RDONLY) = 65

fstat(65, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0

mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2acdea8c6000

read(65, "2147483648\n", 1024)          = 11

close(65)                              = 0

munmap(0x2acdea8c6000, 4096)            = 0

get_mempolicy(NULL, NULL, 0, NULL, 0)   = 0

open("/proc/self/status", O_RDONLY)     = 65

fstat(65, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0

mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2acdea8c6000

read(65, "Name:\tpostgres\nUmask:\t0077\nState"..., 1024) = 1024

read(65, ",00000000,00000000,00000000,0000"..., 1024) = 209

close(65)                              = 0

munmap(0x2acdea8c6000, 4096)            = 0

open("/proc/meminfo", O_RDONLY)         = 65

fstat(65, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0

mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2acdea8c6000

read(65, "MemTotal:       527439000 kB\nMem"..., 1024) = 1024

read(65, "ages_Total:       0\nHugePages_Fr"..., 1024) = 208

close(65)                              = 0

munmap(0x2acdea8c6000, 4096)            = 0

open("/app/instantclient/rdbms/mesg/ocius.msb", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/app/instantclient/rdbms/mesg/ocius.msb", O_RDONLY) = -1 ENOENT (No such file or directory)

mmap(NULL, 176128, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2ad6398e7000

brk(NULL)                              = 0x298c000

brk(0x29ad000)                         = 0x29ad000

times(NULL)                            = 524110648

rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0

write(2, "\0\0p\1`|\1\0t2020-09-21 15:00:44.604"..., 377) = 377

sendto(10, "E\0\0\0\215SERROR\0VERROR\0CHV00N\0Merror"..., 142, 0, NULL, 0) = 142

rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0

sendto(9, "\2\0\0\0(\0\0\0\0275\0\0\0\0\0\0\0\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0"..., 40, 0, NULL, 0) = 40

sendto(10, "Z\0\0\0\5I", 6, 0, NULL, 0) = 6

recvfrom(10, 0xd21740, 8192, 0, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable)

yum -y localinstall compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm

yum -y localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

yum -y localinstall oracle-database-ee-18c-1.0-1.x86_64.rpm

export ORACLE_HOME=/opt/oracle/product/18c/dbhome_1

export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:$POSTGRES_HOME/lib:$POSTGRES_HOME/lib/postgresql:$POSTGRES_HOME/plugin:$ORACLE_HOME/lib"

pg_ctl stop -D app/xxx/data

pg_ctl start -D app/xxx/data

su - postgres

unzip oracle_fdw-2.2.1.zip

cd oracle_fdw-2.2.1

make

make install

psql

create extension oracle_fdw