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

Would the backup be unrecoverable if I shutdown the databse first?
"Chris Browne" <cbbrowne@acm.org> wrote in message
news:87ab4qfs48.fsf@dba2.int.libertyrms.com...
> "Carlos Oliva" <carlos@pbsinet.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