PostgreSql Installation document Step by Step

Postgres Server Installation Document

This document discusses detail installation process of PostgreSQL on Linux.PostgreSQL is available integrated with the package management on most Linux platforms. When available, this is the recommended way to install PostgreSQL, since it provides proper integration with the operating system, including automatic patching and other management functionality.

By default, Postgres uses a concept called "roles" to aid in authentication and authorization. These are, in some ways, similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term "role".

Upon installation Postgres is set up to use "ident" authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a Postgres role exists, it can be signed in by logging into the associated Linux system account.

The installation procedure created a user account called postgres that is associated with the default Postgres role.

1-Copy the tar file postgres.tar and extract it to Master & Slave Server by root user .

$tar -xvf postgres.tar

$chown postgres:postgres postgres

2- SSH Authentication

2-(i)-In Master Server:-

ssh-keygen -t rsa

ssh-keygen -t dsa

$cd /root/.ssh

$ cat *.pub >>authorized_keys.node1

2(ii)-In Slave server:-

$ ssh-keygen -t rsa

$ ssh-keygen -t dsa

$ cd /root/.ssh

$ cat *.pub >>authorized_keys.node2

3-

3(i)-In Master server:-

$ cd /root/.ssh

$ scp authorized_keys.node1 root@Slave_IP:/root/.ssh/

3(ii)-In Slave server:-

$ cd /root/.ssh

$ scp authorized_keys.node2 root@Master_IP:/root/.ssh/

4-In Master server and Slave server :-

cat authorized_keys.node* >> authorized_keys

sudo chmod 600 /root/.ssh/authorized_keys

5-Both Master and Slave

$ mkdir –p /var/lib/pgsql

$ groupadd -r -g 26 postgres

$ useradd -r -u 26 -M -d /var/lib/pgsql -n -g postgres postgres

$ passwd postgres

6-Both Master and Slave by root user

scp -r /root/.ssh Master_IP:/var/lib/pgsql // run in slave

scp -r /root/.ssh Slave_IP:/var/lib/pgsql // run in Master

chown -R postgres:postgres /var/lib/pgsql

restorecon -R /var/lib/pgsql/Both Master and Slave by root user

7-run the following rpm for Installation

$cd /postgres

$ rpm -ivh postgresql95-libs-9.5.3-2PGDG.rhel7.x86_64.rpm

$ rpm -ivh postgresql95-9.5.3-2PGDG.rhel7.x86_64.rpm

$ rpm -ivh postgresql95-server-9.5.3-2PGDG.rhel7.x86_64.rpm

$ rpm -ivh postgresql95-contrib-9.5.3-2PGDG.rhel7.x86_64.rpm

$ rpm -ivh repmgr95-3.1.3-1.rhel7.x86_64.rpm

8-Initilize the postgresql by given command

/usr/pgsql-9.5/bin/postgresql95-setup initdb

9-Master Server Replication

Login to Master server

$ cd /var/lib/pgsql/9.5/data

$ mkdir -p archive

$ chown -R postgres:postgres archive

10-Edit the pg_hba.conf file & add the following entry

host repmgr repmgr 10.32.12.131/24 trust

host replication repmgr 10.32.12.131/24 trust

host repmgr repmgr 10.32.12.88/24 trust

host replication repmgr 10.32.12.88/24 trust

Note: Add all slave server ip

11-Edit the postgresql.conf file & add the following entry

listen_addresses = '*'

wal_level = hot_standby

archive_mode = on

archive_command = 'test ! -f /var/lib/pgsql/9.5/data/archive/%f && cp %p /var/lib/pgsql/9.5/data/archive/%f'

max_wal_senders = 2 (Note: this parameter depends on the numbers of server machines , if total nos of server machine is 5 then parameter value will be 5)

wal_keep_segments = 5000

hot_standby = on

wal_log_hints = on

max_replication_slots = 5

shared_preload_libraries = 'repmgr_funcs'

12-Execute commands in Master

$ cd /var/lib/pgsql

$ su - postgres -c "mkdir -p /var/lib/pgsql/repmgr/"

$ vi repmgr.conf

Add the following entry

cluster=db_cluster

node=1

node_name=aglinux54 (Note: Master server's hostname)

conninfo='host=10.32.12.131 user=repmgr dbname=repmgr' (Note: Master server's IP)

pg_bindir=/usr/pgsql-9.5/bin/master_response_timeout=5

reconnect_attempts=2

reconnect_interval=2

failover=automatic

promote_command='/usr/pgsql-9.5/bin/repmgr standby promote -f /var/lib/pgsql/repmgr/repmgr.conf'

follow_command='/usr/pgsql-9.5/bin/repmgr standby follow -f /var/lib/pgsql/repmgr/repmgr.conf'

Change owner

13-Change the permission to postgres

$ chown postgres:postgres /var/lib/pgsql/repmgr/repmgr.conf

14-check the status of the firewalld by using given command

systemctl status firewalld // If enable then stop it.

15-Enable and Start PostgreSQL on the master by root user:

systemctl enable postgresql-9.5

systemctl start postgresql-9.5

16-Create the required users for replication and repmgr and the repmgr DB

$ sudo su – postgres

psql

CREATE ROLE pgpool SUPERUSER CREATEDB CREATEROLE INHERIT REPLICATION LOGIN

ENCRYPTED PASSWORD 'secret';

CREATE USER repmgr SUPERUSER LOGIN ENCRYPTED PASSWORD 'secret';

CREATE DATABASE repmgr OWNER repmgr;

\q

psql -U repmgr -h 10.32.12.131 < /usr/pgsql-9.5/share/contrib/repmgr.sql

psql -U repmgr -h 10.32.12.131 < /usr/pgsql-9.5/share/contrib/repmgr_funcs.sql

17-The below step is to register 10.32.12.131 as master node for repmgr:

$ sudo su - postgres

$ /usr/pgsql-9.5/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf master register

18-When all packages are installed, we can sync the configuration and contents of the primary

with the standby:(Note: Repeat these steps for all Slave server) .

19-login to Slave Server

$ su - postgres

$ /usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ -l logfile status --->check the status if running

then stop using below command

$ /usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ -l logfile stop

20-To clone data from Master to Slave

$ rm -rf /var/lib/pgsql/9.5/data/*

$ /usr/pgsql-9.5/bin/repmgr -D /var/lib/pgsql/9.5/data -d repmgr -p 5432 -U repmgr -R postgres standby clone 10.32.12.131

$ su - postgres -c "mkdir -p /var/lib/pgsql/repmgr/"

$ cd /var/lib/pgsql/repmgr/

$ vi repmgr.conf

Add the following entry

cluster=db_cluster

node=2 (Note: this parameter depends on the numbers of server machines , if total nos of server

machine is 5then parameter value will be 5)

node_name=aglinux49_3 (Note: put the Slvae server's hostname in this parameter)

conninfo='host=10.32.12.88 user=repmgr dbname=repmgr' (Note: pass the Slave server's ip)

pg_bindir=/usr/pgsql-9.5/bin/

master_response_timeout=5

reconnect_attempts=2

reconnect_interval=2

failover=automatic

promote_command='/usr/pgsql-9.5/bin/repmgr standby promote -f /var/lib/pgsql/repmgr/repmgr.conf'

follow_command='/usr/pgsql-9.5/bin/repmgr standby follow -f /var/lib/pgsql/repmgr/repmgr.conf'

21- Change permission to postgres

$ chown postgres:postgres /var/lib/pgsql/repmgr/repmgr.conf

22- check the status of the firewalld by using given command (Note: by root user)

systemctl status firewalld // If enable then stop it.

23-Enable and Start PostgreSQL on the standby: (Note: by root user)

systemctl enable postgresql-9.5

systemctl start postgresql-9.5

24-Register the Slave servers in repmgr:

$ su - postgres

$ /usr/pgsql-9.5/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby register

25- Test the replication on all Slave servers

$ /usr/pgsql-9.5/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show

26-Before we create the database on the primary, let’s list the databases from the standby-node:

on Slave Servers

$ psql -U postgres -c "\list"

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

repmgr | repmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

Create the database on the primary:

$ psql -U postgres -c "CREATE DATABASE test"

27-Login to the Slave Servers & check

$ psql -U postgres -c "\list"

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

repmgr | repmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| | | | | postgres=CTc/postgres

test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

(5 rows)

28-To check the role of the Server either Master or Slave

$ /usr/pgsql-9.5/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf cluster show

29-To Start the Daemon

To start daemon on Master server

/usr/pgsql-9.5/bin/repmgrd -d -f /var/lib/pgsql/repmgr/repmgr.conf --verbose –monitoring-history >>/tmp/repsetup.log 2>&1

Connect to Postgres with the New User

Let's assume that you have a Linux system account called test1 (you can create one by typing: adduser test1), and that you have created a Postgres role and database also called test1.

sudo
-i -u test1

You can then connect to the test1 database as the test1 Postgres role by typing:

psql

Configure and initializing PostgreSQL Server

$ sudo /etc/init.d/postgresql-9x start

$ sudo /etc/init.d/postgresql-9x start PostgreSQL 9.x Configuration

---------------------------------

This will initialize PostgreSQL database cluster. The following

questions will determine the database server port, superuser password and

to start server on system boot.Press to accept defaults.
Specify PostgreSQL server port [5432]:
Specify superuser password [password]:
Do you want PostgreSQL server to be started on boot (y/n) [y]:
useradd: warning: the home directory already exists.
Not copying any file from skel directory into it.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale en_US.UTF-8.
The default text search configuration will be set to "english".
fixing permissions on existing directory /opt/postgresql/pg9x/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok c
reating template1 database in /opt/postgresql/pg9x/data/base/1 ... ok
initializing pg_authid ... ok
setting password ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
Success. You can now start the database server using:
/opt/postgresql/pg9x/bin/postgres -D /opt/postgresql/pg9x/data
or
/opt/postgresql/pg9x/bin/pg_ctl -D /opt/postgresql/pg9x/data -l logfile start
Configuring PostgreSQL server startup scripts
Starting PostgreSQL 9.x:
waiting for server to start.... done
server started
PostgreSQL 9.x started successfully
To load this postgres into your environment, source the env file:
. /opt/postgresql/pg9x/pg9x.env

Default database

The default database created at initialization time is:postgres

Default username

The default superuser created at initialization time is:postgres

Default password

The default password (unless you set it at initialization time) is:password

Change the default password

To change the default password, you first need to login to the database with your current password:

$ /opt/postgresql/pg9x/bin/psql -U postgres -d postgres

Password:

psql (9.x)

Type "help" for help.

postgres=#

Now, change the password for the postgres user:

postgres=# ALTER USER postgres WITH PASSWORD 'newpasswordgoeshere';

ALTER ROLE

postgres=#