Message-ID: <4DFB5560.4050804@dalibo.com>
Date: Fri, 17 Jun 2011 15:23:44 +0200
From: Nicolas Thauvin <nicolas.thauvin@dalibo.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110510 Icedove/3.1.10
MIME-Version: 1.0
To: Ibrahim Harrani <ibrahim.harrani@gmail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: pg_dump and drop table if exists statement
References: <BANLkTim45-uyjEQR5nL9kRBn0D0c_sJf7g@mail.gmail.com>
In-Reply-To: <BANLkTim45-uyjEQR5nL9kRBn0D0c_sJf7g@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit


On 13/06/2011 09:56, Ibrahim Harrani wrote:
> Hi,
> 
> I am using PostgreSQL 9.0 I would like to  dump some tables and all
> functions,triggers on the database.
> If I drop with pg_dump -c  parameter, it removes public schema as
> well.  

With -c, the drop of the public schema does not cascade, so the objects
of the public schema that are not in the dump won't be deleted when
restoring.

> pg_dump -C parameter output has create table
> but I can't restore this output directly(Because the tables are
> already exist on the db)

-C creates the database and connect to it inside the dump, it is
different from -c.

> There is no problem with functions. They have already this feature.
>
> How can backup/restore tables with DROP TABLE IF EXISTS statement like
> in MySQL?  without dropping all database and create again?

Another way to do it is to use the custom format of pg_dump (-Fc), then
pg_restore can be used with -c to first drop the objects to be restored.

More, you can get pg_restore to echo the TOC with -l to a file, modify
this file to choose what to restore, then run pg_restore again with -L
/your/custom/list.

PS: be careful with the -f option of pg_restore, if you don't know the tool

-- 
Nicolas Thauvin
DBA
http://www.dalibo.com