From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Smith <sodgodofall@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Backup/Restore of single table in multi TB database
Date: Wed, 07 May 2008 17:41:01 -0400
Message-ID: <19037.1210196461@sss.pgh.pa.us> (raw)
In-Reply-To: <b88f0d670805071302h469141e0ke29413edddb444e0@mail.gmail.com>
References: <b88f0d670805071302h469141e0ke29413edddb444e0@mail.gmail.com>
"John Smith" <sodgodofall@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sodgodofall@gmail.com, pgsql-general@postgresql.org
Subject: Re: Backup/Restore of single table in multi TB database
In-Reply-To: <19037.1210196461@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox