Обсуждение: Duplicating data folder without tablespace, for read access
Hi --
I'm wondering whether, in my specific situation, it would be safe to copy a database cluster's data folder, and bring up the copy for read access, without copying a tablespace linked from it. My situation (described below) involves a database with a 100GB table and a 600GB table where I want to routinely clone just the 100GB table for web access.
---
For context, the last discussion I've found is from 2013, in this blog post from Christophe Pettus and response from Tom Lane:
In that discussion, Christophe summarized the situation this way:
> I would not count on it. And if it works 100% reliably now, it might not on a future version of PostgreSQL.
> As Josh Berkus pointed out to my off-list, there are two competing definitions of the term "recover" in use here:
> 1. In my blog post, the definition of "recover" was "bring up the database without having unusually extensive knowledge of PostgreSQL's internals."
> 2. For Tom, the definition of "recover" is "bring up the database if you have appropriate knowledge of PostgreSQL's internals."
> You can't recover from the lost of a tablespace per definition #1. You can per definition #2.
> I'd strongly suggest that relying on definition #2, while absolutely correct, is a poor operational decision for most users.
> As Josh Berkus pointed out to my off-list, there are two competing definitions of the term "recover" in use here:
> 1. In my blog post, the definition of "recover" was "bring up the database without having unusually extensive knowledge of PostgreSQL's internals."
> 2. For Tom, the definition of "recover" is "bring up the database if you have appropriate knowledge of PostgreSQL's internals."
> You can't recover from the lost of a tablespace per definition #1. You can per definition #2.
> I'd strongly suggest that relying on definition #2, while absolutely correct, is a poor operational decision for most users.
---
Now here's the situation where I want to do what Christophe said not to do: :)
I have a large database of text, with a 600GB table and a 100GB table connected by a join table. They both see occasional updates throughout the week. Once a week I want to "cut a release," meaning I will clone just the 100GB table and copy it to a "release" server for read-only web access.
My procedure would be:
- keep the 600GB table on a separate tablespace
- cleanly stop postgres on both servers
- copy the data folder to the release server
- delete pg_tblspc/* on the release server
- start postgres on both servers
In local testing this seems to work -- the release server works fine, and I only get an error message if I try to access the missing tables, which is expected. But are there reasons this is going to bite me if I try it in production? I'm hoping it helps that (a) I'm only doing read access, (b) I can cleanly stop both servers before cutting a release, and (c) I'm not worried about losing data, since it's just an access copy.
Alternatives I've considered:
- I could pg_dump and restore, but the 100GB table has lots of indexes and I'd rather not have to reindex on the release server each week.
- I could replicate with pglogical and use some sort of blue-green setup on the release server to cut a release, but this adds a lot of moving parts, especially to deal with schema migrations.
Thanks for any advice you might have!
-Jack
Jack Cushman <jcushman@gmail.com> writes:
> Now here's the situation where I want to do what Christophe said not to do:
> :)
> I have a large database of text, with a 600GB table and a 100GB table
> connected by a join table. They both see occasional updates throughout the
> week. Once a week I want to "cut a release," meaning I will clone just the
> 100GB table and copy it to a "release" server for read-only web access.
> My procedure would be:
> - keep the 600GB table on a separate tablespace
> - cleanly stop postgres on both servers
> - copy the data folder to the release server
> - delete pg_tblspc/* on the release server
> - start postgres on both servers
> In local testing this seems to work -- the release server works fine, and I
> only get an error message if I try to access the missing tables, which is
> expected. But are there reasons this is going to bite me if I try it in
> production?
Sooner or later, autovacuum is going to try to touch the missing table.
Maybe you can limp along with autovac failing in the background,
especially if this is a throwaway copy of the DB with little or no
write activity. But I'm not sure how well that will work.
Probably, the worst-case scenario would be for the database to shut
down because it thinks it's in XID wraparound trouble. But it's hard
to see how you get to that without lots of write traffic, so maybe
you can get away with this.
regards, tom lane
Greetings, * Jack Cushman (jcushman@gmail.com) wrote: > I have a large database of text, with a 600GB table and a 100GB table > connected by a join table. They both see occasional updates throughout the > week. Once a week I want to "cut a release," meaning I will clone just the > 100GB table and copy it to a "release" server for read-only web access. My general recommendation to people who are thinking about something like this is to use their restore-tests as a way to stage things (you are testing your backups by doing a restore, right?) and then copy over the results. > My procedure would be: > > - keep the 600GB table on a separate tablespace > - cleanly stop postgres on both servers > - copy the data folder to the release server > - delete pg_tblspc/* on the release server > - start postgres on both servers So, instead of that procedure, it'd be: - Back up the database as per usual - Restore the database somewhere - Run some sanity checks on the restored database - go in and drop the table and sanitize anything else necessary - Shut down the database and copy it into place - OR take a new backup of the sanitized database and then restore it into place Much cleaner, and tests your backup/restore process. Alternatively, you could just track changes to the "main" database using triggers into an audit log and then replay the changes made to the 100GB table into the other database. > In local testing this seems to work -- the release server works fine, and I > only get an error message if I try to access the missing tables, which is > expected. But are there reasons this is going to bite me if I try it in > production? I'm hoping it helps that (a) I'm only doing read access, (b) I > can cleanly stop both servers before cutting a release, and (c) I'm not > worried about losing data, since it's just an access copy. Still, it's a hacked up and not entirely proper PG database which will likely lead to confusion- maybe you won't be confused, but I strongly suspect others looking at it will be, and you might run into other issues too along the lines of what Tom mentioned (background jobs failing and such). Thanks! Stephen
Вложения
Tom Lane <tgl@sss.pgh.pa.us> writes: > Jack Cushman <jcushman@gmail.com> writes: > >> Now here's the situation where I want to do what Christophe said not to do: >> :) > >> I have a large database of text, with a 600GB table and a 100GB table >> connected by a join table. They both see occasional updates throughout the >> week. Once a week I want to "cut a release," meaning I will clone just the >> 100GB table and copy it to a "release" server for read-only web access. > >> My procedure would be: > >> - keep the 600GB table on a separate tablespace >> - cleanly stop postgres on both servers >> - copy the data folder to the release server >> - delete pg_tblspc/* on the release server >> - start postgres on both servers > >> In local testing this seems to work -- the release server works fine, and I >> only get an error message if I try to access the missing tables, which is >> expected. But are there reasons this is going to bite me if I try it in >> production? > > Sooner or later, autovacuum is going to try to touch the missing table. > Maybe you can limp along with autovac failing in the background, > especially if this is a throwaway copy of the DB with little or no > write activity. But I'm not sure how well that will work. > > Probably, the worst-case scenario would be for the database to shut > down because it thinks it's in XID wraparound trouble. But it's hard > to see how you get to that without lots of write traffic, so maybe > you can get away with this. > > regards, tom lane > With Pg being pretty good about letting you drop objects that have missing backend storage bits, such as when we snapshot large DBs on the SAN which does *not* capture our NVMe temp tablespace... I'd say find the objects supposed to be in the missing tablespace by catalog scraping and drop them. You'll see warnings or whatever in the logs that file $foo not found but the objects are then gone nonetheless. Presuming this avoids risk of eventual wrap conditions etc due to cronic autovac failure if tables left in catalogs. FWIW > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
On 2018-08-14 11:57:38 -0400, Jack Cushman wrote:
> I have a large database of text, with a 600GB table and a 100GB table connected
> by a join table. They both see occasional updates throughout the week. Once a
> week I want to "cut a release," meaning I will clone just the 100GB table and
> copy it to a "release" server for read-only web access.
>
> My procedure would be:
>
> - keep the 600GB table on a separate tablespace
> - cleanly stop postgres on both servers
> - copy the data folder to the release server
> - delete pg_tblspc/* on the release server
> - start postgres on both servers
If you copy the whole database anyway before deleting the tablespace:
Why don't you just drop the 600 GB table on the release server?
hp
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Вложения
Thanks for such quick and helpful answers! My plan sounds probably better to avoid, but if it turns out to be necessary, you all gave me some helpful avenues and things to look out for.
Best,
Jack
On Tue, Aug 14, 2018 at 1:06 PM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Jack Cushman (jcushman@gmail.com) wrote:
> I have a large database of text, with a 600GB table and a 100GB table
> connected by a join table. They both see occasional updates throughout the
> week. Once a week I want to "cut a release," meaning I will clone just the
> 100GB table and copy it to a "release" server for read-only web access.
My general recommendation to people who are thinking about something
like this is to use their restore-tests as a way to stage things (you
are testing your backups by doing a restore, right?) and then copy over
the results.
> My procedure would be:
>
> - keep the 600GB table on a separate tablespace
> - cleanly stop postgres on both servers
> - copy the data folder to the release server
> - delete pg_tblspc/* on the release server
> - start postgres on both servers
So, instead of that procedure, it'd be:
- Back up the database as per usual
- Restore the database somewhere
- Run some sanity checks on the restored database
- go in and drop the table and sanitize anything else necessary
- Shut down the database and copy it into place
- OR take a new backup of the sanitized database and then restore it
into place
Much cleaner, and tests your backup/restore process.
Alternatively, you could just track changes to the "main" database using
triggers into an audit log and then replay the changes made to the 100GB
table into the other database.
> In local testing this seems to work -- the release server works fine, and I
> only get an error message if I try to access the missing tables, which is
> expected. But are there reasons this is going to bite me if I try it in
> production? I'm hoping it helps that (a) I'm only doing read access, (b) I
> can cleanly stop both servers before cutting a release, and (c) I'm not
> worried about losing data, since it's just an access copy.
Still, it's a hacked up and not entirely proper PG database which will
likely lead to confusion- maybe you won't be confused, but I strongly
suspect others looking at it will be, and you might run into other
issues too along the lines of what Tom mentioned (background jobs
failing and such).
Thanks!
Stephen
To follow up, ZFS snapshots (appear to) offer a great solution to the problem I posed a couple of weeks ago, and avoid any hacking around with misuse of tablespaces.
My goal was to have a database with a 100GB table and a 600GB table, and to routinely and efficiently clone the 100GB table and its indexes to a cluster on another machine.
The general procedure for solving this with ZFS is:
- zfs snapshot the source data directory (after shutting down the database or taking appropriate steps to get a clean copy)
- zfs clone to the same machine. This takes no actual disk space or time because of copy-on-write.
- Run postgres using the cloned data directory and truncate unwanted tables. This still takes minimal real disk space.
- zfs send the cloned data directory to the remote machine. If running repeatedly (as I am), use incremental send to avoid resending unchanged blocks.
The upshot is to waste minimal time copying bits that are unwanted or haven't changed. To mix in Stephen's suggestion, do this from a backup server to exercise the backups.
This blog post was helpful in figuring out how to get all that working: https://blog.2ndquadrant.com/pg-phriday-postgres-zfs/
Thanks,
Jack
On Tue, Aug 14, 2018 at 11:57 AM Jack Cushman <jcushman@gmail.com> wrote:
Hi --I'm wondering whether, in my specific situation, it would be safe to copy a database cluster's data folder, and bring up the copy for read access, without copying a tablespace linked from it. My situation (described below) involves a database with a 100GB table and a 600GB table where I want to routinely clone just the 100GB table for web access.---For context, the last discussion I've found is from 2013, in this blog post from Christophe Pettus and response from Tom Lane:In that discussion, Christophe summarized the situation this way:> I would not count on it. And if it works 100% reliably now, it might not on a future version of PostgreSQL.
> As Josh Berkus pointed out to my off-list, there are two competing definitions of the term "recover" in use here:
> 1. In my blog post, the definition of "recover" was "bring up the database without having unusually extensive knowledge of PostgreSQL's internals."
> 2. For Tom, the definition of "recover" is "bring up the database if you have appropriate knowledge of PostgreSQL's internals."
> You can't recover from the lost of a tablespace per definition #1. You can per definition #2.
> I'd strongly suggest that relying on definition #2, while absolutely correct, is a poor operational decision for most users.---Now here's the situation where I want to do what Christophe said not to do: :)I have a large database of text, with a 600GB table and a 100GB table connected by a join table. They both see occasional updates throughout the week. Once a week I want to "cut a release," meaning I will clone just the 100GB table and copy it to a "release" server for read-only web access.My procedure would be:- keep the 600GB table on a separate tablespace- cleanly stop postgres on both servers- copy the data folder to the release server- delete pg_tblspc/* on the release server- start postgres on both serversIn local testing this seems to work -- the release server works fine, and I only get an error message if I try to access the missing tables, which is expected. But are there reasons this is going to bite me if I try it in production? I'm hoping it helps that (a) I'm only doing read access, (b) I can cleanly stop both servers before cutting a release, and (c) I'm not worried about losing data, since it's just an access copy.Alternatives I've considered:- I could pg_dump and restore, but the 100GB table has lots of indexes and I'd rather not have to reindex on the release server each week.- I could replicate with pglogical and use some sort of blue-green setup on the release server to cut a release, but this adds a lot of moving parts, especially to deal with schema migrations.Thanks for any advice you might have!-Jack