managing tablespaces like files?

Sam Carleton wrote:

The server of my client/server program is currently using SQLite, an
embedded file based SQL engine. I am looking for a client/server
based RDBM and have narrowed it down to either PosgreSQL or Firebird.
I would prefer to use PosgreSQL, but the management of the physical
files are concerning me.

A very key element of the program I am developing is the ability for
the user to pickup one folder that contains all the data for that
"document" and copy it somewhere else, then allow the program to read
it from that other location. It is equally important that when the
database server initially start, if one of the "tablespaces" was
deleted by the user, the database be none the wiser.

This is, of course, not a problem with Firebird since it is file
based. What I could do is simply disconnect from the tablespace each
and every time the server program shutdown and reconnect to that
tablespace each time the server starts up, pointing to a new
tablespace if need be. So how would one:

1: "disconnect" from a tablespace, in such a way that the tablespace,
nor it's contents is changed.
2: "connect" to an existing tablespace.

I don't think PostgreSQL is going to work for you if thats a
requirement. A tablespace doesn't contain the metadata for the items
in it, thats stored in the main cluster space (pg_catalog, etc)

On Mon, Mar 8, 2010 at 3:52 PM, John R Pierce <pierce@hogranch.com> wrote:

I don't think PostgreSQL is going to work for you if thats a requirement.
A tablespace doesn't contain the metadata for the items in it, thats stored
in the main cluster space (pg_catalog, etc)

Would it be difficult to export the metadata as part of the shutdown process
of the service? Or am I simply reaching a bit too much? (For the record,
my application exists because I run into this type of "things don't work
that way" and than I find some interesting solutions <grin> But maybe this
time I am simply better off with Firebird.)

Sam Carleton
Developer of Photo Parata <http://www.miltonstreet.com&gt;

Sam Carleton wrote:

On Mon, Mar 8, 2010 at 3:52 PM, John R Pierce <pierce@hogranch.com
<mailto:pierce@hogranch.com>> wrote:

I don't think PostgreSQL is going to work for you if thats a
requirement. A tablespace doesn't contain the metadata for the
items in it, thats stored in the main cluster space (pg_catalog, etc)

Would it be difficult to export the metadata as part of the shutdown
process of the service? Or am I simply reaching a bit too much? (For
the record, my application exists because I run into this type of
"things don't work that way" and than I find some interesting
solutions <grin> But maybe this time I am simply better off with
Firebird.)

and what happens if someone copies your directory without shutting down
the instance?

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.

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?

Sam

Sam Carleton wrote:

On Mon, Mar 8, 2010 at 4:07 PM, John R Pierce <pierce@hogranch.com
<mailto: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?

how about...
pg_dump -Fc dbname >filename.dump

On Mon, Mar 8, 2010 at 5:46 PM, John R Pierce <pierce@hogranch.com> wrote:

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?

how about...
pg_dump -Fc dbname >filename.dump

One of the cases I need to be concerned with is the case where the
application is shut down, the user goes in and deletes the tablespace folder
by way of deleting the parent folder. How will PostgreSQL handle such a
situation?

Sam

Sam Carleton <scarleton@miltonstreet.com> writes:

One of the cases I need to be concerned with is the case where the
application is shut down, the user goes in and deletes the tablespace folder
by way of deleting the parent folder. How will PostgreSQL handle such a
situation?

It won't like it. If your users are likely to do such a thing, Postgres
is not the database for you.

regards, tom lane

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

I would like to thank both John and Scott for the help. It is very clear to
me that PostgreSQL isn't the ideal solution for my current model. The
conversation has gotten me thinking of ways the model could be modified to
work with PostgrSQL (and other client/server RDBM). Thus I will return to
the drawing board just to double check to see if there might be a better
model.

Thank you all, this has been truly valuable!

Sam

On 3/9/2010 12:07 AM, Sam Carleton wrote:

I would like to thank both John and Scott for the help. It is very
clear to me that PostgreSQL isn't the ideal solution for my current
model. The conversation has gotten me thinking of ways the model
could be modified to work with PostgrSQL (and other client/server
RDBM). Thus I will return to the drawing board just to double check
to see if there might be a better model.

Thank you all, this has been truly valuable!

Sam

Hello Sam:

I've had similar conversation with Application developers who are used
to working with Access databases aka file based databases. When it comes
time to backup or move the databases to other computer or share the
database over a file-share they look at databases as just files to be
copied around from machine to machine. No database server like oracle,
postgresql, mssql, mysql, or db2 allow simple copying of the database to
another location/server having it start right up. None are going to work
that way.

These databases expressly deny direct access to the files and make the
assumption their the only process accessing the files. Another problem
you may run into and need to think about is Anti-virus apps have a
tendency to create all kinds of problems with database servers.

The application can work and make the user life even easier. The
approach has to be different on how the backup restore and moving the
databases around is going to work .

Take a look at a couple of examples how others have solved this problem

One company using postgresql, directly aimed at small companies (where
there is no IT help let money to pay for IT) wrote a very nice and easy
to use front end for there application to backup, restore and move the
application to another server. go to www.3cx.com and download there
app they have a free version check out the instruction for back and
restore. Its very easy clean interface how they backup and restore the
database.

Another company that has very nice and easy to use backup and restore
of a MySQL database is Gordano Messaging Server. www.gordano.com their
backup, recovery and moving to another server is very simply. A simple
little command line tool backups the database, user profiles, and other
system settings into a nice little file called settings.txt. They also
have gui tool that will automate the backing up of the database.

All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

Justin,

It is an image viewing system for events. Each event could have a few
hundred images (each one will be between a 3 to 12 megs file) and as many as
one hundred thousand images. So these are the other files, the other
folders is how all these images are stored and managed. The database
contains basic customer info and references to the images they picked (path
and image name). Each event is a unique entity, such that the customers and
their selection of images are unique to the event in which the images where
created. Because of this, right now I am using SQLite which creates a new
"event" database for each and every event. Depending on my customer, some
folks simply delete the whole event when it is over, while others back it up
off their dedicated event server machine and load it up on their desktop at
home to finish processing the images.

The images are served up via the Apache web server, so currently there is
only one process access the SQLite process, well, possibly two or three,
depending on how many instances Apache start, but it is limited and there
are a ton more reads then there are writes.

Why I am looking for other options is it time to develop some new .Net
client modules to access the data in new and different ways. Initially I
was going to have everything access the data through Apache, but now I am
thinking that I can speed up the time to market if I where to move to a
traditional database and let the new .Net clients connect directly to the
database.

From the prospective of my end users, most who know NOTHING about databases,
keeping a file based database makes a LOT of sense. From the standpoint of
eventually taking this to also be hosted online, it makes a LOT of sense to
rework the current model to one central DB that can hold multiple events.
The only question is how do I keep the system user friendly for my
customers that know nothing about computers. That is the question. In the
end, my software is all about letting my customers have their cake and eat
it too!

Sam

On Tue, Mar 9, 2010 at 2:15 PM, Justin Graf <justin@magwerks.com> wrote: