Install PostgreSQL on CentOS7

Installation

if you want install latest version check official site

I will install postgres9.6 for CentOS7.

default version of postgres in CentOS7 rpm is not recommanded, I need some new features in postgres9.5+.

1
2
3
4
5
sudo yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
sudo yum install postgresql96-server postgresql96-contrib

# initialize the database
sudo postgresql96-setup initdb

Config for remote access

Set listen port to wildcard

1
2
# sudo vim /var/lib/pgsql/9.6/data/postgresql.conf
listen_addresses = '*'

Change HBA (host-based authentication) configuration

1
2
# sudo vim /var/lib/pgsql/9.6/data/pg_hba.conf
host all all 0.0.0.0/0 md5

Start server

1
2
3
4
5
sudo systemctl enable postgresql-9.6
sudo systemctl start postgresql-9.6

# check listen port
ss -ant | grep 5432

Login default role

1
2
3
4
5
sudo -i -u postgres
# to access PostgreSQL prompt
psql
# for quit PostgreSQL prompt
\q

Create a new role

1
createuser --interactive

My new role is rwuser

Create a new database

1
createdb test1

Login new role

1
2
3
4
5
# access PostgreSQL prompt with new role
sudo su -c "psql -d test1" - rwuser
# type following command in PostgreSQL prompt
\password
# then set your new password

You can use navicat to connect pgsql later.

Reference