postgres

postgres

ubuntu apt-get 数据源

sudo vim /etc/apt/source.list

# deb cdrom:[Debian GNU/Linux 7.2.0 _Wheezy_ - Official amd64 CD Binary-1 20131012-14:05]/ wheezy main#deb http://mirrors.163.com/debian/ wheezy main non-free contrib#deb-src http://mirrors.163.com/debian/ wheezy main non-free contribdeb http://mirrors.sohu.com/debian/ stable contrib main non-freedeb-src http://mirrors.sohu.com/debian/ stable contrib main non-freedeb http://security.debian.org/ wheezy/updates main contrib non-freedeb-src http://security.debian.org/ wheezy/updates main contrib non-free# wheezy-updates, previously known as 'volatile'#deb http://mirrors.163.com/debian/ wheezy-updates main contrib non-free#deb-src http://mirrors.163.com/debian/ wheezy-updates main contrib non-free

修改dns:

sudo vi /etc/resolvconf/resolv.conf.d/base（这个文件默认是空的）在里面插入：nameserver 8.8.8.8nameserver 8.8.4.4如果有多个DNS就一行一个修改好保存，然后执行sudo resolvconf -u再看/etc/resolv.conf，最下面就多了2行：cat /etc/resolv.conf# Dynamic resolv.conf(5) file for glibc resolver(3) generated by resolvconf(8)#     DO NOT EDIT THIS FILE BY HAND -- YOUR CHANGES WILL BE OVERWRITTENnameserver 8.8.8.8nameserver 8.8.4.4可以看到我们的设置已经加上了，然后再ping一个域名，当时就可以解析了，无需重启。ping baidu.com

安装

下载postgresql最新版：http://www.postgresql.org/ftp/source/

安装前检查系统所依赖的包 
dpkg -l 

安装依赖包 
apt-get install libxml2 
apt-get install libxml2-dev 
apt-get install libxml2-libxslt 
apt-get install libxml2-dev 
apt-get install libxslt1-dev 
apt-get install libossp-uuid-dev 
apt-get install gettext 
apt-get install python-dev 
apt-get install libperl-dev 

apt-get install libreadline-dev 
apt-get install libtool 


nk.conf 内容 
/srv/.soft/postgresql/postgresql-9.2.4 # less ../nk.conf 
./configure --prefix=/srv/pgsql92 \ 
--enable-nls \ 
--enable-depend \ 
--with-perl \ 
--with-python \ 
--with-openssl \ 
--with-ossp-uuid \ 
--with-libxml \ 
--with-libxslt 


安装pgsql 
cd /srv/.soft/postgresql/postgresql-9.2.4 
sh ../nk.conf   # or  ( sh ./configure --prefix=/srv/pgsql935)

make 
make install 

#安装contrib目录下的一些工具，是第三方组织的一些工具代码，建议安装
cd /srv/.soft/postgresql/postgresql-9.2.4/contrib;
make && make install;

groupadd pgsql 
useradd -g pgsql pgsql 

mkdir /srv/pgdata92 
[or mkdir /srv/pgsql92/data] 

cd /srv/pgsql92 
chown pgsql.pgsql data 
[or chown pgsql.pgsql -R /srv/pgsql92/] 
cd /srv/ 
chown pgsql.pgsql -R pgsq92/ 
chown pgsql.pgsql -R pgdata92/ 


su - pgsql 
/srv/pgsql92/bin/initdb -D /srv/pgdata92 #数据库初始化 
[or /srv/pgsql92/bin/initdb -D /srv/pgsql92/data] 

Success. You can now start the database server using:    /srv/pgsql935/bin/postgres -D /srv/pgdata935or    /srv/pgsql935/bin/pg_ctl -D /srv/pgdata935 -l logfile start

/srv/pgsql92/bin/pg_ctl -D /srv/pgdata92 start #启动数据库 

服务管理脚本 

cd /srv/pgsql92/bin vim server_ctl.sh #!/bin/bash #cd /srv/pgsql92/bin #su pgsql -c "/srv/pgsql92/bin/pg_ctl -D /srv/pgsql92/data/ -l /srv/pgsql92/log/pgsql.log $1" chown -R pgsql:pgsql /srv/logs/pgsql92 cd /srv/pgsql92/bin su pgsql -c "/srv/pgsql92/bin/pg_ctl -D /srv/pgdata92 $1" chmod 755 server_ctl.sh ./server_ctl.sh start 

====================================================

数据库管理

连接数据库, 默认的用户和数据库是postgrespsql -U user -d dbname切换数据库,相当于mysql的use dbname\c dbname列举数据库，相当于mysql的show databases\l列举表，相当于mysql的show tables\dt查看表结构，相当于desc tblname,show columns from tbname\d tblname\di 查看索引 创建数据库： create database [数据库名]; 删除数据库： drop database [数据库名];  *重命名一个表： alter table [表名A] rename to [表名B]; *删除一个表： drop table [表名]; 

ps aux | grep postgres

psql postgres

数据备份还原

数据还原bunzip2 -c huafei3_development.bz2 | psql <OPTIONS> template1备份：pg_dump -h qa.fun-guide.mobi -U pgsql huafei_development > huafeibak.outpsql -h localhost -U postgres -d huafei_development < huafeibak.out

http://my.oschina.net/zhao/blog/55071

桥接连接登陆

桥接bjv1db tunnelHost bjv1db  HostName 119.90.35.166  User root  Port 1022  identityfile ~/.ssh/funguide key  ServerAliveInterval 10  LocalForward 5435 localhost:5432  localForward 本地端口 远端端口

连接表同时常见一个 接口

huafei_state(master) ✗: ruby 话费报表统计.rb > /tmp/0428.csvdiscount_service(test_production): ssh -vvv -N -L 5433/192.168.0.132/6435 -p 6521 menxu@211.95.79.214

超级用户密码丢失， 卸载重新安装

rm -rf /usr/local/var/postgresrm -rf /usr/local/opt/postgresqlrm -rf /usr/local/bin/postgres rm -rf /usr/local/Cellar/postgresql rm -rf /usr/local/share/postgresqlrm -rf /usr/local/include/postgres_ext.hbrew uninstall postgresqlbrew install postgresql brew unlink postgresqlbrew link --overwrite postgresqlln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgentscreateuser -s -r postgrespsql -U postgresALTER USER menxu WITH PASSWORD '';~: su menxu 系统用户和数据库用户是分开的Password:/Users/menxu/.rvm/scripts/initialize:44: __rvm_cleanse_variables: function definition file not found/Users/menxu/.rvm/scripts/initialize:46: command not found: rvm_error应该是用这种方式登陆用户不会运行shell的设置脚本

查看passenger使用情况

rvmsudo passenger-memory-stats