Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 8DE3E2E0068 for ; Wed, 7 May 2008 18:41:10 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 51864-01 for ; Wed, 7 May 2008 18:41:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by developer.postgresql.org (Postfix) with ESMTP id 84AB72E0057 for ; Wed, 7 May 2008 18:41:05 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m47Lf1Hq019038; Wed, 7 May 2008 17:41:01 -0400 (EDT) To: "John Smith" cc: pgsql-general@postgresql.org Subject: Re: Backup/Restore of single table in multi TB database In-reply-to: References: Comments: In-reply-to "John Smith" message dated "Wed, 07 May 2008 13:02:57 -0700" Date: Wed, 07 May 2008 17:41:01 -0400 Message-ID: <19037.1210196461@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/243 X-Sequence-Number: 132642 "John Smith" writes: > After reading the documentation, it seems like the following might > work. Suppose the database has two tables foo and bar, and we're only > interested in backing up table foo: > 1. Call pg_start_backup > 2. Use the pg_class table in the catalog to get the data file names > for tables foo and bar. > 3. Copy the system files and the data file for foo. Skip the data file for bar. > 4. Call pg_stop_backup() > 5. Copy WAL files generated between 1. and 4. to another location. > Later, if we want to restore the database somewhere with just table > foo, we just use postgres's normal recovery mechanism and point it at > the files we backed up in 2. and the WAL files from 5. > Does anyone see a problem with this approach Yes: it will not work, not even a little bit, because the WAL files will contain updates for all the tables. You can't just not have the tables there during restore. Why are you not using pg_dump? regards, tom lane