X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C3457D1B4E8 for ; Sun, 16 Nov 2003 05:19:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 75697-05 for ; Sun, 16 Nov 2003 01:18:44 -0400 (AST) Received: from web21404.mail.yahoo.com (web21404.mail.yahoo.com [216.136.232.74]) by svr1.postgresql.org (Postfix) with SMTP id A0F7BD1B4FB for ; Sun, 16 Nov 2003 01:18:43 -0400 (AST) Message-ID: <20031116051842.57962.qmail@web21404.mail.yahoo.com> Received: from [64.81.205.234] by web21404.mail.yahoo.com via HTTP; Sat, 15 Nov 2003 21:18:42 PST Date: Sat, 15 Nov 2003 21:18:42 -0800 (PST) From: ow Subject: Re: Programatically switching database To: Jan Wieck Cc: pgsql-sql@postgresql.org In-Reply-To: <3FB6DE5B.1020000@Yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/181 X-Sequence-Number: 15996 --- Jan Wieck wrote: > #!/bin/sh > > ( > echo "start transaction;" > cat $2 > echo "commit transaction;" > ) psql $1 > > > > then call it as > > reload_in_transaction my_db my_namespace.dump > > Since the whole dump will be restored inside of one transaction, nobody > will see it while it's reloading. Interesting idea. I know some RDBMSs that are very picky about DDL in transactions but it appears pgSql handles it without any problems. My concern though ... wouldn't pgSql server collapse when faced with transaction spawning across 100M+ records? And how would that affect overall restore time? I've should've tried it myself but I can't, my "regular" pg_restore has been running for 4+ hours, can't kill now ... Thanks __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree