PostgreSQL8.0的使用与管理 - IdeaGrace | 数据库 | PostgreSQL,IdeaGrace,web,开发,技术,交流,教程 - http://www.ideagrace.com/
---------------------- 用户管理-------------------------------
1.创建和删除用户：
(1)创建用户：
CREATE USER bearzhang;
(2)删除用户
DROP USER bearzhang;
(3)查看系统中的已有用户：
select * from pg_user;

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)认识数据类型
数据库中的数据类型主要用来限制用户输入的数据的值和类别，如下面的表格：
姓名 职位 工资
张磊 技术支持 4000
李亮 咨询师 五千
…… …… ……

在上面的表格中，如果我们要统计员工的平均工资，由于有的是数字，有的是字符，因此无法进行统计，为了方便统计员工工资，必须规定工资这一列使用同一种数据格式。这就是数据类型的主要目的和作用，当然它还有其他的用途 ，请在使用过程中慢慢体会。


常用数值型数据类型:
------------------------------------------------------------------------------
| 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)创建表格
创建表格实际上就是定义我们日常生活中的一张表的表头，例如我们要用一张表格来存储员工的信息，表的格式如下：
员工编号 姓名 联系电话 家庭住址 工资
1 张磊 12345678 北京 4000
2 李量 87654321 上海 5000

现在我们用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

喜欢本文？那就在线订阅更多文章更新吧！
加入技术论坛讨论
访问IdeaGrace开发者博客
浏览更多java开源项目
IdeaGrace开发者Wiki

更好的浏览体验，