Scribd
Upload
63 views

HS - Postgres 9.6

This document contains instructions for setting up PostgreSQL replication between two servers (PG-1 and PG-2) using Replication Manager (repmgr). It details installing PostgreSQL and repmgr, initializing replication on PG-1, setting up the standby on PG-2, and configuring repmgr to monitor the replication. It also contains steps for promoting PG-2 to the new primary if needed.

Uploaded by

Piort Ilich
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
63 views

HS - Postgres 9.6

This document contains instructions for setting up PostgreSQL replication between two servers (PG-1 and PG-2) using Replication Manager (repmgr). It details installing PostgreSQL and repmgr, initializing replication on PG-1, setting up the standby on PG-2, and configuring repmgr to monitor the replication. It also contains steps for promoting PG-2 to the new primary if needed.

Uploaded by

Piort Ilich
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

# Todos

--- root
sudo yum install -y dnf
sudo dnf install -y epel-release vim
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-
x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql96-server postgresql96-contrib rsync
sudo dnf install -y repmgr96

cat<<EOF>/etc/sudoers.d/postgres
Defaults:postgres !requiretty
postgres ALL = NOPASSWD: /usr/bin/systemctl stop postgresql-9.6, \\
/usr/bin/systemctl start postgresql-9.6, \\
/usr/bin/systemctl restart postgresql-9.6, \\
/usr/bin/systemctl reload postgresql-9.6, \\
/usr/bin/systemctl stop repmgr96, \\
/usr/bin/systemctl start repmgr96, \\
/usr/bin/systemctl restart repmgr96
EOF

sudo chmod 440 /etc/sudoers.d/postgres

--- postgres

cat<<EOF>~/.bash_profile
if [ -f /var/lib/pgsql/.pgsql_profile ]; then
source /var/lib/pgsql/.pgsql_profile
fi
EOF

cat<<EOF>>~/.pgsql_profile
PATH=$PATH:/usr/pgsql-9.6/bin
EOF

# PG-1
--- root
/usr/pgsql-9.6/bin/postgresql96-setup initdb

vim /var/lib/pgsql/9.6/data/postgresql.conf
--
listen_addresses = '*'
shared_preload_libraries = 'pg_stat_statements, repmgr'
wal_log_hints = on
wal_level = logical
wal_keep_segments = 20

max_replication_slots = 10
max_wal_senders = 12

archive_mode = on
archive_command = '/bin/true'
archive_timeout = 6h

hot_standby = on
hot_standby_feedback = on

log_line_prefix = '%t [%u@%r/%a/%d:%p]: [%l]'


log_min_duration_statement = 5s
log_checkpoints = on
log_lock_waits = on
log_temp_files = 8192
log_statement = ddl
log_autovacuum_min_duration = 5s

pg_stat_statements.max = 1000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on
-----
sudo systemctl restart postgresql-9.6

--- postgres

# PG-1
createuser --superuser --replication --pwprompt repmgr

# TODOS
echo "*:5432:*:repmgr:CONTRASEÑA_USUARIO_REPMGR" >> ~/.pgpass
chmod 600 ~/.pgpass

# PG-1
cat<<EOF>>/var/lib/pgsql/9.6/data/pg_hba.conf
host replication all PGSERVER1-IP/32 md5
host replication all PGSERVER2-IP/32 md5
host all all 0.0.0.0/0 md5
EOF

sudo systemctl reload postgresql-9.6

# TODOS
--- root
sudo chown postgres:postgres /etc/repmgr/9.6/repmgr.conf

vim /etc/repmgr/9.6/repmgr.conf
---
node_id = 1
node_name ='PGSERVER'
conninfo ='host=PGSERVER-IP port=5432 dbname=repmgr user=repmgr
application_name=PGSERVER connect_timeout=2'
data_directory ='/var/lib/pgsql/9.6/data'
pg_bindir ='/usr/pgsql-9.6/bin'
use_replication_slots = 1
async_query_timeout = 60
reconnect_attempts = 6
reconnect_interval = 10
failover = automatic
ssh_options ='-q'

log_level = INFO
log_facility=STDERR
log_file='/var/log/repmgr/repmgr.log'

monitoring_history =true

promote_command ='/etc/repmgr/9.6/promote_command.sh'
follow_command ='/usr/pgsql-9.6/bin/repmgr standby follow -f
/etc/repmgr/9.6/repmgr.conf'

service_start_command ='sudo systemctl start postgresql-9.6'


service_stop_command ='sudo systemctl stop postgresql-9.6'
service_restart_command ='sudo systemctl restart postgresql-9.6'
service_reload_command ='sudo systemctl reload postgresql-9.6'
---

# PG-1
--- postgres

createdb -O repmgr repmgr

# TODOS - Local
scp promote_command.sh root@PGSERVER-IP:/etc/repmgr/9.6/
scp config.sh root@PGSERVER-IP:/etc/repmgr/9.6/

# PG-1
--- postgres
repmgr primary register

# PG-2
repmgr standby clone -c -U repmgr -d repmgr -h PRIMARY-IP --dry-run # Probar
replicación
repmgr standby clone -c -U repmgr -d repmgr -h PRIMARY-IP # Crear replicación
sudo systemctl start postgresql-9.6
repmgr standby register

# TODOS
--- root
cd /etc/repmgr/9.6/
chown postgres:postgres config.sh
chown postgres:postgres promote_command.sh
chmod 744 promote_command.sh
chmod 744 config.sh
cd /run
mkdir repmgr
chown postgres:postgres -R repmgr
sudo systemctl start repmgr96
sudo systemctl enable repmgr96
sudo systemctl enable postgresql-9.6

# FILEBACK
--- old-primary
sudo systemctl stop postgresql-9.6
repmgr --force --host=NEWPRIMARY-IP --dbname=repmgr --user=repmgr standby clone
sudo systemctl start postgresql-9.6
repmgr standby register --force

You might also like