再谈PostgreSQL创建数据库

1背景

在实践中使用Postgresql数据库时,发现很多同学经常直接使用postgres用户和系统的postgres库,这是非常不安全的.

postgres用户和系统的postgres库一般只用来对数据库进行管理,其它任何情况时都不应该使用postgres用户和系统的postgres库.

题外话,好的设计可以达到事半功倍的效果…

2 正确的姿势

以linux为例,windows同理.

2.1 规划数据库程序目录

博主的习惯是将编译好的Postgresql安装到

2.2 规划数据库数据目录

现在我们测试创建test数据库,博主习惯数据目录如下

#数据库数据根目录.这个目录如果能直接mount比较好,和操作系统分开,这样比较安全.操作系统出问题后重新mount这个目录就可以快速恢复.
#Windows双击热备也是这个原理,数据库数据根目录直接放到活动硬盘上
/data
#postgresql系统目录,也是initdb时的目录.PostgreSQL的系统配置如postgresql.conf,pg_hba.conf等都在这个目录中
/data/pgdata
#数据库test的表空间目录
/data/test
#数据库test的索引表空间目录.索引表空间目录一般建议设置,因为当使用SSD和机械硬盘混合时,可以直接修改索引表空间的目录指至SSD
/data/idxtest
           

2.3 初始化DB

注意要先在操作系统创建postgres用户.

# mount /data略
sudo rm -rf /data/pgdata 

sudo mkdir /data/pgdata &&\
sudo chown -R postgres /data/pgdata &&\
sudo chmod -R 700 /data/pgdata 

su postgres

initdb -D /data/pgdata -E UTF-8 --locale=zh_CN.UTF-8 -A md5 -W -U postgres
           

完成以后按需求修改postgresql.conf,pg_hba.conf等配置信息.然后启动数据库.

2.4 创建test库的表空间和索引表空间目录

sudo rm -rf /data/test
sudo rm -rf /data/idxtest

sudo mkdir /data/test &&\
sudo chown -R postgres /data/test &&\
sudo chmod -R 700 /data/test

sudo mkdir /data/idxtest &&\
sudo chown -R postgres /data/idxtest &&\
sudo chmod -R 700 /data/idxtest
           

2.5 创建test库和用户

为方便记忆,数据库用户和数据库名称相同,都是test.你可以根据自己的需要修改.

#这里才需要使用postgres用户
psql -h localhost -U postgres
#initdb完成后,第一次链接时,执行以下sql,从postgres数据库收回public的所有权限
revoke all on database postgres from public;
           

drop database if exists test;
drop tablespace if exists test;
drop tablespace if exists idxtest;
drop user if exists test;
--创建用户test
create user test with password '123' nocreatedb;
--创建表空间
create tablespace test owner test location '/data/test';
--创建索引表空间
create tablespace idxtest owner test location '/data/idxtest';
--创建数据库test
create database test
	with owner = test						--指定用户test为此库的所有者
			template template0			
			encoding 'UTF8'			
			LC_COLLATE 'zh_CN.UTF-8'
			LC_CTYPE 'zh_CN.UTF-8'
			tablespace = test				--使用第2节创建的数据目录,和pg系统数据目录分开
			connection limit = -1;

--创建扩展
--create extension "uuid-ossp";
--其它扩展

--从test数据库收回public的所有权限
revoke all on database test from public;
#创建完成后退出postgres
\q
           

2.6 创建表

#以后我们操作test库都使用test用户
psql -h localhost -U test -d test
           

drop table if exists test009;
create table test009(
	objectid bigint not null,	
	name text not null,	
	flag integer default(2) not null,
	constraint pk_test009_objectid primary key (objectid) with (fillfactor=100) using index tablespace idxtest
) with (fillfactor=100)
tablespace test;						/*可以不用指定表空间,因为在创建数据库里已经指定*/
#注意创建索引语法,where最后,然后是tablespace
create index idx_test009_flag on test009(flag) with (fillfactor=100)  tablespace idxtest where flag=1;
           

OK,现在一个标准的数据库就创建完成了,以后我们都用test用户对这个库进行管理.

小结

除修改数据库配置时,任何情况时都不应该使用postgres用户和系统的postgres库

除修改数据库配置时,任何情况时都不应该使用postgres用户和系统的postgres库

除修改数据库配置时,任何情况时都不应该使用postgres用户和系统的postgres库

重要的事情说三遍