Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 30C436347A9 for ; Tue, 20 Apr 2010 13:36:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 48643-04-4 for ; Tue, 20 Apr 2010 16:35:44 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gw0-f46.google.com (mail-gw0-f46.google.com [74.125.83.46]) by mail.postgresql.org (Postfix) with ESMTP id 07DC06322D8 for ; Tue, 20 Apr 2010 13:25:41 -0300 (ADT) Received: by gwj19 with SMTP id 19so889852gwj.19 for ; Tue, 20 Apr 2010 09:25:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=+bykwNo1TIh5nlJq1yQN0IlqCFWRwwyXAp9QfOA2AJA=; b=ciIVwtJP4J9V9lPoA0W/RJi4ot3OZ2CJNC69vPKOKyqG6xyowxh4xSxsthxwsEGjVK fDVbvdbIolsRjZYNlvINKbey1Hp0L0ca4DzQQrjTuJ/tbQdTidPWABaadWuWEyD79NVD jU2qbpDMuSV85fiu8BllsO+RcebRCetrvxyfk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=WhUEVc60xe5X0Dj4G4qjBq26cdnCpzu+jMyDYFDSqGswV/gzeEoTi3i4htd3syO4mi lw1hjbGSJ8vExCD5TQF/gNHxIwMkQ4b4sq7u33LRT6bSKx97MKDcadXj/+v+DB+gofJC wFoW1YhvPfC4gTS51Xk8YD7SZ+gXzUJ9mDm8s= MIME-Version: 1.0 Received: by 10.150.218.9 with HTTP; Tue, 20 Apr 2010 09:25:41 -0700 (PDT) In-Reply-To: References: Date: Tue, 20 Apr 2010 10:25:41 -0600 Received: by 10.150.208.5 with SMTP id f5mr3006566ybg.271.1271780741523; Tue, 20 Apr 2010 09:25:41 -0700 (PDT) Message-ID: Subject: Re: drop database / create database / data still here ? From: Scott Marlowe To: Mitch Collinsworth Cc: pgsql-admin@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.968 tagged_above=-10 required=5 tests=AWL=-0.068, BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201004/290 X-Sequence-Number: 34384 On Tue, Apr 20, 2010 at 10:06 AM, Scott Marlowe w= rote: > On Tue, Apr 13, 2010 at 12:16 PM, Mitch Collinsworth > wrote: >> >> Hi Folks, >> >> I thought I understood how this worked, but now it's baffling me. >> I want to drop a database named coral from my test server and load >> a fresh dump of it from my production server. =A0This has worked when >> I've done it before, but now it's giving me fits. =A0Here's an >> example: >> >> coral=3D# \c postgres >> You are now connected to database "postgres". >> postgres=3D# drop database coral; >> DROP DATABASE >> postgres=3D# create database coral; >> CREATE DATABASE >> postgres=3D# \c coral >> You are now connected to database "coral". >> coral=3D# \dt *.* >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 List of relations >> =A0 =A0 =A0 Schema =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0Name =A0 =A0= =A0 =A0 =A0 =A0 =A0| Type =A0| =A0Owner >> --------------------+--------------------------------+-------+---------- >> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_rate =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0| table | accmgr >> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_rate_working =A0 =A0 =A0 =A0 = =A0 =A0 =A0| table | accmgr >> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_sum =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 | table | accmgr >> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_sum_raw =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 | table | accmgr > > I'm pretty sure that at some time you accidentally created those > tables in template1 and now you're getting them every time you create > a database. =A0Clean out template1 and things should be ok. Note that if you have thousands of objects in template1, you can drop it and recreate it from template0 like so: update pg_database set datistemplate=3Dfalse where datname=3D'template1'; drop database template1; create database template1 with template template0; update pg_database set datistemplate=3Dtrue where datname=3D'template1'; \c template1 vacuum freeze;