Postgres Pro
Downloads
Home   >   mailing lists

Thread: A question on PSQL 8.3 setup

A question on PSQL 8.3 setup

From
Jong Chun Park
Date:
Hi, folks?

I'm hoping this is the right mailing list for this sort of question. If not, I apologize you
any inconvenience in advance, though.

I'm trying to set up postgreql 8.3 in a box running Ubuntu 8.04 and PSQL 8.3 This
box is sort of misconfigured because it has 15GB for / and 3 TB for /home. Due to
the relatively large size of database, about 470 GB, I need to configure PSQL to store 
all DB-related data into /home/pgsql/data instead of somewhere in / such as /var/lib/
postgresql/8.3/... or /usr/local/pgsql/data. Unfortunately, however, I'm not allowed to 
re-partition but to use the existing ones. 

If I'm not mistaken, I can use initdb and tablespace in order to expand database
clusters and tables to a new location such as /home/pgsql/data. However, whenever
I try to dump a DB in, it seems to use / and consumes the whole space assigned 
for /, no matter what I do with initdb and tablespace. I might be wrong with using 
initdb and tablespaces, but it's very weird to me, though. It'd be greatly appreciated if 
someone helps me to redirect all DB stuff into /home/pgsql/data directory instead of 
some directories in /. Here are the procedure that I did:

> sudo mkdir /home/pgsql
> sudo mkdir /home/pgsql/data
> sudo chown postgres /home/pgsql/data
> sudo mkdir /home/pgsql/ts
> sudo chown postgres /home/pgsql/ts
> sudo su - postgres
> initdb -D /home/pgsql/data
> pg_ctl -D /home/pgsql/data -l logfile start
> createdb DBNAME
> createuser USER
> psql -s DBNAME
db=> create tablespace dbts location '/home/pgsql/ts';
db=> set default_tablespace=dbts;
>ctrl+D
> exit
> psql db < 470GB_DB_DUMPED_FILE

SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
  setval   
-----------
 200607607
(1 row)

ALTER TABLE
ERROR:  could not extend relation 1663/16384/21734: No space left on device
HINT:  Check free disk space.
CONTEXT:  COPY packets, line 22968771: "53 115989288 2008-09-20 23:13:00.640317-06 4 5 1500 14995 2 16384 64 6 26411 64.106.46.186 222.134..."


Thanks a lot,
Jong

Re: A question on PSQL 8.3 setup

From
"Daniel Verite"
Date:
    Jong Chun Park wrote:

> > sudo mkdir /home/pgsql
> > sudo mkdir /home/pgsql/data
> > sudo chown postgres /home/pgsql/data
> > sudo mkdir /home/pgsql/ts
> > sudo chown postgres /home/pgsql/ts
> > sudo su - postgres
> > initdb -D /home/pgsql/data
> > pg_ctl -D /home/pgsql/data -l logfile start

Can you issue as a superuser in psql:
 SHOW data_directory;
and check that the result is what you expect?

Also, are you using the ubuntu postgresql-8.3 package or a self-compiled
version?

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

Re: A question on PSQL 8.3 setup

From
Sam Mason
Date:
On Thu, Jul 23, 2009 at 01:08:26AM -0600, Jong Chun Park wrote:
> I need to configure PSQL to store all DB-related data into
> /home/pgsql/data instead of somewhere in / such as /var/lib/
> postgresql/8.3/... or /usr/local/pgsql/data.

Assuming you're using the standard builds in Ubuntu, I'd probably do
something like:

1) make your directory if you haven't got one already:
  mkdir /home/postgres
  chown postgres:postgres /home/postgres

2) shut down postgres
  /etc/init.d/postgres-8.3 stop

3) copy the existing database cluster over to its new location
  cp -a /var/lib/postgresql/8.3 /home/postgres

4) move the old cluster out of the way for safe keeping and put a
   symlink back in place
  mv /var/lib/postgresql/8.3 /var/lib/postgresql/8.3-old
  ln -s /home/postgres/8.3 /var/lib/postgresql/8.3

5) start PG again
  /etc/init.d/postgres-8.3 start

if that all works then you've got the option of dumping the old data
that was stashed away above and update the config file to point straight
to the new location.

--
  Sam  http://samason.me.uk/

Re: A question on PSQL 8.3 setup

From
Jong Chun Park
Date:
Yes, it's working now. I appreciate your help. The problem was to not stop
psql process even before doing anything.

Thanks a lot,
Jong

On Thu, Jul 23, 2009 at 7:38 AM, Sam Mason <sam@samason.me.uk> wrote:
On Thu, Jul 23, 2009 at 01:08:26AM -0600, Jong Chun Park wrote:
> I need to configure PSQL to store all DB-related data into
> /home/pgsql/data instead of somewhere in / such as /var/lib/
> postgresql/8.3/... or /usr/local/pgsql/data.

Assuming you're using the standard builds in Ubuntu, I'd probably do
something like:

1) make your directory if you haven't got one already:
 mkdir /home/postgres
 chown postgres:postgres /home/postgres

2) shut down postgres
 /etc/init.d/postgres-8.3 stop

3) copy the existing database cluster over to its new location
 cp -a /var/lib/postgresql/8.3 /home/postgres

4) move the old cluster out of the way for safe keeping and put a
  symlink back in place
 mv /var/lib/postgresql/8.3 /var/lib/postgresql/8.3-old
 ln -s /home/postgres/8.3 /var/lib/postgresql/8.3

5) start PG again
 /etc/init.d/postgres-8.3 start

if that all works then you've got the option of dumping the old data
that was stashed away above and update the config file to point straight
to the new location.

--
 Sam  http://samason.me.uk/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



citext contrib module (building indexes)

From
Michael Gould
Date:

I've got several columns in my database that need to have case insensitive searches done so I've loaded the citext control module and have changed the data types to citext.  When I create a index on a column that is defined as a citext, how is that going to be treated.  Will Postgres use the index to search or will it always do a table scan?

 

Best Regards

Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax