postgresql

install postgresql 9.5 on ubuntu14.04

1
2
3
4
sudo sh -c ‘echo “deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main” >> /etc/apt/sources.list.d/pgdg.list’
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O – | sudo apt-key add –
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

login and create table

sudo su – postgres
psql

1
2
3
4
5
6
7
8
\conninfo
create table test (name char(12));
select * from test;
insert into test select 'aaa';
insert into test select 'aaabbb';
insert into test select 'aaabbbccc';
select * from test;
\q

exit

install pgadmin3

sudo apt-get install pgadmin3

instal postgresql 9.5 via rpm on CentOS/RHEL/Fendora

CentOS/RHEL 7
rpm -Uvh http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm

CentOS/RHEL 6
rpm -Uvh http://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/pgdg-redhat95-9.5-2.noarch.rpm

CentOS/RHEL 5
rpm -Uvh http://yum.postgresql.org/9.5/redhat/rhel-5-x86_64/pgdg-redhat95-9.5-2.noarch.rpm

Fedora 23
rpm -Uvh http://yum.postgresql.org/9.5/fedora/fedora-23-x86_64/pgdg-fedora95-9.5-3.noarch.rpm

Fedora 22
rpm -Uvh http://yum.postgresql.org/9.5/fedora/fedora-22-x86_64/pgdg-fedora95-9.5-3.noarch.rpm

Fedora 21
rpm -Uvh http://yum.postgresql.org/9.5/fedora/fedora-21-x86_64/pgdg-fedora95-9.5-2.noarch.rpm

yum install postgresql95-server postgresql95
/usr/pgsql-9.5/bin/postgresql95-setup initdb

start/enable

CentOS/RHEL 7 and Fedora 23
systemctl start postgresql-9.5
systemctl enable postgresql-9.5

CentOS/RHEL 6/5 and Fedora 22/21
service postgresql-9.5 start
chkconfig postgresql-9.5 on

login

su - postgres
psql

password

1
\password postgres

sudo su postgres -c psql postgres

1
2
alter user postgres with password 'assignednewpassword';
\q

sudo passwd -d postgres
sudo su postgres -c passwd

configure postgresql

sudo vi /etc/postgresql/9.0/main/postgresql.conf

1
2
3
4
#listen_addresses = ‘localhost’
listen_addresses = ‘*’
#password_encryption = on
password_encryption = on

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
#"local" is for Unix domain socket connections only
#開放本地端同資料庫使用者帳號的 linux 使用者可以不用密碼從本機登入系統
local   all         all                               ident sameuser
#IPv4 local connections: IPV4本地端
host    all         all         127.0.0.1/32          ident sameuser
#IPv6 local connections: IPV6本地端
host    all         all         ::1/128               ident sameuser

#IPv4 local connections:
#可以透過 tcp/ip 從 127.0.0.1/32 及 110.111.69.0/24 登入
host    all         all         127.0.0.1/32          md5
host    all         all         110.111.69.0/24       md5

#本地端的使用者使用 Unix domain socket及 md5 密碼登入 
local   all         all                               md5

create user and database

1
2
3
4
5
create user testuser with password 'password4testuser';
alter role testuser with superuser;
create database testdb
GRANT ALL PRIVILEGES ON DATABASE testdatabase to testuser;
\q

check alive on socket

netstat -an | grep 5432