Обсуждение: pg_ctl -D?
pg_ctl seems to want to be told where "data" is:
pg_ctl stop [-W] [-s] [-D datadir]
I've been scattering data across spindles and mount points. The
doc speaks to this in the singular:
-D datadir
Specifies the file system location of the database files.
If this is omitted, the environment variable PGDATA is used.
Do I need to tell pg_ctl where all the data is or does it figure it out
internally?
Postgres expects all your data files to be in one location. The
workaround if you have chosen to spread them is to use symbolic links to
point to that one location again.
Sincerely,
--
Husam
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ray Stell
Sent: Wednesday, October 04, 2006 7:39 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] pg_ctl -D?
pg_ctl seems to want to be told where "data" is:
pg_ctl stop [-W] [-s] [-D datadir]
I've been scattering data across spindles and mount points. The
doc speaks to this in the singular:
-D datadir
Specifies the file system location of the database files.
If this is omitted, the environment variable PGDATA is used.
Do I need to tell pg_ctl where all the data is or does it figure it out
internally?
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may
containinformation that is legally privileged. If you are not the addressee, or the person responsible for delivering
itto the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is
strictlyprohibited. If you have received this message by mistake, please immediately notify us by replying to the
messageand delete the original message immediately thereafter.
Thank you.
FADLD Tag
**********************************************************************
Ray Stell <stellr@cns.vt.edu> writes:
> pg_ctl seems to want to be told where "data" is:
> pg_ctl stop [-W] [-s] [-D datadir]
> I've been scattering data across spindles and mount points.
Via tablespaces you mean? pg_ctl only cares about the top-level data
directory (basically, so that it can find the postmaster.pid lock file).
regards, tom lane
On Wed, Oct 04, 2006 at 08:19:31AM -0700, Tomeh, Husam wrote:
>
> Postgres expects all your data files to be in one location. The
> workaround if you have chosen to spread them is to use symbolic links to
> point to that one location again.
Apparently, not. I used tablespaces to spread out indexes and tables. Anyway,
pg found the other data after starting with just the base:
pg_ctl -D /var/pgsql/data -l logfile start
testdb=# \db
List of tablespaces
Name | Owner | Location
------------+------------+-----------------
pg_default | postgresql |
pg_global | postgresql |
testts | postgresql | /opt/pgsql/data
(3 rows)
This may come back to bite me some other way. Having all the data on one
drive doesn't make any sense to me at all. Would this not cause i/o bottlenecks?
On Wed, Oct 04, 2006 at 12:57:35PM -0400, Tom Lane wrote: > Ray Stell <stellr@cns.vt.edu> writes: > > pg_ctl seems to want to be told where "data" is: > > pg_ctl stop [-W] [-s] [-D datadir] > > > I've been scattering data across spindles and mount points. > > Via tablespaces you mean? correct, to try to mitigate i/o contention. Doesn't this make sense to do this? > pg_ctl only cares about the top-level data > directory (basically, so that it can find the postmaster.pid lock file). good, maybe a note to this effect is the pg_ctl doc page is in order.
> This may come back to bite me some other way. Having all the data on one > drive doesn't make any sense to me at all. Would this not cause i/o bottlenecks? Yes it probably would create i/o bottlenecks to some degree. This is why using a multi-disc hardware raid-array with a BBU is so popular. There is alot of discussion of this on the preformance list. A raid 10 array will allow for the increase I/O through-put. I have seen advice to the effect that hand partioning your database accross muliple drives many not produce desirable results and is a bad idea. It seems and it is hard to balance tuple growth evenly across all of the drives. Also most of your data writes are on one table, then you are limited to the i/o bandwidth of one drive anyway. However, a better/safer way to relieve i/o conjestion is to move your wal-archieve directory to another drive. Regards, Richard Broersma Jr.
On Wed, Oct 04, 2006 at 03:38:04PM -0700, Richard Broersma Jr wrote: > I have seen advice to the effect that hand partioning your database accross muliple drives many > not produce desirable results and is a bad idea. Really! Interesting. Must stop drinking ora Koolaide. I wonder if there are test results published on this? > It seems and it is hard to balance tuple growth > evenly across all of the drives. Hmmm, not sure how to accomplish this, bad or not. Is this done with create tablespace? The doc shows the create tablespace directory as singular. How would you get a table to cross drives otherwise? Maybe you are not refering to tuple growth in one table? My idea (from standard ora practices) was to split table and indexes on that table across drives with create tablespace, standard ora practice. > Also most of your data writes are on one table, then you are > limited to the i/o bandwidth of one drive anyway. One table? What table are you refering to? Novice here... > However, a better/safer way to relieve i/o conjestion is to move your wal-archieve directory to > another drive. This would match ora dogma as well.
I wonder if there are test results > published on this? I haven't seen any, I am just recalling a previous thread on one of the PostgreSQL lists that mentioned that manually partitioning data is a complicated process. Spreading the I/O load and db tuple growth across all of these drives will continue to be a challenge. All of these is better left to a raid array which handles all of this for you. > > It seems and it is hard to balance tuple growth > > evenly across all of the drives. > > Is this done with > create tablespace? The doc shows the create tablespace directory > as singular. How would you get a table to cross drives otherwise? > Maybe you are not refering to tuple growth in one table? Yes, other than creating a tablespace, you can use symbolic links that point to data that you moved to other drives. I do not believe that it is possible to split a table across multiple partitions without using table partitioning in your logical data model. However a raid array should spread your data across all of the drives evenly without any creativity on our part. > My idea (from standard ora practices) was to split table and indexes on that > table across drives with create tablespace, standard ora practice. That is a very enteresting feature with oracle. I haven't seen any automatic "splitting" functionality in PostgreSQL that would do table and index partitioning for you. However, if you create table partitions in your logical data model, you could move those partitions to different drives. Also, you could manually create partial indexes for each of the table partitions that could also be moved to different drives. But all of this would have to modeled and constructed on your part. > > Also most of your data writes are on one table, then you are > > limited to the i/o bandwidth of one drive anyway. > > One table? What table are you refering to? Novice here... My mistake, I meant, "If most of you data writes are on one of your tables, then you are limited to the i/o bandwidth of the drive that the table is physically located on". Regards, Richard Broersma Jr.