Thread: 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.
yes, it is called tablespace.
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
"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--
"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... > "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/>
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
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, isit sufficient to backup the tablespace folder once? We want to make sure that we can restore from tape the tablespacefolder 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.