Setup PostgreSQL on Linux

Install from source

The current latest release version is 9.5.4.

# get and install postgres from source
$ cd $YOUR_WORKING_PATH
$ curl https://ftp.postgresql.org/pub/source/v9.5.4/postgresql-9.5.4.tar.gz > postgresql-9.5.4.tar.gz
$ tar xzf postgresql-9.5.4.tar.gz
$ cd postgresql-9.5.4
$ ./configure # default --prefix=/usr/local/pgsql
$ make

$ sudo make install
$ sudo adduser postgres
$ sudo passwd postgres
$ sudo mkdir /usr/local/pgsql/data
$ sudo chown postgres /usr/local/pgsql/data

$ su - postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data # initialize database cluster

Configure Access from network

By default, postgres allows connection only from localhost. To allow clients connect over network, host entries need to be added in /usr/local/pgsql/data/pg_hba.conf, such as

host all all 192.168.0.0/16 md5

allows all users from 192.168.x.x to access all database, using md5 to hash their password. See more details in the manual 18.3 Connection and Authentication

Auto start when system boots

Although 17.3. Starting the Database Server in the manual gives the solution for old-style /etc/rc.d, modern linux distributions use systemd, here is the solution

add postgresql.service

[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

# ... but allow it still to be effective for child processes
# (note that these settings are ignored by Postgres releases before 9.5)
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

# Maximum number of seconds pg_ctl will wait for postgres to start.  Note that
# PGSTARTTIMEOUT should be less than TimeoutSec value.
Environment=PGSTARTTIMEOUT=270
Environment=PGDATA=/usr/local/pgsql/data

ExecStart=/usr/local/pgsql/bin/pg_ctl start -o "-h '*'" -D ${PGDATA} -s -w -t ${PGSTARTTIMEOUT}
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s

# Give a reasonable amount of time for the server to start up/shut down.
# Ideally, the timeout for starting PostgreSQL server should be handled more
# nicely by pg_ctl in ExecStart, so keep its timeout smaller than this value.
TimeoutSec=300

[Install]
WantedBy=multi-user.target

as /usr/lib/systemd/system/postgresql.service, then run following commands

sudo systemctl daemon-reload
sudo systemctl enable postgresql
sudo systemctl start postgresql

After that you can connect the postgresql cluster as

/usr/local/pgsql/bin/psql -U postgres -h hostname
postgres=# create role myaccount login password='my passowrd';
postgres=# create database mydb;
postgres=# alter database mydb owner to myaccount;

 

Reference

 

Advertisements