PostgreSQL 설치, dump import

$ apt-get install postgresql
$ apt-get install postgresql-client
$ sudo -u postgres -i
$ psql templqte1
$ CREATE ROLE <username> superuser;
$ CREATE DATABASE <username>
$ ALTER ROLE <username> WITH LOGIN;
$ exit
$ pg_restore -d <username> <dump_name>

나는 Notebook 서버 -웹 서버- 와 DB서버를 나누고싶어서 외부접속 허용을 위해 configuration을 수정했다.

$ sudo -u postgres -i
$ cat >> /etc/postgresql/9.3/main/pg_hba.conf
host <host>/1 trust
^D
$ vi /etc/postgresql/9.3/main/postgresql.conf
:%s/#listen_addresses='localhost'/listen_addresses=<host>/g
:wq
$ service postgresql restart
$ exit

Python에서 접속

$ sudo apt-get install libpq-dev
$ pip install pygresql
$ ipython # python이어도 상관 없다(취향의 문제).
In [1]: import pgdb
In [2]: conn = pgdb.connect(database='<username>',host='<host>')
In [3]: df = pd.read_sql('SELECT <columns> FROM <tables>', conn)
In [4]: data = df['<column>'].values
In [8]: ^D # CTRL+D