Re: Duplicating data folder without tablespace, for read access

Jack Cushman <jcushman(at)gmail(dot)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