Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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-infrastructureBasic Clients and Utilities for PostgreSQL
postgresql113PostgreSQL client programs and libraries
postgresql113-contribContributed source and binaries distributed with PostgreSQL
postgresql113-develPostgreSQL development header files and libraries
postgresql113-docsExtra documentation for PostgreSQL
postgresql113-libsThe shared libraries required for any PostgreSQL clients
postgresql113-plperlThe Perl procedural language for PostgreSQL
postgresql113-plpythonThe Python procedural language for PostgreSQL
postgresql113-pltclThe Tcl procedural language for PostgreSQL
postgresql113-serverThe programs needed to create and run a PostgreSQL server
postgresql113-testThe 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
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              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            trust
#host    replication     nijam          ::1/128                 trust
host    all             all               trust
host    all             all             trust
host    all             all             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  
log_filename = 'postgresql-%a.log'
log_directory = 'log'
log_rotation_age = 1d 
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


Popular posts from this blog

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Database startup / shutdown /restart

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger