pgsql如何创建本地库

### 如何使用 Navicat 连接和管理 PostgreSQL 数据库 #### 创建新连接 为了建立与 PostgreSQL 的连接，需打开 Navicat 并创建一个新的连接。在新建连接窗口内，选择 PostgreSQL 类型并填写必要的信息，包括但不限于主机地址、端口号（通常是 5432）、用户名以及密码[^4]。 #### 配置安全设置 当目标数据库位于云环境中时，比如华为云服务器，则除了上述基本信息外，还需确保服务器的安全组已允许来自客户端 IP 地址对指定端口（如 5432 或自定义的其他端口）的访问请求[^1]。 #### 测试连接稳定性 完成以上配置之后，可以通过点击“测试连接”按钮来验证所填入的信息是否正确无误，并确认能够成功连通远程数据库实例。如果一切正常，将会显示连接成功的提示；反之则应按照错误提示调整相应参数直至顺利建立连接为止。 #### 备份与恢复操作 对于已经建立了稳定连接的数据表而言，利用 Navicat 提供的功能可以方便地执行备份任务。通过导航至菜单栏中的“工具”—> “数据传输”，用户可以根据向导指引轻松实现整个数据库结构及其内容的完整复制或迁移工作。此过程支持多种目标存储方式的选择，既可以直接覆盖现有数据库也可以另存为独立 SQL 文件形式保存于本地磁盘之中[^2]。 ```sql -- 示例SQL语句用于展示如何查看当前数据库内的所有表格列表 SELECT table_schema || '.' || table_name AS full_table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_catalog=current_database(); ``` #### 日常管理和维护活动 一旦完成了初始设置阶段的工作，管理员便可以在图形界面下直观高效地开展诸如查询优化、性能监控等一系列日常管理工作。借助内置的各种实用工具和服务，即使是没有深厚技术背景的操作人员也能快速上手掌握基本技能，从而保障业务系统的持续稳定运行。

### PostgreSQL 跨数据库查询方法与示例 在 PostgreSQL 中，跨数据库的关联查询并非直接支持的功能，因为 PostgreSQL 的设计原则之一是每个数据库都是独立的实体。然而，通过一些扩展功能和配置，可以实现跨数据库的数据访问。 #### 使用 `postgres_fdw` 实现跨数据库查询 `postgres_fdw` 是一种外部数据封装器（Foreign Data Wrapper），允许在一个数据库中访问另一个数据库中的表。以下是具体步骤： 1. **安装并启用 `postgres_fdw` 扩展** 需要在目标数据库中创建并启用 `postgres_fdw` 扩展。 ```sql CREATE EXTENSION IF NOT EXISTS postgres_fdw; ``` 2. **创建服务器对象** 定义远程数据库的信息。 ```sql CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remote_host', port '5432', dbname 'foreign_db'); ``` 3. **定义用户映射** 映射本地用户到远程数据库用户。 ```sql CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'remote_user', password 'password'); ``` 4. **导入远程表结构** 将远程数据库中的表作为外部表引入。 ```sql IMPORT FOREIGN SCHEMA remote_schema LIMIT TO (table_name) FROM SERVER foreign_server INTO local_schema; ``` 完成上述操作后，可以在本地数据库中像对待普通表一样查询这些外部表[^2]。 #### 使用 `dblink` 实现动态跨库查询 `dblink` 提供了一种更灵活的方式执行动态 SQL 并从其他数据库检索数据。其主要特点是无需提前定义外部表即可运行查询。 1. **安装并启用 `dblink` 扩展** ```sql CREATE EXTENSION IF NOT EXISTS dblink; ``` 2. **执行跨库查询** 下面的例子展示了如何使用 `dblink` 查询另一台主机上的数据库。 ```sql SELECT * FROM dblink('dbname=otherdb host=another_host user=someuser password=somepass', 'SELECT col1, col2 FROM sometable') AS t(col1 text, col2 int); ``` 此方式适合于需要临时或复杂逻辑处理的情况，但它可能不如 `postgres_fdw` 性能优越[^1]。 #### 设置 `search_path` 来简化多模式查询 虽然严格意义上讲这不是真正的“跨数据库”查询，但如果两个数据库位于同一集群下，则可通过调整 `search_path` 参数来间接达到目的。例如： ```sql SET search_path = 'public, other_database.other_schema'; ``` 之后可尝试基于全限定名称（即包含数据库名、模式名）来进行联合查询[^4]。 请注意这种方式仅限于单个 PostgreSQL 集群内的不同 schema 之间协作，并不适用于完全分离的不同物理机器上部署的服务环境。 --- ### 示例代码展示 假设我们有两个不同的数据库实例分别存储客户订单信息 (`orders`) 及产品详情(`products`) ，现在希望统计每类商品销售数量总和: 利用 `postgres_fdw` 方案: ```sql -- 假设已在 orders 数据库里完成了对外部 products 表的映射工作 SELECT p.product_category, COUNT(o.order_id) as total_orders FROM public.orders o JOIN external.products p ON o.product_id = p.id GROUP BY p.product_category; ``` 采用 `dblink` 方法则如下所示: ```sql SELECT category, SUM((data->>'quantity')::int) AS quantity_sum FROM ( SELECT product.category, jsonb_array_elements_text(data -> 'items') data FROM dblink( 'host=localhost port=5432 dbname=products user=user password=password', $$SELECT id, items FROM inventory$$ ) AS inv(id integer, data jsonb), LATERAL jsonb_to_recordset(inv.data) AS product(category TEXT) ) subquery GROUP BY category; ``` ---