【PgSQL管理】 dblink的配置

#su - postgres

# vi .bash_profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export PGHOME=/usr/local/pgsql/
export PGDATA=/data/pgsql_5432/data/
export LD_LIBRARY_PATH=/usr/local/pgsql/lib

export BACKUP_PATH=/data/db_backup
export ARCLOG_PATH=/data/pgsql_5432/archive/
export SRVLOG_PATH=/data/pgsql_5432/logs

export PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin:$PATH

$ cd /usr/local/pgsql/contrib/dblink/

$make && make install

In file included from ../../src/include/postgres.h:47:0,
from path.c:17:
../../src/include/utils/elog.h:71:28: fatal error: utils/errcodes.h: No such file or directory
#include "utils/errcodes.h"
^
compilation terminated.
make[2]: *** [path_srv.o] Error 1
make[2]: Leaving directory `/usr/local/postgresql-12.3/src/port'
make[1]: *** [submake-libpgport] Error 2
make[1]: Leaving directory `/usr/local/postgresql-12.3/src/interfaces/libpq'
make: *** [submake-libpq] Error 2

关闭现有的PgSQL数据库，然后重新编译安装postgresql软件。

如果配置安装了其他插件，需要在参数文件postgresql.conf将该项注释掉。例如本例安装了wal2json，需要注释掉shared_preload_libraries 

#for wal2json
#shared_preload_libraries = 'wal2json'
wal_level = logical
#max_wal_senders = 4
max_replication_slots = 4

新编译安装之后，启动旧的数据库集簇，创建dblink

postgres=# select * from pg_extension;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
13577 | plpgsql | 10 | 11 | f | 1.0 | |
(1 row)

postgres=# create extension dblink;
CREATE EXTENSION
postgres=#
postgres=#
postgres=# select * from pg_extension;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------+---------+----------+--------------+----------------+------------+-----------+--------------
13577 | plpgsql | 10 | 11 | f | 1.0 | |
604691 | dblink | 10 | 2200 | t | 1.2 | |
(2 rows)

postgres=#

 接下来，重新安装wal2json，取消注释shared_preload_libraries 。

 

重启数据库集簇。