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

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools

7 Steps to configure BDR replication in postgresql