Would the backup be unrecoverable if I shutdown the databse first?
"Chris Browne" <cbbrowne(at)acm(dot)org> wrote in message
news:87ab4qfs48(dot)fsf(at)dba2(dot)int(dot)libertyrms(dot)com(dot)(dot)(dot)
> "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