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 1pUBy1-0005ia-Ns for pgsql-general@arkaria.postgresql.org; Mon, 20 Feb 2023 19:36:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUBy0-0005eH-Jf for pgsql-general@arkaria.postgresql.org; Mon, 20 Feb 2023 19:36:52 +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 1pUBxz-0005e8-TA for pgsql-general@lists.postgresql.org; Mon, 20 Feb 2023 19:36:52 +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 1pUBxs-0006Zp-LL for pgsql-general@lists.postgresql.org; Mon, 20 Feb 2023 19:36:51 +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 31KJadZZ316869 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NOT); Mon, 20 Feb 2023 19:36:40 GMT Date: Mon, 20 Feb 2023 14:36:39 -0500 From: pf@pfortin.com To: pgsql-general@lists.postgresql.org Cc: Rick Yost Subject: Re: pg_dump'ed file contains "DROP DATABASE" Message-ID: <20230220143639.2ff966b8@pfortin.com> In-Reply-To: References: <20230220132705.3a81a48f.pfortin@pfortin.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 31KJadZZ316869 sent by user #22429 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pfortin.com; h=date:from:to:cc:subject:message-id:in-reply-to:references:mime-version:content-type:content-transfer-encoding; s=dkim; bh=3lpytTrifYD1XAP/TmzZInYTkW3LYfYX0wojEHgJo8k=; b=XiytJGN6qaLnhH3T2ZPQEdFugmhs+smv1ty+6WcWCuoGB45N+h3tANRvU8sIISwuD5ZqQMje5KShVQreJoH7veYMVDTVr9HTNGYggEvpMBFa4uVH8sz3hFGoyvwLxAEtdYAgmi2p1BmN8SGnd6Ruz2SIaJXotcIV5mzc/IHVoI0= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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, >> >> A remote team member is helping out by dumping some of his tables via >> pgAdmin4 on Windows. My DB is on Linux. >> >> The other day, I restored his first file with: >> pg_restore --host "localhost" --port "5432" --username "postgres" >> --no-password --dbname "myname" --create --clean --verbose "dumpfile" >> >> when I saw this: >> >> pg_restore: dropping DATABASE myname >> Command was: DROP DATABASE myname; >> >> pg_restore: error: could not execute query: >> ERROR: cannot drop the currently open database >> >> Digging into the pg_dump'ed files, I see: >> >> CREATE DATABASE myname WITH TEMPLATE = template0 ENCODING = 'UTF8' >> LOCALE_PROVIDER = libc LOCALE = 'English_United States.1252'; >> DROP DATABASE myname; >> ^^^^^^^^^^^^^^^^^^^^^ I thought the --clean applied to the table being restored. The man page reads: -c ||||||| --clean VVVVVVV Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination vs => database.) so I took that to mean table; not the entire DB. 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. 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 >> >> >