Nginx PostgreSQL
1: 安装
1: 安装
代码:
#
cd /usr/ports/lang/添加如下支持：CLI CGI FPM SUHOSIN IPV6
# echo '> /etc/rc.conf
# /usr/local/etc/rc.d/# vim /usr/local/etc/
2: 安装 Nginx 1.2.0
http://ark.intel.com/products/49024/Intel-Core-i7-740QM-Processor-(6M-cache-1_73-GHz)
3: 安装 postgresql 9.1.3
安装后的提示：
建议编辑sysctl.conf 而不修改内核
( FROM: http://www.linuxquestions.org/questions/linux-kernel-70/shmall-and-shmmax-646293/ )
To my understanding,
SHMALL means how much total shared memory can be allocated on the server, which means a hard limit, but doens't mean researved
SHMMAX means maximum size for each single segments.
According to IBM's suggestion, we set SHMALL with90% physical memory =(16GB *0.9 /4096)
SHMALL ===> 可以是字节或者页面!
如果是字节: SHMALL == SHMMAX;
如果是页面: SHMALL = ceil(SHMMAX/PAGE_SIZE)
http://www.postgresql.org/docs/9.1/static/kernel-resources.html
http://svnweb.freebsd.org/base/head/sys/kern/sysv_sem.c?r1=223825&r2=224016
Please reboot your server now..
创建数据库
优化配置文件：
( 我所看到的是1/4 ! FROM: “ I received a tip from the postgresql mailing list, that the shared_buffers need to be set the 1/4 of the total system memory.” )
4: 安装 官方网站地址：简体中文语言包：
备注：事实上隐藏软件版本信息对安全没有任何帮助，建议始终使用最新版本！
Also note that disabling the Server: header does nothing at all to make your server more secure; the idea of "security through obscurity" is a myth and leads to a false sense of safety.
cd /usr/ports/lang/添加如下支持：CLI CGI FPM SUHOSIN IPV6
# echo '> /etc/rc.conf
# /usr/local/etc/rc.d/# vim /usr/local/etc/
- Edit the users and groups for the Unix socket and the processes [www:www]
- Address and port on which
- Amount of simultaneous requests that will be served
- IP address(es) allowed to connect to
代码:
# cp /usr/local/etc/# cd /usr/ports/lang/添加如下支持：BZ2 FTP GD PGSQL SESSION XML ZIP ZLIB
# cd /usr/ports/devel/pecl-uploadprogress && make install clean (上传进度库)
# cd /usr/ports/converters/# more /usr/local/etc/
# cd /usr/ports/devel/pecl-uploadprogress && make install clean (上传进度库)
# cd /usr/ports/converters/# more /usr/local/etc/
2: 安装 Nginx 1.2.0
代码:
# cd /usr/ports/www/nginx && make config install clean
添加如下支持：
IPV6
HTTP_MODULE
HTTP_CACHE_MODULE
HTTP_GZIP_STATIC_MODULE
HTTP_IMAGE_FILTER_MODULE
HTTP_REALIP_MODULE
HTTP_REWRITE_MODULE
HTTP_SSL_MODULE
HTTP_STATUS_MODULE
HTTP_SUB_MODULE
WWW
# echo 'nginx_enable="YES"'>>/etc/rc.conf
# chown -R root:www /usr/local/etc/nginx
# cd /usr/ports/lang/===> The following configuration options are available for CLI=on "Build CLI version"
CGI=on "Build CGI version"
FPM=off "Build FPM version (experimental)" ==> APACHE=on "Build Apache module"
AP2FILTER=off " Use Apache 2.x filter interface (experimental)"
DEBUG=off "Enable debug"
SUHOSIN=on "Enable Suhosin protection system"
MULTIBYTE=off "Enable zend multibyte support"
IPV6=on "Enable ipv6 support"
MAILHEAD=off "Enable mail header patch"
LINKTHR=off "Link thread lib (for threaded extensions)"
===> Use 'make config' to modify these settings
# sysctl hw | head
hw.machine: amd64
hw.model: Intel(R) Core(TM) i7 CPU Q 740 @ 1.73GHz
hw.ncpu: 8 ===> (CPU数为8，但其实是4核)
hw.byteorder: 1234
hw.physmem: 8474169344
hw.usermem: 5156204544
hw.pagesize: 4096
hw.floatingpoint: 1
hw.machine_arch: amd64
hw.realmem: 9663676416
添加如下支持：
IPV6
HTTP_MODULE
HTTP_CACHE_MODULE
HTTP_GZIP_STATIC_MODULE
HTTP_IMAGE_FILTER_MODULE
HTTP_REALIP_MODULE
HTTP_REWRITE_MODULE
HTTP_SSL_MODULE
HTTP_STATUS_MODULE
HTTP_SUB_MODULE
WWW
# echo 'nginx_enable="YES"'>>/etc/rc.conf
# chown -R root:www /usr/local/etc/nginx
# cd /usr/ports/lang/===> The following configuration options are available for CLI=on "Build CLI version"
CGI=on "Build CGI version"
FPM=off "Build FPM version (experimental)" ==> APACHE=on "Build Apache module"
AP2FILTER=off " Use Apache 2.x filter interface (experimental)"
DEBUG=off "Enable debug"
SUHOSIN=on "Enable Suhosin protection system"
MULTIBYTE=off "Enable zend multibyte support"
IPV6=on "Enable ipv6 support"
MAILHEAD=off "Enable mail header patch"
LINKTHR=off "Link thread lib (for threaded extensions)"
===> Use 'make config' to modify these settings
# sysctl hw | head
hw.machine: amd64
hw.model: Intel(R) Core(TM) i7 CPU Q 740 @ 1.73GHz
hw.ncpu: 8 ===> (CPU数为8，但其实是4核)
hw.byteorder: 1234
hw.physmem: 8474169344
hw.usermem: 5156204544
hw.pagesize: 4096
hw.floatingpoint: 1
hw.machine_arch: amd64
hw.realmem: 9663676416
http://ark.intel.com/products/49024/Intel-Core-i7-740QM-Processor-(6M-cache-1_73-GHz)
代码:
查看CPU支持的指令集合：
# grep -i features /var/run/dmesg.boot
Features=0xbfebfbff
Features2=0x98e3fd
AMD Features=0x28100800
AMD Features2=0x1
Features=0xbfebfbff
Features2=0x98e3fd
AMD Features=0x28100800
AMD Features2=0x1
查看当前操作系统开启的指令集合：
# make -V CPUTYPE
core2
# make -V MACHINE_CPU
ssse3 sse3 amd64 sse2 sse mmx
# grep -i features /var/run/dmesg.boot
Features=0xbfebfbff
Features2=0x98e3fd
AMD Features=0x28100800
AMD Features2=0x1
Features=0xbfebfbff
Features2=0x98e3fd
AMD Features=0x28100800
AMD Features2=0x1
查看当前操作系统开启的指令集合：
# make -V CPUTYPE
core2
# make -V MACHINE_CPU
ssse3 sse3 amd64 sse2 sse mmx
代码:
修改默认配置文件：
# vim /usr/local/etc/nginx/nginx.conf
user www www; ( Unprivileged user for security reason)
# ----------------------------------
worker_processes 4;
# It is highly recommended to increase this value; you should have at least one process per CPU core.
# Note that affinity is only recommended for multi-core CPUs, not for processors with hyper-treading or similar technologies.
# ----------------------------------
worker_cpu_affinity 1000 0100 0010 0001;
# This directive works in conjunction with worker_processes. It lets you affect worker processes to CPU cores.
# The first block (1000) indicates that the first worker process should be affected to the first core.
# The third block (0010) indicates that the third worker process should be affected to the third core.
# 以此类推 ！
# ----------------------------------
worker_rlimit_nofile 11095; (确保与 ulimit -n 保持一致)
> ulimit -a
cpu time (seconds, -t) unlimited
file size (512-blocks, -f) unlimited
data seg size (kbytes, -d) 33554432
stack size (kbytes, -s) 524288
core file size (512-blocks, -c) unlimited
max memory size (kbytes, -m) unlimited
locked memory (kbytes, -l) unlimited
max user processes (-u) 5547
open files (-n) 11095
virtual mem size (kbytes, -v) unlimited
swap limit (kbytes, -w) unlimited
sbsize (bytes, -b) unlimited
pseudo-terminals (-p) unlimited
> ulimit -n
11095
# ----------------------------------
worker_priority 0;
events {
use kqueue; ==> only for BSD BASE( FreeBSD/NetBSD/OpenBSD/MacOS X )
worker_connections 4096;
}
http {
include mime.types;
default_type application/octet-stream;
charset utf-8;
sendfile on;
keepalive_timeout 65;
open_file_cache max=11095 inactive=180; # max=ulimit -n
open_file_cache_valid 60;
open_file_cache_min_uses 3;
# If you are running an older version of Nginx and do not plan to update it,
# it might be a good idea to hide your version number.
# This directive allows you to define whether or not Nginx should
# inform the clients of the running version number.
server_tokens off; # eg: >curl -I http://kotzu.org > wget -S --spider !$
# Compression - requires gzip and gzip static modules.
gzip on;
gzip_static on;
gzip_vary on;
gzip_http_version 1.1;
gzip_min_length 700;
# Compression levels over 6 do not give an appreciable improvement
# in compression ratio, but take more resources.
gzip_comp_level 6;
# IE 6 and lower do not support gzip with Vary correctly.
gzip_disable "msie6";
# Before nginx 0.7.63:
#gzip_disable "MSIE [1-6]\.";
server {
listen 8080;
server_name .kotzu.org;
location / {
root /usr/local/www/nginx;
index index. # Blocking requests based on HTTP referrers.
# To block spammer bots from visiting your site.
if ($http_referer ~* (babes|click|diamond|forsale|girl|jewelry|lo
ve|nudit|organic|poker|porn|poweroversoftware|***|teen|video|webcam|z
ippo)) {
return 444;
}
}
# === added by ko BEGIN ===
location /nginx_status {
stub_status on; # This module is not included in the default Nginx build.
access_log off;
allow 192.168.1.5; # you may want to protect the information
deny all;
}
# === added by ko BEGIN ===
location ~* \. root /usr/local/www/nginx;
# specify the listening address and port that you configured previously
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index. # the document path to be passed to fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
# the script filename to be passed to fastcgi_param PATH_INFO $fastcgi_script_name;
# include other FastCGI related configuration settings
include fastcgi_params;
}
}
}
:wq!
# echo " /usr/local/www/nginx/index.# /usr/local/etc/rc.d/nginx restart
测试一下吧！http://localhost
# vim /usr/local/etc/nginx/nginx.conf
user www www; ( Unprivileged user for security reason)
# ----------------------------------
worker_processes 4;
# It is highly recommended to increase this value; you should have at least one process per CPU core.
# Note that affinity is only recommended for multi-core CPUs, not for processors with hyper-treading or similar technologies.
# ----------------------------------
worker_cpu_affinity 1000 0100 0010 0001;
# This directive works in conjunction with worker_processes. It lets you affect worker processes to CPU cores.
# The first block (1000) indicates that the first worker process should be affected to the first core.
# The third block (0010) indicates that the third worker process should be affected to the third core.
# 以此类推 ！
# ----------------------------------
worker_rlimit_nofile 11095; (确保与 ulimit -n 保持一致)
> ulimit -a
cpu time (seconds, -t) unlimited
file size (512-blocks, -f) unlimited
data seg size (kbytes, -d) 33554432
stack size (kbytes, -s) 524288
core file size (512-blocks, -c) unlimited
max memory size (kbytes, -m) unlimited
locked memory (kbytes, -l) unlimited
max user processes (-u) 5547
open files (-n) 11095
virtual mem size (kbytes, -v) unlimited
swap limit (kbytes, -w) unlimited
sbsize (bytes, -b) unlimited
pseudo-terminals (-p) unlimited
> ulimit -n
11095
# ----------------------------------
worker_priority 0;
events {
use kqueue; ==> only for BSD BASE( FreeBSD/NetBSD/OpenBSD/MacOS X )
worker_connections 4096;
}
http {
include mime.types;
default_type application/octet-stream;
charset utf-8;
sendfile on;
keepalive_timeout 65;
open_file_cache max=11095 inactive=180; # max=ulimit -n
open_file_cache_valid 60;
open_file_cache_min_uses 3;
# If you are running an older version of Nginx and do not plan to update it,
# it might be a good idea to hide your version number.
# This directive allows you to define whether or not Nginx should
# inform the clients of the running version number.
server_tokens off; # eg: >curl -I http://kotzu.org > wget -S --spider !$
# Compression - requires gzip and gzip static modules.
gzip on;
gzip_static on;
gzip_vary on;
gzip_http_version 1.1;
gzip_min_length 700;
# Compression levels over 6 do not give an appreciable improvement
# in compression ratio, but take more resources.
gzip_comp_level 6;
# IE 6 and lower do not support gzip with Vary correctly.
gzip_disable "msie6";
# Before nginx 0.7.63:
#gzip_disable "MSIE [1-6]\.";
server {
listen 8080;
server_name .kotzu.org;
location / {
root /usr/local/www/nginx;
index index. # Blocking requests based on HTTP referrers.
# To block spammer bots from visiting your site.
if ($http_referer ~* (babes|click|diamond|forsale|girl|jewelry|lo
ve|nudit|organic|poker|porn|poweroversoftware|***|teen|video|webcam|z
ippo)) {
return 444;
}
}
# === added by ko BEGIN ===
location /nginx_status {
stub_status on; # This module is not included in the default Nginx build.
access_log off;
allow 192.168.1.5; # you may want to protect the information
deny all;
}
# === added by ko BEGIN ===
location ~* \. root /usr/local/www/nginx;
# specify the listening address and port that you configured previously
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index. # the document path to be passed to fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
# the script filename to be passed to fastcgi_param PATH_INFO $fastcgi_script_name;
# include other FastCGI related configuration settings
include fastcgi_params;
}
}
}
:wq!
# echo " /usr/local/www/nginx/index.# /usr/local/etc/rc.d/nginx restart
测试一下吧！http://localhost
3: 安装 postgresql 9.1.3
代码:
# cd /usr/ports/databases/postgresql91-server/ && make install clean
安装后的提示：
代码:
For procedural languages and postgresql functions, please note that
you might have to update them when updating the server.
If you have many tables and many clients running, consider raising
kern.maxfiles using sysctl(8), or reconfigure your kernel
appropriately.
------------------------------------------------------
(kern.maxfiles: 12328
Run-time variable and boot-time tunable. The maximum number of files
that the system can have open for reading or writing at any one time.)
------------------------------------------------------
The port is set up to use autovacuum for new databases, but you might
also want to vacuum and perhaps backup your database regularly. There
is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that
you may find useful. You can use it to backup and perfom vacuum on all
databases nightly. Per default, it perfoms `vacuum analyze'. See the
script for instructions. For autovacuum settings, please review
~pgsql/data/postgresql.conf.
you might have to update them when updating the server.
If you have many tables and many clients running, consider raising
kern.maxfiles using sysctl(8), or reconfigure your kernel
appropriately.
------------------------------------------------------
(kern.maxfiles: 12328
Run-time variable and boot-time tunable. The maximum number of files
that the system can have open for reading or writing at any one time.)
------------------------------------------------------
The port is set up to use autovacuum for new databases, but you might
also want to vacuum and perhaps backup your database regularly. There
is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that
you may find useful. You can use it to backup and perfom vacuum on all
databases nightly. Per default, it perfoms `vacuum analyze'. See the
script for instructions. For autovacuum settings, please review
~pgsql/data/postgresql.conf.
代码:
To allow many simultaneous connections to your PostgreSQL server, you
should raise the SystemV shared memory limits in your kernel. Here are
example values for allowing up to 180 clients (configurations in
postgresql.conf also needed, of course):
options SYSVSHM
options SYSVSEM
options SYSVMSG
options SHMMAXPGS=65536
options SEMMNI=40
options SEMMNS=240
options SEMUME=40
options SEMMNU=120
should raise the SystemV shared memory limits in your kernel. Here are
example values for allowing up to 180 clients (configurations in
postgresql.conf also needed, of course):
options SYSVSHM
options SYSVSEM
options SYSVMSG
options SHMMAXPGS=65536
options SEMMNI=40
options SEMMNS=240
options SEMUME=40
options SEMMNU=120
建议编辑sysctl.conf 而不修改内核
( FROM: http://www.linuxquestions.org/questions/linux-kernel-70/shmall-and-shmmax-646293/ )
To my understanding,
SHMALL means how much total shared memory can be allocated on the server, which means a hard limit, but doens't mean researved
SHMMAX means maximum size for each single segments.
According to IBM's suggestion, we set SHMALL with90% physical memory =(16GB *0.9 /4096)
代码:
比如： i've got 1GB RAM.. (PGSQL 专用内存)
1*1024*1024*1024*0.9=966367641 Bytes
# getconf PAGESIZE
4096
# getconf PAGE_SIZE
4096
kern.ipc.shmmax=966367641
kern.ipc.shmall=4*1024*1024*1024*0.9/4096=235929
1*1024*1024*1024*0.9=966367641 Bytes
# getconf PAGESIZE
4096
# getconf PAGE_SIZE
4096
kern.ipc.shmmax=966367641
kern.ipc.shmall=4*1024*1024*1024*0.9/4096=235929
SHMALL ===> 可以是字节或者页面!
如果是字节: SHMALL == SHMMAX;
如果是页面: SHMALL = ceil(SHMMAX/PAGE_SIZE)
代码:
> ipcs -M
shminfo:
shmmax: 536870912 (max shared memory segment size)
shmmin: 1 (min shared memory segment size)
shmmni: 192 (max number of shared memory identifiers)
shmseg: 128 (max shared memory segments per process)
shmall: 32768 (max amount of shared memory in pages)
# man ipcs
ipcs — report System V interprocess communication facilities status
shminfo:
shmmax: 536870912 (max shared memory segment size)
shmmin: 1 (min shared memory segment size)
shmmni: 192 (max number of shared memory identifiers)
shmseg: 128 (max shared memory segments per process)
shmall: 32768 (max amount of shared memory in pages)
# man ipcs
ipcs — report System V interprocess communication facilities status
代码:
# vim /etc/sysctl.conf
kern.ipc.somaxconn=32768
kern.ipc.shmall=32768
kern.ipc.shmmax=536870912
:wq
# vim /boot/loader.conf:
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
kern.ipc.somaxconn=32768
kern.ipc.shmall=32768
kern.ipc.shmmax=536870912
:wq
# vim /boot/loader.conf:
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
http://www.postgresql.org/docs/9.1/static/kernel-resources.html
代码:
PS:
kern.ipc.semmap=256 ===> 很多旧的教程里面有修改！
But this has been removed in r224016:
kern.ipc.semmap=256 ===> 很多旧的教程里面有修改！
But this has been removed in r224016:
http://svnweb.freebsd.org/base/head/sys/kern/sysv_sem.c?r1=223825&r2=224016
- 名字 描述 合理取值
- SHMMAX最大共享内存段尺寸(字节)最少若干兆(见本文)
- SHMMIN最小共享内存段尺寸(字节)1
- SHMALL可用共享内存的总数量(字节或者页面)
如果是字节，就和 SHMMAX 一样；
如果是页面，ceil(SHMMAX/PAGE_SIZE) - SHMSEG每进程最大共享内存段数量只需要 1 个段，不过缺省比这高得多。
- SHMMNI系统范围最大共享内存段数量类似 SHMSEG 加上用于其它应用的空间
- SEMMNI信号灯标识符的最小数量(也就是套)至少 ceil(max_connections / 16)
- SEMMNS系统范围的最大信号灯数量ceil(max_connections / 16) * 17 加上用于其它应用的空间
- SEMMSL每套信号灯最小信号灯数量至少 17
- SEMMAP信号灯映射里的记录数量参阅本文
- SEMVMX信号灯的最大值至少 1000 ，缺省通常是 32767 ，除非被迫，否则不要修改
代码:
(kern.ipc.somaxconn
The kern.ipc.somaxconn sysctl variable limits the size of the listen queue for accepting new TCP connections. The default value of 128 is typically too low for robust handling of new connections in a heavily loaded web server environment. For such environments, it is recommended to increase this value to 1024 or higher. The service daemon may itself limit the listen queue size (e.g., sendmail(8), or Apache) but will often have a directive in its configuration file to adjust the queue size. Large listen queues also do a better job of avoiding Denial of Service (DoS) attacks.)
(kern.ipc.shmall
Run-time variable. This is the maximum number of pages available for
System V shared memory)
The kern.ipc.somaxconn sysctl variable limits the size of the listen queue for accepting new TCP connections. The default value of 128 is typically too low for robust handling of new connections in a heavily loaded web server environment. For such environments, it is recommended to increase this value to 1024 or higher. The service daemon may itself limit the listen queue size (e.g., sendmail(8), or Apache) but will often have a directive in its configuration file to adjust the queue size. Large listen queues also do a better job of avoiding Denial of Service (DoS) attacks.)
(kern.ipc.shmall
Run-time variable. This is the maximum number of pages available for
System V shared memory)
代码:
If you plan to access your PostgreSQL server using ODBC, please
consider running the SQL script /usr/local/share/postgresql/odbc.sql
to get the functions required for ODBC compliance.
------------------------------------------------------
Please note that if you use the rc script,
/usr/local/etc/rc.d/postgresql, to initialize the database, unicode
(UTF-8) will be used to store character data by default. Set
postgresql_initdb_flags or use login.conf settings described below to
alter this behaviour. See the start rc script for more info.
To set limits, environment stuff like locale and collation and other
things, you can set up a class in /etc/login.conf before initializing
the database. Add something similar to this to /etc/login.conf:
---
postgres:\
:lang=en_US.UTF-8:\
:setenv=LC_COLLATE=C:\
:tc=default:
---
and run `cap_mkdb /etc/login.conf'.
Then add 'postgresql_class="postgres"' to /etc/rc.conf.
# more /usr/local/etc/rc.d/postgresql (查看管理脚本可配置的参数)
# Add the following line to /etc/rc.conf to enable PostgreSQL:
#
# postgresql_enable="YES"
# # optional
# postgresql_data="/usr/local/pgsql/data"
# postgresql_flags="-w -s -m fast"
# postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
# postgresql_class="default"
# postgresql_profiles=""
#
======================================================================
To initialize the database, run
/usr/local/etc/rc.d/postgresql initdb
You can then start PostgreSQL by running:
/usr/local/etc/rc.d/postgresql start
For postmaster settings, see ~pgsql/data/postgresql.conf
NB. FreeBSD's PostgreSQL port logs to syslog by default
See ~pgsql/data/postgresql.conf for more info
======================================================================
To run PostgreSQL at startup, add
'postgresql_enable="YES"' to /etc/rc.conf
===> Installing rc.d startup script(s)
===> Correct pkg-plist sequence to create group(s) and user(s)
===> Registering installation for postgresql-server-9.1.3
===> SECURITY REPORT:
This port has installed the following files which may act as network
servers and may therefore pose a remote security risk to the system.
/usr/local/bin/postgres
This port has installed the following startup scripts which may cause
these network services to be started at boot time.
/usr/local/etc/rc.d/postgresql
If there are vulnerabilities in these programs there may be a security
risk to the system. FreeBSD makes no guarantee about the security of
ports included in the Ports Collection. Please type 'make deinstall'
to deinstall the port if this is a concern.
For more information, and contact details about the security
status of this software, see the following webpage:
http://www.postgresql.org/
===> Cleaning for postgresql-server-9.1.3
consider running the SQL script /usr/local/share/postgresql/odbc.sql
to get the functions required for ODBC compliance.
------------------------------------------------------
Please note that if you use the rc script,
/usr/local/etc/rc.d/postgresql, to initialize the database, unicode
(UTF-8) will be used to store character data by default. Set
postgresql_initdb_flags or use login.conf settings described below to
alter this behaviour. See the start rc script for more info.
To set limits, environment stuff like locale and collation and other
things, you can set up a class in /etc/login.conf before initializing
the database. Add something similar to this to /etc/login.conf:
---
postgres:\
:lang=en_US.UTF-8:\
:setenv=LC_COLLATE=C:\
:tc=default:
---
and run `cap_mkdb /etc/login.conf'.
Then add 'postgresql_class="postgres"' to /etc/rc.conf.
# more /usr/local/etc/rc.d/postgresql (查看管理脚本可配置的参数)
# Add the following line to /etc/rc.conf to enable PostgreSQL:
#
# postgresql_enable="YES"
# # optional
# postgresql_data="/usr/local/pgsql/data"
# postgresql_flags="-w -s -m fast"
# postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
# postgresql_class="default"
# postgresql_profiles=""
#
======================================================================
To initialize the database, run
/usr/local/etc/rc.d/postgresql initdb
You can then start PostgreSQL by running:
/usr/local/etc/rc.d/postgresql start
For postmaster settings, see ~pgsql/data/postgresql.conf
NB. FreeBSD's PostgreSQL port logs to syslog by default
See ~pgsql/data/postgresql.conf for more info
======================================================================
To run PostgreSQL at startup, add
'postgresql_enable="YES"' to /etc/rc.conf
===> Installing rc.d startup script(s)
===> Correct pkg-plist sequence to create group(s) and user(s)
===> Registering installation for postgresql-server-9.1.3
===> SECURITY REPORT:
This port has installed the following files which may act as network
servers and may therefore pose a remote security risk to the system.
/usr/local/bin/postgres
This port has installed the following startup scripts which may cause
these network services to be started at boot time.
/usr/local/etc/rc.d/postgresql
If there are vulnerabilities in these programs there may be a security
risk to the system. FreeBSD makes no guarantee about the security of
ports included in the Ports Collection. Please type 'make deinstall'
to deinstall the port if this is a concern.
For more information, and contact details about the security
status of this software, see the following webpage:
http://www.postgresql.org/
===> Cleaning for postgresql-server-9.1.3
代码:
# echo 'postgresql_enable="YES"' >> /etc/rc.conf
# echo 'postgresql_class="postgres"' >> /etc/rc.conf
或者(我没有这么干,一向以修改最少文件为益！)：
/etc/master.passwd 格式如下：
Username:Encrypted Password:UID:GID:User’s Class:Password Expiration:Account Expiration:Personal Data:User’s Home Directory:User’s Shell
# grep pgsql pgsql:*:70:70::0:0:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh
所以直接修改 User's Class 亦可:
# vipw
pgsql:*:70:70:postgres:0:0:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh
# echo 'postgresql_class="postgres"' >> /etc/rc.conf
或者(我没有这么干,一向以修改最少文件为益！)：
/etc/master.passwd 格式如下：
Username:Encrypted Password:UID:GID:User’s Class:Password Expiration:Account Expiration:Personal Data:User’s Home Directory:User’s Shell
# grep pgsql pgsql:*:70:70::0:0:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh
所以直接修改 User's Class 亦可:
# vipw
pgsql:*:70:70:postgres:0:0:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh
Please reboot your server now..
代码:
# /usr/local/etc/rc.d/postgresql initdb (初始化 /usr/local/pgsql/data)
# /usr/local/etc/rc.d/postgresql start (启动PGSQL)
# grep pgsql pgsql:*:70:70::0:0:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh
(PGSQL 创建的默认帐号：pgsql 该帐号为PGSQL的superuser !)
# psql -U pgsql postgres (postgres 为数据库名称)
psql (9.1.3)
Type "help" for help.
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# \password (修改密码)
Enter new password:
Enter it again:
postgres=# SELECT rolname FROM pg_roles; (查看角色)
rolname
---------
pgsql
(1 row)
postgres=# \du (查看角色的另外一种方法)
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
pgsql | Superuser, Create role, Create DB, Replication | {}
postgres=# \l (列出所有数据库)
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------------+-------------------
postgres | pgsql | UTF8 | C | en_US.UTF-8 |
template0 | pgsql | UTF8 | C | en_US.UTF-8 | =c/pgsql +
| | | | | pgsql=CTc/pgsql
template1 | pgsql | UTF8 | C | en_US.UTF-8 | =c/pgsql +
| | | | | pgsql=CTc/pgsql
(3 rows)
postgres=# \conninfo ( display information about current connection )
You are connected to database "postgres" as user "pgsql" via socket in "/tmp" at port "5432".
postgres=#\q
修改 基于主机的认证(HBA) pg_hba.conf：
# vim /usr/local/pgsql/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# === added by ko BEGIN ===
host all all 0.0.0.0/0 md5
# === added by ko END ===
:wq
# /usr/local/etc/rc.d/postgresql reload (重新载入配置文件)
# /usr/local/etc/rc.d/postgresql start (启动PGSQL)
# grep pgsql pgsql:*:70:70::0:0:PostgreSQL pseudo-user:/usr/local/pgsql:/bin/sh
(PGSQL 创建的默认帐号：pgsql 该帐号为PGSQL的superuser !)
# psql -U pgsql postgres (postgres 为数据库名称)
psql (9.1.3)
Type "help" for help.
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# \password (修改密码)
Enter new password:
Enter it again:
postgres=# SELECT rolname FROM pg_roles; (查看角色)
rolname
---------
pgsql
(1 row)
postgres=# \du (查看角色的另外一种方法)
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
pgsql | Superuser, Create role, Create DB, Replication | {}
postgres=# \l (列出所有数据库)
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------------+-------------------
postgres | pgsql | UTF8 | C | en_US.UTF-8 |
template0 | pgsql | UTF8 | C | en_US.UTF-8 | =c/pgsql +
| | | | | pgsql=CTc/pgsql
template1 | pgsql | UTF8 | C | en_US.UTF-8 | =c/pgsql +
| | | | | pgsql=CTc/pgsql
(3 rows)
postgres=# \conninfo ( display information about current connection )
You are connected to database "postgres" as user "pgsql" via socket in "/tmp" at port "5432".
postgres=#\q
修改 基于主机的认证(HBA) pg_hba.conf：
# vim /usr/local/pgsql/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# === added by ko BEGIN ===
host all all 0.0.0.0/0 md5
# === added by ko END ===
:wq
# /usr/local/etc/rc.d/postgresql reload (重新载入配置文件)
创建数据库
代码:
# su pgsql
$ createuser -P Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
Password:
$ /usr/local/bin/createdb Password:
$ exit
ko# psql -U Password for user psql (9.1.3)
Type "help" for help.
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+--------+----------+---------+-------------+-------------------
postgres | pgsql | UTF8 | C | en_US.UTF-8 |
template0 | pgsql | UTF8 | C | en_US.UTF-8 | =c/pgsql +
| | | | | pgsql=CTc/pgsql
template1 | pgsql | UTF8 | C | en_US.UTF-8 | =c/pgsql +
| | | | | pgsql=CTc/pgsql
(4 rows)
$ createuser -P Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
Password:
$ /usr/local/bin/createdb Password:
$ exit
ko# psql -U Password for user psql (9.1.3)
Type "help" for help.
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+--------+----------+---------+-------------+-------------------
postgres | pgsql | UTF8 | C | en_US.UTF-8 |
template0 | pgsql | UTF8 | C | en_US.UTF-8 | =c/pgsql +
| | | | | pgsql=CTc/pgsql
template1 | pgsql | UTF8 | C | en_US.UTF-8 | =c/pgsql +
| | | | | pgsql=CTc/pgsql
(4 rows)
优化配置文件：
代码:
修改 postgresql.conf：
# vim /usr/local/pgsql/data/postgresql.conf
listen_addresses = '*'
port = 5432
unix_socket_permissions = 0700
# (如下 所列出的均系默认值，请根据下面的提示修改！)
max_connections = 100
shared_buffers = 32MB
work_mem = 1MB
maintenance_work_mem = 16MB
effective_cache_size = 128MB
:wq
# /usr/local/etc/rc.d/postgresql restart (重新启动服务)
# vim /usr/local/pgsql/data/postgresql.conf
listen_addresses = '*'
port = 5432
unix_socket_permissions = 0700
# (如下 所列出的均系默认值，请根据下面的提示修改！)
max_connections = 100
shared_buffers = 32MB
work_mem = 1MB
maintenance_work_mem = 16MB
effective_cache_size = 128MB
:wq
# /usr/local/etc/rc.d/postgresql restart (重新启动服务)
代码:
出处：http://bbs.chinaunix.net/thread-987207-1-1.html
-----------------------------------------
shared_buffers：最重要的参数，postgresql通过shared_buffers和内核 和磁盘打交道，
应该尽量大，让更多的数据缓存在shared_buffers中。通常设 置为实际RAM的10％是合理的。
-----------------------------------------
shared_buffers：最重要的参数，postgresql通过shared_buffers和内核 和磁盘打交道，
应该尽量大，让更多的数据缓存在shared_buffers中。通常设 置为实际RAM的10％是合理的。
( 我所看到的是1/4 ! FROM: “ I received a tip from the postgresql mailing list, that the shared_buffers need to be set the 1/4 of the total system memory.” )
代码:
work_mem： 在执行排序操作时，根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和 work_mem差不多大小的临时文件。显然拆分的结果是降低了排序的速度，因此增加work_mem有助于提高排序的速度。通常设 置为实际RAM的2% -4% 。
effective_cache_size：PostgreSQL能够使用的最大缓存，这个数字对于独立的PostgreSQL服务器而言应该足够大，比如4G的内存，可以设置为3.5G 。
maintence_work_mem：这里定义的内存只是在CREATE INDEX, VACUUM等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，应该尽快让这些指令快速执行完毕：给 maintence_work_mem大的内存，比如512M。
max_connections： max_connections的目的是防止( max_connections * work_mem )超出了实际内存大小。
如果将work_mem设置为实际内存的2%大小， 则在极端情况下， 比如有50个查询都有排序要求，而且都使用2％的内存，则会导 致swap的产生，系统性能就会大大降低。当然，如果有4G的内存，同时出现50个如 此大的查询的几率应该是很小的。不过，要清楚 max_connections和work_mem的关系。
effective_cache_size：PostgreSQL能够使用的最大缓存，这个数字对于独立的PostgreSQL服务器而言应该足够大，比如4G的内存，可以设置为3.5G 。
maintence_work_mem：这里定义的内存只是在CREATE INDEX, VACUUM等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，应该尽快让这些指令快速执行完毕：给 maintence_work_mem大的内存，比如512M。
max_connections： max_connections的目的是防止( max_connections * work_mem )超出了实际内存大小。
如果将work_mem设置为实际内存的2%大小， 则在极端情况下， 比如有50个查询都有排序要求，而且都使用2％的内存，则会导 致swap的产生，系统性能就会大大降低。当然，如果有4G的内存，同时出现50个如 此大的查询的几率应该是很小的。不过，要清楚 max_connections和work_mem的关系。
4: 安装 官方网站地址：简体中文语言包：
代码:
如何安装语言包
1.下载语言包并解压；
2.将language和styles文件夹上传到论坛的根目录，也就是有config.3.登陆到论坛后台，点击上方SYSTEM标签，然后左边点击Language Packs；
4.右边显示已安装和未安装的语言包，点击简体中文右边的Install安装即可；
5.如果想把简体中文设为论坛的默认语言，请点击左上方General标签，然后点击左边的Board Settings，这时在右边找到Default language这一项，选择简体中文，下方点击submit即可。
6.如果按照以上步骤操作完毕，从新登陆论坛时还不能显示中文，请打开User Control Panel，Board Preferences标签，左边点击global settings，将My language设置成简体中文，点击下方submit即可。
注意：在未安装论坛之前，也可将语言包按照上面第一步替换，这样安装过程就可以选择中文了。
1.下载语言包并解压；
2.将language和styles文件夹上传到论坛的根目录，也就是有config.3.登陆到论坛后台，点击上方SYSTEM标签，然后左边点击Language Packs；
4.右边显示已安装和未安装的语言包，点击简体中文右边的Install安装即可；
5.如果想把简体中文设为论坛的默认语言，请点击左上方General标签，然后点击左边的Board Settings，这时在右边找到Default language这一项，选择简体中文，下方点击submit即可。
6.如果按照以上步骤操作完毕，从新登陆论坛时还不能显示中文，请打开User Control Panel，Board Preferences标签，左边点击global settings，将My language设置成简体中文，点击下方submit即可。
注意：在未安装论坛之前，也可将语言包按照上面第一步替换，这样安装过程就可以选择中文了。
代码:
# wget http://www.# tar -jxvf # mv # chown -R www:www /var/www/# vim /usr/local/etc/date.timezone = Asia/Chongqing
:wq!
:wq!
备注：事实上隐藏软件版本信息对安全没有任何帮助，建议始终使用最新版本！
Also note that disabling the Server: header does nothing at all to make your server more secure; the idea of "security through obscurity" is a myth and leads to a false sense of safety.
01-01 20:46