PostgreSQL also called Postgres is a powerful and open source object-relational database system. It is an enterprise level database having features such as write ahead logging for fault tolerance, asynchronous replication, Multi-Version Concurrency Control (MVCC),online/hot backups, point in time recovery, query planner/optimizer, tablespaces, nested transactions (savepoints) etc.

Postgres has its latest version 10 released on 5th Oct 2017 by postgres global development group.

PostgreSQL Features

Features of New version are as follows:

Logical Replication : This feature enables replication of individual database objects (be it rows, tables, or selective databases) across standby servers. It provides more control over data replication. Implemented by using publisher-subscriber model.

: This feature enables replication of individual database objects (be it rows, tables, or selective databases) across standby servers. It provides more control over data replication. Implemented by using publisher-subscriber model. Quorum Commit for Synchronous Replication : In this feature, dba can now specify the number of standby’s that acknowledge that the changes to database has done, so that data can be considered safely written.

: In this feature, dba can now specify the number of standby’s that acknowledge that the changes to database has done, so that data can be considered safely written. SCRAM-SHA-256 authentication : Improved security that existing MD5-based password authentication and storage.

: Improved security that existing MD5-based password authentication and storage. Improved parallel query execution.

Declarative table partitioning.

Full text search support for JSON and JSONB.

In this article, we will explain how to install PostgreSQL 10 using source code installation in Linux systems. Those who looking for easy installation from distribution package manager they can follow these below guides.

Install PostgreSQL Using Source Code

As postgres is open source database, it can be built from source code according to one’s needs/requirements. we can customize the build and installation process by supplying one or more command line options for various additional features.

Major advantage of using source code installation is it can be highly customized during installation.

1. First install required prerequisites such as gcc, readline-devel and zlib-devel using package manager as shown.

# yum install gcc zlib-devel readline-devel [On RHEL/CentOS] # apt install gcc zlib1g-dev libreadline6-dev [On Debian/Ubuntu]

2. Download the source code tar file from the official postgres website using the following wget command directly on system.

# wget https://ftp.postgresql.org/pub/source/v10.0/postgresql-10.0.tar.bz2

3. Use tar command to extract the downloaded tarball file. New directory named postgresql-10.0 will be created.

# tar -xvf postgresql-10.0.tar.bz2 # ll

Sample Output

total 19236 -rw-------. 1 root root 933 Mar 18 2015 anaconda-ks.cfg -rw-r--r--. 1 root root 8823 Mar 18 2015 install.log -rw-r--r--. 1 root root 3384 Mar 18 2015 install.log.syslog drwxrwxrwx 6 1107 1107 4096 Oct 3 2017 postgresql-10.0 -rw-r--r-- 1 root root 19639147 Oct 3 2017 postgresql-10.0.tar.bz2

4. Next step for installation procedure is to configure the downloaded source code by choosing the options according to your needs.

# cd postgresql-10.0

use ./configure --help to get help about various options.

Sample Output

# ./configure --help Defaults for the options are specified in brackets. Configuration: -h, --help display this help and exit --help=short display options specific to this package --help=recursive display the short help of all the included packages -V, --version display version information and exit -q, --quiet, --silent do not print `checking ...' messages --cache-file=FILE cache test results in FILE [disabled] -C, --config-cache alias for `--cache-file=config.cache' -n, --no-create do not create output files --srcdir=DIR find the sources in DIR [configure dir or `..'] Installation directories: --prefix=PREFIX install architecture-independent files in PREFIX [/usr/local/pgsql] --exec-prefix=EPREFIX install architecture-dependent files in EPREFIX [PREFIX]

5. Now create a directory where you want to install postgres files and use prefix option with configure.

# mkdir /opt/PostgreSQL-10/ # ./configure --prefix=/opt/PostgreSQL-10

Sample Output

checking build system type... x86_64-pc-linux-gnu checking host system type... x86_64-pc-linux-gnu checking which template to use... linux checking whether NLS is wanted... no checking for default port number... 5432 checking for block size... 8kB checking for segment size... 1GB checking for WAL block size... 8kB checking for WAL segment size... 16MB checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking whether gcc supports -Wdeclaration-after-statement... yes checking whether gcc supports -Wendif-labels... yes checking whether gcc supports -Wmissing-format-attribute... yes checking whether gcc supports -Wformat-security... yes checking whether gcc supports -fno-strict-aliasing... yes checking whether gcc supports -fwrapv... yes checking whether gcc supports -fexcess-precision=standard... no ....

6. After configuring, next we will start to build postgreSQL using following make command.

# make

After build process finishes, now install postgresql using following command.

# make install

Postgresql 10 has been installed in /opt/PostgreSQL-10 directory.

7. Now create a postgres user and directory to be used as data directory for initializing database cluster. Owner of this data directory should be postgres user and permissions should be 700 and also set path for postgresql binaries for our ease.

# useradd postgres # passwd postgres # mkdir /pgdatabase/data # chown -R postgres. /pgdatabase/data # echo 'export PATH=$PATH:/opt/PostgreSQL-10/bin' > /etc/profile.d/postgres.sh

8. Now initialize database using the following command as postgres user before using any postgres commands.

# su postgres $ initdb -D /pgdatabase/data/ -U postgres -W

Where -D is location for this database cluster or we can say it is data directory where we want to initialize database cluster, -U for database superuser name and -W for password prompt for db superuser.

For more info and options we can refer initdb –help.

9. After initializing database, start the database cluster or if you need to change port or listen address for server, edit the postgresql.conf file in data directory of database server.

$ pg_ctl -D /pgdatabase/data/ -l /pglog/db_logs/start.log start

10. After starting database, verify the status of postgres server process by using following commands.

$ ps -ef |grep -i postgres $ netstat -apn |grep -i 51751

We can see that database cluster is running fine, and startup logs can be found at location specified with -l option while starting database cluster.

11. Now connect to database cluster and create database by using following commands.

$ psql -p 51751 postgres=# create database test; postgres=# \l to list all databases in cluster postgres=# \q to quit form postgres console

That’s It! in our upcoming articles, I will cover configuration, replication setup and installation of pgAdmin tool, till then stay tuned to Tecmint.