Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUFWM-0000Yj-Gv for pgsql-general@arkaria.postgresql.org; Mon, 20 Feb 2023 23:24:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUFWL-00066x-CK for pgsql-general@arkaria.postgresql.org; Mon, 20 Feb 2023 23:24:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUFWJ-00066n-Ts for pgsql-general@lists.postgresql.org; Mon, 20 Feb 2023 23:24:33 +0000 Received: from wout2-smtp.messagingengine.com ([64.147.123.25]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUFWE-0002tV-VC for pgsql-general@lists.postgresql.org; Mon, 20 Feb 2023 23:24:29 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.west.internal (Postfix) with ESMTP id 37A313200B66; Mon, 20 Feb 2023 18:24:25 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Mon, 20 Feb 2023 18:24:25 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to; s=fm3; t=1676935464; x= 1677021864; bh=87IacvxFFHCF0UoCSBYmCLfwttz6KYnhY2y67RsRIkE=; b=E V3XvanZi05XmzwyOgrj/XqWlvDsmflEeurN30YbyWLQkzMca9yA4R2YWBQ7gOTUk W144yZzxAhKbgezbddSE0ph2GiMZ6TyKenhhfS7LVXsJnfy4VQi42rNEbjKb+k67 ozShKpNRSbOA/7nGRkobTDhDeys9gqfYDydK28olDC/jtZSUxpocb2lQcsi8qQGB OieAomN0aOLubxtAUCRQf+Qc9j6mNrCzii5QzziQV1wHnqUot/fLH1O13BC5zDpZ lMfwUTnR9ymQRL7qIkF6gT/H5LFmuFibXhh6YsUuiBO4h0oml3sgB0FmIhpJqolW zXhNyi7UEZ6LO2rxUsTxg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1676935464; x= 1677021864; bh=87IacvxFFHCF0UoCSBYmCLfwttz6KYnhY2y67RsRIkE=; b=a ay8mEJ8btKIWPwy9jpYgCoptuNQJCn/eCyjqxer8rqCVcyLaEW2e6nr6GTiSDF0h 4DCEnGt4m9QSdceF1mkeJpcu7F/sGjU0JHRxvJSZ6MSD6+jyDbfxp1CFPBEi+FgX JJrLKt0N35XqSwkSMb1iJaTQOVsmgKPshk6RF7thjrN7/JG0KmSot8gqFbHT2E5U 4BhmPpfHk13HWg6VViHXBVGL4XBWgvud1rxinUHuQA3ut4LsRg9y1yEqoM8Lyftd ccpJAzmbQ38YoaxkV8PYN3sjxn1Se5PktmyG11zw56XGoYDrPib33dkhYE1F81ZL Dge7XC3UOsOlAo/jUHAdg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrudejiedgtdelucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtjeertddtfeejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhepgfetueektdevgeehgeethfffjeefgfektddvhfeh gefgffeggfefkeeujeekleejnecuffhomhgrihhnpehpghgrughmihhnrdhorhhgnecuve hluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhirghn rdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 20 Feb 2023 18:24:24 -0500 (EST) Message-ID: <73beed04-25a2-e28b-20f8-855b81fca8ec@aklaver.com> Date: Mon, 20 Feb 2023 15:24:23 -0800 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.8.0 Subject: Re: pg_dump'ed file contains "DROP DATABASE" Content-Language: en-US To: pf@pfortin.com, pgsql-general@lists.postgresql.org Cc: Rick Yost References: <20230220132705.3a81a48f.pfortin@pfortin.com> <20230220143639.2ff966b8@pfortin.com> From: Adrian Klaver In-Reply-To: <20230220143639.2ff966b8@pfortin.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2/20/23 11:36, pf@pfortin.com wrote: > On Mon, 20 Feb 2023 11:06:34 -0800 Adrian Klaver wrote: > >> On 2/20/23 10:27, pf@pfortin.com wrote: >>> [Still a newbie; but learning fast...] >>> >>> Hi, > > Notwithstanding the man page, my take is that the DROP DATABASE statement > needs to be eliminated at pg_dump creation by pgAdmin4. Taking this to > that mailing list. It just dawned on me you might be doing all of this through the pgAdmin4 GUI. In which case from most recent documentation: https://www.pgadmin.org/docs/pgadmin4/6.20/index.html Backup Dialog: https://www.pgadmin.org/docs/pgadmin4/6.20/backup_dialog.html Options tab: " Move the switch next to Include CREATE DATABASE statement towards right position to include a command in the backup that creates a new database when restoring the backup. Move the switch next to Include DROP DATABASE statement towards right position to include a command in the backup that will drop any existing database object with the same name before recreating the object during a backup. " So the default is not to include those options. For Restore dialog: https://www.pgadmin.org/docs/pgadmin4/6.20/restore_dialog.html Options tab(for custom format): " Move the switch next to Include CREATE DATABASE statement towards right position to include a command that creates a new database before performing the restore. Move the switch next to Clean before restore towards right position to drop each existing database object (and data) before restoring. " Again the default is to not include those options. > > Thanks Tom & Adrian! > >>> Was my 134 table[1] myname DB saved because it was open? > > Tom: Yup. > >>> If the dump file >>> contains the above statements, how can I be absolutely certain I won't >>> lose the DB? > > Tom: > Reading the manual is advisable. --create --clean specifies exactly > that the target database is to be dropped and recreated. > > regards, tom lane > >>> I'm obviously quite paranoid now... >> >> You will lose the database if you do as the docs specify for -C: >> >> " >> -C >> >> ... >> >> When this option is used, the database named with -d is used only to >> issue the initial DROP DATABASE and CREATE DATABASE commands. All data >> is restored into the database name that appears in the archive. >> " >> >> >> It will then be recreated with whatever information is in "dumpfile". If >> that is the same data or new data you want then you are fine. Otherwise >> you will need to be more specific about what you are trying to achieve. >> >> >>> >>> [1] 3 types of tables: ~40%=8.5M rows; ~40%=33M rows; ~20%=varying sizes >>> >>> Thanks, >>> Pierre >>> >>> >> > > -- Adrian Klaver adrian.klaver@aklaver.com