Re: Schema, databse, or tables in different system folder

"Carlos Oliva" <carlos(at)pbsinet(dot)com> writes:
> Is there a way to create a database or a table of a database in its own
> folder? We are looking for ways to backup the sytem files of the database
> to tape and one to exclude some tables from this backup. We can selectively
> backup folders of the file system so we figure that if we can create a
> schema or database or table in its own folder, we can backup our database
> and exclude the tables selectively. We are using Linux RedHat. Thank you.

What you are describing is the use of tablespaces, which are documented here:
http://www.postgresql.org/docs/8.3/static/manage-ag-tablespaces.html

There is, however, a severe problem with your backup plans, namely
that an attempt to selectively include/exclude tables in a physical
"file backup" is more or less certain to result in a totally
unrecoverable database.

Quoting from the section on filesystem level backup:
<http://www.postgresql.org/docs/8.3/static/backup-file.html>

"If you have dug into the details of the file system layout of the
database, you might be tempted to try to back up or restore only
certain individual tables or databases from their respective files
or directories. This will not work because the information contained
in these files contains only half the truth. The other half is in
the commit log files pg_clog/*, which contain the commit status of
all transactions. A table file is only usable with this
information. Of course it is also impossible to restore only a table
and the associated pg_clog data because that would render all other
tables in the database cluster useless. So file system backups only
work for complete backup and restoration of an entire database
cluster."

Let me reiterate that last sentence:

So file system backups only work for complete backup and restoration
of an entire database cluster.

Your attempt to selectively backup specific directories will render
the backup effectively useless.
--
"cbbrowne","@","linuxdatabases.info"
http://linuxdatabases.info/info/unix.html
"Microsoft builds product loyalty on the part of network
administrators and consultants, [these are] the only people who really
count in the Microsoft scheme of things. Users are an expendable
commodity." -- Mitch Stone 1997