Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s2Sca-003Btb-Cu for pgsql-general@arkaria.postgresql.org; Thu, 02 May 2024 09:20:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s2ScX-00GtFQ-Mf for pgsql-general@arkaria.postgresql.org; Thu, 02 May 2024 09:20:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s2ScX-00GtFE-A8 for pgsql-general@lists.postgresql.org; Thu, 02 May 2024 09:20:54 +0000 Received: from mail-ed1-x534.google.com ([2a00:1450:4864:20::534]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s2ScV-0019F1-C4 for pgsql-general@lists.postgresql.org; Thu, 02 May 2024 09:20:53 +0000 Received: by mail-ed1-x534.google.com with SMTP id 4fb4d7f45d1cf-572ad86dc8aso1839538a12.3 for ; Thu, 02 May 2024 02:20:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1714641650; x=1715246450; darn=lists.postgresql.org; h=mime-version:subject:user-agent:references:in-reply-to:message-id :date:cc:to:from:from:to:cc:subject:date:message-id:reply-to; bh=YRNqcGtpMN35rqwFAo/eaQiECZsCRNZ3ktNwjRF+gt0=; b=klDbzLvJl9M+szK7jJy4qqMlkQPd0cm0/TAiAaxIraBu18G25WtY56tugx6ycwMtvT Sb9OoEOPnTdcTr7krcmNQK2vnUsjEqfUjXSWXvVHuz5Y0c69HM8UrN9oS2aP8UkjJpZv LRg+BitGi+vufsVyKga8kaKnkAT/NwY8cbU6hCeUpuMrXuAQwzqzJ9bkEyrR/UY2ksdl f0zn9QFBgAb8FdwRjIYkXiFPhUirhCOUiojS4dVTiG95FWKPIX4G5OeoxQKVvV6mb3FS tzuVphc0g2KJYwA5hwWI1vBk1n/GbG5WQUpE4inMIA5hHBhA36Tw+adBUrcdkixac6Ri +MPA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1714641650; x=1715246450; h=mime-version:subject:user-agent:references:in-reply-to:message-id :date:cc:to:from:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=YRNqcGtpMN35rqwFAo/eaQiECZsCRNZ3ktNwjRF+gt0=; b=k+WhA0puRB/1Mt53b+T/Wi0LwXEbhZLYiYk5XYYEbplKeJZ6AZN2hEpqeQjh3bHNdF uRA7EwYw+VNM2CjHXWsn0RJjC27EYMYToreBW3HlEOR2EK0iIO8zkfr7KyLz6+R5nLtr W9MRl0G4k18Qe78XVZmJACt97pMbITRKaQPHmSGgsotA769R3YjUAd1r65Bj6Ha5Lqxc PNNFvmdIAwjAqgAvKstL3dApmZXIhjVHzzqNpynRD0dLNgkhXZGDsIdopvLVQ90quRDf zVSRTIRl/WurZEJhR/LezFAL/znD7Y7uVI+DrCpUgtodgv3U50V0P93jxB76qu3qSBd+ A7Jg== X-Gm-Message-State: AOJu0YyiW6k5CEhk2mFiAIEcjVfulZsHf4miP0mzcUGly4MBCaZj2p7T vpLToVO5+aMwPAlFCuNd4vrw6e2A1Q6IrNgsUNnHb1EZuxg4H+RTfavXmA== X-Google-Smtp-Source: AGHT+IHakkB7VvYtiCgG6sHO5XMJPR4e5NLZdow7c64bIRLrC3oHpi1pseiQnMFOqfwOUDz+YSgu2w== X-Received: by 2002:a17:906:2858:b0:a58:eb52:9f16 with SMTP id s24-20020a170906285800b00a58eb529f16mr3186332ejc.37.1714641650066; Thu, 02 May 2024 02:20:50 -0700 (PDT) Received: from [192.168.1.7] (p57b5c0a1.dip0.t-ipconnect.de. [87.181.192.161]) by smtp.gmail.com with ESMTPSA id a9-20020aa7d909000000b00572aa4a75a6sm302686edr.83.2024.05.02.02.20.49 (version=TLS1_2 cipher=ECDHE-ECDSA-CHACHA20-POLY1305 bits=256/256); Thu, 02 May 2024 02:20:49 -0700 (PDT) From: Fire Emerald To: Tom Lane CC: Date: Thu, 02 May 2024 11:20:48 +0200 Message-ID: <18f389b3980.2815.a5aef60df33e8d2ac3d54c6545825f63@gmail.com> In-Reply-To: <18e85c76c98.2815.a5aef60df33e8d2ac3d54c6545825f63@gmail.com> References: <18e84674c10.2815.a5aef60df33e8d2ac3d54c6545825f63@gmail.com> <3571436.1711634406@sss.pgh.pa.us> <18e85c76c98.2815.a5aef60df33e8d2ac3d54c6545825f63@gmail.com> User-Agent: AquaMail/1.51.1 (build: 105101461) Subject: Re: How to interpret 'depends on' errors in pg_restore? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="18f389b3ce52e87281577c527a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --18f389b3ce52e87281577c527a Content-Type: text/plain; format=flowed; charset="UTF-8" Content-Transfer-Encoding: 8bit I didn't used pg_dump/restore until today and finally found my mistake which lead to the "problem" described below. The output "depends on" comes from the -l (l as Lima) flag, what i wanted was the -1 (number one) flag, which stands for single transaction in pg_restore. As -l does not execute anything, nothing was logged in the postgres server log and none error was shown anywhere. Both chars looked so identical in my editors/shells that i thought i used -1, in fact using -l. It's always the tiny obvious thing, which we do not see. Best regards, Chris Am 28. März 2024 16:57:04 schrieb Fire Emerald : > Am 28. März 2024 15:00:06 schrieb Tom Lane : > >> Fire Emerald writes: >>> Then i did a pg_restore -d target --verbose -Fc file.dump and saw in the >>> output this: >> >>> 5145 0 730750 TABLE subpartitions backends_y2024w03 userA >>> ; depends on: 237 >>> .... and so on ... >> >> That is not an error, it's just verbose display of one of the items >> in the dump. > > Well, I know it's not an error, but it's everything i got. There was no > error shown. The command completed, but without anything imported. > >> >>> Nothing was restored. >> >> You would need to show us the actual errors. (Suggestion: >> leave off --verbose, it's just clutter.) A guess though is >> that the import failed because of foreign key constraints. >> --data-only mode is not good at ordering the table loads to >> ensure that FK constraints are satisfied on-the-fly. >> >> regards, tom lane > > As i said, the same import but with INSERT INTOs worked without any issues. > So no, there are no FK constraints failing. > > But the target and source table had partitioned tables attached, using > ATTACH PARTITION. > > The schema was like: > db1 schema1 public table1 (links to the listed below) > db1 schema1 subpartitions backends_y2024w03 > db1 schema1 subpartitions backends_y2024w04 > db1 schema1 subpartitions backends_y2024w05 > > The partitioning must be the problem somehow. --18f389b3ce52e87281577c527a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I didn't used pg_dump/restore until today and finally fou= nd my mistake which lead to the "problem" described below.

The output "depends on" comes from the -= l (l as Lima) flag, what i wanted was the -1 (number one) flag, which stand= s for single transaction in pg_restore. As -l does not execute anything, no= thing was logged in the postgres server log and none error was shown anywhe= re.

Both chars looked so= identical in my editors/shells that i thought i used -1, in fact using -l.=

It's always the tiny ob= vious thing, which we do not see.

Best regards,
Chris

Am 28. M=C3=A4rz 2024 16:57:04 schrieb Fire Emerald <fire.githu= b@gmail.com>:

Am 28. M=C3=A4rz 2024 15= :00:06 schrieb Tom Lane <tgl@sss.pgh.pa.us>:

Fire Emerald <fire.github@gmail.com> writes:
Then i did a pg_restore -d target --verbose -Fc file.dump= and saw in the 
output this:

5145 0 730750 TABLE subpartitions backends_y2024w03 userA=
;        depends on: 237
.... and so on ...

That is not an error, it's just verbose display of one of= the items
in the dump.
Well, I know it's not an error, but it's everythi= ng i got. There was no error shown. The command completed, but without anyt= hing imported.

<= div dir=3D"auto">
Nothing was restored.

You would need to show us the actual errors.  (Sugge= stion:
leave off --verbose, it's just clutter.)  A guess th= ough is
that the import failed because of foreign key constraints= .
--data-only mode is not good at ordering the table loads = to
ensure that FK constraints are satisfied on-the-fly.

 =09=09regards, tom lane

As i said, the same imp= ort but with INSERT INTOs worked without any issues. So no, there are no FK= constraints failing.

But the target and source table had partitioned tables attached, using= ATTACH PARTITION.

The s= chema was like:
db1 schema1 public table1 (links to = the listed below)
db1 schema1 subpartitions backends= _y2024w03
db1 schema1 subpartitions backends_y2024w0= 4
db1 schema1 subpartitions backends_y2024w05
<= div dir=3D"auto">
The partitioning must be the p= roblem somehow.

--18f389b3ce52e87281577c527a--