Обсуждение: Database Management Setup

Поиск
Список
Период
Сортировка

Database Management Setup

От
Aris wendy
Дата:
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.......

TIA
Best regards
Aris Wendy

Re: Database Management Setup

От
Oliver Elphick
Дата:
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.Elphick@lfix.co.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

Re: Database Management Setup

От
Aris wendy
Дата:
> 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.

OK thx.....but I still got a problem
[root@aris pgsql]# su postgres
bash-2.05b$ /usr/local/pgsql/bin/initdb -D /database
bash-2.05b$ /usr/local/pgsql/bin/initdb -D /database1
bash-2.05b$ /usr/local/pgsql/bin/postmaster -D /database
bash-2.05b$ /usr/local/pgsql/bin/createdb -D arisdb
[root@aris pgsql]# ls -al /database/base
drwx------    7 postgres postgres     4096 Feb  3 10:59 .
drwx------    6 postgres postgres     4096 Feb  3 11:00 ..
drwx------    2 postgres postgres     4096 Jan 31 18:09 1
drwx------    2 postgres postgres     4096 Jan 31 17:59 16975
drwx------    2 postgres postgres     4096 Feb  3 11:01 16976

[root@aris pgsql]# mv /database/base/16976 /database1/base/16976
[root@aris pgsql]# /usr/local/pgsql/bin/psql -Upostgres arisdb
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
[root@aris pgsql]#mkdir /database/base/16976
[root@aris pgsql]#chown postgres.postgres /database/base/16976
[root@aris pgsql]#su postgres
bash-2.05b$ ln -s /database/base/16976 /database1/base/16976
[root@aris pgsql]# ls -al /database/base/16976
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-rw-------    1 postgres postgres     8192 Jan 31 18:09 16680
lrwxrwxrwx    1 postgres postgres       20 Feb  3 11:35 16976 ->
/database/base/16976
-rw-------    1 postgres postgres    65776 Jan 31 18:09 pg_internal.init
-rw-------    1 postgres postgres        4 Jan 31 18:09 PG_VERSION
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
[root@aris pgsql]# /usr/local/pgsql/bin/psql -Upostgres arisdb
psql: FATAL:  File /database/base/16976/PG_VERSION is missing. This is not a
valid data directory.
[root@aris pgsql]# cp /database1/base/16976/PG_VERSION /database/base/16976
[root@aris 16976]# /usr/local/pgsql/bin/psql -Upostgres arisdb
psql: FATAL:  _mdfd_getrelnfd: cannot open relation pg_class: No such file or
directory


Sorry if my e-mail is to long to read.........
Best regards
Aris

FYI : I use RH 8.0 and postgresql 7.3

Re: Database Management Setup

От
Oliver Elphick
Дата:
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@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@linda:~$ pg_ctl stop
waiting for postmaster to shut down......done
postmaster successfully shut down
postgres@linda:~$ export PGDATA=/usr3/db
postgres@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@linda:~$ pg_ctl start
postmaster successfully started
postgres@linda:~$ psql -l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(2 rows)

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

postgres@linda:~$ pg_ctl stop
waiting for postmaster to shut down.....ls. -done
postmaster successfully shut down
postgres@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@linda:~$ mv /usr3/db/base/16976 /usr3/db1
postgres@linda:~$ ln -s /usr3/db1/16976/ /usr3/db/base/16976
postgres@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@linda:~$ pg_ctl start
postmaster successfully started
postgres@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@linda:~$

--
Oliver Elphick <olly@lfix.co.uk>
LFIX Limited