From: David Wilson <david.t.wilson@gmail.com>
To: John Smith <sodgodofall@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Backup/Restore of single table in multi TB database
Date: Wed, 7 May 2008 16:09:45 -0400
Message-ID: <e7f9235d0805071309o6d6d7778r6920e5df03a55d3b@mail.gmail.com> (raw)
In-Reply-To: <b88f0d670805071302h469141e0ke29413edddb444e0@mail.gmail.com>
References: <b88f0d670805071302h469141e0ke29413edddb444e0@mail.gmail.com>
On Wed, May 7, 2008 at 4:02 PM, John Smith <sodgodofall@gmail.com> wrote:
> Does anyone see a problem with this approach (e.g., correctness,
> performance, etc.)? Or is there perhaps an alternative approach using
> some other postgresql mechanism that I'm not aware of?
Did you already look at and reject pg_dump for some reason? You can
restrict it to specific tables to dump, and it can work concurrently
with a running system. Your database is large, but how large are the
individual tables you're interested in backing up? pg_dump will be
slower than a file copy, but may be sufficient for your purpose and
will have guaranteed correctness.
I'm fairly certain that you have to be very careful about doing simple
file copies while the system is running, as the files may end up out
of sync based on when each individual one is copied. I haven't done it
myself, but I do know that there are a lot of caveats that someone
with more experience doing that type of backup can hopefully point you
to.
--
- David T. Wilson
david.t.wilson@gmail.com
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: david.t.wilson@gmail.com, sodgodofall@gmail.com, pgsql-general@postgresql.org
Subject: Re: Backup/Restore of single table in multi TB database
In-Reply-To: <e7f9235d0805071309o6d6d7778r6920e5df03a55d3b@mail.gmail.com>
References: <b88f0d670805071302h469141e0ke29413edddb444e0@mail.gmail.com> <e7f9235d0805071309o6d6d7778r6920e5df03a55d3b@mail.gmail.com>
* 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