PostgreSQL

October 5, 2013

{% include JB/setup %}

macOS

Install postgres

brew update
brew remove postgresql
brew install postgresql

Create a database

initdb /usr/local/var/postgres -E utf8

Serve that database

postgres -D /usr/local/var/postgres

To have launchd start postgresql at login

ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

Then to load postgresql now.

Option 1 (suggested)

sudo gem install lunchy
lunchy list
lunchy stop postgres
lunchy start postgres

Option 2

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

How to Install PostgreSQL on a Mac With Homebrew and Lunchy

CentOS

Configure your YUM repository

sudo cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak
sudo vim /etc/yum.repos.d/CentOS-Base.repo

Add the following line to /etc/yum.repos.d/CentOS-Base.repo, [base] and [updates] sections

exclude=postgresql*

Download and install PGDG RPM files

mkdir ~/bin/
cd ~/bin/
curl -O http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm

Now install RPM distribution

sudo rpm -ivh pgdg-centos93-9.3-1.noarch.rpm

Install PostgreSQL

sudo yum list postgres*
sudo yum install postgresql93-server

Initialize

sudo service postgresql-9.3 initdb

Startup

sudo chkconfig postgresql-9.3 on

Control service

sudo service postgresql-9.3 restart
su postgres

YUM Installation

Remote Access

Disable Firewall

Linux Firewall

Edit conf File

To be able to reach the server remotely

sudo cp /var/lib/pgsql/9.3/data/postgresql.conf /var/lib/pgsql/9.3/data/postgresql.conf.bak
sudo vim /var/lib/pgsql/9.3/data/postgresql.conf

Add the line listen_addresses = '*'

PostgreSQL, by default, refuses all connections it receives from any remote address. You have to relax these rules by adding this line to /var/lib/pgsql/9.3/data/pg_hba.conf

sudo cp /var/lib/pgsql/9.3/data/pg_hba.conf /var/lib/pgsql/9.3/data/pg_hba.conf.bak
sudo vim /var/lib/pgsql/9.3/data/pg_hba.conf

IMPORTANT: You must delete the origin corresponding lines, or your configure won't take effect!

If password is not needed

host all all  0.0.0.0/0 trust

If password is not needed

host all all  0.0.0.0/0 md5

IMPORTANT: You can't change user postgres' password through command passwd. Instead, it can be changed by SQL command in psql prompt

ALTER USER postgres with encrypted password 'your_password'

Configure Firewall

CentOS

sudo cp /etc/sysconfig/iptables /etc/sysconfig/iptables.bak
sudo vim /etc/sysconfig/iptables

Add these lines before -A INPUT -j REJECT line

-A INPUT -p tcp -s 0/0 --sport 1024:65535 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Enable Firewall

Linux Firewall

Control Service

sudo service postgresql-9.3 restart

Test

psql -h 192.168.0.81 -U postgres -d weibominer

How Do I Enable remote access to PostgreSQL database server?

Tuning

Tuning Your PostgreSQL Server

PostgreSQL Studio

Download pgstudio_*.tar.bz2 from PostgreSQL Studio

Uncompress and put the *.war under

CentOS Tomcat6

cp pgstudio.war /var/lib/tomcat6/webapps/

Add PostgreSQL JDBC Driver (Not a must because PostgreSQL Studio owns it)

Download from PostgreSQL JDBC Driver

Put the postgresql-*-jdbc*.jar under

CentOS Tomcat6

/usr/share/tomcat6/lib/

Start Tomcat

service tomcat6 restart

Browse in browser.

PostgreSQL Studio

Installation Guide

PgAdmin

Install from yum

yum search pgadmin
yum install pgadmin3_93

SQL

psql -l
psql postgres
\?

SET time_zone = "+08:00";
CREATE DATABASE weibominer;

\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
\c weibominer
\l+
\encoding [ENCODING]

DROP TABLE IF EXISTS sina_user_filter;

CREATE TABLE IF NOT EXISTS sina_user_filter
(uid SERIAL4, ui INT8 NOT NULL UNIQUE, un INT8 NOT NULL UNIQUE, sn TEXT, sx CHAR(1), ad TEXT, de TEXT, iu TEXT, an INT8, fn INT8, mn INT8, iv BOOLEAN, id INT2, vi TEXT, wt TIMESTAMP, tg TEXT, at TIMESTAMP, fui TEXT);

DROP TABLE IF EXISTS sina_friendship;

CREATE TABLE IF NOT EXISTS sina_friendship 
(fid SERIAL4, 
friend_uid INT8 NOT NULL, follower_uid INT8 NOT NULL, 
hit INT4 NOT NULL DEFAULT 0, crawl_time TIMESTAMP, 
PRIMARY KEY (fid), UNIQUE (friend_uid, follower_uid));

DROP TABLE IF EXISTS sina_user;

CREATE TABLE IF NOT EXISTS sina_user 
(uid SERIAL4, 
user_uid INT8 NOT NULL UNIQUE, 
screen_name TEXT, name TEXT, province INT2, 
city INT2, location TEXT, description TEXT, 
url TEXT, profile_image_url TEXT, 
domain TEXT, gender BOOLEAN, 
followers_count INT8, friends_count INT8, bi_followers_count INT8, 
statuses_count INT8, favourites_count INT8, 
group_count INT8, 
created_at TIMESTAMP, allow_all_act_msg BOOLEAN, 
remark TEXT, geo_enabled BOOLEAN, verified INT2, 
allow_all_comment BOOLEAN, verified_reason TEXT, 
online_status INT2, 
status_id INT8, 
friend_uid INT8, follower_uid INT8, rank INT2, 
visited_user INT4 NOT NULL DEFAULT 0, 
visited_friend INT8 NOT NULL DEFAULT 0, 
visited_follower INT8 NOT NULL DEFAULT 0, 
visited_follower_active INT8 NOT NULL DEFAULT 0, 
visited_bilateral INT8 NOT NULL DEFAULT 0, 
visited_timeline INT8 NOT NULL DEFAULT 0, 
visited_user_info INT8 NOT NULL DEFAULT 0, 
visited_microblog INT8 NOT NULL DEFAULT 0, 
visited_repost INT8 NOT NULL DEFAULT 0, 
hit INT8 NOT NULL DEFAULT 0, crawl_time TIMESTAMP, 
PRIMARY KEY (uid));

DROP TABLE IF EXISTS sina_status_info;

CREATE TABLE IF NOT EXISTS sina_status_info 
(sid SERIAL4, 
status_id INT8 UNIQUE, 
status_mid TEXT UNIQUE, 
source_url TEXT, source_rel TEXT, source_name TEXT, url TEXT, created_at TIMESTAMP, 
previous_id INT8, origin_id INT8, 
previous_mid TEXT, origin_mid TEXT, 
previous_idstr TEXT, origin_idstr TEXT, 
repost_count INT4 NOT NULL DEFAULT 0, 
comment_count INT4 NOT NULL DEFAULT 0, 
attitude_count INT4 NOT NULL DEFAULT 0, 
user_uid INT8 NOT NULL, 
retweeted_status_id INT8, 
visited_query_mid INT4 NOT NULL DEFAULT 0, 
visited_query_id INT4 NOT NULL DEFAULT 0, 
hit INT4 NOT NULL DEFAULT 0, crawl_time TIMESTAMP, 
PRIMARY KEY (sid));

DROP TABLE IF EXISTS sina_status_detail;

CREATE TABLE IF NOT EXISTS sina_status_detail 
(sid SERIAL4, 
status_id INT8 UNIQUE, 
text TEXT, 
hit INT4 NOT NULL DEFAULT 0, crawl_time TIMESTAMP, 
PRIMARY KEY (sid));

\dt+

UPDATE sina_user SET visited_friendship=0;
UPDATE sina_user SET visited_follower=0;
UPDATE sina_user SET visited_follower_active=0;
UPDATE sina_user SET visited_bilateral=0;

DELETE FROM sina_user *;
SELECT NOW(); SELECT COUNT(uid) FROM sina_user; SELECT COUNT(fid) FROM sina_friendship; SELECT COUNT(uid) FROM sina_user WHERE visited_friend>0; SELECT COUNT(uid) FROM sina_user WHERE visited_follower>0; SELECT COUNT(sid) FROM sina_status_info; SELECT COUNT(sid) FROM sina_status_detail;
INSERT INTO test1 VALUES ('ok');
INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation

\q