Thread: location for pgdata

location for pgdata

From
"Jules Alberts"
Date:
hi everyone,

i'm setting up a pg 7.1.3 test system on a redhat 7.2 box and i'm
wondering what the best location would be to put the data. the manual
gives /usr/local/pgsql/data as an example, but i thought somewhere in
/var would be a better idea, because the size of the db will obviously
vary. would /var/pgsql/data be a good idea?

also i noticed the redhat install created an empty dir /var/db. i have
a feeling that using this one for pg isn't a good idea, or is it?

thanks for any tips!

--
Jules Alberts.

Re: location for pgdata

From
"Josh Berkus"
Date:
Jules,

> i'm setting up a pg 7.1.3 test system on a redhat 7.2 box and i'm
> wondering what the best location would be to put the data. the manual
> gives /usr/local/pgsql/data as an example, but i thought somewhere in
> /var would be a better idea, because the size of the db will
> obviously
> vary. would /var/pgsql/data be a good idea?

4 rules:
1. The pgsql/data directory, as all pgsql/ directories, must belong to
the postgres user.
2. The database will grow, and the log file sizes will fluctuate
constantly.  So you want plenty of extra partition space.
3. Nothing other than Postgres data and files should be in the data
directory to avoid overwriting important files.
4. You and anybody else who uses the system should be able to identify
the Postgres directory easily.

I fulfill the above by putting the data in /usr/local/pgsql/data, but
then putting that directory on its own partition.  You could do it by
putting it in /var/pgsql/data/.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: location for pgdata

From
"Jules Alberts"
Date:
On 9 Jan 2002 at 10:11, Josh Berkus wrote:
<snip>
> 4 rules:
> 1. The pgsql/data directory, as all pgsql/ directories, must belong to
> the postgres user. 2. The database will grow, and the log file sizes
> will fluctuate constantly.  So you want plenty of extra partition space.
> 3. Nothing other than Postgres data and files should be in the data
> directory to avoid overwriting important files. 4. You and anybody else
> who uses the system should be able to identify the Postgres directory
> easily.
>
> I fulfill the above by putting the data in /usr/local/pgsql/data, but
> then putting that directory on its own partition.  You could do it by
> putting it in /var/pgsql/data/.

hi Josh,

thanks for reacting.

since the (test) server will be mainly a database server, i made my
/var the biggest partition and put the pg data in there. but, is there
any convention or default on where pg data are placed? i ask this to
make sure i don't run into trouble with an upgrade / RPM / DEB by
making an exotic choice now.

--
Jules Alberts.

Re: location for pgdata

From
"Jules Alberts"
Date:
On 10 Jan 2002 at 8:02, Jason Earl wrote:
> "Jules Alberts" <julesa@arbodienst-limburg.nl> writes:
<snip>
> > since the (test) server will be mainly a database server, i made my
> > /var the biggest partition and put the pg data in there. but, is there
> > any convention or default on where pg data are placed? i ask this to
> > make sure i don't run into trouble with an upgrade / RPM / DEB by
> > making an exotic choice now.
>
> Debian puts the PostgreSQL data in /var/lib/postgres and the PGDG RPMs
> (the ones that PostgreSQL produces) puts the data in /var/lib/pgsql .

thanks Jason!

i think i'll go for /var/lib/pgsql, especially since my RPM install
already created that dir. i didn't notice it wat there until you
mentioned it.

> One other thing to think about.  If your machine has more than one hard
> drive moving the pg_xlog directory to another spindle (and using a
> symlink in your data directory) can really be helpful performance wise,
> but you want to have plenty of room for the log files to grow.

thanks, i'll remember that. right now it's just one harddisk with a ca.
8gb /var partition on which performance isn't important. when it
eventually goes into production however...

--
Jules Alberts.

Re: location for pgdata

From
Jason Earl
Date:
"Jules Alberts" <julesa@arbodienst-limburg.nl> writes:

> On 9 Jan 2002 at 10:11, Josh Berkus wrote:
> <snip>
> > 4 rules:
> > 1. The pgsql/data directory, as all pgsql/ directories, must belong to
> > the postgres user. 2. The database will grow, and the log file sizes
> > will fluctuate constantly.  So you want plenty of extra partition space.
> > 3. Nothing other than Postgres data and files should be in the data
> > directory to avoid overwriting important files. 4. You and anybody else
> > who uses the system should be able to identify the Postgres directory
> > easily.
> >
> > I fulfill the above by putting the data in /usr/local/pgsql/data, but
> > then putting that directory on its own partition.  You could do it by
> > putting it in /var/pgsql/data/.
>
> hi Josh,
>
> thanks for reacting.
>
> since the (test) server will be mainly a database server, i made my
> /var the biggest partition and put the pg data in there. but, is there
> any convention or default on where pg data are placed? i ask this to
> make sure i don't run into trouble with an upgrade / RPM / DEB by
> making an exotic choice now.

Debian puts the PostgreSQL data in /var/lib/postgres and the PGDG RPMs
(the ones that PostgreSQL produces) puts the data in /var/lib/pgsql .
One other thing to think about.  If your machine has more than one
hard drive moving the pg_xlog directory to another spindle (and using
a symlink in your data directory) can really be helpful performance
wise, but you want to have plenty of room for the log files to grow.

Jason

Re: location for pgdata

From
"Josh Berkus"
Date:
Jules,

> since the (test) server will be mainly a database server, i made my
> /var the biggest partition and put the pg data in there. but, is
> there
> any convention or default on where pg data are placed? i ask this to
> make sure i don't run into trouble with an upgrade / RPM / DEB by
> making an exotic choice now.

Conventions are weak.  The source defaults to /usr/local/pgsql/
SuSE RPMs put it in /opt/lib/pgsql/.  Deb puts it in /var/lib/postgresql
(I think).

So, put it whereever you want.  Although /boot/ would bporbably not be a
good idea.  ;-)

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: location for pgdata

From
Oliver Elphick
Date:
On Thu, 2002-01-10 at 15:02, Jason Earl wrote:
> Debian puts the PostgreSQL data in /var/lib/postgres

By default.  You can configure it to go wherever you like.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "Be patient therefore, brethren, unto the coming of the
      Lord...Be patient; strengthen your hearts, for
      the coming of the Lord draweth nigh."
                                   James 5:7,8

Attachment

Re: location for pgdata

From
"pg"
Date:
..
----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: <julesa@arbodienst-limburg.nl>; <pgsql-novice@postgresql.org>
Sent: Thursday, January 10, 2002 7:34 PM
Subject: Re: [NOVICE] location for pgdata


> Jules,
>
> > since the (test) server will be mainly a database server, i made my
> > /var the biggest partition and put the pg data in there. but, is
> > there
> > any convention or default on where pg data are placed? i ask this to
> > make sure i don't run into trouble with an upgrade / RPM / DEB by
> > making an exotic choice now.
>
> Conventions are weak.  The source defaults to /usr/local/pgsql/
> SuSE RPMs put it in /opt/lib/pgsql/.  Deb puts it in /var/lib/postgresql
> (I think).
>
> So, put it whereever you want.  Although /boot/ would bporbably not be a
> good idea.  ;-)
>
> -Josh
>
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>