Thread: managing tablespaces like files?
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.
Sam
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)
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 3:52 PM, John R Pierce <pierce@hogranch.com> wrote:
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.)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)
Sam Carleton
Developer of Photo Parata
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:
how about...
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?
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
Sam
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 ourproprietary 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 informationproprietary 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 thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. 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:
On 3/9/2010 12:45 PM, Sam Carleton wrote:For more comments you will have to explain what the other files and folders are doing.Justin,Thank you for the reply. My application uses two databases at one time: system db and the "event" db. The system db is exactly that, used all the time, aka system settings. Each time a user starts the program, the user has the option of creating a new event. The concept is that an event is like a MS Word Document, so every time a user creates a new event, a new db is created along with a number of other files and folders.It is very natural for my customers what to pick up the event and copy it/back it up to another drive and/or computer. What I am doing now is running throw the pro's and con's of changing the model such that all the different events are stored in one database. There are differently advantages, but there are also drawbacks. Life as a one man MicroISV, what fun! ;)Sam Carleton
If the database stays small and does not need to manage simultaneous access by different users then Postgresql, MSSQL, Firebird, Mysql will just cause more headaches than what you gain.
If the files are shared and accessed by more than 3 users at the same time then its time to look at RDMS, or if the the data set is massive.
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.