使用PgAdmin向PostgreSQL导入数据时触发关系不存在错误

问题背景 #

通过PgAdmin创建新PostgreSQL数据库后，先导入schema文件成功创建了数据库结构，但紧接着导入数据文件时触发报错。

创建数据库的SQL代码：

CREATE DATABASE alphashop
  WITH
  OWNER = postgres
  ENCODING = 'UTF8'
  LOCALE_PROVIDER = 'libc'
  CONNECTION LIMIT = -1
  IS_TEMPLATE = False;

通过PgAdmin生成的备份命令，分别导出schema和数据：

/usr/local/pgsql-16/pg_dump --file "/var/lib/pgadmin/storage/myemail_gmail.com/schema.sql" --host "172.21.0.2" --port "5432" --username "postgres" --no-password --role "postgres" --format=p --encoding "UTF8" --schema-only --verbose "alphashop"

/usr/local/pgsql-16/pg_dump --file "/var/lib/pgadmin/storage/myemail_gmail.com/data.sql" --host "172.21.0.2" --port "5432" --username "postgres" --no-password --role "postgres" --format=p --encoding "UTF8" --data-only --inserts --verbose "alphashop"

执行schema.sql成功，但执行data.sql时出现以下错误：

ERROR: relation "scontrini" does not exist
LINE 1: SELECT SUM(totale) FROM scontrini WHERE codfi...
^
QUERY: SELECT SUM(totale) FROM scontrini WHERE codfid = NEW.codfid
CONTEXT: PL/pgSQL function public.tr_inserisci_coupon() line 4 at SQL statement

SQL state: 42P01

已确认scontrini表已通过schema正确创建，且执行顺序为先schema后数据。

环境信息 #

• Ubuntu 20.04系统，使用PgAdmin 4.3
• PostgreSQL版本为14
• PgAdmin4与PostgreSQL运行在Docker中，对应的docker-compose.yml配置：

version: '3.3'

networks: 
  ntpgsql:
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 172.21.0.0/24

services:
  pgsql:
    image: postgres:latest
    restart: unless-stopped
    container_name: postgresql
    volumes: 
      - psdb-volume:/var/lib/postgresql/data
    networks: 
      ntpgsql:
        ipv4_address: 172.21.0.2
    ports:
      - target: 5432
        published: 5433 
        protocol: tcp
        mode: host
    environment:
      - POSTGRES_PASSWORD=mypass
  pgadmin:
    image: dpage/pgadmin4
    restart: unless-stopped
    container_name: pgadmin4
    
    networks:
      ntpgsql:
        ipv4_address: 172.21.0.3
    ports:
      - target: 80
        published: 80
        protocol: tcp
        mode: host
    environment:
      - PGADMIN_CONFIG_SERVER_MODE=True
      - PGADMIN_DEFAULT_EMAIL=myemail@gmail.com
      - PGADMIN_DEFAULT_PASSWORD=mypass


volumes:
  psdb-volume:

解决方案 #

问题源于数据备份时未禁用触发器。在PgAdmin导出数据的选项中，勾选**禁用触发器（Disable Triggers）**后重新导出数据，再执行导入即可解决该报错。

内容的提问来源于stack exchange，提问作者Cibs84