Обсуждение: Schema, database, or tables in different folders?
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 table space. Check this groups archive, it was discussed quite recently. -- GJ
oops, I just realised you asked that question before :P
On Tuesday 02 June 2009, "Carlos Oliva" <CarlosO@pbsinet.com> wrote: > 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. You can put them in their own tablespace, but backing them up would not be useful. Filesystem backups of the cluster must include the whole database to be useful (and see the documentation on PITR for how to do it right). You can easily back up single databases with pg_dump, though. -- I contend that for a nation to try to tax itself into prosperity is like a man standing in a bucket and trying to lift himself up by the handle. -- Winston Churchill
Alan Hodgson wrote: > On Tuesday 02 June 2009, "Carlos Oliva" <CarlosO@pbsinet.com> wrote: > >> 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. >> > > You can put them in their own tablespace, but backing them up would not be > useful. Filesystem backups of the cluster must include the whole database > to be useful (and see the documentation on PITR for how to do it right). > > You can easily back up single databases with pg_dump, though. > > And pg_dump has a parameter to dump only a particular schema. I'd also suggest that a schema is a unit of logical partitioning whereas a tablespace is unit of physical partitioning and the need to back up only certain tables suggests a difference /requirement at the logical level to me, so I suspect schema is the more appropriate design choice anyway.
On 2009-06-02, Carlos Oliva <CarlosO@pbsinet.com> wrote: > This is a multi-part message in MIME format. > > ------_=_NextPart_001_01C9E386.D2E5B79B > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > Is there a way to create a database or a table of a database in its own > folder? this is what tablespaces are. > We are looking for ways to backup the sytem files of the > database to tape and one to exclude some tables from this backup. pg_dump has options to include only named tables, so you could in theory name all ther other tables on the command-line but what I do (being lazy) is use sed to remove the unwanted tables from the dump. sed '/^COPY unwantd_table ([^ ]*) FROM stdin;$/,/^\\.$/ d' the whitespace inside the [] is a tab character.