postgres笔记

postgres 笔记

原文：http://my.oschina.net/zhao/blog/55071

发表于2 年 前(2012-04-25 07:57)   阅读（ 798） | 评论（ 0） 1人收藏此文章,我要收藏

赞0

http://wiki.ubuntu.org.cn/PostgreSQL

sudo apt-get install postgresql
sudo apt-get install pgadmin3
sudo /etc/init.d/postgresql start
sudo /etc/init.d/postgresql stop
ALTER USER postgres WITH PASSWORD ' <***password***> ';

mac:

brew install postgres

psql -h localhost postgres -U _postgres

postgres=# CREATE ROLE pgsql LOGIN PASSWORD 'postgres' SUPERUSER CREATEDB CREATEROLE REPLICATION;

https://wido.me/sunteya/setting-up-a-rails-development-on-mac/

http://www.postgresql.org/ftp/pgadmin3/release/v1.16.1/osx/
备份：
pg_dump -h qa.fun-guide.mobi -U pgsql huafei_development > huafeibak.out
psql -h localhost -U postgres -d huafei_development < huafeibak.out

sudo su postgres
psql
CREATE USER pgsql WITH PASSWORD '';
CREATE DATABASE xxxx2;
GRANT ALL PRIVILEGES ON DATABASE xxxx2 to xxxx1;

psql database_tst

 

//将查询语句写入文件

database_tst=#>\w /tmp/write.txt

database_tst=#>select * from views;

//将查询结果写入文件

database_tst=#>\o /tmp/write.txt

database_tst=#>select * from views;

//列状显示

database_tst=#>\x

database_tst=#>select * from views;

/?

//查询更多用法

删除数据库:

DROP DATABASE demodb;

DROP DATABASE 要大写

修改用户密码：
sudo su postgres
psql postgres
alter user postgres with password 'new password'
GRANT ALL PRIVILEGES ON DATABASE huafei_test to pgsql

http://jianlee.ylinux.org/Computer/%E6%9C%8D%E5%8A%A1%E5%99%A8/postgresql%E6%95%B0%E6%8D%AE%E5%BA%93%E5%85%A5%E9%97%A8.html

 

 

http://www.postgresql.org/

sudo apt-get install postgresql
initdb /usr/local/var/postgres X
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start X
pgsql --version
which psql
sudo - postgres
psql

\h
\s 刚才的操作
SELECT version();
SELECT current_date;
\password postgres
createdb mydb
dropdb mydb

sudo apt-get install pgadmin3
http://www.pgadmin.org/

版本不够
sudo apt-get install libxml2 libxslt
但没有libxslt
./configure --with-gtk --enable-gtk2 --enable-unicode
make
sudo make install
# Install wxWidgets contrib modules.
cd contrib/
make
sudo make install

rails new blog -d postgresql
gem 'pg'不用写，
rake db:create:all
rails g ..
rails db:migrate
rails s
rails db
select * from articles;
\d
\d articles
\?
\h select
\q
www.postgresql.org/docs/

sqlite3->postgresql
database.yml->postgresql
gem 'sqlite' gem 'pg'
rake db:create:all

gem install taps
taps server sqlite://db/development.sqlite3 rbates secret
taps pull postgres://rbates@locahost/store_development http://rbates:secret@locahost:5000
rails s

queue_classic
hstore ---nosql


http://www.postgresql.org/docs/9.1/interactive/tutorial-createdb.html http://railscasts.com/episodes/344-queue-classic

quenu_classic
队列任务

1, gem 'quenu_classic'
config/application.rb
config.active_record.schema_format = :sql
2. queue_classic.rake
3. queue_classic.rb
ENV[] =
4 . rails g migration

add
setup
rake db:migrate
rake qc:work
rails c
QC.enqueue "puts","helloWorld"

gem install queue_classic
失败：
You need to install postgresql-server-dev-X.Y
sudo apt-get install libpq-dev libkrb5-dev krb5-multidev
再gem install queue_classic就OK了
gem intall pg #不知道需要不需要
这个要在postgres用户下：
createdb queue_classic_test

export QC_DATABASE_URL="postgres://postgres:weizhao@localhost/queue_classic_test"
$ ruby -r queue_classic -e "QC::Setup.create"
$ ruby -r queue_classic -e "QC.enqueue('Kernel.puts', 'hello world')"
$ ruby -r queue_classic -e "QC::Worker.new.work"
错误：
uninitialized constant QC::Setup (NameError)
gem install pg


$ createdb queue_classic_test
$ psql queue_classic_test
psql- CREATE TABLE queue_classic_jobs (id serial, details text, locked_at timestamp);
psql- CREATE INDEX queue_classic_jobs_id_idx ON queue_classic_jobs (id);
$ export QC_DATABASE_URL="postgres://username:password@localhost/queue_classic_test"
$ gem install queue_classic
$ ruby -r queue_classic -e "QC::Database.new.load_functions"
$ ruby -r queue_classic -e "QC.enqueue('Kernel.puts', 'hello world')"

$ ruby -r queue_classic -e "QC::Worker.new.start"

使用Hstore

https://github.com/softa/activerecord-postgres-hstore

sudo apt-get install postgresql-contrib

Gemfile:
gem 'activerecord-postgres-hstore'

bundle install

createdb hstorage_dev

development:
adapter: postgresql
host: 127.0.0.1
database: hstorage_dev
encoding: unicode
username: postgres
password:
pool: 5

hstore support for your postgresql database:
rails g hstore:setup

rake db:migrate

rails g model Person name:string data:hstore

add indexes. Like this:

CREATE INDEX people_gist_data ON people USING GIST(data);
or
CREATE INDEX people_gin_data ON people USING GIN(data);

To my experience GIN is faster for searching records.

ruby :add_index(:people, :data, :gin => true)
add_index(:table_name, :column_name, :gin => true)

rake db:migrate



Usage:
Person.where("data ? 'foo'")
Person.where("data -> 'foo' = 'bar'")
Person.where("data @> 'foo=>bar'") 用index来查询，快
Person.where("data -> 'foo' <> 'bar'")
or
Person.where("not data @> 'foo=>bar'")

Find records where ‘foo’ is like ‘bar’:

Person.where("data -> 'foo' LIKE '%bar%'")
or something like …
Person.where("data -> 'foo' ILIKE '%bar%'")

If you need to delete a key in a record, you can do it that way:

person.destroy_key(:data, :foo)

This way you’ll also save the record:

person.destroy_key!(:data, :foo)

The destroy_key method returns ‘self’, so you can chain it:

person.destroy_key(:data, :foo).destroy_key(:data, :bar).save

But there it a shortcut for that:

person.destroy_keys(:data, :foo, :bar)

or…

person.destroy_keys!(:data, :foo, :bar)

And finally, if you need to delete keys in many rows, you can:

Person.delete_key(:data, :foo)

and with many keys:

Person.delete_keys(:data, :foo, :bar)