A question on PSQL 8.3 setup

Started by Jong Chun Parkabout 17 years ago5 messagesgeneral

joumon@gmail.com

about 17 years ago

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
Thisbox 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

Daniel Verite

daniel@manitou-mail.org

about 17 years ago

In reply to: Jong Chun Park (#1)

Re: A question on PSQL 8.3 setup

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

Sam Mason

sam@samason.me.uk

about 17 years ago

In reply to: Jong Chun Park (#1)

Re: A question on PSQL 8.3 setup

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/

Jong Chun Park

joumon@gmail.com

about 17 years ago

In reply to: Sam Mason (#3)

Re: A question on PSQL 8.3 setup

Yes, it's working now. I appreciate your help. The problem was to not stoppsql
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:

Show quoted text

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

Michael Gould

mgould@intermodalsoftwaresolutions.net

about 17 years ago

In reply to: Jong Chun Park (#4)

citext contrib module (building indexes)

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