On Mon, Mar 8, 2010 at 4:58 PM, Sam Carleton
<scarleton@miltonstreet.com> wrote:
On Mon, Mar 8, 2010 at 4:07 PM, John R Pierce
<pierce@hogranch.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
