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 1pUJF9-0003Dc-FE for pgsql-general@arkaria.postgresql.org; Tue, 21 Feb 2023 03:23:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUJF8-0005LE-Ck for pgsql-general@arkaria.postgresql.org; Tue, 21 Feb 2023 03:23:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUJF4-0005K8-P2 for pgsql-general@lists.postgresql.org; Tue, 21 Feb 2023 03:23:02 +0000 Received: from rs6162.luxsci.com ([162.142.76.255]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pUJF1-0002ZQ-P7 for pgsql-general@lists.postgresql.org; Tue, 21 Feb 2023 03:22:58 +0000 Received: from pfortin.com (h163.145.185.173.dynamic.ip.windstream.net [173.185.145.163]) (authenticated bits=0) by rs6162.luxsci.com (8.15.2/8.14.9) with ESMTPSA id 31L3MoVG708284 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NOT) for ; Tue, 21 Feb 2023 03:22:51 GMT Date: Mon, 20 Feb 2023 22:22:50 -0500 From: pf@pfortin.com To: pgsql-general@lists.postgresql.org Subject: Re: pg_dump'ed file contains "DROP DATABASE" Message-ID: <20230220222250.6b0df32c@pfortin.com> In-Reply-To: <73beed04-25a2-e28b-20f8-855b81fca8ec@aklaver.com> References: <20230220132705.3a81a48f.pfortin@pfortin.com> <20230220143639.2ff966b8@pfortin.com> <73beed04-25a2-e28b-20f8-855b81fca8ec@aklaver.com> X-Mailer: Claws Mail 4.1.1 (GTK 3.24.36; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Lux-Comment: Message 31L3MoVG708284 sent by user #22429 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pfortin.com; h=date:from:to:subject:message-id:in-reply-to:references:mime-version:content-type:content-transfer-encoding; s=dkim; bh=A7XH8Jvv0D/G3H1QNZGEXvPmfVexyayYTeagP62o+Fw=; b=rwIg+OIHQsqowN4uZeczHqWGGjeKPG0zMnpWsNWSPjsD8vbXfLvsU6OXsPjNeUTtN7foBWCAODexJFTe897AcKth1SvsL9FTAHwK69ZnyPGJ/n7srwxRJ7TpJikAWy5HqoNWxXVu9/UE3owEZBc21NFoUpa1PQVP+NDs3RMWPZI= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 20 Feb 2023 15:24:23 -0800 Adrian Klaver wrote: >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. Sorry for any confusion... I get it now... A team member uses pgAdmin4 to load separate table(s) into his DB; then creates dump files (one per table) of those _individual_ tables which are uploaded to me. I maintain a complete set of tables in my DB. pgAdmin4 is never used here; the restore is done with a simple bash script which inserts the dbname and dumpfile name into the command. Those restore tables should only be created with DROP DATABASE _off_. All that should happen with my DB is to add these tables (99.9% of the time, they are totally new to me). A DROP DATABASE from the one-table per dumpfile creator is UNwanted. Looks like I really did dodge a bullet... >Again the default is to not include those options. Glad to know pgAdmin4 has those switches. Thanks again!! >> >> 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 >>>> >>>> >>> >> >> >