== DRAFT ==

Installation

1
2
3
4
5
6
7
8
9
10

# echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
# apt-get install wget ca-certificates
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
# apt-get update
# apt-get install postgresql
# echo "export PATH=$PATH:/usr/lib/postgresql/9.5/bin" >> /etc/profile
# apt-get install rsync
# apt-get install repmgr
# su - postgres
# ssh-keygen

Configuration

On echange les clefs des users postgres entre les differents serveurs

• Set des autorisations postgres

1
2
3
4

# tail -3 /etc/postgresql/9.5/main/pg_hba.conf 
local   repmgr        repmgr                              trust
host    repmgr        repmgr      127.0.0.1/32            trust
host    repmgr        repmgr      10.0.69.0/24            trust

• Conf Postgres

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

# diff /etc/postgresql/9.5/main/postgresql.conf /tmp/postgresql.conf.ori 
59c59
< listen_addresses = '*'                # what IP address(es) to listen on;
---
> #listen_addresses = 'localhost'               # what IP address(es) to listen on;
143c143
< shared_preload_libraries = 'repmgr_funcs'             # (change requires restart)
---
> #shared_preload_libraries = ''                # (change requires restart)
171c171
< wal_level = hot_standby               # minimal, archive, hot_standby, or logical
---
> #wal_level = minimal                  # minimal, archive, hot_standby, or logical
204c204
< archive_mode = on             # enables archiving; off, on, or always
---
> #archive_mode = off           # enables archiving; off, on, or always
206c206
< archive_command = '/bin/true'         # command to use to archive a logfile segment
---
> #archive_command = ''         # command to use to archive a logfile segment
222c222
< max_wal_senders = 10          # max number of walsender processes
---
> #max_wal_senders = 0          # max number of walsender processes
224c224
< wal_keep_segments = 5000              # in logfile segments, 16MB each; 0 disables
---
> #wal_keep_segments = 0                # in logfile segments, 16MB each; 0 disables
245c245
< hot_standby = on                      # "on" allows queries during recovery
---
> #hot_standby = off                    # "on" allows queries during recovery

• Conf Repmgr

toute les commande repmgr sont a lancer en postgres

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# cat /etc/repmgr.conf 
cluster=jaytest
node=2
node_name=node2
conninfo='host=10.0.69.110 user=repmgr dbname=repmgr'
pg_basebackup_options='--xlog-method=fetch'
pg_bindir=/usr/lib/postgresql/9.5/bin/
#upstream_node=1
#priority=100

monitor_interval_secs=2
master_response_timeout=60
#reconnect_attempts=6
#reconnect_interval=10

failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr.conf'
follow_command='repmgr standby follow -f /etc/repmgr.conf'

logfile='/var/log/postgresql/repmgrd.log'

1
2

$ createuser -s repmgr
$ createdb repmgr -O repmgr

Replication

• Initialise the master server

1

repmgr -f /etc/repmgr.conf primary register

On check

1
2

=# \c repmgr
=# SELECT * FROM repmgr_jaytest.repl_nodes;

• Clone the standby server (stop postgresql ; purge /var/lib/postgresql/9.5/main/ first)

1
2
3
4

repmgr -h node1 -U repmgr -d repmgr -D /var/lib/postgresql/9.5/main/ -f /etc/repmgr.conf standby clone

# rsync node1:/etc/postgresql/9.5/main/pg_hba.conf /etc/postgresql/9.5/main/
# rsync node1:/etc/postgresql/9.5/main/postgresql.conf  /etc/postgresql/9.5/main/

On check (depuis le master)

1

=# SELECT * FROM pg_stat_replication;

• Register the standby

1

repmgr -f /etc/repmgr.conf standby register

On check

1
2

=# \c repmgr
=# SELECT * FROM repmgr_jaytest.repl_nodes ORDER BY id;

Recovery

node1 est dead

1
2
3
4
5
6

$ repmgr cluster show
Role      | Name  | Upstream | Connection String
----------+-------|----------|-------------------------------------------
  standby | node3 | node2    | host=10.0.69.110 user=repmgr dbname=repmgr
  FAILED  | node1 |          | host=10.0.69.108 user=repmgr dbname=repmgr
* master  | node2 |          | host=10.0.69.109 user=repmgr dbname=repmgr

on stop ses services

1

# /etc/init.d/postgresql stop

on resync & register

1
2
3

# repmgr -h node3 -U repmgr -d repmgr -D /var/lib/postgresql/9.5/main/ -f /etc/repmgr.conf standby clone

# repmgr -f /etc/repmgr.conf standby register -F

node1 is back

1
2
3
4
5
6

$ repmgr cluster show
Role      | Name  | Upstream | Connection String
----------+-------|----------|-------------------------------------------
  standby | node3 | node2    | host=10.0.69.110 user=repmgr dbname=repmgr
* master  | node2 |          | host=10.0.69.109 user=repmgr dbname=repmgr
  standby | node1 | node2    | host=10.0.69.108 user=repmgr dbname=repmgr

Lancement de repmgrd

• pour quand le script d’init marchera :

  1 2 3 4

  # sed -i 's/REPMGRD_ENABLED=no/REPMGRD_ENABLED=yes/' /etc/default/repmgrd # sed -i 's/\#REPMGRD_CONF\=\"\/path\/to\/repmgr.conf\"/REPMGRD_CONF\=\"\/etc\/repmgr.conf\"/' /etc/default/repmgrd # sed -i 's/\#REPMGRD_OPTS=\"\"/REPMGRD_OPTS="-m -d"/' /etc/default/repmgrd # sed -i 's/\#REPMGR_PIDFILE\=\/var\/run\/repmgrd.pid/REPMGR_PIDFILE\=\/var\/run\/postgresql\/repmgrd.pid/' /etc/default/repmgrd

• mon script d’init de remplacement :

  1

  # cat /etc/init.d/repmgrd.init #!/bin/bash ### BEGIN INIT INFO # Provides: repmgrd # Required-Start: $remote_fs $all # Should-Start: # Required-Stop: # Default-Start: 2 3 5 # Default-Stop: 0 6 # Description: Starts and stops repmgrd. ### END INIT INFO REPMGRD=/usr/bin/repmgrd REPMGR_PIDFILE=/var/run/postgresql/repmgrd.pid REPMGRD_CONF=/etc/repmgr.conf case $1 in # Startup repmgrd servers. start) su postgres -c "$REPMGRD -m -d -p $REPMGR_PIDFILE -f $REPMGRD_CONF" #renice -5 -p `cat $REPMGR_PIDFILE` echo "starting..." ;; # Stop repmgrd servers. stop) if [ -f $REPMGR_PIDFILE ] then if ps auxww | grep `cat $REPMGR_PIDFILE` | grep -v grep then kill `cat $REPMGR_PIDFILE` echo "stopping..." else echo "Repmgrd is not running, but $REPMGR_PIDFILE is present" fi else echo "Repmgrd is not running" fi ;; # status status) if ps auxww | grep `cat $REPMGR_PIDFILE` | grep -v grep then echo "Repmgrd is running" else echo "Repmgrd is stopped" fi ;; *) echo "Init de Repmgrd: stop / start ou status" ;; esac

Notif

repmgr.conf

%n - node ID
%e - event type
%s - success (1 or 0)
%t - timestamp
%d - details

event_notification_command=’/path/to/some/script %n %e %s “%t” “%d”‘

event_notifications=master_register,standby_register,witness_create
https://github.com/2ndQuadrant/repmgr/blob/master/README.md#generating-event-notifications-with-repmgrrepmgrd

Gestion de la vip avec heartbeat dans le cas d’un master/slave

Install

1

# apt-get install heartbeat

conf de la vip

1

# cat <<EOF >> /etc/network/interfaces
#auto ens160:pg # nonauto
iface ens160:pg inet static
address 10.0.69.0.120
netmask 255.255.255.0
EOF
# echo "net.ipv4.ip_nonlocal_bind=1" >> /etc/sysctl.conf
# sysctl -p

• commande heartbeat utiles
  /usr/share/heartbeat/hb_standby
  /usr/share/heartbeat/hb_takeover

• conf heartbeat

  1

  # cat /etc/ha.d/ha.cf ############################### # logging logfile /var/log/ha/ha-log debugfile /var/log/ha/ha-debug ############################### # communication autojoin none udpport 694 ucast ens160 10.0.69.100 bcast ens160 ############################### # thresholds warntime 5 deadtime 15 initdead 60 keepalive 2 ############################### # nodes node bdd1 node bdd2 auto_failback off

1

# cat /etc/ha.d/haresources
# VIP
bdd1 IPaddr::10.0.69.120/24/ens160:pg #MailTo::moi@pouet.fr::HA_Postgresql

• on adapte la conf /etc/repmgr.conf

  1

  # diff /etc/repmgr.conf_heartbeat /etc/repmgr.conf_ori - promote_command='repmgr standby promote -f /etc/repmgr.conf' - follow_command='repmgr standby follow -f /etc/repmgr.conf' + promote_command='sudo /usr/share/heartbeat/hb_takeover && repmgr standby promote -f /etc/repmgr.conf' + follow_command='sudo /usr/share/heartbeat/hb_standby && repmgr standby follow -f /etc/repmgr.conf'

• conf sudo
  postgres ALL=(root) NOPASSWD:/usr/share/heartbeat/hb_takeover

change to postgres user and open psql prompt

1

# su - postgres
# psql

list databases

1

postgres=# \l

list roles

1

postgres=# \du

create role

1

postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;

create role with multiple privileges

1

postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD
postgres=# 'password1' CREATEDB CREATEROLE REPLICATION SUPERUSER;

alter role

1

postgres=# ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;

drop role

1

postgres=# DROP ROLE demorole1;

create database

1

postgres=# CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8';

grant privileges to new user

1

postgres=# GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;

drop database

1

postgres=# DROP DATABASE demodb1;

connect to database

1

postgres=# \c <databasename>

list tables in connected database

1

postgres=# \dt

list columns on table

1

postgres=# \d <tablename>

show processlist;

1

SELECT * from pg_stat_activity ;

Kill session on a database

1

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydataabse' AND leader_pid IS NULL;

nice output

1

postgres=# \x on

output in csv file

1

postgres=#
\copy (
select * from truc
) To 'output.csv' With CSV DELIMITER ';' HEADER

kill query

1

postgres=# SELECT pg_cancel_backend(<pid of the process>)
postgres=# SELECT pg_terminate_backend(<pid of the process>)

show tables

1

SELECT * FROM pg_catalog.pg_tables;

backup/restore database

1

$ pg_dump -h ${TARGET_HOST} -p ${PG_PORT} -U ${PG_USER} -Fd -f ${TARGET_DIR} ${PG_DB}
$ pg_restore -c -Fd -d ${PG_DB} ${BACKUP_DIR}

or

1

$ pg_dump -U username -h hostname dbname > dump.sql
$ psql -U username -d dbname -f dump.sql

check repli

coté master

1

postgres=# select client_addr, state, sent_location, write_location,flush_location, replay_location from pg_stat_replication;

coté slave

1

postgres=# select now() - pg_last_xact_replay_timestamp() AS replication_delay;

Pause de la repli

1

select pg_xlog_replay_pause();

Resume repli

1

select pg_xlog_replay_resume();

Some Tunning

max_connections = 3cores
shared_buffers = 4G < 64G RAM ; 8G > 64G RAM
work_mem = 8M < 32G ram ; 16M < 64G RAM ; 32M > 64G RAM ; if max_connections > 400 => work_mem/2
maintenance_work_mem = 1G
wal_level = hot_standby
archive_mode = on
archive_command = /bin/true
max_wal_senders = 5
wal_keep_segments = 3 checkpoint_segments
random_page_cost = 2.0 for raid, san ; 1.0 for SSD
effective_cache_size = RAM /2
log_min_duration_statement = 1000
log_checkpoints = on

Purge des pg_xlog

1

/etc/init.d/postgresql stop

/usr/lib/postgresql/9.5/bin/pg_controldata /var/lib/postgresql/9.5/main/

sudo -u postgres /usr/lib/postgresql/9.5/bin/pg_resetxlog -o $NEXTOID -x $NEXTXID -f /var/lib/postgresql/9.5/main/

/etc/init.d/postgresql start

Point In Time Recovery
PTR