泊远的博客

外观

Postgresql指导手册

约 2050 字大约 7 分钟

postgresdatabase

2024-11-01

数据库服务端安装

客户端工具

1.工具安装

以Ubuntu为例，其他平台参考官网。客户端工具主要为命令行工具，提供数据库访问、导出、导入等功能。系统默认安装的版本比较低，若想安装最新版本按一下方式更新源。

sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Create the repository configuration file: 
apt-get install lsb-release

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Update the package lists:
sudo apt update

# Install the latest version of PostgreSQL:
# If you want a specific version, use 'postgresql-16' or similar instead of 'postgresql'
#安装完整数据库
sudo apt -y install postgresql

#只安装客户端工具
sudo apt install postgresql-client-17

2.数据库导出

使用 pg_dump 工具可以方便地导出 PostgreSQL 数据库的表结构。以下是一些常用的命令示例，可以帮助您导出表结构而不包含数据。

导出整个数据库的表结构

如果您想导出整个数据库的表结构，可以使用以下命令：

pg_dump -h hostname -U username -d dbname -s -F p > schema.sql
  • -h hostname：指定数据库服务器的主机名或IP地址。
  • -U username：指定连接数据库的用户名。
  • -d dbname：指定要导出的数据库名称。
  • -s：仅导出表结构，不包含数据。
  • -a: 只导出数据
  • -C：导出create database语句
  • -t：之导出指定的表
  • -F p：指定输出格式为纯文本（plain）。--format=c|d|t|p （custom, directory, tar, plain text (default)）
  • --inserts：导出data为insert命令。默认是COPY数据行制表符分隔列
  • > schema.sql：将输出重定向到一个文件，例如 schema.sql。

导出特定表的表结构

如果您只想导出某个特定表的表结构，可以使用以下命令：

pg_dump -h hostname -U username -d dbname -t table_name -s -F p > table_schema.sql
  • -t table_name：指定要导出的表名。

导出多个表的表结构

如果您想导出多个表的表结构，可以使用逗号分隔的表名列表：

pg_dump -h hostname -U username -d dbname -t table1 -t table2 -s -F p > multiple_tables_schema.sql

使用 --schema-only 选项

pg_dump 还提供了一个 --schema-only 选项，可以用来导出表结构和其他模式对象（如索引、约束等）：

pg_dump -h hostname -U username -d dbname --schema-only -F p > schema_only.sql

导出特定模式的表结构

如果您只想导出某个特定模式的表结构，可以使用 --schema 选项：

pg_dump -h hostname -U username -d dbname --schema=myschema --schema-only -F p > schema_only.sql
  • --schema=myschema：指定要导出的模式名称。

导出表结构并压缩

如果您希望导出的文件是压缩的，可以使用 gzip 命令：

pg_dump -h hostname -U username -d dbname -s -F p | gzip > schema.sql.gz

示例

假设您有一个名为 mydb 的数据库，用户名为 myuser，主机名为 localhost，您想导出 users 表的表结构，可以使用以下命令：

pg_dump -h localhost -U myuser -d mydb -t users -s -F p > users_schema.sql

注意事项

  • 确保您有足够的权限来访问数据库和表。

  • 如果需要输入密码，可以使用 PGPASSWORD 环境变量来传递密码，但请注意这可能不安全：

# 配置到~/.bashrc 中
export PGPASSWORD=mypassword

pg_dump -h localhost -U myuser -d mydb -t users -s -F p > users_schema.sql

#导出数据库指定的schema 下所有表结构
pg_dump -h iserver.cn -U root -d gdb --schema=fina -s -F p > fina_ddl.sql
#导出数据库指定的schema 下所有数据
pg_dump -h iserver.cn -U root -d gdb --schema=fina -a -F p > fina_data.sql

3.数据库导入

在 PostgreSQL 中导入数据有多种方法，包括使用 pg_restore、psql、COPY 命令等。以下是一些常见的数据导入方法及其示例。

方法一：使用 pg_restore 导入数据

pg_restore 是一个用于恢复由 pg_dump 创建的备份文件的工具。适用于备份文件为自定义格式（.dump）的情况。

1. 导入整个数据库

假设您有一个名为 backup.dump 的备份文件，可以使用以下命令将其恢复到名为 mydatabase 的数据库中：

# 文本方式导出，文本格式无法选择部分恢复，按业务拆分开多个文件导出。
pg_dump -h iserver.cn -U root -d gdb -t fina.sys_config -s -v -f ./fina_sys_config_ddl.sql
pg_dump -h iserver.cn -U root -d gdb -t fina.sys_config -a -v -f ./fina_sys_config_data.sql

# 文本方式恢复
psql -h iserver.cn -U root -d postgres -f ./fina_sys_config_ddl.sql
psql -h iserver.cn -U root -d postgres -f ./fina_sys_config_data.sql

# 自定义格式导出，可以将数据和结构放在一个文件
pg_dump -h iserver.cn -U root -d gdb -t fina.sys_config -v -F c -f ./fina_sys_config.dump

# 自定义格式恢复：只恢复结构，注意-t后面的表名称不用写schema
pg_restore -h iserver.cn -U root -d postgres -t sys_config -s -v ./fina_sys_config.dump
# 自定义格式恢复：只恢复数据，注意-t后面的表名称不用写schema
pg_restore -h iserver.cn -U root -d postgres -t sys_config -a -v ./fina_sys_config.dump
  • -h hostname：指定数据库服务器的主机名或IP地址。
  • -U username：指定连接数据库的用户名。
  • -d mydatabase：指定要恢复的数据库名称。
  • -v：显示详细输出。

2. 导入特定表

如果您只想恢复备份文件中的特定表，可以使用 --table 选项。注意：这种方式只能是自定义导出格式才可以。

# 自定义格式：导出多张表
pg_dump -h iserver.cn -U root -d gdb -t fina.sys_* -v -F c -f ./fina_sys_tables.dump

# 恢复dump文件中的所有内容
pg_restore -h iserver.cn -U root -d postgres -v ./fina_sys_tables.dump
# 恢复指定的表结构和数据
pg_restore -h iserver.cn -U root -d postgres -t sys_config -v ./fina_sys_tables.dump
# 只恢复指定表的结构
pg_restore -h iserver.cn -U root -d postgres -t sys_config -s -v ./fina_sys_tables.dump
# 只恢复指定表的数据
pg_restore -h iserver.cn -U root -d postgres -t sys_config -a -v ./fina_sys_tables.dump

方法二：使用 psql 导入数据

psql 是 PostgreSQL 的命令行工具，可以用来执行 SQL 脚本文件。适用于备份文件为 SQL 格式的情况。

1. 导入整个数据库

假设您有一个名为 backup.sql 的 SQL 脚本文件，可以使用以下命令将其导入到名为 mydatabase 的数据库中：

psql -h hostname -U username -d mydatabase -f backup.sql
  • -h hostname：指定数据库服务器的主机名或IP地址。
  • -U username：指定连接数据库的用户名。
  • -d mydatabase：指定要导入的数据库名称。
  • -f backup.sql：指定要执行的 SQL 脚本文件。

方法三：使用 COPY 命令导入数据

COPY 命令可以用来从文件中导入数据到表中。适用于 CSV 或其他文本格式的数据文件。

1. 导入 CSV 文件

假设您有一个名为 data.csv 的 CSV 文件，可以使用以下命令将其导入到名为 mytable 的表中：

psql -h hostname -U username -d mydatabase -c "COPY mytable FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER;"
  • COPY mytable FROM '/path/to/data.csv'：指定要导入的表和数据文件路径。
  • DELIMITER ','：指定字段分隔符。
  • CSV HEADER：指定文件包含标题行。

2. 导入其他文本格式文件

如果您的数据文件不是 CSV 格式，可以调整 COPY 命令的参数：

psql -h hostname -U username -d mydatabase -c "COPY mytable FROM '/path/to/data.txt' WITH (FORMAT text, DELIMITER '|', NULL 'NULL');"
  • WITH (FORMAT text, DELIMITER '|', NULL 'NULL')：指定文件格式、字段分隔符和空值表示。

4.删除库及其他

在 PostgreSQL 中删除某个 schema 下的所有表可以通过多种方法实现。以下是几种常用的方法：

方法一：使用 SQL 脚本您可以编写一个 SQL 脚本来删除指定 schema 下的所有表。以下是一个示例脚本：

DO $$
DECLARE
    r RECORD;
BEGIN
    -- 获取 schema 下的所有表名
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'your_schema') LOOP
        -- 执行 DROP TABLE 语句
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

方法二：使用 psql 命令

如果您更喜欢使用命令行工具 psql，可以执行以下命令来生成并执行删除表的 SQL 语句：

psql -d your_database -c "SELECT 'DROP TABLE IF EXISTS ' || quote_ident(tablename) || ' CASCADE;' FROM pg_tables WHERE schemaname = 'your_schema';" | psql -d your_database

方法三：使用 pgAdmin

如果您使用 pgAdmin 图形界面工具，可以手动删除 schema 下的所有表：

  1. 打开 pgAdmin。
  2. 连接到您的数据库。
  3. 展开左侧的树状结构，找到目标 schema。
  4. 展开 schema，右键点击每个表，选择“删除/删除”（Delete/Drop）。

陕ICP备2021014644号-1

© 2022-2025 口袋鱼