Обсуждение: Database over multiple drives
Hi all, Can I spread a database over multiple physical drives? I want to put temp files somewhere, some tables somewhere, some other tables somewhere else etc. Is this possible? Why? My db is growing at an enormous rate, last month, it didn't exist, now it's 530 MB - and that's just one client, we're expecting about 50 clients by year end. Best regards, Chris _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
You can't do it with Postgres. To do it, Postgres must implement "tablespaces" to spread the database in different localizations (who can be different physical localizations, of course). Then a table can be assigned to a tablespace. If the RDBMS implements partitions also, you can distribute a table (by rows) in different tablespaces. Of course it can improve the perfomance by reducing the I/O operations. >Hi all, > >Can I spread a database over multiple physical drives? I want to put temp >files somewhere, some tables somewhere, some other tables somewhere else >etc. Is this possible? Why? My db is growing at an enormous rate, last >month, it didn't exist, now it's 530 MB - and that's just one client, we're >expecting about 50 clients by year end. > >Best regards, >Chris > > >_________________________________________________________ >Do You Yahoo!? >Get your free @yahoo.com address at http://mail.yahoo.com > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html
On Thu, May 31, 2001 at 10:37:26AM +0200, David Lizano wrote: > You can't do it with Postgres. To do it, Postgres must implement > "tablespaces" to spread the database in different localizations (who can be > different physical localizations, of course). Then a table can be assigned > to a tablespace. Sure you can. You can move some files to a different drive, and put a symlink in the original directory. Or, if you have an operatingsystem that has an logical volume manager, you can concatenate several disks, use striping or whatever, to get a logical device that spans several physical devices. -- Ragnar Kjørstad Big Storage
>On Thu, May 31, 2001 at 10:37:26AM +0200, David Lizano wrote: > > You can't do it with Postgres. To do it, Postgres must implement > > "tablespaces" to spread the database in different localizations (who > can be > > different physical localizations, of course). Then a table can be assigned > > to a tablespace. > >Sure you can. >You can move some files to a different drive, and put a symlink in the >original directory. > >Or, if you have an operatingsystem that has an logical volume manager, >you can concatenate several disks, use striping or whatever, to get a >logical device that spans several physical devices. > Yes, you can do it, but it should be managed by the RDBMS, so it isn't the optimal solution. Of course a RAID of disk improve the I/O perfomance. If the RDBMS implements "tablespaces" is easier also to make a backup in a big database.
Has anybody *really* tried this solution ? Is it safe ? TIA Andy ----- Original Message ----- From: "Ragnar Kjørstad" <postgres@ragnark.vestdata.no> To: "David Lizano" <david.lizano@izanet.com> Cc: <pgsql-admin@postgresql.org> Sent: Thursday, May 31, 2001 4:33 PM Subject: Re: Database over multiple drives > On Thu, May 31, 2001 at 10:37:26AM +0200, David Lizano wrote: > > You can't do it with Postgres. To do it, Postgres must implement > > "tablespaces" to spread the database in different localizations (who can be > > different physical localizations, of course). Then a table can be assigned > > to a tablespace. > > Sure you can. > You can move some files to a different drive, and put a symlink in the > original directory. > > Or, if you have an operatingsystem that has an logical volume manager, > you can concatenate several disks, use striping or whatever, to get a > logical device that spans several physical devices. > > > -- > Ragnar Kjørstad > Big Storage >
At 11:55 2001-06-01 +0700, Andy Samuel wrote: >Has anybody *really* tried this solution ? >Is it safe ? Not with LVM, but with Postgres.....7.0 I think, we had some extraordinary large files and used symlinks to other partitions. Went fine. Using a non-beta LVM should be as fine as using a single partition. Stefan -- If a man stands in the middle of the forest speaking and there is no woman around to hear him... is he still wrong?
Yes!! I have done this very successfully. I have mounts: /usr/local/pgsql/data 8Gig slice on Primary IDE-Slave /usr/local/pgsql/data2 8Gig slice on Secondary IDE-Master /usr/local/pgsql/data3 4Gig slice on Primary IDE-Master Then I move files from data/base/<dbdir> to data2/base/<dbdir> and create a symbolic link with the following. $ pg_ctl stop *NOTE* Make sure it really shut down, I have some long running tasks which have bitten me during a moment of stupidity $ cd /usr/local/pgsql/data/base/<dbdir> $ mv <datafile> /usr/local/pgsql/data2/base/<dbdir>/<datafile> $ ln -s /usr/local/pgsql/data2/base/<dbdir>/<datafile> . $ pg_ctl start I currently only have the pg_xlog directory on data3 because that drive is also shared with the operating system. But just moving the pg_xlog directory alone gave me a significant performance boost. By freeing up the data drives from having to write those log files, I am less likely to have to wait for the heads to move around after fsyncing a log file. You can also move entire database directories using commands similar to those above. - brian ----- Original Message ----- From: "Andy Samuel" <andysamuel@geocities.com> To: <pgsql-admin@postgresql.org> Sent: Thursday, May 31, 2001 11:55 PM Subject: [ADMIN] Re: Database over multiple drives > Has anybody *really* tried this solution ? > Is it safe ? > > TIA > Andy > > ----- Original Message ----- > From: "Ragnar Kjørstad" <postgres@ragnark.vestdata.no> > To: "David Lizano" <david.lizano@izanet.com> > Cc: <pgsql-admin@postgresql.org> > Sent: Thursday, May 31, 2001 4:33 PM > Subject: Re: Database over multiple drives > > > > On Thu, May 31, 2001 at 10:37:26AM +0200, David Lizano wrote: > > > You can't do it with Postgres. To do it, Postgres must implement > > > "tablespaces" to spread the database in different localizations (who can > be > > > different physical localizations, of course). Then a table can be > assigned > > > to a tablespace. > > > > Sure you can. > > You can move some files to a different drive, and put a symlink in the > > original directory. > > > > Or, if you have an operatingsystem that has an logical volume manager, > > you can concatenate several disks, use striping or whatever, to get a > > logical device that spans several physical devices. > > > > > > -- > > Ragnar Kjørstad > > Big Storage > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >