环境 Ubuntu: 12.04.1 PostgreSQL: 9.1

安装命令

sudo apt-get install postgresql-9.1

默认安装后的路径 配置文件: /etc/postgresql/9.1/main 数据: /var/lib/postgresql/9.1/main

网络和安全设置

修改监听的网络和端口

sudo -u postgres vi /etc/postgresql/9.1/main/postgresql.conf

修改以下内容,如果是production就只监听局域网的网络ip

listen_addresses = '*'
port = Your_port

password_encryption = on

修改 /etc/postgresql/9.1/main/pg_hba.conf

sudo -u postgres vi /etc/postgresql/9.1/main/pg_hba.conf

在最后加上以下内容

host    DB_NAME    USER_NAME        10.0.0.0/8              md5

新增数据库用户

创建table space的目录, 并设置权限

sudo -u postgres mkdir /var/lib/postgresql/9.1/main/pg_tblspc/TABLESPACE_NAME
sudo -u postgres chmod 750 /var/lib/postgresql/9.1/main/pg_tblspc/TABLESPACE_NAME


sudo -u postgres psql

# generate md5 password using SELECT md5('passwordusername'); or python MD5 lib

CREATE ROLE db_user LOGIN
  PASSWORD 'md5338a08eb2f188c3825cdfbeb558d6bfa'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;


CREATE TABLESPACE ts_name OWNER db_user
  LOCATION '/var/lib/postgresql/9.1/main/pg_tblspc/TABLESPACE_NAME';

CREATE DATABASE db_name
  WITH OWNER = user_name
       ENCODING = 'UTF8'
       TABLESPACE = ts_name
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;

重启数据库服务

sudo service postgresql restart