ubuntu 使用、配置postgresql以及psycopg2

来源：互联网发布：域名备案代办编辑：程序博客网时间：2024/05/29 14:40

1 网络配置

桥接方式，很简单
配置ip

sudo gedit /etc/network/interfaces

auto loiface lo inet loopbackauto eth0iface eth0 inet staticaddress X.X.X.Xnetmask 255.255.0.0gateway X.X.X.X

配置DSN

sudo gedit /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 OVERWRITTENnameserver 192.168.1.1search localdomain

重启网卡
sudo /etc/init.d/networking restart

ping一下，外网内网均可以用。
对了虚拟机配置别忘了选为桥接。

2. 安装PostgreSQL

参考http://www.crazyant.net/754.html
http://www.2cto.com/os/201306/221645.html

sudo apt-get install postgresql postgresql-client postgresql-contrib

一直yes直到安装完毕
可执行程序为

sudo /etc/init.d/postgresql {start|stop|restart|reload|force-reload|status}

登录数据库

sudo -u postgres psql

设置密码并退出（aaa）

postgres=# ALTER ROLE postgres WITH ENCRYPTED PASSWORD ‘mypassword’;postgres=# \q

这里设置允许远程连接权限

sudo gedit /etc/postgresql/9.1/main/postgresql.conf

listen_addresses = '*'password_encryption = on

这里设置允许远程进行数据库操作：

sudo vi /etc/postgresql/9.1/main/pg_hba.conf

最后一行添加语句

host all all X.X.X.X/24 md5

修改linux 用户postgres的密码

sudo passwd -d postgres 删除密码sudo su postgres -c passwd 设置密码

反正是练习用，设置为公司名，比较好记

进入psql，创建用户和数据库

sudo -u postgres psqlcreate user "usr" with password 'usr' nocreatedb;postgres=# create user "usr" with password 'usr' nocreatedb;CREATE ROLEpostgres=# create user "usr" with password 'usr' nocreatedb;ERROR:  role "usr" already existspostgres=# create database  usr owner usr encoding 'UTF-8'postgres-# ;CREATE DATABASEpostgres=# \q

之后重启数据库

sudo /etc/init.d/postgresql restart

如果想在服务器psql中操作数据库，需连接到相应数据库

postgres=# \c usr;You are now connected to database "usr" as user "postgres".usr=# select *  from ztest; id | num | data ----+-----+------  1 |   1 | aaa  2 |   2 | bbb  3 |   3 | ccc(3 rows)usr=# \d #查看内容            List of relations Schema |     Name     |   Type   | Owner --------+--------------+----------+------- public | ztest        | table    | usr public | ztest_id_seq | sequence | usr(2 rows)usr=# \di #查看索引结构              List of relations Schema |    Name    | Type  | Owner | Table --------+------------+-------+-------+------- public | ztest_pkey | index | usr   | ztest(1 row)

这样才能从远程连接数据库
之后可以通过pdadminIII 连接数据库

用py在windows上运行建立了一个新的表ztest

# -*- coding: utf-8 -*-"""Created on Wed Mar 09 11:08:16 2016@author: wangxin2"""import psycopg2# 数据库连接参数conn = psycopg2.connect(database="usr", user="usr", password="usr", host="X.X.X.X", port="5432")cur = conn.cursor()cur.execute("CREATE TABLE ztest(id serial PRIMARY KEY, num integer,data varchar);")# insert one itemcur.execute("INSERT INTO ztest(num, data)VALUES(%s, %s)", (1, 'aaa'))cur.execute("INSERT INTO ztest(num, data)VALUES(%s, %s)", (2, 'bbb'))cur.execute("INSERT INTO ztest(num, data)VALUES(%s, %s)", (3, 'ccc'))cur.execute("SELECT * FROM ztest;")rows = cur.fetchall()        # all rows in tableprint(rows)for i in rows:    print(i)conn.commit()cur.close()conn.close()

至此数据库安装完毕。

3 安装psycopg2

sudo apt-get install python-psycopg2
验证安装完成

aaa@ubuntu:~$ pythonPython 2.7.3rc2 (default, Mar 21 2012, 21:13:11) [GCC 4.6.3] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> import psycopg2>>> psycopg2.apilevel'2.0'

0 0