PostgreSQL8.0的使用与管理

作者：张招雄

注:本文主要列出大家在使用和管理PostgreSQL8.0时经常会使用到的一些工具和命令，关于工具和命令的使用，请自己查阅文中指出的手册中的相关章节。由于时间关系，列举实例较少，在以后会不断补充。希望这篇文档能够为您学习PostgreSQL数据库节省一定的时间。

---------------------- 客户端工具psql的使用-------------------------------

要管理PostgreSQL数据库服务器，首先需要使用psql工具连接(登陆)到服务器，基本用法如下：

用户postgres登陆到服务器192.168.10.18的maildb数据库上：
psql -h 192.168.10.18 -d maildb -U postgres

关于psql工具的使用，请参阅：http://www.clusting.com/PostgreSQL/docs8.0/app-psql.html

---------------------- 用户管理-------------------------------
1.创建和删除用户：
(1)创建用户：
CREATE USER bearzhang;

2. 用户属性管理
(1)口令管理：
设置明文口令：ALTER USER bearzhang WITH PASSWORD '123456';
设置加密口令：ALTER USER bearzhang WITH ENCRYPTED PASSWORD '123456';

(2)用户口令的有效日期:
ALTER USER bearzhang VALID UNTIL 'Jan 31 2030';

使用户的口令永远有效:
ALTER USER bearzhang VALID UNTIL 'infinity';

(3)使用户成为超级用户(superuser):
ALTER USER bearzhang CREATEUSER;

使用户成为普通用户：
ALTER USER bearzhang NOCREATEUSER;

(4)使用户拥有创建数据库的权限：

ALTER USER bearzhang CREATEDB;
superuser拥有对数据库的完全控制权限，因此，superuser用户不需要赋予CREATEDB权限就可以创建数据库。

取消用户创建数据库的权限：
ALTER USER bearzhang NOCREATEDB;

(5)重命名用户：
ALTER USER bearzhang RENAME TO newuser;

2.用户组管理：
(1)创建用户组mail_group:
CREATE GROUP mail_group;

(2)将用户bearzhang和john加入到用户组mail_group:
ALTER GROUP mail_group ADD USER bearzhang,john;

(3)删除用户组mail_group:
DROP GROUP mail_group;

(4)查看当前用户组的信息:
SELECT * FROM pg_group;

3. 用户权限管理
(1) 用户权限的类型:
数据库中的用户可以拥有如下的权限:
SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, 和 USAGE.
要了解各个权限的详情，请参看：http://www.clusting.com/PostgreSQL/docs8.0/sql-grant.html

(2) 使用户bearzhang对当前数据库内的mailbox表拥有SELECT，UPDATE，DELETE权限
GRANT SELECT,UPDATE,DELETE ON mailbox TO bearzhang;

(3)使mail_group用户组的用户对当前数据库内的mailbox表拥有SELECT权限:
GRANT SELECT ON mailbox TO GROUP mail_group;

(4)使用户bearzhang对当前数据库内的mailbox表拥有所有权限:
GRANT ALL ON mailbox TO bearzhang;

(5)删除除用户bearzhang对当前数据库内的mailbox表拥有的所有权限:
REVOKE ALL ON mailbox FROM bearzhang;

删除用户组mail_group对当前数据库内的mailbox表的SELECT权限:
REVOKE ALL ON mailbox FROM bearzhang;

-------------------------------数据库管理-------------------------------------------
--系统数据库:
template0 (该数据库为模版数据库,不会被系统或用户改动,创建数据库时可以使用该数据库为模版创建一个“干净”的数据库。 )
template1 (该数据库为用户模版数据库，同时也是系统数据库。缺省情况下，用户新创建的数据库由该模版生成，当用户对该模版修改后，新创建的数据库也会随模版改变。)


查看当前数据库的信息:
SELECT * FROM pg_database;

1.创建数据库
(1)使用缺省参数创建数据库maildb:
CREATE DATABASE maildb;

(2)创建数据库，并使数据库的拥有者为用户bearzhang:
CREATE DATABASE maildb OWNER bearzhang;

(3)创建数据库maildb，并使数据库的拥有者为bearzhang,同时将数据库的数据放到mailspace表空间上：
CREATE DATABASE maildb OWNER bearzhang TABLESPACE mailspace;

2.创建表空间
(1)创建表空间mailspace,其物理存储空间为/mnt/sda1/postgresql/data ：
a. 在shell命令提示符下使用root用户创建目录/mnt/sda1/postgresql/data:
mkdir -p /mnt/sda1/postgresql/data
chown postgres /mnt/sda1/postgresql/data

b. 在psql工具中使用SQL语句创建表空间：
CREATE TABLESPACE mailspace LOCATION '/mnt/sda1/postgresql/data';

(2) 在创建表格时使用表空间：
在test数据库中创建表格person，使其数据存储在表空间mailspace上（即/mnt/sda1/postgresql/data目录中）
CREATE TABLE person(
id int,
lastname varchar(30),
address varchar(300)
) TABLESPACE mailspace;

(3)查看当前表空间的信息：
SELECT * FROM pg_tablespace

3.数据表管理
(1)认识数据类型

常用数值型数据类型:
------------------------------------------------------------------------------
| smallint | 2 bytes | -32768 to +32767
------------------------------------------------------------------------------
| integer | 4 bytes | -2147483648 to +2147483647
-----------------------------------------------------------------------------
| bigint | 8 bytes | -9223372036854775808 to 9223372036854775807
------------------------------------------------------------------------------
| real | 4 bytes | 6 decimal digits precision
------------------------------------------------------------------------------
| double precision| 8 bytes | 15 decimal digits precision
------------------------------------------------------------------------------
| serial | 4 bytes | 1 to 2147483647
------------------------------------------------------------------------------
| bigserial | 8 bytes | 1 to 9223372036854775807
------------------------------------------------------------------------------

常用字符型数据类型:
变长字符串：varchar(n)
定长字符串：character(n), char(n)
文本型：text

常用日期时间型:
日期时间型: timestamp
--select current_timestamp(2);
日期型：date
--select current_date;
时间型：time
--select current_time;

布尔型:
boolean
(valuse:
TRUE
't'
'true'
'y'
'yes'
'1'

FALSE
'f'
'false'
'n'
'no'
'0')
更多数据类型以及每种数据类型的详情，请参看：http://www.clusting.com/PostgreSQL/docs8.0/datatype.html

(2)创建表格
创建表格实际上就是定义我们日常生活中的一张表的表头，例如我们要用一张表格来存储员工的信息，表的格式如下：

现在我们用emp_id来代表员工编号，username代表员工姓名，phone代表员工联系电话，address代表家庭住址，salary代表员工工资，则在数据库内应该定义的表格如下：

CREATE TABLE employees (
emp_id serial,
username varchar(20),
phone varchar(20),
address varchar(300),
salary real
)

更多定义表格的方法，请参阅：数据定义 和 CREATE TABLE的使用

(3)修改表:
将products表中的product_no重命名为product_number:
ALTER TABLE products RENAME COLUMN product_no TO product_number;

将products表的名称重命名为items:
ALTER TABLE products RENAME TO items;

在products表中增加一列description，其数据类型是text:
ALTER TABLE products ADD COLUMN description text;

在products表中增加一列description，其数据类型是text,并且该列的值不能为空字符串:
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

在products表中删除description列：
ALTER TABLE products DROP COLUMN description;

将products表中的price列的数据类型修改为数据长度（精度）为10位，小数点后取2位的数值型:
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

将products表的price的缺省值设置为7.77:
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

创建约束:
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

删除约束:
ALTER TABLE products DROP CONSTRAINT some_name;

更多修改表格的方法，请参阅：数据定义 和 ALTER TABLE的使用

-----------------------------------------客户端认证配置-----------------------
缺省安装完PostgreSQL数据库服务器后，它只接受本机的连接，为了使网络用户也能使用该数据库，我们必须配置它的用户认证(客户端认证)配置文件pg_hba.conf,该文件在数据库的初始化目录下，即$PGDATA/pg_hba.conf。

该文件中每行为一条配置记录（项）,每条配置中共有5个域(列)，列之间用一个制表符（或空格）分隔，例如：
host all all 127.0.0.1/32 trust
依次代表：类型(TYPE)，数据库(DATABASE)，用户(USER)，子网(CIDR-ADDRESS)，认证方式(METHOD)。

下面分别列出出每个域可使用的值（或方式）：
TYPE: local,host,hostssl,hostnossl
DATABASE: all,databaselist,@filename,sameuser
USER: all,userlist,+groupname,@filename
CIDR-ADDRESS: 192.168.0.0/24
METHOD: trust,reject,md5,crypt,password,krb4,krb5,ident,pam

Example pg_hba.conf entries:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust
host all all 192.168.54.1/32 reject
host all all 192.168.0.0/16 password
host db1,db2,@dblistfile @userlist,+mygroup 192.168.0.0/16 password
(@filename is in $PGDATA/filename)

更多详细参数和详细方法，请参看：用户认证

--------------------------------备份和恢复---------------------------------------------
1. SQL Dump
pg_dump dbname > outfile
psql dbname < infile

dump a database directly from one server to another:
pg_dump -h host1 dbname | psql -h host2 dbname

pg_dumpall > outfile
psql -f infile template1

Use compressed dumps:
pg_dump dbname | gzip > filename.gz
gunzip -c filename.gz | psql dbname

2.File system level backup:
#The database server must be shut down
tar -cf backup.tar /usr/local/pgsql/data

3.On-line backup and point-in-time recovery:
(1)Setting up WAL(write ahead log) archiving:
postgresql.conf file:
archive_command = 'test ! -f /mnt/backup/%f && cp %p /mnt/backup/%f'

(2)Making a Base Backup:
a. Ensure that WAL archiving is enabled and working.

b. Connect to the database as a superuser, and issue the command(start the online backup):
SELECT pg_start_backup('lable'); #One good idea is to use the full path where you intend to put the backup dump file.

c. perform the backup, using any convenient file-system-backup tool:
tar -cvf /backup/pg_backup.tar /mnt/database/pg_data

d. Again connect to the database as a superuser, and issue the command:
SELECT pg_stop_backup();
This should return successfully.

(3) Recovering with an On-line Backup
a. Stop the postmaster, if it's running.

b. copy the contents of the pg_xlog subdirectory of the cluster data directory to a secrity place.

c. Clean out all existing files and subdirectories under the cluster data directory and under the root directories of any tablespaces you are using.

d. Restore the database files from your backup dump.

e. Remove any files present in pg_xlog/;

f. If you had unarchived WAL segment files that you saved in step 2, copy them into pg_xlog/

g. Create a recovery command file recovery.conf in the cluster data directory. You may also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery has worked.
the content of recovery.conf file:
restore_command = 'cp /mnt/server/archivedir/%f "%p"'
recovery_target_time = '2005-07-01 00:45:00' #(default is recovery to the WAL tail)

h. Start the postmaster. The postmaster will go into recovery mode and proceed to read through the archived WAL files it needs.Upon completion of the recovery process, the postmaster will rename recovery.conf to recovery.done.

i. let in your users by restoring pg_hba.conf to normal.

(4) test on line backup:

[postgres@server postgres]$ createlang plpgsql test

create function insert_data() returns bool as '
DECLARE
increment integer;
begin
increment :=0;
while increment < 100000 loop
insert into mailbox (username) values(''bear.zhang'');
increment := increment+1;
end loop;
return true;
end;'
language plpgsql;

select insert_data();

----------------------------------Run-time Configuration------------------------------
1.Connections and Authentication
listen_addresses = '*' (any)
listen_addresses = 'localhost' (default)

port = 5432

max_connections = 100

unix_socket_directory = /var/tmp
unix_socket_directory = /tmp (default)

2. Security and Authentication
authentication_timeout = 60 (default)

password_encryption = true (true)
(When a password is specified in CREATE USER or ALTER USER without writing either ENCRYPTED or UNENCRYPTED)

3.Resource Consumption
(1) memory
shared_buffers = 1000 (default)
(the size is 1000*8KB, the values is at least twice the value of max_connections: 100*2 )

work_mem = 1024 (default)
(the unit is KB,the default is 1M. Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files.)

maintenance_work_mem = = 16384 (default)
(the default is 16MB. Specifies the maximum amount of memory to be used in maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.)

max_stack_depth = 2048 (default)
(the default is 2MB. Specifies the maximum safe depth of the server's execution stack. The ideal setting for this parameter is the actual stack size limit enforced by the kernel (as set by ulimit -s or local equivalent. )

(2) Free Space Map
max_fsm_pages = 20000 (Sets the maximum number of disk pages for which free space will be tracked in the shared free-space map. This setting must be more than 16 * max_fsm_relations.)

max_fsm_relations = 1000 (Sets the maximum number of relations (tables and indexes) for which free space will be tracked in the shared free-space map.)

(3) Kernel Resource
max_files_per_process = 1000 (Sets the maximum number of simultaneously open files allowed to each server subprocess.)

-----------------------System kernel config----------------------------------
/etc/sysctl.conf:
kernel.shmall = 2147483648
#(Total amount of shared memory available )

kernel.shmmax = 2147483648

kernel.shmmni = 4096
#(the unit is segments. Maximum number of shared memory segments system-wide)

kernel.sem = 250 32000 100 128

fs.file-max = 65536

Copyright© 2005 Clusting.com All Rights Reserved

联系：