mkdir /var/lib/pgsql/9.5/data/ -p
mkdir /pg_xlog
mkdir /pg_log
########dont ########initdb -D /var/lib/pgsql/9.5/data/ on slave only ###if did rm -rf inside all data folder in slave###
/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ start
########dont ####crete PGRS1 on slave only
/var/lib/pgsql/repmgr.conf (slave)
cluster=mycluster
node=2
node_name=node2
conninfo='host=172.16.16.41 dbname=repmgr user=repmgr'
upstream_node=1
logfile='/var/log/repmgr/repmgr-9.5.log'
pg_bindir=/usr/pgsql-9.5/bin/
/var/lib/pgsql/repmgr.conf (master)
cluster=mycluster
node=1
node_name=node1
conninfo='host=172.16.16.40 dbname=repmgr user=repmgr'
upstream_node=2
logfile='/var/log/repmgr/repmgr-9.5.log'
pg_bindir=/usr/pgsql-9.5/bin/
vim /var/lib/pgsql/9.5/data/pg_hba.conf in master (stop firewalld)
host all all 172.16.16.40/32 trust
host all all 172.16.16.41/32 trust
host all all 172.16.16.43/32 trust
in master ---su postgres---->/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ restart
in master
repmgr -f /var/lib/pgsql/repmgr.conf master register (/usr/pgsql-9.5/bin/repmgr)
in slave
repmgr -h 172.16.16.40 -U repmgr -d repmgr -D /var/lib/pgsql/9.5/data/ -f /var/lib/pgsql/repmgr.conf standby clone ##this will auto create /var/lib/pgsql/9.5/data/recovery.conf#####
/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ start
repmgr -f /var/lib/pgsql/repmgr.conf standby register (/usr/pgsql-9.5/bin/repmgr)
###OPTIONAL###verify (can in master and slave )
###OPTIONAL###/usr/pgsql-9.5/bin/repmgr -f /var/lib/pgsql/repmgr.conf cluster show
###OPTIONAL###remove from cluster (replication)
###OPTIONAL###repmgr -f /var/lib/pgsql/repmgr.conf standby unregister
=========================slave===================
vim /var/lib/pgsql/9.5/data/postgresql.auto.conf
listen_addresses = '*'
logging_collector = 'ON'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d'
log_checkpoints = 'ON'
log_connections = 'ON'
log_disconnections = 'ON'
log_lock_waits = 'ON'
log_temp_files = 0
log_statement = 'ddl'
log_min_duration_statement = 0
shared_preload_libraries = '"plugin_debugger", "plprofiler", "repmgr_funcs", "pg_stat_statements"'
wal_level = 'hot_standby'
hot_standby = 'on'
max_wal_senders = 20
archive_mode = 'ON'
archive_command = '/bin/true'
#archive_command = 'envdir /etc/wal-e.d/env /bin/wal-e wal-push %p'
max_connections = 300
max_locks_per_transaction = 100
wal_keep_segments = 5000
shared_buffers = '400MB'
log_directory = '/pg_log'
timezone = 'US/Eastern
=========
vim /var/lib/pgsql/9.5/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=repmgr port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres host=172.16.16.40 application_name=node2 password=repmgr'
recovery_target_timeline = 'latest'
#restore_command = 'envdir /etc/wal-e.d/env wal-e wal-fetch "%f" "%p"' ####for cloud wal updates######
========
vim /etc/repmgr/9.5/repmgr.conf
conninfo='host=localhost dbname=repmgr_db user=repmgr_usr'
========
========================Master=========================
listen_addresses = '*'
logging_collector = 'ON'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d'
log_checkpoints = 'ON'
log_connections = 'ON'
log_disconnections = 'ON'
log_lock_waits = 'ON'
log_temp_files = 0
log_statement = 'ddl'
log_min_duration_statement = 0
shared_preload_libraries = '"plugin_debugger", "plprofiler", "repmgr_funcs", "pg_stat_statements"'
wal_level = 'hot_standby'
hot_standby = 'on'
max_wal_senders = 20
archive_mode = 'ON'
archive_command = '/bin/true'
#archive_command = 'envdir /etc/wal-e.d/env /bin/wal-e wal-push %p'
max_connections = 300
max_locks_per_transaction = 100
wal_keep_segments = 5000
shared_buffers = '400MB'
log_directory = '/pg_log'
timezone = 'US/Eastern'
###########################################
############Real master DB#################
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
listen_addresses = '*'
logging_collector = 'ON'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d'
log_checkpoints = 'ON'
log_connections = 'ON'
log_disconnections = 'ON'
log_lock_waits = 'ON'
log_temp_files = '0'
log_statement = 'ddl'
log_min_duration_statement = '6s'
shared_preload_libraries = '"plugin_debugger", "plprofiler", "repmgr_funcs", "pg_stat_statements"'
wal_level = 'hot_standby'
hot_standby = 'on'
max_wal_senders = '20'
archive_mode = 'ON'
archive_command = 'envdir /etc/wal-e.d/env /bin/wal-e wal-push %p'
max_connections = '600'
max_locks_per_transaction = '200'
wal_keep_segments = '7000'
shared_buffers = '32GB'
timezone = 'US/Eastern'
wal_log_hints = 'on'
log_directory = '/pglogs'
autovacuum = 'on'
autovacuum_analyze_threshold = '50'
autovacuum_vacuum_threshold = '50'
constraint_exclusion = 'partition'
effective_cache_size = '188GB'
lc_messages = 'en_US.UTF-8'
maintenance_work_mem = '1GB'
max_prepared_transactions = '300'
temp_buffers = '16MB'
work_mem = '128MB'
hot_standby_feedback = 'ON'
max_standby_streaming_delay = '-1'
######################################################
=======
vim /etc/repmgr/9.5/repmgr.conf
conninfo='host=localhost dbname=repmgr_db user=repmgr_usr'
========
stop replication in slave and make it read/write nomal db
vim /var/lib/pgsql/9.5/data/recovery.conf
add line : trigger_file='/tmp/psql.trigger'
touch /tmp/psql.trigger
/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ restart
start again replication in slave
/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ stop
rm -rf /var/lib/pgsql/9.5/data
repmgr -h 172.16.16.40 -U repmgr -d repmgr -D /var/lib/pgsql/9.5/data/ -f /var/lib/pgsql/repmgr.conf standby clone ##this will auto create /var/lib/pgsql/9.5/data/recovery.conf#####
/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ start
stop slave streaming temporory--->
psql -U postgres PGRS1
SELECT pg_xlog_replay_pause();
start slave streaming again ----> SELECT pg_xlog_replay_resume();
check status---> SELECT pg_is_xlog_replay_paused();
https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE
@@@@@@@@@@@@@@@@@@@@@@@@2WAL-E@@@@@@@@@@@@@@@@@@@@@@@
yum install gcc libffi-devel python-devel openssl-devel
yum install python-pip
pip install pip --upgrade
yum install lzop
yum install pv
pip install gevent
pip install boto
pip install azure
pip install python-swiftclient
pip install python-keystoneclient
pip install argparse
yum install python-devel
pip install gevent
pip install boto
pip install azure
pip install python-swiftclient
pip install python-keystoneclient
pip install argparse
pip install envdir
####unmask u=rwx,g=rx,o=
sudo mkdir -p /etc/wal-e.d/env
login to rackspace
Storage-->Create container-->WALE-TEST
echo 'swift://WALE-TEST' | sudo tee /etc/wal-e.d/env/WALE_SWIFT_PREFIX
echo 'https://identity.api.rackspacecloud.com/v2.0/' | sudo tee /etc/wal-e.d/env/SWIFT_AUTHURL
echo 'IAD' | sudo tee /etc/wal-e.d/env/SWIFT_REGION
echo 'xxxxx' | sudo tee /etc/wal-e.d/env/SWIFT_TENANT
echo 'xxxxxx.xxxx' | sudo tee /etc/wal-e.d/env/SWIFT_USER
echo 'xxxxxx' | sudo tee /etc/wal-e.d/env/SWIFT_PASSWORD
sudo chown -R root:postgres /etc/wal-e.d
cd /etc/wal-e.d/env/
cat *
To test if it worked for you just reload the console and run below code:
python -c 'import locale; print(locale.getdefaultlocale());'
################Download and Install wal-e
cd /tmp/
wget https://github.com/wal-e/wal-e/archive/v0.9.zip
unzip v0.9.zip
cd wal-e-0.9/
python setup.py build
python setup.py install
to test-->envdir /etc/wal-e.d/env/ wal-e wal-push /pg_xlog/000000010000000000000001
@@@@@@@@@@vim /var/lib/pgsql/9.5/data/postgresql.auto.conf in master@@@@@@@@@@@@@@@@
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'envdir /etc/wal-e.d/env /bin/wal-e wal-push %p'
###archive_timeout = 60
/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ stop
/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ start
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@vi /var/lib/pgsql/9.5/data/recovery.conf in slave@@@@@@@@@@@@@@when need updates via cloud@@when cant update data from master
restore_command = 'envdir /etc/wal-e.d/env wal-e wal-fetch "%f" "%p"'
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
==================================================================
&&&&&&&&&&&&&&&&&&&&&&&&&&&&BUCADO&&&&&&&&&&&
==================================================================
install postgres in slave
init db -->/usr/pgsql-9.5/bin/initdb -D /var/lib/pgsql/9.5/data/
/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ start
create PGRS1
vim /var/lib/pgsql/9.5/data/pg_hba.conf (stop firewalld)
host all all 172.16.16.40/32 trust
host all all 172.16.16.41/32 trust
host all all 172.16.16.43/32 trust
vim /var/lib/pgsql/9.5/data/postgresql.auto.conf
$$$$$$
listen_addresses = '*'
logging_collector = 'ON'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d'
log_checkpoints = 'ON'
log_connections = 'ON'
log_disconnections = 'ON'
log_lock_waits = 'ON'
log_temp_files = '0'
log_statement = 'ddl'
log_min_duration_statement = '6s'
shared_preload_libraries = '"plugin_debugger", "plprofiler", "repmgr_funcs", "pg_stat_statements"'
wal_level = 'hot_standby'
hot_standby = 'on'
max_wal_senders = '20'
archive_mode = 'ON'
archive_command = '/bin/true'
max_connections = '600'
max_locks_per_transaction = '200'
wal_keep_segments = '7000'
shared_buffers = '2GB'
timezone = 'US/Eastern'
wal_log_hints = 'on'
log_directory = '/pg_log'
autovacuum = 'on'
autovacuum_analyze_threshold = '50'
autovacuum_vacuum_threshold = '50'
constraint_exclusion = 'partition'
effective_cache_size = '4000MB'
lc_messages = 'en_US.UTF-8'
maintenance_work_mem = '1GB'
max_prepared_transactions = '300'
temp_buffers = '16MB'
work_mem = '32MB'
hot_standby_feedback = 'ON'
max_standby_streaming_delay = '-1'
$$$$$$$
pg_dumpall --host 172.16.16.40 -l PGRS1 -p 5432 -g |psql -d PGRS1 -p 5432
pg_dump -s -h 172.16.16.40 -d PGRS1 -p 5432 -v |psql -d PGRS1 -p 5432
above all in slave&&&&&&&&&&&&&&&&&
in master (any server need bucado master )
yum -y install bucardo
bucardo show all (--> Validation, just run)
Note : edit pg_hba.conf for necessary changes to establish the connections between master and slave (
vim /var/lib/pgsql/9.5/data/pg_hba.conf host all all 172.16.16.41/32 trust
bucardo install --batch
su postgres
bucardo add db master_1 dbname='PGRS1' host=172.16.16.40 port=5432 user=postgres
bucardo add db slave_1 dbname='PGRS1' host=172.16.16.42 port=5432 user=postgres
bucardo add table ss1.tt1, ss1.tt2 db=master_1 herd=set1
########bucardo add sequences ss1.seq1 db=master_1 herd=set1 ###dont know
bucardo add sync sync_test dbs=master_1:source,slave_1:target relgroup=set1 onetimecopy=1
bucardo start in root
&&&&&&&&&&&&&&&&&&
https://s3-us-west-2.amazonaws.com/hasaranga.com/index.html
Hamiyaaa patta aaaaaaaaaaaaaaaaaaaaaaaaa
ReplyDelete