PostgreSQL 11 Installation – RPM

In this tutorial, we will show you how to install PostgreSQL on your local system for learning and practicing PostgreSQL.

PostgreSQL was developed for UNIX-like platforms, however, it was designed to be portable. It means that PostgreSQL can also run on other platforms such as Mac OS X, Solaris, and Windows.

Since version 8.0, PostgreSQL offers an installer for Windows systems that makes the installation process easier and faster. For development purpose, we will install PostgreSQL version 11.3  on Linux

There are three steps to complete the PostgreSQL installation:

1. Download the PostgreSQL RPM.
2. Install the PostgreSQL RPM.
3. Verify the postgres installation.

Step 1.  Download and install the below rpm’s  in postgres server.

rpm -ivh postgresql-infrastructure-1-6.noarch.rpm
rpm -ivh postgresql113-libs-11.3-1.x86_64.rpm
rpm -ivh postgresql113-server-11.3-1.x86_64.rpm
rpm -ivh postgresql113-contrib-11.3-1.x86_64.rp
rpm -ivh postgresql113-devel-11.3-1.x86_64.rpm
rpm -ivh postgresql113-docs-11.3-1.x86_64.rpm
rpm -ivh postgresql113-plperl-11.3-1.x86_64.rpm
rpm -ivh postgresql113-plpython-11.3-1.x86_64.rpm
rpm -ivh postgresql113-pltcl-11.3-1.x86_64.rpm
rpm -ivh postgresql113-server-11.3-1.x86_64.rpm
rpm -ivh postgresql113-test-11.3-1.x86_64.rpm

For postgres version 1o installation :

rpm -ivh postgresql1010-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-contrib-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-contrib-debuginfo-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-debuginfo-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-debugsource-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-devel-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-devel-debuginfo-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-docs-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-libs-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-libs-debuginfo-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-pglogical-2.2.2-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-pglogical-debuginfo-2.2.2-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-pglogical-debugsource-2.2.2-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-plperl-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-plperl-debuginfo-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-plpython-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-plpython-debuginfo-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-pltcl-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-pltcl-debuginfo-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-server-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-server-debuginfo-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-test-10.10-1.x86_64.rpm --nodeps
rpm -ivh postgresql1010-test-debuginfo-10.10-1.x86_64.rpm --nodeps

Step 2. Now create a linux  user to access the postgres database  and create  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 nijam
# passwd nijam
# mkdir -p /data_11.3/
# chown -R nijam. /data_11.3/

Step 3. Initialize the postgres cluster as user nijam.

$ /opt/11.3/bin/initdb -D /data_11.3/ -U nijam

->>> 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.

Step 4. put the Web/Apps details on pg_ha.conf file.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32           trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     nijam                                  trust
#host    replication     nijam          127.0.0.1/32            trust
#host    replication     nijam          ::1/128                 trust
host    all             all             127.0.0.1/32            trust
host    all             all             54.99.18.56/32          trust
host    all             all             54.99.17.86/32          trust

Step 5 . Allocate the  memory and connection as per the  CPU and RAM.

For Example if you have 100 GB  RAM.

1. Max_connection =1000×16mb(work_mem)=16 GB
2. Effective_cache_size=40gb optimized execution path.the query planner also requires some space 
3. Shared_buffer=25GB,
4.Wall_buffer=3% of shared buffer,if lot of insert, update, delete set maximum 8mb
5.Maintenance_work_mem=4GB to 6GB(RAM/8) for vacuum,create,alter, reindex, backup/restoring dump.
6.Temp_buffer=8mb default session-local buffers used only for access to temporary tables.

Including this change the below parameter :

$ cat postgresql.conf
listen_addresses = '*'         
port = 5420  
logging_collector=on
log_filename = 'postgresql-%a.log'
log_directory = 'log'
log_rotation_age = 1d 
archive_mode=on
archive_command = 'gzip < %p > /srv/postgresql/var/inst1/log/archive_logs/%f'

Step 6. Startup the new cluster as user nijam .

/opt/11.3/bin/pg_ctl -D /data_11.3/ restart

Step 7. Set the bash profile to access the postgres utility on user nijam home path

$ cat /home/nijam/.profile
export PATH=/opt/11.3/bin:$PATH
export PGHOME=/opt/11.3/
export PGDATA=/data_11.3/
export LD_LIBRARY_PATH=/opt/11.3/lib
export PGDATABASE=nijam
export PGUSER=nijam
export PGPORT=5420
export PGLOCALEDIR=/opt/11.3/share/locale
export MANPATH=$MANPATH:/opt/11.3/share/man

Step 8. Run the bash profile and try to  connect the database .

$ ..profile
$ psql -p 5420
postgres=# \l --- to list all databases in cluster
postgres=# \q --- to quit form postgres console