百度360必应搜狗淘宝本站头条
php redisredis 命令redis 锁redis 主从redis 删除
当前位置：网站首页 > 技术教程 > 正文

你不知道的PostgreSQL数据库安装及实现跨库查询PG和Oracle

mhr18 2024-10-13 02:57 19 浏览 0 评论


PG作为近几年最火热的关系型数据，已经被很多开发者所使用，尤其是5G网络普及完毕后，IOT和AI的应用场景下，数据的读写速度要求非常高，MYSQL已经开始不能满足高强度的数据吞吐（这里有争议，这里只是说单台服务的情况，使用负载的不在此讨论范围），ORACLE数据由于其不是开源项目，导致很多开发者在进行技术选型的时候，不会优先选择ORACLE作为第一选择，这时候PG作为一款可以媲美ORACLE的开源关系型数据库的优势就很明显了。下面我来介绍一下在CentOS下安装PG和使用PG进行跨库查询PG和ORACLE。

环境准备

一台CentOS7的Lunix服务器。

PostgreSQL 10.10, 64-bit，官网 https://www.postgresql.org/进行下载（window或者lunix都可以直接下载，我们这里使用yum进行镜像下载）

PG安装步骤如下：

1.安装rpm文件

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2.安装PG客户端

yum install postgresql10

3.安装服务端

yum install postgresql10-server

4.初始化PG

/usr/pgsql-10/bin/postgresql-10-setup initdb

5、设置自动启动并且启动postgresql服务

systemctl enable postgresql-10
systemctl start postgresql-10

下面进行PG数据库初始化，创建用户和数据库，步骤如下：

1.切换用户到postgres（PG安装成功后会自动创建postgres，初始化没有密码）

su - postgres

2.登陆postgresql数据库

3.创建用户和数据库

create user admin with password 'admin';            // 创建用户
create database iot_admin owner admin;                 // 创建数据库
grant all privileges on database iot_admin to admin;   // 授权

4、退出psql

\q

PG在安装之后，默认不开启远程访问的，需要手动开启远程访问，步骤如下

1、修改/var/lib/pgsql/10/data/postgresql.conf文件，取消 listen_addresses 的注释，将参数值改为“*”

2、修改/var/lib/pgsql/10/data/pg_hba.conf文件，增加下图红框部分内容

3.切换root用户后，重启PG服

su - root
systemctl restart postgresql-10.service

4.使用Navicat连接PG


到这里PG已经正常安装到CentOS上，并且已经正常连接了。

下面我们来介绍PG最为强大的fdw功能

什么是fdw，我们来说下业务场景，比如我想做获取其他服务器上的的数据，一般情况我们的获取方式都是使用webservice或者rest-api接口的方式进行获取，有了fdw之后，我们可以直接将服务器上数据库的表映射（可以理解为一种特殊的外部数据接口），这样我们就可以直接读取我们自己库上映射的表进行数据构建，这里可能会有人认为接口也可以实现这种功能，我想说的是，接口总时有一定的延迟性的，而且映射过来的表我们是可以使用join来进行数据关联的，这也极大的方便了我们的开发和应用。

下面我来介绍使用Postgreslq_fdw来实现跨库查询Postgresql和使用Oracle_fdw来查询Oracle

使用Postgresql_fdw实现跨库查询Postgreql库(使用Navicat进行操作)

根据上文，我们已经创建了一个数据库iot_admin

1.我们新建一个数据库iot_all



2.这里我们在iot_admin中新建一张表base_user,

然后将base_user的数据同步到iot_all中。


iot_admin的建表SQL语句如下：

CREATE TABLE "public"."base_user" (

"id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,

"username" varchar(255) COLLATE "pg_catalog"."default",

"password" varchar(255) COLLATE "pg_catalog"."default",

"name" varchar(255) COLLATE "pg_catalog"."default",

"birthday" varchar(255) COLLATE "pg_catalog"."default",

"address" varchar(255) COLLATE "pg_catalog"."default",

"mobile_phone" varchar(255) COLLATE "pg_catalog"."default",

"tel_phone" varchar(255) COLLATE "pg_catalog"."default",

"email" varchar(255) COLLATE "pg_catalog"."default",

"sex" varchar(1) COLLATE "pg_catalog"."default",

"status" varchar(1) COLLATE "pg_catalog"."default",

"description" varchar(255) COLLATE "pg_catalog"."default",

"crt_time" timestamp(6),

"crt_user" varchar(255) COLLATE "pg_catalog"."default",

"crt_name" varchar(255) COLLATE "pg_catalog"."default",

"crt_host" varchar(255) COLLATE "pg_catalog"."default",

"upd_time" timestamp(6),

"upd_user" varchar(255) COLLATE "pg_catalog"."default",

"upd_name" varchar(255) COLLATE "pg_catalog"."default",

"upd_host" varchar(255) COLLATE "pg_catalog"."default",

"attr1" varchar(255) COLLATE "pg_catalog"."default",

"attr2" varchar(255) COLLATE "pg_catalog"."default",

"attr3" varchar(255) COLLATE "pg_catalog"."default",

"attr4" varchar(255) COLLATE "pg_catalog"."default",

"attr5" varchar(255) COLLATE "pg_catalog"."default",

"attr6" varchar(255) COLLATE "pg_catalog"."default",

"attr7" varchar(255) COLLATE "pg_catalog"."default",

"attr8" varchar(255) COLLATE "pg_catalog"."default",

"delete_flag" int2 NOT NULL DEFAULT 0,

"user_type" int2 NOT NULL DEFAULT 0,

"office_id" varchar(64) COLLATE "pg_catalog"."default",

"company_id" varchar(64) COLLATE "pg_catalog"."default",

"is_frist_login" varchar(10) COLLATE "pg_catalog"."default"

)

;

ALTER TABLE "public"."base_user" ADD CONSTRAINT "pk_base_user" PRIMARY KEY ("id");


3.在iot_all中安装FDW插件

SQL如下：

CREATE EXTENSION postgres_fdw;

4.在iot_all中创建iot_admin的远程服务

CREATE SERVER admin_server

FOREIGN DATA WRAPPER postgres_fdw

OPTIONS (host '10.100.2.156', port '5432', dbname 'iot_admin');

数据库ip 端口 数据库名称

5.验证服务创建成功

SELECT * from pg_foreign_server;


6.创建user mapping 相当于用户数据映射，将服务的权限给用户，没有给权限的用户无法使用相应的服务，这里使用初始化用户posgres

CREATE USER MAPPING FOR postgres

SERVER admin_server

OPTIONS (user 'postgres', password 'xgit_123');

用户名 密码

7.在iot_all中创建外部表，映射iot_admin中的base_user;

CREATE FOREIGN TABLE "public"."base_user" (

"id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,

"username" varchar(255) COLLATE "pg_catalog"."default",

"password" varchar(255) COLLATE "pg_catalog"."default",

"name" varchar(255) COLLATE "pg_catalog"."default",

"birthday" varchar(255) COLLATE "pg_catalog"."default",

"address" varchar(255) COLLATE "pg_catalog"."default",

"mobile_phone" varchar(255) COLLATE "pg_catalog"."default",

"tel_phone" varchar(255) COLLATE "pg_catalog"."default",

"email" varchar(255) COLLATE "pg_catalog"."default",

"sex" varchar(1) COLLATE "pg_catalog"."default",

"status" varchar(1) COLLATE "pg_catalog"."default",

"description" varchar(255) COLLATE "pg_catalog"."default",

"crt_time" timestamp(6),

"crt_user" varchar(255) COLLATE "pg_catalog"."default",

"crt_name" varchar(255) COLLATE "pg_catalog"."default",

"crt_host" varchar(255) COLLATE "pg_catalog"."default",

"upd_time" timestamp(6),

"upd_user" varchar(255) COLLATE "pg_catalog"."default",

"upd_name" varchar(255) COLLATE "pg_catalog"."default",

"upd_host" varchar(255) COLLATE "pg_catalog"."default",

"attr1" varchar(255) COLLATE "pg_catalog"."default",

"attr2" varchar(255) COLLATE "pg_catalog"."default",

"attr3" varchar(255) COLLATE "pg_catalog"."default",

"attr4" varchar(255) COLLATE "pg_catalog"."default",

"attr5" varchar(255) COLLATE "pg_catalog"."default",

"attr6" varchar(255) COLLATE "pg_catalog"."default",

"attr7" varchar(255) COLLATE "pg_catalog"."default",

"attr8" varchar(255) COLLATE "pg_catalog"."default",

"delete_flag" int2 NOT NULL DEFAULT 0,

"user_type" int2 NOT NULL DEFAULT 0,

"office_id" varchar(64) COLLATE "pg_catalog"."default",

"company_id" varchar(64) COLLATE "pg_catalog"."default",

"is_frist_login" varchar(10) COLLATE "pg_catalog"."default"

) Server admin_server options (table_name 'base_user');

服务名 映射的表名称

6.验证同步

查看iot_admin中base_user的数据


查看iot_all中的base_user的数据


这里需要重点说明的一个问题是，外部表映射仅仅可以对表的增删改查进行操作，也就是对表数据进行操作，但是不能对表结构进行操作，也就是all里面字段的改变不影响admin的改变，同时ALL中的字段是可以少于admin中，但是不能多于admin中，或者存在admin中不存在的字段


使用Oracle_fdw实现跨库查询Oracle库

1.安装Oracle_fdw

官方地址：http://pgxn.org/dist/oracle_fdw/ ，选择一个版本下载。

2.安装Oracle Instant Client

从oralce官网下载 'Basic' and 'SDK'，假如下载后文件所在位置在/opt/oracle中。

cd /opt/oracle
unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip
mv instantclient_12_2 instantclient
cd instantclient
ln -s libclntsh.so.12.1 libclntsh.so
vi /etc/profile
export ORACLE_HOME=/opt/oracle/instantclient
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
source /etc/profile

3.启动用户变量

source /home/postgres/.bashrc

4.安装oracle_fdw

unzip oracle_fdw-1.5.0.zip 
cd oracle_fdw-1.5.0
make
make install

下面是创建FDW的扩展

同样是iot_all数据库

1.安装oracle_fdw扩展

create extension oracle_fdw;

2.创建server

create server oradb_test foreign data wrapper oracle_fdw options(dbserver '192.168.0.215:1521/ora_db');

这里和PG互相跨库查询略有不同

4.服务授权

grant usage on foreign server oradb_test to postgres;

5.创建服务映射给用户

create user mapping for postgres server oradb_test  options(user 'MG_APP',password 'QWERasdf');


下面进行表的创建和做映射，可以参照PG实现跨库，步骤相同。

结语

PG作为一款关系型数据库，可以实现如此多的功能扩展，可以是一款全栈型数据库也不为过。

相关推荐

C++开发必知的内存问题及常用的解决方法-经典文章

1.内存管理功能问题由于C++语言对内存有主动控制权，内存使用灵活和效率高，但代价是不小心使用就会导致以下内存错误：omemoryoverrun：写内存越界odoublefree：同一块内...

缓存用不好，系统崩得早!10条军规让你成为缓存高手

凌晨三点，我被电话惊醒：“苏工！首页崩了！”监控显示：缓存命中率0%，数据库QPS10万+，线程阻塞2000+。根本原因竟是同事没加缓存！不会用缓存的程序员，就像不会刹车的赛车手——...

彻底搞清楚内存泄漏的原因，如何避免内存泄漏，如何定位内存泄漏

作为C/C++开发人员，内存泄漏是最容易遇到的问题之一，这是由C/C++语言的特性引起的。C/C++语言与其他语言不同，需要开发者去申请和释放内存，即需要开发者去管理内存，如果内存使用不当，就容易造成...

Java中间件-Memcached（Java中间件大全）

一、知识结构及面试题目分析缓存技术的大规模使用是互联网架构区别于传统IT技术最大的地方，是整体高并发高性能架构设计中是重中之重的关键一笔，也是互联网公司比较偏好的面试题目。按照在软件系统中所处位置...

linux内存碎片防治技术（linux内存碎片整理）

推荐视频：90分钟了解Linux内存架构，numa的优势，slab的实现，vmalloc原理剖析Linux内核内存分配与回收Linuxkernel组织管理物理内存的方式是buddysystem（伙...

Redis主从架构详解（redis主从配置详细过程）

Redis主从架构搭建Redis主节点配置创建主节点目录（/opt/redis-master），复制redis.conf到该目录下，redis.conf配置项修改#后台启动daemonizeyes...

揭开CXL内存的神秘面纱（内存c1）

摘要：现代数据中心对内存容量的高需求促进了内存扩展和分解方面的多条创新线，其中一项获得极大关注的工作是基于ComputeeXpressLink（CXL）的内存扩展。为了更好地利用CXL，研究人员建...

一文彻底弄懂 TPS RPS QPS（tps cps）

以下是关于RPS、QPS、TPS的核心区别与关联的总结，结合实际场景和优化建议：一、核心定义与区别RPS：RequestsPerSecond每秒请求数客户端到服务器的完整请求数量Web服务...

用Redis的“集合”找出你和朋友的“共同关注”

你是不是在刷抖音、微博、小红书的时候，常常会看到这样的提示：“你和XXX有共同关注的博主/朋友”？或者当你关注了一个新的明星，系统会推荐“你的朋友YYY也关注了这位明星”？这个看似简单的功能背后，其实...

WOT2016彭哲夫:科班出身开发者对运维人员的期许

“运维与开发”是老生常谈的话题，前几天和一个运维人聊天，TA说一些公司运维岗位都不公开招聘了，这让众多运维人员情何以堪？是运维的岗位真的饱和了？是找到合适的运维人才难？还是有这样那样的因素？带着这些疑...

Java程序员最常用的20%技术总结（java程序员要掌握什么）

我听说编程语言，经常使用的是其中20％的技术。在Java这门语言中，这20％包括哪些内容？找到一份Java初级程序员的工作，有哪些是必须掌握的，有哪些是可以现学现卖的？一个完整的Javaweb项目，有...

秒杀系统实战(四)| 缓存与数据库双写一致性实战

前言微笑挖坑，努力填坑。————已经拥有黑眼圈，但还没学会小猪老师时间管理学的蛮三刀同学本文是秒杀系统的第四篇，我们来讨论秒杀系统中「缓存热点数据」的问题，进一步延伸到数据库和缓存的...

头条评论精灵翻牌子（头条评论精灵翻牌子怎么弄）

关于“头条评论精灵翻牌子”功能，这通常是指平台通过算法或运营手段，将用户的优质评论随机或定向推送到更显眼的位置（如信息流顶部、独立曝光位等），以提升互动率和用户参与感。以下是详细解析和建议：一、功能理...

15个程序员们都应该知道的大模型高级提示词指令模板和示例

作为程序员你如何写大模型指令？你写的指令是不是更专业呢？下面是15个程序员使用的专业的大模型指令，如果早知道可以能节省你很多时间。这些指令可以用在chatgpt,deepseek等大模型。1.一键...

MyBatis-Plus内置的主键生成策略有大坑，要注意!

昨天小伙伴使用Mybaits-Plus开发的项目线上（集群、K8S）出现了主键重复问题，其报错如下：Mybatis-Plus启动时会通过com.baomidou.mybatisplus.core.to...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表