#Install postgres in the primary and standby server as usual
root# wget http://yum.pgrpms.org/reporpms/9.1/pgdg-centos91-9.1-4.noarch.rpm
#install the repo
root# rpm -i pgdg-centos91-9.1-4.noarch.rpm
#We now need to edit the CentOS-Base.repo to exclude postgre.Simply edit CentOS-Base.repo and add 'exclude=postgresql*' to the [base] and [updates] sections:
root# cd /etc/yum.repos.d
root# vi CentOS-Base.repo
[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
root# yum list postgres*
root# yum install postgresql91-libs
root# yum install postgresql91
root# yum install postgresql91-server
root# yum install postgresql91-contrib
root# yum install postgresql91-devel
root# yum install postgresql91-plpython
#create postgresql data directory
root# mkdir -p /pgdata/data/
root# chown -R postgres.postgres /pgdata/
#We now need to edit /etc/init.d/postgresql file.
root# vi /etc/init.d/postgresql-9.1
PGDATA=/pgdata/data
PGLOG=/pgdata/pgstartup.log
#initialize Postgre and start server
root# chkconfig postgresql-9.1 on
root# service postgresql-9.1 initdb
Initializing database: [ OK ]
root# service postgresql-9.1 start
Starting postgresql-9.1 service: [ OK ]
-----#Hot_standby server
root# mkdir /pgdata/pg_arc/
root# chown postgres.postgres -R /pgdata/pg_arc/
service postgresql-9.1 stop
# install scp
root# yum provides */scp
root# yum -y install openssh-clients
#SSH Tunnel Creation
#On the master server do the following:
// sestatus | grep -i mode
// cat /etc/selinux/config
// #and change the SELINUX line to SELINUX=disabled
// vi /etc/selinux/config
// echo 0 >/selinux/enforce
// sestatus | grep -i mode
// Current mode: permissive
// Mode from config file: disabled
root# su - postgres
$ ssh-keygen -t rsa
$ cd /var/lib/pgsql/.ssh
//$cat id_rsa.pub>>authorized_keys
$ ssh-copy-id -i id_rsa.pub postgres@postgresql2.mydomain.com
$ ssh postgres@postgresql2.mydomain.com
//ssh slave and see if you are able to login without password. On slave server do steps like this.
#On master server, connect to postgresql server. Create replication user on database
root# su - postgres
$ psql postgres
psql (9.1.8)
Type "help" for help.
postgres=# CREATE USER myuser_replicator WITH SUPERUSER ENCRYPTED PASSWORD 'a12345';
postgres=# \q
root# service postgresql-9.1 start
#Editing pg_hba.conf
#on mastser Edit /pgdata/data/pg_hba.conf. Give permission to repuser user on standby machine (IP = (postgresql2.mydomain.com)) to connect to the master.
$ vim /pgdata/data/pg_hba.conf
#replication
host replication myuser_replicator 10.1.1.118/32 md5
# you will need to enable streaming replication and WAL logging for your database in
$ vim pgdata/data/postgresql.conf
listen_addresses = '*'
shared_buffers = 2048MB # ( ~= RAM/4)
maintenance_work_mem = 256MB # (as much as you need)
# Bu miktarda ihtiyaca göre artırılabilir.
effective_cache_size = 4096MB ( ~= RAM/2 or ~= RAM3/4)
wal_level = hot_standby
archive_mode = on
archive_command = 'scp %p postgres@10.1.1.118:/pgdata/pg_arc/%f'
max_wal_senders = 1 # (slave servers count)
# optionally, you can optimize server kernel parameter in /etc/sysctl.conf (Total system RAM = 8GB)
root# echo 'kernel.shmmax = 68719476736' >> /etc/sysctl.conf
root# echo 'kernel.shmall = 4294967296' >> /etc/sysctl.conf
root# sysctl –p
#In order for the standby to start replicating, the entire database needs to be archived and then reloaded into the standby.
#This process is called a “base backup”, and can be performed on the master and then transferred to the slave.
#Let’s create a snapshot to transfer to the slave by capturing a binary backup of the entire PostgreSQL data directory.
#Master
root# su - postgres
$ psql postgres
psql (9.1.8)
Type "help" for help.
postgres=# SELECT pg_start_backup('base_backup');
postgres=# \q
$ cd /pgdata
$ tar -czvf backupdata.tar.gz data
$ scp backupdata.tar.gz postgres@postgresql2.mydomain.com:/pgdata/
$ psql postgres
postgres=# SELECT pg_stop_backup();
postgres-# \q
# Standby Setup: First, shutdown the database on the standby
root# service postgresql-9.1 stop
root# cd /pgdata/
root# rm -rf data
root# tar -xzvf backupdata.tar.gz
root# chown -R postgres:postgres data
# modify /pgdata/data/postgresql.conf file on standby
$ vim /pgdata/data/postgresql.conf
shared_buffers = 2048MB # ( ~= RAM/4)
effective_cache_size = 4096MB ( ~= RAM/2 or ~= RAM3/4)
#wal_level = hot_standby
#archive_mode = on
#archive_command = 'scp %p postgres@10.1.1.118:/pgdata/pg_arc/%f'
#max_wal_senders = 1 # (slave servers count)
hot_standby = on
#modify or create new recovery settings on slave
root# vim /pgdata/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=10.1.1.183 port=5432 user=myuser_replicator password=a12345'
restore_command = 'cp /pgdata/pg_arc/%f %p'
trigger_file = '/pgdata/trigger_file' # /pgdata/trigger_file parameter which tells the standby when to become the master.
archive_cleanup_command = 'pg_archivecleanup /pgdata/pg_arc %r'
root# chown postgres.postgres /pgdata/data/recovery.conf
#You can also check certain processes on the machines to ensure replication is established:
postgres[master]$ ps -ef | grep sender
postgres 1854 1725 0 11:09 pts/0 00:00:00 grep sender
postgres[slave]$ ps -ef | grep receiver
postgres 1837 1752 0 11:00 pts/0 00:00:00 grep receiver
postgres(master)=# select * from pg_stat_replication;
procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_st
ate
---------+----------+---------+------------------+-------------+-----------------+-------------+---------------+-------+---------------+----------------+----------------+-----------------+---------------+--------
----
(0 rows)
# Verify Setup
postgres=# CREATE DATABASE first_db;
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
first_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | 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
(4 rows)
postgres=# \c FIRST_DB
first_db=# CREATE table personel (id integer PRIMARY KEY,name varchar(100),email varchar(255));
first_db=# INSERT into personel values (1, 'ercan', 'ercanxcan.gmail.com');
first_db=# INSERT into personel values (2, 'can', 'canercan04.yahoo.com');
first_db=# INSERT into personel values (3, 'eeee', 'eee.yahoo.com');
#Disaster recovery
Felaket kurtarma işlemi yapıldıktan ve eski master sunucuda sorunlar düzeltildiğinde,
eski master sunucunuzu tekrar master olarak devreye almak isterseniz.
Hali hazırda çalışan sunucuda sistemin full backup'ını alıp veritabanına dışarıdan erişimleri kapatmanız gerekir.
Master makinaya en son aldığınız backup kopyalanır ve sample şema yerine bu backup kullanılarak tüm adımlar tekrar
edilerek sistem başlanğıç konumuna dönmesi sağlanır.
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
root# wget http://yum.pgrpms.org/reporpms/9.1/pgdg-centos91-9.1-4.noarch.rpm
#install the repo
root# rpm -i pgdg-centos91-9.1-4.noarch.rpm
#We now need to edit the CentOS-Base.repo to exclude postgre.Simply edit CentOS-Base.repo and add 'exclude=postgresql*' to the [base] and [updates] sections:
root# cd /etc/yum.repos.d
root# vi CentOS-Base.repo
[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
root# yum list postgres*
root# yum install postgresql91-libs
root# yum install postgresql91
root# yum install postgresql91-server
root# yum install postgresql91-contrib
root# yum install postgresql91-devel
root# yum install postgresql91-plpython
#create postgresql data directory
root# mkdir -p /pgdata/data/
root# chown -R postgres.postgres /pgdata/
#We now need to edit /etc/init.d/postgresql file.
root# vi /etc/init.d/postgresql-9.1
PGDATA=/pgdata/data
PGLOG=/pgdata/pgstartup.log
#initialize Postgre and start server
root# chkconfig postgresql-9.1 on
root# service postgresql-9.1 initdb
Initializing database: [ OK ]
root# service postgresql-9.1 start
Starting postgresql-9.1 service: [ OK ]
-----#Hot_standby server
root# mkdir /pgdata/pg_arc/
root# chown postgres.postgres -R /pgdata/pg_arc/
service postgresql-9.1 stop
# install scp
root# yum provides */scp
root# yum -y install openssh-clients
#SSH Tunnel Creation
#On the master server do the following:
// sestatus | grep -i mode
// cat /etc/selinux/config
// #and change the SELINUX line to SELINUX=disabled
// vi /etc/selinux/config
// echo 0 >/selinux/enforce
// sestatus | grep -i mode
// Current mode: permissive
// Mode from config file: disabled
root# su - postgres
$ ssh-keygen -t rsa
$ cd /var/lib/pgsql/.ssh
//$cat id_rsa.pub>>authorized_keys
$ ssh-copy-id -i id_rsa.pub postgres@postgresql2.mydomain.com
$ ssh postgres@postgresql2.mydomain.com
//ssh slave and see if you are able to login without password. On slave server do steps like this.
#On master server, connect to postgresql server. Create replication user on database
root# su - postgres
$ psql postgres
psql (9.1.8)
Type "help" for help.
postgres=# CREATE USER myuser_replicator WITH SUPERUSER ENCRYPTED PASSWORD 'a12345';
postgres=# \q
root# service postgresql-9.1 start
#Editing pg_hba.conf
#on mastser Edit /pgdata/data/pg_hba.conf. Give permission to repuser user on standby machine (IP = (postgresql2.mydomain.com)) to connect to the master.
$ vim /pgdata/data/pg_hba.conf
#replication
host replication myuser_replicator 10.1.1.118/32 md5
# you will need to enable streaming replication and WAL logging for your database in
$ vim pgdata/data/postgresql.conf
listen_addresses = '*'
shared_buffers = 2048MB # ( ~= RAM/4)
maintenance_work_mem = 256MB # (as much as you need)
# Bu miktarda ihtiyaca göre artırılabilir.
effective_cache_size = 4096MB ( ~= RAM/2 or ~= RAM3/4)
wal_level = hot_standby
archive_mode = on
archive_command = 'scp %p postgres@10.1.1.118:/pgdata/pg_arc/%f'
max_wal_senders = 1 # (slave servers count)
# optionally, you can optimize server kernel parameter in /etc/sysctl.conf (Total system RAM = 8GB)
root# echo 'kernel.shmmax = 68719476736' >> /etc/sysctl.conf
root# echo 'kernel.shmall = 4294967296' >> /etc/sysctl.conf
root# sysctl –p
#In order for the standby to start replicating, the entire database needs to be archived and then reloaded into the standby.
#This process is called a “base backup”, and can be performed on the master and then transferred to the slave.
#Let’s create a snapshot to transfer to the slave by capturing a binary backup of the entire PostgreSQL data directory.
#Master
root# su - postgres
$ psql postgres
psql (9.1.8)
Type "help" for help.
postgres=# SELECT pg_start_backup('base_backup');
postgres=# \q
$ cd /pgdata
$ tar -czvf backupdata.tar.gz data
$ scp backupdata.tar.gz postgres@postgresql2.mydomain.com:/pgdata/
$ psql postgres
postgres=# SELECT pg_stop_backup();
postgres-# \q
# Standby Setup: First, shutdown the database on the standby
root# service postgresql-9.1 stop
root# cd /pgdata/
root# rm -rf data
root# tar -xzvf backupdata.tar.gz
root# chown -R postgres:postgres data
# modify /pgdata/data/postgresql.conf file on standby
$ vim /pgdata/data/postgresql.conf
shared_buffers = 2048MB # ( ~= RAM/4)
effective_cache_size = 4096MB ( ~= RAM/2 or ~= RAM3/4)
#wal_level = hot_standby
#archive_mode = on
#archive_command = 'scp %p postgres@10.1.1.118:/pgdata/pg_arc/%f'
#max_wal_senders = 1 # (slave servers count)
hot_standby = on
#modify or create new recovery settings on slave
root# vim /pgdata/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=10.1.1.183 port=5432 user=myuser_replicator password=a12345'
restore_command = 'cp /pgdata/pg_arc/%f %p'
trigger_file = '/pgdata/trigger_file' # /pgdata/trigger_file parameter which tells the standby when to become the master.
archive_cleanup_command = 'pg_archivecleanup /pgdata/pg_arc %r'
root# chown postgres.postgres /pgdata/data/recovery.conf
#You can also check certain processes on the machines to ensure replication is established:
postgres[master]$ ps -ef | grep sender
postgres 1854 1725 0 11:09 pts/0 00:00:00 grep sender
postgres[slave]$ ps -ef | grep receiver
postgres 1837 1752 0 11:00 pts/0 00:00:00 grep receiver
postgres(master)=# select * from pg_stat_replication;
procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_st
ate
---------+----------+---------+------------------+-------------+-----------------+-------------+---------------+-------+---------------+----------------+----------------+-----------------+---------------+--------
----
(0 rows)
# Verify Setup
postgres=# CREATE DATABASE first_db;
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
first_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | 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
(4 rows)
postgres=# \c FIRST_DB
first_db=# CREATE table personel (id integer PRIMARY KEY,name varchar(100),email varchar(255));
first_db=# INSERT into personel values (1, 'ercan', 'ercanxcan.gmail.com');
first_db=# INSERT into personel values (2, 'can', 'canercan04.yahoo.com');
first_db=# INSERT into personel values (3, 'eeee', 'eee.yahoo.com');
#Disaster recovery
Felaket kurtarma işlemi yapıldıktan ve eski master sunucuda sorunlar düzeltildiğinde,
eski master sunucunuzu tekrar master olarak devreye almak isterseniz.
Hali hazırda çalışan sunucuda sistemin full backup'ını alıp veritabanına dışarıdan erişimleri kapatmanız gerekir.
Master makinaya en son aldığınız backup kopyalanır ve sample şema yerine bu backup kullanılarak tüm adımlar tekrar
edilerek sistem başlanğıç konumuna dönmesi sağlanır.
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
Comments
Post a Comment