Schema, databse, or tables in different system folder
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.
In response to Carlos Oliva :
Is there a way to create a database or a table of a database in its own
Database or table?
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.
You can use tablespaces, see:
16:16 < akretschmer> ??tablespace
16:16 < rtfm_please> For information about tablespace
16:16 < rtfm_please> see http://people.planetpostgresql.org/xzilla/index.php?/archives/322-tablespace-management-variables.html
16:16 < rtfm_please> or http://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html
But read also the docu about backup/restore.
A simple file-system-copy on a running DB don't work as backup.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Thank you for your response. The tablespace should work for us. Perhaps you can help me with the following questions:
1) If we were to create a different table space for a database that has archival tables -- they will be backed up once, is it sufficient to backup the tablespace folder once? We want to make sure that we can restore from tape the tablespace folder and we will in fact restore the full database and data
2) Can a schema have its own tablespace in a database that has more than one schema?
Thank you very much.
-----Original Message-----
From: Grzegorz Jaśkiewicz [mailto:gryzman@gmail.com]
Sent: Tuesday, June 02, 2009 10:27 AM
To: Carlos Oliva
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Schema, databse, or tables in different system folder
yes, it is called tablespace.
"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
"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.
If you want to backup specific tables, then you should make use of
pg_dump, which, as of 8.3, has the ability to:
a) Dump named tables, via the -t option, or
b) exclude named tables, via the -T option
That is the appropriate way to selectively backup portions of the
database.
--
let name="cbbrowne" and tld="linuxfinances.info" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/wp.html
--Despite Pending :Alarm--
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...
Show quoted text
"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.htmlThere 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
"Carlos Oliva" <olivac@earthlink.net> writes:
Would the backup be unrecoverable if I shutdown the databse first?
If the backup includes pg_xlog and pg_clog, as well as all of the
database metadata files, then whatever portions *are* included are
likely to be somewhat usable.
The portions not included in the backup will obviously not be usable.
But supposing your backup does not include *all* metadata (e.g. -
pg_catalog), pg_xlog and pg_clog, then it is unlikely that you'll be
able to recover *anything at all* from it.
The intent is that filesystem backups only work for complete backup
and restoration of an entire database cluster.
Backing up *part* of your filesystem is Not Recommended in the
documentation. If you set a policy of doing so, you have to accept
that you are doing this contrary to documented recommendations, and at
considerable peril to your data.
--
(reverse (concatenate 'string "ofni.secnanifxunil" "@" "enworbbc"))
http://cbbrowne.com/info/multiplexor.html
Rules of the Evil Overlord #25. "No matter how well it would perform,
I will never construct any sort of machinery which is completely
indestructible except for one small and virtually inaccessible
vulnerable spot." <http://www.eviloverlord.com/>
Thank you for your response Chris. It is helping us a great deal to
understand the issues around backups. Would any of the pg_xlog, pg_clog,
etc change for a table that has a stable structure and data? That is, the
table undergoes several inserts and then it is never updated.
"Chris Browne" <cbbrowne@acm.org> wrote in message
news:87prdlgurv.fsf@dba2.int.libertyrms.com...
Show quoted text
"Carlos Oliva" <olivac@earthlink.net> writes:
Would the backup be unrecoverable if I shutdown the databse first?
If the backup includes pg_xlog and pg_clog, as well as all of the
database metadata files, then whatever portions *are* included are
likely to be somewhat usable.The portions not included in the backup will obviously not be usable.
But supposing your backup does not include *all* metadata (e.g. -
pg_catalog), pg_xlog and pg_clog, then it is unlikely that you'll be
able to recover *anything at all* from it.The intent is that filesystem backups only work for complete backup
and restoration of an entire database cluster.Backing up *part* of your filesystem is Not Recommended in the
documentation. If you set a policy of doing so, you have to accept
that you are doing this contrary to documented recommendations, and at
considerable peril to your data.
--
(reverse (concatenate 'string "ofni.secnanifxunil" "@" "enworbbc"))
http://cbbrowne.com/info/multiplexor.html
Rules of the Evil Overlord #25. "No matter how well it would perform,
I will never construct any sort of machinery which is completely
indestructible except for one small and virtually inaccessible
vulnerable spot." <http://www.eviloverlord.com/>