postgreSQL学习笔记

petterla

摘自：http://blog.chinaunix.net/u1/45510/showart_462552.html
1、基本操作：
./configure
gmake
su
gmake install
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
export PATH=/usr/local/pgsql/binPATH
adduser postgres
mkdir /opt/data/pgsql/data_sp
chown -R postgres /opt/data/pgsql
su - postgres
/usr/local/pgsql/bin/initdb -D /opt/data /pgsql
/usr/local/pgsql/bin/postmaster -D /opt/data /pgsql >logfile 2>&1 & 或：
su -c ‘pg_ctl start -D /opt/work/data/pgsql -l serverlog’ postgres
/usr/local/pgsql/bin/createdb testdb
/usr/local/pgsql/bin/psql testdb
testdb=# create tablespace data_sp location ‘/opt/work/data/data_sp’;
testdb=# set default_tablespace = data_sp;
testdb=# create table foo(i int, a char);
testdb=# insert into foo values(1,’a’);
testdb=# select * from foo;
testdb=# \\q
pg_dump -f testdb.tar -F t -h realserver-1.ltsp.com -U wlming testdb 或者：
tar -cf backup.tar /opt/work/data
pg_ctl -D /opt/work/data stop
2、配置：
1) cat /opt/work/data/postgresql.conf
……
listen_addresses = 'localhost,192.168.53.12'
port = 5432
# 配置通过kerberos认证：
krb_server_keyfile = '/etc/krb5.keytab'
krb_srvname = 'postgres'
krb_server_hostname = 'realserver-1.ltsp.com'
……
2) echo “host all all 192.168.53.20/24 krb5” >> /opt/work/data/pg_hba.conf
3)在kerberos中增加记录：
kadmin% addprinc -randkey postgres/realserver-1.ltsp.com@LTSP.COM
kadmin% addprinc testuser@LTSP.COM
kadmin% ktadd postgres/realserver-1.ltsp.com@LTSP.COM
4)在postgresql中增加角色：
create user testuser with superuser;
3、启动postgresql:
pg_ctl -D /opt/work/data -l logfile start
4、登录：
在另一台机器上：
kinit -p testuser@LTSP.COM
psql -h realserver-1.ltsp.com testdb testuser
5、数据库迁移：
pg_dump -h host1 dbname | psql -h host2 dbname