Duplicating data folder without tablespace, for read access

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/&gt;

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:

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: