Re: Database Management Setup

On Mon, 2003-02-03 at 04:55, Aris wendy wrote:
> > You can use symbolic links, if your operating system allows them.
...
> > 4. Move the relevant directories from $PGDATA/base to the desired
> > locations on the other disks -- their parent directories should have the
> > same ownership and permissions as $PGDATA.
> >
> > 5. In $PGDATA/base, make symbolic links to the moved directories.
> > PostgreSQL should notice no difference in the structure.
...
> OK thx.....but I still got a problem

Yes! You have embroidered on what I said!

OK; here it is in a real session:

olly(at)linda$ su
Password:
linda:/home/olly# mkdir /usr3/db /usr3/db1
linda:/home/olly# chown postgres.postgres /usr3/db /usr3/db1
linda:/home/olly# chmod 0700 /usr3/db /usr3/db1
linda:/home/olly# su - postgres
postgres(at)linda:~$ pg_ctl stop
waiting for postmaster to shut down......done
postmaster successfully shut down
postgres(at)linda:~$ export PGDATA=/usr3/db
postgres(at)linda:~$ initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale C.

Fixing permissions on existing directory /usr3/db... ok
creating directory /usr3/db/base... ok
creating directory /usr3/db/global... ok
creating directory /usr3/db/pg_xlog... ok
creating directory /usr3/db/pg_clog... ok
creating template1 database in /usr3/db/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:

/usr/lib/postgresql/bin/postmaster -D /usr3/db
or
/usr/lib/postgresql/bin/pg_ctl -D /usr3/db -l logfile start

postgres(at)linda:~$ pg_ctl start
postmaster successfully started
postgres(at)linda:~$ psql -l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(2 rows)

postgres(at)linda:~$ createdb arisdb
CREATE DATABASE
postgres(at)linda:~$ psql -l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
arisdb | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)

postgres(at)linda:~$ pg_ctl stop
waiting for postmaster to shut down.....ls. -done
postmaster successfully shut down
postgres(at)linda:~$ ls -l /usr3/db/base
total 12
drwx------ 2 postgres postgres 4096 Feb 3 11:30 1
drwx------ 2 postgres postgres 4096 Feb 3 11:30 16975
drwx------ 2 postgres postgres 4096 Feb 3 11:30 16976
postgres(at)linda:~$ mv /usr3/db/base/16976 /usr3/db1
postgres(at)linda:~$ ln -s /usr3/db1/16976/ /usr3/db/base/16976
postgres(at)linda:~$ ls -l /usr3/db/base
total 8
drwx------ 2 postgres postgres 4096 Feb 3 11:30 1
drwx------ 2 postgres postgres 4096 Feb 3 11:30 16975
lrwxrwxrwx 1 postgres postgres 16 Feb 3 11:32 16976 -> /usr3/db1/16976/
postgres(at)linda:~$ pg_ctl start
postmaster successfully started
postgres(at)linda:~$ psql arisdb
Welcome to psql 7.3.1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

arisdb=# \q
postgres(at)linda:~$

--
Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
LFIX Limited