学步园

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 contrib


deb http://mirrors.sohu.com/debian/ stable contrib main non-free
deb-src http://mirrors.sohu.com/debian/ stable contrib main non-free




deb http://security.debian.org/ wheezy/updates main contrib non-free
deb-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.8
nameserver 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 OVERWRITTEN
nameserver 8.8.8.8
nameserver 8.8.4.4

可以看到我们的设置已经加上了，然后再ping一个域名，当时就可以解析了，无需重启。

ping baidu.com

安装

Success. You can now start the database server using:

    /srv/pgsql935/bin/postgres -D /srv/pgdata935
or
    /srv/pgsql935/bin/pg_ctl -D /srv/pgdata935 -l logfile 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 

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

数据库管理

连接数据库, 默认的用户和数据库是postgres
psql -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.out
psql -h localhost -U postgres -d huafei_development < huafeibak.out

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

桥接连接登陆

桥接


bjv1db tunnel


Host 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.csv




discount_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/postgres
rm -rf /usr/local/opt/postgresql
rm -rf /usr/local/bin/postgres 
rm -rf /usr/local/Cellar/postgresql 
rm -rf /usr/local/share/postgresql
rm -rf /usr/local/include/postgres_ext.h
brew uninstall postgresql

brew install postgresql 
brew unlink postgresql
brew link --overwrite postgresql
ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
createuser -s -r postgres
psql -U postgres
ALTER 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的设置脚本