Re: Database Management Setup

On Thu, 2003-01-30 at 04:11, Aris wendy wrote:
> I am a new comer in this millis. Can you help me how to configure the
> postgreSQL server which has some databases. I mean 5 databses is spread to
> different hardisk. For example a_db to hda1, b_db to hdb1, etc.......

You can use symbolic links, if your operating system allows them.

1. For safety, make a full backup with pg_dumpall.

2. Identify the database directories -- they are named by the oid of the
database in pg_databases:

template1=# select datname, oid from pg_database;
datname | oid
----------------+---------
accounts | 16983
comanagers | 1063179
template1 | 1
...

$ sudo ls $PGDATA/base
1 1063179 16983 ...

3. Shut down the postmaster.

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.

6. Restart the postmaster.

If you destroy the database structure and recreate it by restoring from
a full dump, the symbolic link structure will not be restored, since
PostgreSQL knows nothing about it. It will have to be maintained
manually.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"The Lord knoweth how to deliver the godly out of
temptations, and to reserve the unjust unto the day of
judgment to be punished;" II Peter 2:9