PostgreSQL


安装

emerge postgresql-server
# for initdb locale
vi /etc/conf.d/postgresql-9.0
emerge --config =dev-db/postgresql-server-9.0.1

Python

psycopg for gentoo

emerge psycopg
import psycopg2
cxn = psycopg2.connect('dbname=test user=postgres password=password')
cur = cxn.cursor()
sql = 'select version();'
cur.execute(sql)
rows = cur.fetchall()
print(rows)

postgresql-python for rhel5.5

yum install postgresql-python
import pg
cxn = pg.connect(dbname='test', user='postgres')
cur = cxn.cursor()
sql = 'select version();'
cur.execute(sql)
rows = cur.fetchall()
print(rows)

管理

修改 Postgresql 的认证方式 ,将 pg_hba.conf 中 ident 为 trust。

  

passwd postgres

su - postgres

$ psql test
psql (8.4.5)
Type “help” for help.

test=# \d
List of relations
Schema | Name | Type | Owner
——–+———+——-+———-
public | weather | table | postgres
(1 row)

test=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
———–+———-+———–+———–+——-+———————–
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres
: postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres
: postgres=CTc/postgres
test | postgres | SQL_ASCII | C | C |
(4 rows)

test=# \c postgres
psql (8.4.5)
You are now connected to database “postgres”.
postgres=# \q


### wwwsqldesigner

createuser -A -D -R -S -P -E -U postgres -W wwwsqldesigner

createdb -O wwwsqldesigner -E UTF-8 -T template0 -U postgres -W wwwsqldesigner

psql -U wwwsqldesigner wwwsqldesigner

psql (8.4.5)
Type “help” for help.

wwwsqldesigner=# \i database.sql
psql:database.sql:1: NOTICE: table “wwwsqldesigner” does not exist, skipping
DROP TABLE
psql:database.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index “wwwsqldesigner_pkey” for table “wwwsqldesigner”
CREATE TABLE
wwwsqldesigner=> \q


### SQL
```sql
CREATE USER user WITH NOCREATEDB NOCREATEUSER ENCRYPTED PASSWORD 'password';
CREATE DATABASE db WITH OWNER user TEMPLATE template0;

GRANT ALL PRIVILEGES ON DATABASE db TO user;
GRANT ALL PRIVILEGES ON table TO user;
GRANT ALL PRIVILEGES ON sequence TO user;

清空一个数据库

PostgreSQL有一个Schema的概念,所有的表和序列是先存储在Schema中的。一个数据可以包括多个Schema,而通常我们会把表和序列放在一个名为public的Schema里,删除public这个Schema,再重新创建它即可达到清空数据库的目的。

DROP SCHEMA public CASCADE;
# 再创建它
CREATE SCHEMA public AUTHORIZATION username;

数据导入导出

pg_dump dbname > dump.sql
psql dbname < dump.sql