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 1rYEnZ-00EzuO-Eb for pgsql-general@arkaria.postgresql.org; Fri, 09 Feb 2024 00:31:22 +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 1rYEnY-004emz-2A for pgsql-general@arkaria.postgresql.org; Fri, 09 Feb 2024 00:31:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rYEnX-004emp-1m for pgsql-general@lists.postgresql.org; Fri, 09 Feb 2024 00:31:19 +0000 Received: from fout1-smtp.messagingengine.com ([103.168.172.144]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rYEnU-005ndR-2V for pgsql-general@lists.postgresql.org; Fri, 09 Feb 2024 00:31:17 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailfout.nyi.internal (Postfix) with ESMTP id AF91D1380089; Thu, 8 Feb 2024 19:31:14 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute6.internal (MEProxy); Thu, 08 Feb 2024 19:31:14 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm2; t=1707438674; x=1707525074; bh=MlrIRNs3Rj79mVWOaBbY+g5fBrqJ71Sf/R6tZ6JzoqE=; b= JQ+TWFr2CLj4/Q6pNYMF+9XmaeAdO3BNS9FIxUrSiSmKD+GEnuNV+oFjpg158DbW /02S0zpkNn8mVdYbDLu7jeW+wP6dLnx+xgjs5nk6vl9nNRzEQmEqMTbE46UVfgZ1 qc9gx/54DSdAbJuYh4MLAImEo1XpTwpFxrAGkrhd2v+9rDfEnGjbSg6APd32X6t/ hL5aNM5S0YPgIysGPZLbqx+q0WTRq3CmRd+Vo9L2yWX3xv6i920t0y8AUPmHqgsp i2qMWcY/XEX7c9g4deNc13rqQgKjLxg65VeEPkJ77SsRB5Fab4g6VKAS12La2VKF VPxOnhnRUs0atue/3UI0ew== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t=1707438674; x= 1707525074; bh=MlrIRNs3Rj79mVWOaBbY+g5fBrqJ71Sf/R6tZ6JzoqE=; b=A ZLYHsev3vOBpZjoqL9fViZkRMKo/hwDnCrrxN0nCw5yns3pfNss3d2q/nyeEWF0k oMFdNSiI2CUARCbHEPmASBAP1cTqyHSnlb7h6mBXZgAzaeb82av+2EwSPXFTijDO MtJLf2GHGt0/oAA8pWariH9KeoD8DZFDLg2F9sPnILdI4TtV+03em4yhNPMU/4KL +lKvB3oreMuVW6XD+bWxvh85uvHW885MKTS8zeJKYY0E6KxcQN/znLArOREDDxit UUQckZ1vUUBil6MJiEG9fR74MArahJxAzcsVYHHdDFiSH58tlVuMAX2605kpdYHb HtnImjiAe05ISeRGZzoUg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrtdehgddvfecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfhfhfgjtgfgsehtkeertddtvdejnecuhfhrohhmpeetughrihgr nhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh eqnecuggftrfgrthhtvghrnhepleegveekkeekueeigfdtveeileeuhfefudefteekjeff keejueejheegheegkedtnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrghenuc evlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgr nhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 8 Feb 2024 19:31:13 -0500 (EST) Message-ID: Date: Thu, 8 Feb 2024 16:31:13 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Migrate schemas Content-Language: en-US To: Lorusso Domenico , pgsql-general@lists.postgresql.org References: From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/8/24 16:18, Lorusso Domenico wrote: > Hello guys, > I have 4 schemas with cross references (e.g.: a function refers to a > rowtype of a table of another schema, or a table invokes a function). > > Backup schemas by pgadmin the resulting script doesn't follow the > correct order to ensure the object creations. > > There is a way to tell postgresql to check the reference at the end of > script? Or a way to arrange DDL in the right order? Order is not guaranteed: https://www.postgresql.org/docs/current/app-pgdump.html -n pattern --schema=pattern Dump only schemas matching pattern; this selects both the schema itself, and all its contained objects. When this option is not specified, all non-system schemas in the target database will be dumped. Multiple schemas can be selected by writing multiple -n switches. The pattern parameter is interpreted as a pattern according to the same rules used by psql's \d commands (see Patterns), so multiple schemas can also be selected by writing wildcard characters in the pattern. When using wildcards, be careful to quote the pattern if needed to prevent the shell from expanding the wildcards; see Examples below. Note When -n is specified, pg_dump makes no attempt to dump any other database objects that the selected schema(s) might depend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves into a clean database. Note Non-schema objects such as large objects are not dumped when -n is specified. You can add large objects back to the dump with the --large-objects switch. If you think you know the order then, see below. Though if the dumps above where done by schema into separate files then things get complicated. https://www.postgresql.org/docs/current/app-pgrestore.html -l --list List the table of contents of the archive. The output of this operation can be used as input to the -L option. Note that if filtering switches such as -n or -t are used with -l, they will restrict the items listed. -L list-file --use-list=list-file Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored. list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples. Best bet is to dump the entire database. > > thanks' > > > -- > Domenico L. > -- Adrian Klaver adrian.klaver@aklaver.com