PostgreSQL installation

censhare IT installs and updates all internal and customer PostgreSQL databases with Ansible and monitors them with Icinga. In some cases, an installation of unmanaged PostgreSQL databases for testing, or for a customer without Support contract is necessary. There are two options - either use the PostgreSQL packages with your OS distribution, or use the PostgreSQL RPM packages as described here.

CentOS 7 and PostgreSQL 9.6

Install PostgreSQL with the following command:

Replace the RPM version in the yum command according to latest rpm release!

ssh root@DB-Server
yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Then continue with the following command:

yum groupinstall "PostgreSQL Database Server 9.6 PGDG"
/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl enable postgresql-9.6
systemctl start postgresql-9.6
sed -i 's/gpgcheck=1/gpgcheck=1\'$'\nexclude=postgresql*/g' /etc/yum.repos.d/CentOS-Base.repo

CentOS 7 and PostgreSQL 10

Install PostgreSQL with the following command:

Replace the RPM version in the yum command according to latest rpm release!

ssh root@DB-Server
yum -y install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat10-10-2.noarch.rpm

Then continue with the following command:

yum -y groupinstall "PostgreSQL Database Server 10 PGDG"
/usr/pgsql-10/bin/postgresql-10-setup initdb
systemctl enable postgresql-10
systemctl start postgresql-10
sed -i 's/gpgcheck=1/gpgcheck=1\'$'\nexclude=postgresql*/g' /etc/yum.repos.d/CentOS-Base.repo

Installation directories

By default, Postgresql binary/software directory path is as below:

/usr/pgsql-9.6/bin

The Postgresql data directory path is as below:

/var/lib/pgsql/9.6/data/

The postgresql.conf and pg_hba.conf files are available in the /data directory and full path of these configuration files are as follows:

/var/lib/pgsql/9.6/data/

To perform and check different states of the PostgresSQL database (for example stop/start/restart/status from root user), use the following commands:

From root user:
systemctl status postgresql-9.6
systemctl stop postgresql-9.6
systemctl start postgresql-9.6
systemctl restart postgresql-9.6

For Postgresql-10 version, only change postgresql-9.6 as postgresql-10 in above command.

There is another option to check different states (for example stop/start/restart/status) from postgres user using the pg_ctl command:

From postgres user check the database status:
Goto <bin> directory to run ./pg_ctl

-bash-4.2$ cd /usr/pgsql-9.6/bin/
-bash-4.2$ ./pg_ctl status -D /var/lib/pgsql/9.6/data/

The command output is as follows:

pg_ctl: server is running (PID: 24594)
/usr/pgsql-9.6/bin/postgres "-D" "/var/lib/pgsql/9.6/data/"

Stop the database:

-bash-4.2$ cd /usr/pgsql-9.6/bin/
-bash-4.2$ ./pg_ctl stop -D /var/lib/pgsql/9.6/data/
waiting for server to shut down.... done
server stopped

Start the database:

-bash-4.2$ cd /usr/pgsql-9.6/bin/
-bash-4.2$ ./pg_ctl start -D /var/lib/pgsql/9.6/data/
server starting
-bash-4.2$ < 2017-11-30 02:38:19.525 EST > LOG: redirecting log output to logging collector process
< 2017-11-30 02:38:19.525 EST > HINT: Future log output will appear in directory "pg_log".

Re-start the database:

-bash-4.2$ cd /usr/pgsql-9.6/bin/
-bash-4.2$ ./pg_ctl restart -D /var/lib/pgsql/9.6/data/
waiting for server to shut down.... done
server stopped
server starting
-bash-4.2$ < 2018-04-11 10:48:33.555 IST > LOG: redirecting log output to logging collector process
< 2018-04-11 10:48:33.555 IST > HINT: Future log output will appear in directory “pg_log”.

If you are using Postgresql-10 version then /data path will be different. You can use (ps -ef| grep postgres) command to check what Postgres processes are running.

If you want to run postgres command from anywhere(any directory) from "postgres" user, edit the ".bash_profile" file which will be found in "postgres" home directory and add the following lines and "source" it:

PostgreSQL 9.6

[postgres@de-nue-sri ~]$ pwd
/var/lib/pgsql
-bash-4.2$ vi .bash_profile

export PATH=/usr/pgsql-9.6/bin:$PATH
export PGDATA=/var/lib/pgsql/9.6/data
export PGDATABASE=postgres
export PGUSER=postgresexport PGPORT=5432
export PGLOCALEDIR=/usr/pgsql-9.6/share/locale
export MANPATH=$MANPATH:/usr/pgsql-9.6/share/man

Save the file and source it:
-bash-4.2$ source .bash_profile

PostgreSQL 10

[postgres@de-nue-sri ~]$ pwd
/var/lib/pgsql
-bash-4.2$ vi .bash_profile

export PATH=/usr/pgsql-10/bin:$PATH
export PGDATA=/var/lib/pgsql/10/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/usr/pgsql-10/share/locale
export MANPATH=$MANPATH:/usr/pgsql-10/share/man

Save the file and source it: -bash-4.2$ source .bash_profile

Change parameters

After successful installation, change some parameters of "postgresql.conf" file. The default path is /var/lib/pgsql/9.6/data or /var/lib/pgsql/10/data.

Change the below parameters in postgresq.conf file by "Vi" editor and save it and restart the database. Duplicate the postgresql.file and rename it as original:

[postgres@de-nue-sri data]$ cp postgresql.conf postgresql.conf_original
[postgres@de-nue-sri data]$ vi postgresql.conf

listen_addresses = '*'
max_connections = 200

Save the "postgresql.conf" file and restart the database:

sri:~ postgres$ pg_ctl restart

waiting for server to shut down.... done
server stoppedserver starting
sri:~ postgres$ 2019-11-19 11:18:12 IST LOG: redirecting log output to logging collector process
2019-11-19 11:18:12 IST HINT: Future log output will appear in directory "pg_log"

After successful installation, kindly add the entry as below in pg_hba.conf file so that you can connect your application to your PostgreSQL database.

pg_hba.conf file is available in the below path for PostgreSQL 9.6 version.

/var/lib/pgsql/9.6/data

pg_hba.conf file is available in the below path for PostgreSQL 10.x version.

/var/lib/pgsql/10/data

Now, add the below entry with in the row of IPv4 in pg_hba.conf file from "postgres" user OR give your source IP like (host all all 172.16.12.51/32 md5). Kindly make the duplicate copy of the pg_hba.file and rename it 1 file as original:

[root@de-nue-sri ~]# su - postgres
-bash-4.2$cd /var/lib/pgsql/10/data
-bash-4.2$ vi pg_hba.conf

# IPv4 local connections:
host	all		all		0.0.0.0/0		md5

Also, change the "METHOD" type from "peer" & "ident" to "md5" or "trust" in everywhere for all the by default entries in pg_hba.conf file and reload it by the below command. There is no impact on running database, if we are reloading pg_hba.conf file.

-bash-4.2$ pg_ctl reload -D /var/lib/pgsql/10/data
server signaled
-bash-4.2$

If you are working on PostgreSQL 9.6 version then change the data directory path in the command above as follows:

-bash-4.2$ pg_ctl reload -D /var/lib/pgsql/9.6/data

Additional packages and post-configuration

# we also usually need these packages
yum install screen wget sysstat rsync tcpdump nmap less vim

# don't forget to check the Time and Timezone,

# if you have any troubles to connect:
- check (but just don't turn off) firewalld and SELinux
- check the content of file /var/lib/pgsql/*/data/pg_hba.conf
- check parameter listen_addresses in file /var/lib/pgsql/*/data/postgresql.conf

Configuring PostgreSQL for pretty good performance

Every PostgreSQL database has a default configuration but a few basic configuration changes can make it much faster while using fewer system resources. If aserver provides bad performance, you can change some important parameters in “postgresql.conf” file according to the server RAM to get the better DB performance. Some important parameters are as below:

• max_connections

• shared_buffers

• temp_buffers

• work_mem

• maintenance_work_mem

• effective_cache_size

• random_page_cost

• archive_mode

• archive_command

• archive_timeout

• synchronus_commit = off

  

  Change the value of "synchronus_commit" parameter to 'off' because by default it's value as 'on'.

• etc. (Depending on users/applications need and also to enhance the performance of the DB.)

For some of above parameters, a DB restart is required after parameter change. For an example settings, check the PgTune tool to set the exact value of these parameters.

If you use the PgTune tool for settings of PostgreSQL parameters, take care of one most important parameter i.e. “shared_buffers”. PgTune works on thumb rule of PostgreSQL and accordingly suggesting value to us. As per thumb rule of PostgreSQL “shared_buffers” should be 25% of the memory(RAM) in your system. But as per “Robert Haas (Core Member of PostgreSQL community)”, “shared_buffers” should not more than about 8GB on Linux or 512MB on Windows, and sometimes less. See the following blog for details: http://rhaas.blogspot.in/2012/03/tuning-sharedbuffers-and-walbuffers.html.

Example

If our server RAM is 64 GB, set “shared_buffers” maximum to 8 GB according to “Robert Haas” but do not set to 16 GB (25% of the total RAM) according to PgTune. 

The other values are OK for other parameters as per suggestion of PgTune Tool.