Thread: Database Management Setup
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
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
> 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
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