ubuntu 使用、配置postgresql以及psycopg2

最新推荐文章于 2025-01-08 17:40:20 发布

凌乱小风

最新推荐文章于 2025-01-08 17:40:20 发布

阅读量1k

点赞数 2

分类专栏：工作文章标签： ubuntu

工作专栏收录该内容

24 篇文章

订阅专栏

1 网络配置

桥接方式，很简单
配置ip

sudo gedit /etc/network/interfaces

auto lo
iface lo inet loopback
auto eth0
iface eth0 inet static
address X.X.X.X
netmask 255.255.0.0
gateway 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 OVERWRITTEN
nameserver 192.168.1.1
search 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 psql
create user "usr" with password 'usr' nocreatedb;
postgres=# create user "usr" with password 'usr' nocreatedb;
CREATE ROLE
postgres=# create user "usr" with password 'usr' nocreatedb;
ERROR:  role "usr" already exists
postgres=# create database  usr owner usr encoding 'UTF-8'
postgres-# ;
CREATE DATABASE
postgres=# \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 item
cur.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 table
print(rows)
for i in rows:
    print(i)
conn.commit()
cur.close()
conn.close()

至此数据库安装完毕。

3 安装psycopg2

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

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