Re: managing tablespaces like files?

On Mon, Mar 8, 2010 at 4:58 PM, Sam Carleton <scarleton(at)miltonstreet(dot)com>wrote:

> On Mon, Mar 8, 2010 at 4:07 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
>
>>
>> and what happens if someone copies your directory without shutting down
>> the instance?
>>
>
> Well, that is an issue right now, the current SQLite DB is locked by the
> app and cannot currently be copied while the app is running. Or at least I
> don't think it can be copied safely, which is why there is a backup function
> that will simply create a copy of the DB file where it is told to go,
> something I have yet to implement as of now.
>
>
>> sounds like trouble in the making to me. me thinks you're better off
>> sticking with a file based database and not trying to use a server based
>> one.
>
>
> I hear you, but I am not willing to throw in the towel, just yet...
> Generally speaking, is there a lot of metadata that would need to be
> exported? As I think about this, I am thinking I would have to read in ALL
> the system tables and find all the different parts to make up all the
> different aspects of all the stuff in the tablespace, like the tables,
> columns, views, stored procs, etc. It isn't a matter of just dumping one or
> two tables, but a matter of combing lots of tables and exporting lots of
> very specific pieces of those tables. Correct?
>

It's not just the catalogs, you'd have to grab the pg_clog files that were
appropriate and you would need to be 100% sure that you froze the xmins in
the table before you tried the 'detach'. The PG engine doesn't have the
notion of a 'transportable table[space]' internally. If you can live with
just doing a pg_dump and pg_restore to your next server, then postgres will
work for you. Instead of tablespaces, you could keep everything in schemas
in the database, then just dump / restore all objects in that schema.

If you're not able to make a change like that however, you'll probably be
better off with a file-based DB.

--Scott

>
> Sam
>