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.

postgresql-infrastructure	Basic Clients and Utilities for PostgreSQL
postgresql113	PostgreSQL client programs and libraries
postgresql113-contrib	Contributed source and binaries distributed with PostgreSQL
postgresql113-devel	PostgreSQL development header files and libraries
postgresql113-docs	Extra documentation for PostgreSQL
postgresql113-libs	The shared libraries required for any PostgreSQL clients
postgresql113-plperl	The Perl procedural language for PostgreSQL
postgresql113-plpython	The Python procedural language for PostgreSQL
postgresql113-pltcl	The Tcl procedural language for PostgreSQL
postgresql113-server	The programs needed to create and run a PostgreSQL server
postgresql113-test	The test suite distributed with PostgreSQL

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


Copy

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/


Copy

Step 3. Initialize the postgres cluster as user nijam.

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


Copy

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


Copy

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.


Copy

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'


Copy

Step 6. Startup the new cluster as user nijam .

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


Copy

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


Copy

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