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。

sed -i "s/ident/trust/" /var/lib/pgsql/data/pg_hba.conf

# 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

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

---