openbsd +postgresql?
-
junfengfan
- 铜 Cu
- 帖子: 236
- 注册时间: 2010-02-03 18:37
openbsd +postgresql?
mysql,现在能操作；可当我转到postgrelsql感觉无从下手，有些难！！
回复: openbsd +postgresql?
postgresql比mysql容易多了，安装容易，操作容易，编程更容易。我已经基本不用mysql，它的表现让我有些失望。至于还保留着mysql，主要是以前的小项目还应用着，有时还要改改。
很无奈，mysql是个巨无霸，却只能用在非关键性小业务上，给少了资源，跑不起来，给多了资源，自己会搞死自己，经常要重启。用上事务存储过程触发器吧，慢得要命。不用企业特性吧，又特别占资源，每次装它都有鸡肋的感觉。各个版本间又不兼容，唉
还是老老实实用PostgreSQL吧，体积小又精悍，想跑得快又容易，兼容性好，关键是什么都有，跑个几年不理它都可以，呵呵
很无奈，mysql是个巨无霸，却只能用在非关键性小业务上，给少了资源，跑不起来，给多了资源，自己会搞死自己，经常要重启。用上事务存储过程触发器吧，慢得要命。不用企业特性吧，又特别占资源，每次装它都有鸡肋的感觉。各个版本间又不兼容，唉
还是老老实实用PostgreSQL吧，体积小又精悍，想跑得快又容易，兼容性好，关键是什么都有，跑个几年不理它都可以，呵呵
回复: openbsd +postgresql?
PostgreSQL是好东西，不会让你失望的，呵呵。虽然我找不出证据说明它是最快的数据库之一，只能凭我的经验说说，配置好了，速度不会比全面优化过的Oracle差。主要是快且安全，安全第一。我用Oracle是用怕了，单位和客户可以用Oracle，我自己是不敢用的。每个月都要去帮单位和客户修复Oracle，RAC集群经常挂掉，经常起不来，问题多多，每次恢复它的时候我都在想，如果这次修不好呢。。。真怀念当年Oracle8i的时候，服务器都受潮发霉了，它都没挂，现在动不动就over。。。
我用两块刀片装PostgreSQL或Firebird可以做成几十万并发。如果用五台满配的p6-570小型机装Oracle rac，想要做5万并发都难，这也是Oracle应用了共享内存的必然硬伤，并发数是很难上得来的。远程灾备同步每个月都出莫名其妙的各种错误，烦死了。
MySQL出问题也是经常的，长年用得最多，谁都知道，在非单一应用环境下出问题重启MySQL那是经常性的工作，除非一台MySQL只做很简单一种应用还好点。最搞笑的就是监控平台Cacti，用一两个月，所有数据会自动丢光光。无论我用Redhat还是CentOS，换什么版本，不管是刀片、机架服务器，还是虚拟机，一段时间都会丢数据，我要疯了。
对Oracle和MySQL我反正是用怕了，而PostgreSQL持续运行刷新和备份恢复都超级简单。自己的关键东西还是放在PostgreSQL保险些，公家的就由得它去了。
当然，因为firebird可以设置用或不用共享内存、用同一块还是独立多块共享内存，所以它的并发数可以做得超级高，速度也暴快。但最大一个缺点就是外围工具太少，什么在线整理热备什么什么的都很少。这点上PostgreSQL要稍微好些，且针对它写维护脚本也很容易。
数据库不是单单快就可以的，又快又稳才是我们喜欢的。现在我已发觉PostgreSQL已经在性能上也比以前好出一大截了。在提交大批量数据时，也是一瞬间就完成了，快得我都傻眼了。
PostgreSQL + OpenBSD文件系统 + OpenBSD网络底层，我的经验是坚如磐石啊。
今晚喝了两口，把牢骚倒了些出来，啊~~~舒服多了。
我用两块刀片装PostgreSQL或Firebird可以做成几十万并发。如果用五台满配的p6-570小型机装Oracle rac，想要做5万并发都难，这也是Oracle应用了共享内存的必然硬伤，并发数是很难上得来的。远程灾备同步每个月都出莫名其妙的各种错误，烦死了。
MySQL出问题也是经常的，长年用得最多，谁都知道，在非单一应用环境下出问题重启MySQL那是经常性的工作，除非一台MySQL只做很简单一种应用还好点。最搞笑的就是监控平台Cacti，用一两个月，所有数据会自动丢光光。无论我用Redhat还是CentOS，换什么版本，不管是刀片、机架服务器，还是虚拟机，一段时间都会丢数据，我要疯了。
对Oracle和MySQL我反正是用怕了，而PostgreSQL持续运行刷新和备份恢复都超级简单。自己的关键东西还是放在PostgreSQL保险些，公家的就由得它去了。
当然，因为firebird可以设置用或不用共享内存、用同一块还是独立多块共享内存，所以它的并发数可以做得超级高，速度也暴快。但最大一个缺点就是外围工具太少，什么在线整理热备什么什么的都很少。这点上PostgreSQL要稍微好些，且针对它写维护脚本也很容易。
数据库不是单单快就可以的，又快又稳才是我们喜欢的。现在我已发觉PostgreSQL已经在性能上也比以前好出一大截了。在提交大批量数据时，也是一瞬间就完成了，快得我都傻眼了。
PostgreSQL + OpenBSD文件系统 + OpenBSD网络底层，我的经验是坚如磐石啊。
今晚喝了两口，把牢骚倒了些出来，啊~~~舒服多了。
-
junfengfan
- 铜 Cu
- 帖子: 236
- 注册时间: 2010-02-03 18:37
回复: openbsd +postgresql?
openbsd5.0安装完 postgresql 后，如何让它开机就启动？就像 装完 mysql 后 ，加一个
#/etc/rc.conf.local
pkg_scripts="mysqld" 一样。
#/etc/rc.conf.local
pkg_scripts="mysqld" 一样。
回复: openbsd +postgresql?
安装这些软件之后junfengfan 写了:openbsd5.0安装完 postgresql 后，如何让它开机就启动？就像 装完 mysql 后 ，加一个
#/etc/rc.conf.local
pkg_scripts="mysqld" 一样。
/usr/local/share/doc/pkg-readmes
都有上手说明哦
-
junfengfan
- 铜 Cu
- 帖子: 236
- 注册时间: 2010-02-03 18:37
回复: openbsd +postgresql?
/usr/local/share/doc/pkg_readmes目录下的
postgresql-server-9.0.4p1
打开后没有启动选项，这是显示的说明文档
postgresql-server-9.0.4p1
打开后没有启动选项，这是显示的说明文档
-
junfengfan
- 铜 Cu
- 帖子: 236
- 注册时间: 2010-02-03 18:37
回复: openbsd +postgresql?
# cd /usr/local/share/doc/pkg-readmes/
# ls
postgresql-server-9.0.4p1
# more postgresql-server-9.0.4p1
$OpenBSD: README-server,v 1.7 2011/06/06 08:43:59 sthen Exp $
+-----------------------------------------------------------------------
| Running postgresql-9.0.4 on OpenBSD
+-----------------------------------------------------------------------
At least two different accounts are involved when working with PostgreSQL:
One is an OpenBSD userid, '_postgresql', which is used as the userid of files
that are part of PostgreSQL. The other, usually named 'postgres', is not an
OpenBSD userid, i.e. you will not find it in /etc/passwd, but an account
internal to the database system. The 'postgres' account is called the dba
account (database administrator) and is created when a new database is
initialized using the initdb command.
If you are installing PostgreSQL for the first time, you have to create
a default database first. In the following example we install a database
in /var/postgresql/data with a dba account 'postgres' and md5 authentication.
We will be prompted for a password to protect the dba account:
# su - _postgresql
$ mkdir /var/postgresql/data
$ initdb -D /var/postgresql/data -U postgres -A md5 -W
Please note that by default the cluster's encoding will be SQL_ASCII. If
you want to have an another default encoding, use the option -E with initdb.
If your cluster is already created, you can specify an another encoding when
you create a new database with this command:
CREATE DATABASE xxx TEMPLATE template0 ENCODING 'xxx' ;
It is strongly advised that you do not work with the postgres dba account
other than creating more users and/or databases or for administrative task.
Use the PostgreSQL permission system to make sure that a database is only
accessed by programs/users that have the right to do so.
Please consult the PostgreSQL website for more information, especially when
you are upgrading an existing database installation.
Network Connections
===================
To allow connections over TCP (and other options) edit the file:
/var/postgresql/data/postgresql.conf
and also edit the pg_hba.conf (in the same directory) making the
appropriate changes to allow connection from your network.
To allow SSL connections, edit postgresql.conf and enable the
'ssl' keyword, and create keys and certificates:
# su - _postgresql
$ cd /var/postgresql/data
$ umask 077
$ openssl genrsa -out server.key 2048
$ openssl req -new -key server.key -out server.csr
Either take the CSR to a Certifying Authority (CA) to sign your
certificate, or self-sign it:
$ openssl x509 -req -days 365 -in server.csr \
-signkey server.key -out server.crt
Restart PostgreSQL to allow these changes to take effect.
Tuning for busy servers
=======================
The default sizes in the GENERIC kernel for SysV semaphores
are not really large enough for a database with more than a
handful of connections. A server running such a database should
have at least the following in /etc/sysctl.conf:
kern.seminfo.semmni=256
kern.seminfo.semmns=2048
To serve a large number of connections (>250), you may also need
to increase the maximum shared memory segment size, on the i386
try:
kern.shminfo.shmmax=50331648 # this is 48MB.
# default on i386 is 32MB
# other archs will vary
These numbers should be tuned depending on system use. You will also
need to tune the values in the postgresql.conf file to increase the
number of connections to the backend.
By default, the _postgresql user, and so the postmaster and backend
processes run in the login(1) class of "daemon". On a busy server,
it may be advisable to put the _postgresql user and processes in
their own login(1) class with tuned resources, such as more open
file descriptors etc.
For example, add this to the login.conf(5) file:
postgresql:\
penfiles-cur=768:\
:tc=daemon:
Rebuild the login.conf.db file if necessary:
# [ -f /etc/login/conf.db ] && cap_mkdb /etc/login.conf
For more than about 250 connections, these numbers should be
increased. Please report any changes and experiences to the package
maintainers so that we can update this file for future versions.
Kerberos authentication
=======================
By default the postgresql server requires it's own krb5.keytab file. It
should be readable only by the _postgresql user. The default location of
the file is '/etc/postgresql/krb5.keytab' but is tunable by setting the
krb_server_keyfile line in postgresql.conf.
To generate the keytab:
# mkdir /etc/postgresql
# ktutil -k /etc/postgresql/krb5.keytab get postgres/server.domain
# chown _postgresql:_postgresql /etc/postgresql/krb5.keytab
Upgrade Howto (for a major upgrade)
===================================
If you didn't install PostgreSQL by following this README,
you must adapt these instructions to your setup.
Most commands must be launched as the user _postgresql:
# su - _postgresql
1) Backup all your data:
$ pg_dumpall -U postgres > /var/postgresql/full.sqldump
2) Shutdown the server:
$ pg_ctl -D /var/postgresql/data/ stop -m fast
3) As root, upgrade your PostgreSQL package with pkg_add.
# pkg_add -u postgresql-server
4) Backup your old data directory:
$ mv /var/postgresql/data /var/postgresql/data.old
5) Create a new data directory:
$ mkdir /var/postgresql/data
$ initdb -D /var/postgresql/data -U postgres -A md5 -W
6) Restore your old pg_hba.conf
$ cp /var/postgresql/data.old/pg_hba.conf /var/postgresql/data/
Some postgresql.conf settings changed or disappeared in this version.
Examine your old file for local changes and apply them to the new version.
7) Start PostgreSQL:
$ pg_ctl -D /var/postgresql/data -l /var/postgresql/logfile start
8) Restore your data:
$ psql -U postgres < /var/postgresql/full.sqldump
Clients/Frontends
=================
Many applications can use the PostgreSQL database right away. To facilitate
administration of a PostgreSQL database, two clients are notable:
www/phppgadmin A web based user interface that uses PHP5
databases/pgadmin3 A graphical user interface that uses wxWidgets
#
# ls
postgresql-server-9.0.4p1
# more postgresql-server-9.0.4p1
$OpenBSD: README-server,v 1.7 2011/06/06 08:43:59 sthen Exp $
+-----------------------------------------------------------------------
| Running postgresql-9.0.4 on OpenBSD
+-----------------------------------------------------------------------
At least two different accounts are involved when working with PostgreSQL:
One is an OpenBSD userid, '_postgresql', which is used as the userid of files
that are part of PostgreSQL. The other, usually named 'postgres', is not an
OpenBSD userid, i.e. you will not find it in /etc/passwd, but an account
internal to the database system. The 'postgres' account is called the dba
account (database administrator) and is created when a new database is
initialized using the initdb command.
If you are installing PostgreSQL for the first time, you have to create
a default database first. In the following example we install a database
in /var/postgresql/data with a dba account 'postgres' and md5 authentication.
We will be prompted for a password to protect the dba account:
# su - _postgresql
$ mkdir /var/postgresql/data
$ initdb -D /var/postgresql/data -U postgres -A md5 -W
Please note that by default the cluster's encoding will be SQL_ASCII. If
you want to have an another default encoding, use the option -E with initdb.
If your cluster is already created, you can specify an another encoding when
you create a new database with this command:
CREATE DATABASE xxx TEMPLATE template0 ENCODING 'xxx' ;
It is strongly advised that you do not work with the postgres dba account
other than creating more users and/or databases or for administrative task.
Use the PostgreSQL permission system to make sure that a database is only
accessed by programs/users that have the right to do so.
Please consult the PostgreSQL website for more information, especially when
you are upgrading an existing database installation.
Network Connections
===================
To allow connections over TCP (and other options) edit the file:
/var/postgresql/data/postgresql.conf
and also edit the pg_hba.conf (in the same directory) making the
appropriate changes to allow connection from your network.
To allow SSL connections, edit postgresql.conf and enable the
'ssl' keyword, and create keys and certificates:
# su - _postgresql
$ cd /var/postgresql/data
$ umask 077
$ openssl genrsa -out server.key 2048
$ openssl req -new -key server.key -out server.csr
Either take the CSR to a Certifying Authority (CA) to sign your
certificate, or self-sign it:
$ openssl x509 -req -days 365 -in server.csr \
-signkey server.key -out server.crt
Restart PostgreSQL to allow these changes to take effect.
Tuning for busy servers
=======================
The default sizes in the GENERIC kernel for SysV semaphores
are not really large enough for a database with more than a
handful of connections. A server running such a database should
have at least the following in /etc/sysctl.conf:
kern.seminfo.semmni=256
kern.seminfo.semmns=2048
To serve a large number of connections (>250), you may also need
to increase the maximum shared memory segment size, on the i386
try:
kern.shminfo.shmmax=50331648 # this is 48MB.
# default on i386 is 32MB
# other archs will vary
These numbers should be tuned depending on system use. You will also
need to tune the values in the postgresql.conf file to increase the
number of connections to the backend.
By default, the _postgresql user, and so the postmaster and backend
processes run in the login(1) class of "daemon". On a busy server,
it may be advisable to put the _postgresql user and processes in
their own login(1) class with tuned resources, such as more open
file descriptors etc.
For example, add this to the login.conf(5) file:
postgresql:\
penfiles-cur=768:\:tc=daemon:
Rebuild the login.conf.db file if necessary:
# [ -f /etc/login/conf.db ] && cap_mkdb /etc/login.conf
For more than about 250 connections, these numbers should be
increased. Please report any changes and experiences to the package
maintainers so that we can update this file for future versions.
Kerberos authentication
=======================
By default the postgresql server requires it's own krb5.keytab file. It
should be readable only by the _postgresql user. The default location of
the file is '/etc/postgresql/krb5.keytab' but is tunable by setting the
krb_server_keyfile line in postgresql.conf.
To generate the keytab:
# mkdir /etc/postgresql
# ktutil -k /etc/postgresql/krb5.keytab get postgres/server.domain
# chown _postgresql:_postgresql /etc/postgresql/krb5.keytab
Upgrade Howto (for a major upgrade)
===================================
If you didn't install PostgreSQL by following this README,
you must adapt these instructions to your setup.
Most commands must be launched as the user _postgresql:
# su - _postgresql
1) Backup all your data:
$ pg_dumpall -U postgres > /var/postgresql/full.sqldump
2) Shutdown the server:
$ pg_ctl -D /var/postgresql/data/ stop -m fast
3) As root, upgrade your PostgreSQL package with pkg_add.
# pkg_add -u postgresql-server
4) Backup your old data directory:
$ mv /var/postgresql/data /var/postgresql/data.old
5) Create a new data directory:
$ mkdir /var/postgresql/data
$ initdb -D /var/postgresql/data -U postgres -A md5 -W
6) Restore your old pg_hba.conf
$ cp /var/postgresql/data.old/pg_hba.conf /var/postgresql/data/
Some postgresql.conf settings changed or disappeared in this version.
Examine your old file for local changes and apply them to the new version.
7) Start PostgreSQL:
$ pg_ctl -D /var/postgresql/data -l /var/postgresql/logfile start
8) Restore your data:
$ psql -U postgres < /var/postgresql/full.sqldump
Clients/Frontends
=================
Many applications can use the PostgreSQL database right away. To facilitate
administration of a PostgreSQL database, two clients are notable:
www/phppgadmin A web based user interface that uses PHP5
databases/pgadmin3 A graphical user interface that uses wxWidgets
#
回复: openbsd +postgresql?
启动PostgreSQL必须要postgres用户，不能是root，所以我在rc.local文件里加上一句
su - postgres -c 'postgres -D /base/pgdb' &
或
su - postgres -c 'pg_ctl start -D /base/pgdb' &
就能够开机自启动了
su - postgres -c 'postgres -D /base/pgdb' &
或
su - postgres -c 'pg_ctl start -D /base/pgdb' &
就能够开机自启动了
回复: openbsd +postgresql?
在生产环境下部署PostgreSQL，建议配置sysctl.conf调整内核参数，让PostgreSQL系统库支持大块共享内存和多连接数等等提高性能，如何调整，在它的中文手册里有修改例子，我的开发服务器里sysctl.conf配置一个高效的中小型的PGSQL
kern.maxproc=65536
kern.maxfiles=262144
kern.somaxconn=65536
kern.sominconn=128
kern.maxclusters=131072
kern.seminfo.semmni=256
kern.seminfo.semmns=512
kern.seminfo.semmnu=256
kern.seminfo.semmsl=512
kern.shminfo.shmseg=256
kern.shminfo.shmall=32768
## 设置每块共享内存最大为 256MB
kern.shminfo.shmmax=301989888
以上配置能同时连1000个长连接没问题，如果内存有4G，可以把共享内存设置到2G
kern.maxproc=65536
kern.maxfiles=262144
kern.somaxconn=65536
kern.sominconn=128
kern.maxclusters=131072
kern.seminfo.semmni=256
kern.seminfo.semmns=512
kern.seminfo.semmnu=256
kern.seminfo.semmsl=512
kern.shminfo.shmseg=256
kern.shminfo.shmall=32768
## 设置每块共享内存最大为 256MB
kern.shminfo.shmmax=301989888
以上配置能同时连1000个长连接没问题，如果内存有4G，可以把共享内存设置到2G
回复: openbsd +postgresql?
连接数据库命令是
psql -h IP地址 -p 端口 -d 数据库名 -U 用户 -W
如：
$ psql
postgres=#
psql -h IP地址 -p 端口 -d 数据库名 -U 用户 -W
如：
$ psql
postgres=#
回复: openbsd +postgresql?
本不想上来啰嗦，但是帖子放这里没有解决的时间太长了，也许你自己已经解决了。——不管怎样说，我先把自己心里得想法说出来，不吐不快：junfengfan 写了:mysql,现在能操作；可当我转到postgrelsql感觉无从下手，有些难！！
您这种方式的提问已经不只出现了一次了，着实让人无法回复。
详这里：如何获得帮助及帮助别人，看过也请复习一下。:)，新会员用这种方式提问还情有可原，老会员如果“坚持”以这种方式提问、或者违反了里面描述的第“8”条，恐怕没有人愿意再帮助你。
本来可能是很简单的问题，只要你描述得详细一些，可能别人几句话就解决了，你在头贴里就说了一句话，现在人家none兄给你回了三个posts, 您自己却没有下文了，想想如果你自己是回复者寒心不？:(
-
junfengfan
- 铜 Cu
- 帖子: 236
- 注册时间: 2010-02-03 18:37
回复: openbsd +postgresql?
我按照leo兄的教程 OpenBSD+Apache 1.3+php 5.3.10+postgresql+phpbb (chroot环境探讨) 装 drupal7 感觉反应慢，或许 是我不会性能调优？应该是的，因为按照在openbsd5.1的提示，我也不知道为何那样调，每一项的涵义是什么也不知道~~none 写了:postgresql比mysql容易多了，安装容易，操作容易，编程更容易。我已经基本不用mysql，它的表现让我有些失望。至于还保留着mysql，主要是以前的小项目还应用着，有时还要改改。
很无奈，mysql是个巨无霸，却只能用在非关键性小业务上，给少了资源，跑不起来，给多了资源，自己会搞死自己，经常要重启。用上事务存储过程触发器吧，慢得要命。不用企业特性吧，又特别占资源，每次装它都有鸡肋的感觉。各个版本间又不兼容，唉
还是老老实实用PostgreSQL吧，体积小又精悍，想跑得快又容易，兼容性好，关键是什么都有，跑个几年不理它都可以，呵呵
回复: openbsd +postgresql?
你想想，如果OpenBSD服务应用这个版面有几百万个帖子时我们的服务器会是什么样的情况？我在上面提到的老机上跑了一个实验性的phpbb论坛，单个版面的帖子数达到了370万，读取版面的帖子时稍有些滞后的感觉，为了测试有个单个帖子的回复量达到了10000多，但是读取这个帖子的内容时还是很轻松的。读这个版面时内存的使用量变化很小，但是瞬时CPU使用率较高，不过时间很短。考虑到postgresql在这么大数量帖子的版面下仍可以稳定运行，至少我感觉很不一般。
你可以对比一下其它的社区论坛版面，很少可以达到100万的。估计真到了机器页吃不消了。
我的数据库优化前4.83G，优化后2.73G，目前仍然一切正常，很令人吃惊。
此外，你说的反应慢是指什么？数据库读取还是网页显示，你把自己的机器情况和配置贴出来，尽管我不熟悉，但是信息全面none兄才好给你提建议。
OpenBSD i386 的current目前有问题，我现在转战到FreeBSD下进行测试。:)
你可以对比一下其它的社区论坛版面，很少可以达到100万的。估计真到了机器页吃不消了。
我的数据库优化前4.83G，优化后2.73G，目前仍然一切正常，很令人吃惊。
此外，你说的反应慢是指什么？数据库读取还是网页显示，你把自己的机器情况和配置贴出来，尽管我不熟悉，但是信息全面none兄才好给你提建议。
OpenBSD i386 的current目前有问题，我现在转战到FreeBSD下进行测试。:)
在线用户
正浏览此版面之用户: 没有注册用户 和 21 访客