yum -y install zlib-devel or zlib*
yum -y install readline-devel or readline*
yum -y install gcc-c++ or gcc*
yum -y install tcl-devel
-------If you faced “-bash: gmake: command not found” error
yum -y install make
and you have to create link for “gmake” as follows
ln -s /usr/bin/make /usr/bin/gmake-------------------
Create user on shell follow the following commands
adduser -s /bin/sh postgres
# passwd postgres
• Extract the postgres
unzip /home/my/software/postgresql-8.0.3/postgresql-8.0.3.zip
inside that, chmod 776 configure
./configure --with-tcl //./configure --with-tcl --prefix=/mnt/san/usr/local/pgsql1
*******************to slony correct working->
./configure --with-pgport=5434 --with-tcl --prefix=/usr/local/pgsql_5434 --enable-thread-safety*******
gmake
gmake install
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su postgres
initiate the postgres user
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data (inside DB run this)
The "-D" option specifies the location where the data will be stored.
Login as root
Go to /usr/local/pgsql/data
vi pg_hba.conf
Add the following
host all all 192.168.1.0 255.255.255.0 trust # for Internal Servers
host all all 220.247.200.42 255.25.255.255 trust # for External Servers
host all all 203.143.12.26 255.25.255.255 trust # for External Servers
(If the Jboss running in a different server, you should allow that IP as well)
Login as root
Go to /usr/local/pgsql/data
vim postgresql.conf
listen_addresses = '*' # by default this is commented uncomment this, and between the single quote type *
port = 5432 # by default this is commented uncomment this
max_connections = 100 # by default this is commented uncomment this
stats_command_string = true # by default this is coming as “false”. Change it to “true” (if not there don’t insert this manually)
SCRIPT
mkdir -p /my/myadmin
vi pgstart ->>> /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data & ////usr/local/pgsql2
[[if want can change /usr/local/pgsql/bin/postmaster_1 -D /usr/local/pgsql/data &
also soft link as postmaster_1 -> postgres in /usr/local/pgsql/bin/ ]]
//////////just mv postmaster postmaster_1 ///usr/local/pgsql/bin/postmaster_1 -D /usr/local/pgsql/data &
/bin/postmaster2 -D /usr/local/pgsql2/data ((rename usr/local/pgsql2/bin/postmaster2))
(vi pgstop -->>> killall postmaster //killall postmaster2 )
vi pgstop correct one -->
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop -m fast
######################if we want another data file store in new installation 5434 (postmaster_5434)##
after initiate db rm data folder , and copy old data folder (5432)
rm pid file in data folder,
mv postmaster postmaster_5434
run,
usr/local/pgsql/bin/postmaster_5434 -D /usr/local/pgsql/data &
#################################
///////////export PATH="$PATH:/my/myadmin" ////
///////////script add in root ->>>>vi etc/environment ADD THIS LINE -->>PATH="/my/myadmin/:/usr////////////bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/X11/bin:/my/myadmin"
///////////or
////////vi /home/postgres/.bashrc
# User specific aliases and functions
///////////PATH=$PATH:$HOME/bin:/my/myadmin
vi /etc/bashrc
PATH=$PATH:$HOME/bin:/mysystem/myadmin:/usr/local/pgsql/bin
source /etc/bashrc
///////////correct way to ser env
///////////vim /etc/profile
///////////unset i
///////////unset -f pathmunge
///////////export PATH=/mystem/myadmin:$PATH
///////////export PATH=/sbin:$PATH
///////////export PATH=/mnt/newraid/usr/local/pgsql/bin:$PATH
CREATE DB
/usr/local/pgsql1/bin/createdb -U postgres -p 5432 PGRS1
/usr/local/pgsql/bin/createlang pltcl -U postgres PGRS1
/usr/local/pgsql/bin/createlang plpgsql -U postgres PGRS1
////////////////usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data &
/////////////This will start the server
/////////////chown postgres /my/myadmin/ -R
/////////////[root@localhost myadmin]# chmod 776 /my/myadmin/ -R
DBs
/usr/local/pgsql1/bin/createdb -U postgres -p 5432 PGRS1
(if you get this error "libpq.so.4: cannot open shared object file: No such file or directory"
solution
find /usr/local -name libpq.so.4
ln -s /usr/local/pgsql/lib/libpq.so.4 /usr/lib/libpq.so.4
ln -s /usr/local/pgsql/lib64/libpq.so.4 /usr/lib/libpq.so.4
)
/usr/local/pgsql1/bin/createlang -p 5432 -l -U postgres PGRS1
/usr/local/pgsql1/bin/psql -U postgres -l
/usr/local/pgsql1/bin/psql -U postgres PGRS1 //go inside DB
\c PGRS1 //to connect db and create tables
create table t1(id int);
\d t1
\q
\dn //list of schemas
CREATE SCHEMA hollywood;
CREATE TABLE hollywood.films (title text, release date, awards text[]);
DROP SCHEMA mystuff CASCADE;
backup
/usr/local/pgsql1/bin/pg_dump -p 5432 --file=/home/uploads/hollywood.sql --username=postgres --schema=hollywood PGRS1
restore
/usr/local/pgsql1/bin/psql -U postgres --pset pager -f /home/upload/hollywood.sql PGRS1 -p 5432
-------------------------------
PhpPgAdmin
yum install phpPgAdmin httpd
vi /etc/httpd/conf.d/phpPgAdmin.conf
Alias /phpPgAdmin /usr/share/phpPgAdmin
<Location /phpPgAdmin> <IfModule mod_authz_core.c> # Apache 2.4 Require all granted #Require host example.com </IfModule> <IfModule !mod_authz_core.c> # Apache 2.2 Order deny,allow Allow from all # Allow from .example.com </IfModule> </Location>
service httpd start
chkconfig httpd on
vi /etc/phpPgAdmin/config.inc.php
$conf['servers'][0]['host'] = 'localhost';
$conf['extra_login_security'] = false;
$conf['owned_only'] = true;
http://ip-address/phpPgAdmin
SELinux might restrict the users to log in to the PostgreSQL. Just enter the following command
setsebool -P httpd_can_network_connect_db 1
http://www.unixmen.com/postgresql-9-4-released-install-centos-7/
https://sites.google.com/site/arfanahmedcheema/blog/pgsqlandphppgadminoncentos6
===================
jdk
/home/user/software/jdk folder and then execute the following commands
./j2sdk-1_4_2_07-linux-i586.rpm.bin
rpm -ivh j2sdk-1_4_2_07-linux-i586.rpm
get jdk1.5.0_22 folder
jdk1.5.0_22
copy to /usr/java/jdk1.5.0_22
chmod 775 /usr/java/ -R
vi ~/.bashrc
export JAVA_HOME=/usr/java/j2sdk1.4.2_07
export PATH=$JAVA_HOME/bin:$PATH
CLASSPATH=$JAVA_HOME/lib/classes.zip
export CLASSPATH=$CLASSPATH:
export PATH=/sbin:$PATH
java -version
------------
http://sourceforge.net/projects/jboss/files/JBoss/
jboss 4.0.3 sp1
useradd -d /home/jboss -s /bin/bash -m jboss //m=create home dir if not exists;-d=default values;-s=shell
passwd jboss
cp ->>>>> /home/jboss/jbossunzip
mv->>> /home/jboss/jboss4.0.3
su jboss
vi .bash_profile or .bashrc
export JBOSS_HOME=$HOME/jboss4.0.3
export JAVA_HOME=/usr/java/j2sdk1.4.2_07
source .bash_profile or .bashrc
cd jboss4.0.3/bin
./run.sh
root
chown jboss.jboss jboss4.0 -R
chmod 775 jboss4.0 -R
service iptables stop
in log4j.xml use "FILE"--------------->
<root>
<priority value="WARN" />
<appender-ref ref="FILE"/>
</root>
------>
<param name="File" value="/var/log/sys/server.log"/>
------------------>
//localhost:8080
----------------
node2
vim /jboss/jboss_2/server/default/conf/log4j.xml and edit the following;
<param
name="File" value="/var/log/app/ridestream2.log"/>
*****
vim /jboss_2/bin/run.sh
# Setup the
JVM
if [ "x$JAVA" = "x" ];
then
if [
"x$JAVA_HOME" != "x" ]; then
JAVA="$JAVA_HOME/bin/java_2"
else
JAVA="java_2"
fi
fi
*****
********************
vim /rezsystem/jboss/jboss_1/server/default/deploy/jbossweb-tomcat55.sar/server.xml
<Engine jvmRoute="AvStgNode1" name="jboss.web" defaultHost="localhost">
******* *************
********************
vim /rezsystem/jboss/jboss_1/server/default/deploy/jbossweb-tomcat55.sar/server.xml
<Engine jvmRoute="AvStgNode1" name="jboss.web" defaultHost="localhost">
******* *************
vim /jboss/jboss_2/server/default/conf/jboss-service.xml
Find (<mbean
code="org.jboss.services.binding.ServiceBindingManager") line and
add (-->) before the
line. And change the port
number as required.
-->
<mbean
code="org.jboss.services.binding.ServiceBindingManager"
name="jboss.system:service=ServiceBindingManager">
<attribute
name="ServerName">ports-01</attribute>
<attribute
name="StoreURL">${jboss.home.url}/docs/examples/binding-manager/sample-bindings.xml</attribute>
<attribute
name="StoreFactoryClassName">
org.jboss.services.binding.XMLServicesStoreFactory
</attribute>
</mbean>
******SLONY-I
download http://www.slony.info/downloads/
tar xf slony1-1.2.20.tar.bz2 or tar jxf slony1-1.2.20.tar.bz2
yum -y install flexyum -y install flex
yum -y install bison
yum -y install perl-DBD-Pg
cd slony1-1.2.20
./configure --with-pgconfigdir=/usr/local/pgsql/bin/
gmake all
gmake install
type slon to check installed
----------------------------------------------------------------
create schemma and table in both servers
CREATE SCHEMA ss1;
CREATE TABLE ss1.tt1(id INT NOT NULL,title text,PRIMARY KEY (id));
INSERT INTO ss1.tt1 (id,title) VALUES (22,'aaaaaa');
(do all above in both master and slaves)
master script 1
#!/bin/bash
# vim /my/slony/clustersetup.sh
export REPLICATIONUSER=postgres
export CLUSTERNAME=slony_cluster1
export MASTERDBNAME=PGRS1
export MASTERHOST=192.168.43.135
export MASTERPORT=5432
export MASTERDBA=postgres
export SLAVEDBNAME1=PGRS1
export SLAVEHOST1=192.168.43.136
export SLAVEPORT1=5432
export SLAVEDBA1=postgres
export PSQL=/usr/local/pgsql/bin/psql
export CREATEDB=/usr/local/pgsql/bin/createdb
export CREATELANG=/usr/local/pgsql/bin/createlang
export CREATEUSER=/usr/local/pgsql/bin/createuser
export PGDUMP=/usr/local/pgsql/bin/pg_dump
slonik <<_EOF_
cluster name = $CLUSTERNAME;
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER port=5432';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME1 host=$SLAVEHOST1 user=$REPLICATIONUSER port=5432';
#This init cluster only need when first run. comment when you add new slave and run this init cluster if not it will give an error
init cluster ( id=1, comment = 'slony_cluster1');
store node (id=2, comment = 'slony_cluster1', event node=1);
store path (server = 1, client = 2, conninfo='dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER port=5432');
store path (server = 2, client = 1, conninfo='dbname=$SLAVEDBNAME1 host=$SLAVEHOST1 user=$REPLICATIONUSER port=5432');
_EOF_
master script 2
#!/bin/bash
# vim /my/slony/settables.sh
export REPLICATIONUSER=postgres
export CLUSTERNAME=slony_cluster1
export MASTERDBNAME=PGRS1
export MASTERHOST=192.168.43.135
export MASTERPORT=5432
export MASTERDBA=postgres
export SLAVEDBNAME1=PGRS1
export SLAVEHOST1=192.168.43.136
export SLAVEPORT1=5432
export SLAVEDBA1=postgres
export PSQL=/usr/local/pgsql/bin/psql
export CREATEDB=/usr/local/pgsql/bin/createdb
export CREATELANG=/usr/local/pgsql/bin/createlang
export CREATEUSER=/usr/local/pgsql/bin/createuser
export PGDUMP=/usr/local/pgsql/bin/pg_dump
slonik <<_EOF_
cluster name = $CLUSTERNAME;
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER port=5432';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME1 host=$SLAVEHOST1 user=$REPLICATIONUSER port=5432';
create set (id=1, origin=1, comment='set1-slony_cluster1');
set add table (set id=1, origin=1, id=1, fully qualified name = 'ss1.tt1');
_EOF_
#!/bin/bash
# vim /my/slony/subscribe-set1-slony_cluster1.sh
export REPLICATIONUSER=postgres
export CLUSTERNAME=slony_cluster1
export MASTERDBNAME=PGRS1
export MASTERHOST=192.168.43.135
export MASTERPORT=5432
export MASTERDBA=postgres
export SLAVEDBNAME1=PGRS1
export SLAVEHOST1=192.168.43.136
export SLAVEPORT1=5432
export SLAVEDBA1=postgres
export PSQL=/usr/local/pgsql/bin/psql
export CREATEDB=/usr/local/pgsql/bin/createdb
export CREATELANG=/usr/local/pgsql/bin/createlang
export CREATEUSER=/usr/local/pgsql/bin/createuser
export PGDUMP=/usr/local/pgsql/bin/pg_dump
slonik <<_EOF_
cluster name = $CLUSTERNAME;
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER port=5432';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME1 host=$SLAVEHOST1 user=$REPLICATIONUSER port=5432';
subscribe set ( id = 1, provider = 1, receiver = 2, forward = no);
# subscribe set ( id = 1, provider = 1, receiver = 3, forward = no);
_EOF_
------------------------------
execute above 3 scrits in master without error
----------------------------
then start slony in slave
#!/bin/bash
/usr/local/pgsql/bin/slon slony_cluster1 -g256 -o10000 "dbname=PGRS1 user=postgres host=localhost port=5432" &
#/usr/local/pgsql/bin/slon slony_slave_cluster1 -g256 -o10000 "dbname=PGRS1 user=postgres host=localhost port=5432" &
# > /var/log/slony.log &
#echo "SLONY Master Started at $(date)" >> /var/log/sys/slony_daily_restart.txt
then start slony in master
#!/bin/bash
/usr/local/pgsql/bin/slon slony_cluster1 -g256 -o10000 "dbname=PGRS1 user=postgres host=localhost port=5432" &
# > /var/log/slony.log &
#echo "SLONY Master Started at $(date)" >> /var/log/slony_daily_restart.txt
------------------------------
NOTE :
////////////CREATE ROLE replicationuser LOGIN PASSWORD '123456' SUPERUSER INHERIT CREATEDB NOCREATEROLE;
if you create initially this
/usr/local/pgsql/bin/createuser -P replicationuser
pw, y y
we can use "replicationuser" inted of postgres (inside in all scripts all postgress username)
########PGRS1=# CREATE USER t WITH PASSWORD 'test_password' ;
########PGRS1=# ALTER USER t NOCREATEUSER CREATEDB;
-----------------------------
FUTHER MODIFICATIONS
#!/bin/bash
#vi /mysystem/myadmin/restart_slny_mstr
#/usr/local/pgsql/bin/slon slony_cluster2 -g256 -o10000 "dbname=PGRS1 user=replicationuser host=localhost port=5432" &
# > /var/log/slony.log &
#echo "SLONY Master Started at $(date)" >> /var/log/slony_daily_restart.txt
echo "Killing slony PIDs - $(/sbin/pidof slon)" >> /mysystem/myadmin/slony_daily_restart.txt
kill -term `/sbin/pidof slon`
#killall slon
sleep 20
sln=$(ps -e|grep slon |wc -l)
if [ $sln -eq 0 ]; then
#/usr/local/pgsql/bin/slon slony_cluster1 -g256 -o10000 "dbname=PGRS1 user=replicationuser host=localhost port=5432" &
/usr/local/pgsql/bin/slon slony_cluster1 -g256 -o10000 "dbname=PGRS1 user=replicationuser host=localhost port=5432" &
echo "SLONY full_replication restarted at $(date)" >> /mysystem/myadmin/slony_daily_restart.txt
sleep 10
echo "New slony PIDs - $(/sbin/pidof slon)" >> /mysystem/myadmin/slony_daily_restart.txt
echo "============================================" >> /mysystem/myadmin/slony_daily_restart.txt
fi
------------------------------
NOTE :
////////////CREATE ROLE replicationuser LOGIN PASSWORD '123456' SUPERUSER INHERIT CREATEDB NOCREATEROLE;
if you create initially this
/usr/local/pgsql/bin/createuser -P replicationuser
pw, y y
we can use "replicationuser" inted of postgres (inside in all scripts all postgress username)
########PGRS1=# CREATE USER t WITH PASSWORD 'test_password' ;
########PGRS1=# ALTER USER t NOCREATEUSER CREATEDB;
-----------------------------
FUTHER MODIFICATIONS
#!/bin/bash
#vi /mysystem/myadmin/restart_slny_mstr
#/usr/local/pgsql/bin/slon slony_cluster2 -g256 -o10000 "dbname=PGRS1 user=replicationuser host=localhost port=5432" &
# > /var/log/slony.log &
#echo "SLONY Master Started at $(date)" >> /var/log/slony_daily_restart.txt
echo "Killing slony PIDs - $(/sbin/pidof slon)" >> /mysystem/myadmin/slony_daily_restart.txt
kill -term `/sbin/pidof slon`
#killall slon
sleep 20
sln=$(ps -e|grep slon |wc -l)
if [ $sln -eq 0 ]; then
#/usr/local/pgsql/bin/slon slony_cluster1 -g256 -o10000 "dbname=PGRS1 user=replicationuser host=localhost port=5432" &
/usr/local/pgsql/bin/slon slony_cluster1 -g256 -o10000 "dbname=PGRS1 user=replicationuser host=localhost port=5432" &
echo "SLONY full_replication restarted at $(date)" >> /mysystem/myadmin/slony_daily_restart.txt
sleep 10
echo "New slony PIDs - $(/sbin/pidof slon)" >> /mysystem/myadmin/slony_daily_restart.txt
echo "============================================" >> /mysystem/myadmin/slony_daily_restart.txt
fi
================================
add new tables existing cluser (cluster1)
these 2 scripts run in master
1)create set in master , subscribe that in master
thats it
#!/bin/bash
# vim /my/slony/setnewtables.sh
export REPLICATIONUSER=replicationuser
export CLUSTERNAME=slony_cluster1
export MASTERDBNAME=PGRS1
export MASTERHOST=192.168.0.95
export MASTERPORT=5432
export MASTERDBA=replicationuser
export SLAVEDBNAME1=PGRS1
export SLAVEHOST1=192.168.0.74
export SLAVEPORT1=5432
export SLAVEDBA1=replicationuser
export PSQL=/usr/local/pgsql/bin/psql
export CREATEDB=/usr/local/pgsql/bin/createdb
export CREATELANG=/usr/local/pgsql/bin/createlang
export CREATEUSER=/usr/local/pgsql/bin/createuser
export PGDUMP=/usr/local/pgsql/bin/pg_dump
slonik <<_EOF_
cluster name = $CLUSTERNAME;
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER port=5432';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME1 host=$SLAVEHOST1 user=$REPLICATIONUSER port=5432';
create set (id=6, origin=1, comment='set1-slony_cluster1');
set add table (set id=6, origin=1, id=5001, fully qualified name = 'news2.tt1');
//you can add more tables for same set id, ex: set add table (set id=6, origin=1, id=5002, fully qualified name = 'news2.tt2');
#subscribe set(id=5, provider=1,receiver=3);
#merge set(id=1, add id=3,origin=1);
_EOF_
==========================
#!/bin/bash
# vim /my/slony/subscribe-newset1-slony_cluster1.sh
export REPLICATIONUSER=replicationuser
export CLUSTERNAME=slony_cluster1
export MASTERDBNAME=PGRS1
export MASTERHOST=192.168.0.95
export MASTERPORT=5432
export MASTERDBA=replicationuser
export SLAVEDBNAME1=PGRS1
export SLAVEHOST1=192.168.0.74
export SLAVEPORT1=5432
export SLAVEDBA1=replicationuser
#export SLAVEDBNAME2=PGRS1
#export SLAVEHOST2=192.168.0.99
#export SLAVEPORT2=5432
#export SLAVEDBA2=replicationuser
export PSQL=/usr/local/pgsql/bin/psql
export CREATEDB=/usr/local/pgsql/bin/createdb
export CREATELANG=/usr/local/pgsql/bin/createlang
export CREATEUSER=/usr/local/pgsql/bin/createuser
export PGDUMP=/usr/local/pgsql/bin/pg_dump
slonik <<_EOF_
cluster name = $CLUSTERNAME;
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER port=5432';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME1 host=$SLAVEHOST1 user=$REPLICATIONUSER port=5432';
#use this for 2nd slave node 3 admin conninfo = 'dbname=$SLAVEDBNAME2 host=$SLAVEHOST2 user=$REPLICATIONUSER port=5432';
subscribe set ( id = 6, provider = 1, receiver = 2, forward = no);
# this is use for 2nd slave db(3) to update, subscribe set ( id = 6, provider = 1, receiver = 3, forward = no);
_EOF_
-------------------------------------------------------------------
To unsubscribe tables
use these
unsubscribe set ( id = 6, provider = 1, receiver = 2, forward = no);
# unsubscribe set ( id = 6, receiver = 3);
-------------------------------------------------------------------
To DROP SET
export REPLICATIONUSER=replication_user
export CLUSTERNAME=LIKE OTHERS
export MASTERDBNAME=PGRS1
LIKE OTHERS
export SLAVEDBNAME1=PGRS1
LIKE OTHERS
export PSQL=/usr/local/pgsql/bin/psql
LIKE OTHERS
slonik <<_EOF_
cluster name = $CLUSTERNAME;
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER port=5432';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME1 host=$SLAVEHOST1 user=$REPLICATIONUSER port=5432';
drop set (origin=1, id=8);
_EOF_
---------------------------------------
DROP TABLES
all same as drop set
set drop table (origin=1, id=840);
--------------------------------------------------------------
DROP SCHEMA(CLUSTER)
DROP SCHEMA _slony_cluster1 CASCADE;//use this command both master and slave
then again can configure the cluster, then sets, then slony on(subscribe)
-----------------------------------------------------------------------------------------------------------
EXAMPLE FOR MODIFICATION
if want add new table existing schema(=set)
unscribe script
//////////use other body content as subscribescript as it is
unsubscribe set ( id = 6, receiver = 3);
then
set tables new script
//////////use other body content as set tables as it is
set add table (set id=6, origin=1, id=5005, fully qualified name = 'news2.tt2');
//now you have previous one (news2.tt1) and this one both tables
then subscribe again
subscribe set ( id = 6, provider = 1, receiver = 2, forward = no);
///////conclution////
you will have to first unscribe, then add or drop tables, then again subscribe(means connect to the slony).
add new tables existing cluser (cluster1)
these 2 scripts run in master
1)create set in master , subscribe that in master
thats it
#!/bin/bash
# vim /my/slony/setnewtables.sh
export REPLICATIONUSER=replicationuser
export CLUSTERNAME=slony_cluster1
export MASTERDBNAME=PGRS1
export MASTERHOST=192.168.0.95
export MASTERPORT=5432
export MASTERDBA=replicationuser
export SLAVEDBNAME1=PGRS1
export SLAVEHOST1=192.168.0.74
export SLAVEPORT1=5432
export SLAVEDBA1=replicationuser
export PSQL=/usr/local/pgsql/bin/psql
export CREATEDB=/usr/local/pgsql/bin/createdb
export CREATELANG=/usr/local/pgsql/bin/createlang
export CREATEUSER=/usr/local/pgsql/bin/createuser
export PGDUMP=/usr/local/pgsql/bin/pg_dump
slonik <<_EOF_
cluster name = $CLUSTERNAME;
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER port=5432';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME1 host=$SLAVEHOST1 user=$REPLICATIONUSER port=5432';
create set (id=6, origin=1, comment='set1-slony_cluster1');
set add table (set id=6, origin=1, id=5001, fully qualified name = 'news2.tt1');
//you can add more tables for same set id, ex: set add table (set id=6, origin=1, id=5002, fully qualified name = 'news2.tt2');
#subscribe set(id=5, provider=1,receiver=3);
#merge set(id=1, add id=3,origin=1);
_EOF_
==========================
#!/bin/bash
# vim /my/slony/subscribe-newset1-slony_cluster1.sh
export REPLICATIONUSER=replicationuser
export CLUSTERNAME=slony_cluster1
export MASTERDBNAME=PGRS1
export MASTERHOST=192.168.0.95
export MASTERPORT=5432
export MASTERDBA=replicationuser
export SLAVEDBNAME1=PGRS1
export SLAVEHOST1=192.168.0.74
export SLAVEPORT1=5432
export SLAVEDBA1=replicationuser
#export SLAVEDBNAME2=PGRS1
#export SLAVEHOST2=192.168.0.99
#export SLAVEPORT2=5432
#export SLAVEDBA2=replicationuser
export PSQL=/usr/local/pgsql/bin/psql
export CREATEDB=/usr/local/pgsql/bin/createdb
export CREATELANG=/usr/local/pgsql/bin/createlang
export CREATEUSER=/usr/local/pgsql/bin/createuser
export PGDUMP=/usr/local/pgsql/bin/pg_dump
slonik <<_EOF_
cluster name = $CLUSTERNAME;
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER port=5432';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME1 host=$SLAVEHOST1 user=$REPLICATIONUSER port=5432';
#use this for 2nd slave node 3 admin conninfo = 'dbname=$SLAVEDBNAME2 host=$SLAVEHOST2 user=$REPLICATIONUSER port=5432';
subscribe set ( id = 6, provider = 1, receiver = 2, forward = no);
# this is use for 2nd slave db(3) to update, subscribe set ( id = 6, provider = 1, receiver = 3, forward = no);
_EOF_
-------------------------------------------------------------------
To unsubscribe tables
use these
unsubscribe set ( id = 6, provider = 1, receiver = 2, forward = no);
# unsubscribe set ( id = 6, receiver = 3);
-------------------------------------------------------------------
To DROP SET
export REPLICATIONUSER=replication_user
export CLUSTERNAME=LIKE OTHERS
export MASTERDBNAME=PGRS1
LIKE OTHERS
export SLAVEDBNAME1=PGRS1
LIKE OTHERS
export PSQL=/usr/local/pgsql/bin/psql
LIKE OTHERS
slonik <<_EOF_
cluster name = $CLUSTERNAME;
node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER port=5432';
node 2 admin conninfo = 'dbname=$SLAVEDBNAME1 host=$SLAVEHOST1 user=$REPLICATIONUSER port=5432';
drop set (origin=1, id=8);
_EOF_
---------------------------------------
DROP TABLES
all same as drop set
set drop table (origin=1, id=840);
--------------------------------------------------------------
DROP SCHEMA(CLUSTER)
DROP SCHEMA _slony_cluster1 CASCADE;//use this command both master and slave
then again can configure the cluster, then sets, then slony on(subscribe)
-----------------------------------------------------------------------------------------------------------
EXAMPLE FOR MODIFICATION
if want add new table existing schema(=set)
unscribe script
//////////use other body content as subscribescript as it is
unsubscribe set ( id = 6, receiver = 3);
then
set tables new script
//////////use other body content as set tables as it is
set add table (set id=6, origin=1, id=5005, fully qualified name = 'news2.tt2');
//now you have previous one (news2.tt1) and this one both tables
then subscribe again
subscribe set ( id = 6, provider = 1, receiver = 2, forward = no);
///////conclution////
you will have to first unscribe, then add or drop tables, then again subscribe(means connect to the slony).
###################Postgres 9.5#####################
do automation
mkdir /var/lib/pgsql/9.5/data/ -p
mkdir /pg_xlog
mkdir /pg_log
########dont do this if going streaming--->########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 do this if going streaming---> ########crete PGRS1 on slave only
/var/lib/pgsql/repmgr.conf (slave)
cluster=clustertest
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=clustertest
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 pg_hba.conf
in master -->/usr/pgsql-9.5/bin/pg_ctl -D /var/lib/pgsql/9.5/data/ start
in master
repmgr -f /var/lib/pgsql/repmgr.conf master register
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
remove from cluster (replication)
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'
=======
vim /etc/repmgr/9.5/repmgr.conf
conninfo='host=localhost dbname=repmgr_db user=repmgr_usr'
========
stop replication in slave with read/write
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();
PG 9.5 slony
http://dpheenatigala.postach.io/post/install-postgres-9-5-and-slony1-2-2-on-centos-7-or-centos-6
wget http://main.slony.info/downloads/2.2/source/slony1-2.2.4.tar.bz2tar -xvjf slony1-2.2.4.tar.bz2
find / -type f -name 'pg_config'
./configure --with-perltools --with-pgconfigdir=/usr/pgsql-9.5/bin
gmake all
gmake install
Defying Pyramid PostgreSQL Connection Issue? Contact to PostgreSQL Remote Database Service to settle it
ReplyDeleteWith the help of Postgres SQL Support for Linux or Postgres SQL Support for Windows you can without a lot of an extend screen the execution of your Postgres database and perceive if there is any issue. Well in case you are dumbfounding about your affiliation issue by then quickly make our medicinal move and resolve main problems even before it hits your end customers. Our PostgreSQL Relational Database Service can without a doubt track the execution of your entire Postgres condition.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
Issue with PostgreSQL? Contact to Postgres SQL Support for Windows to settle issue
ReplyDeleteAssuming over and over you are attempting to stack pg_trgm module to PostgreSQL however you found that something turning out badly then you have one better choice to handle this specific issue and that choice is Cognegic's Postgres SQL Support for Linux or PostgreSQL Remote Database Service. Here our accomplished group can assist you with securing, keep up and deal with your whole Postgres SQL condition. So straightforwardly contact to Remote PostgreSQL Performance Service and bring the best help.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
Best way to Recover PostgreSQL 8.0 Database with Cognegic’s DB Recovery Support
ReplyDeleteIn the event that you discover any multifaceted nature or trouble to get back your PostgreSQL 8.0 database then first you should check does the document really exist? You need to likewise check your hostile to infection bundles and take a stab at associating as client Postgres to database Postgres or database layout 1. Indeed, if still exists a similar issue at that point rapidly contact to DB Recovery Services or DB Exchange Database Recovery. Here you will get the whole Backup Recovery of your Postgres database and some different databases.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
Contact to PostgreSQL Remote Database Service If PostgreSQL 9.5 not Start
ReplyDeleteFor the most part, new Postgres SQL clients need to negate this issue and they are not set up to begin their PostgreSQL 9.5 satisfactorily. Ensure, by two different ways you can manage that issue, first either run with physically or pick Cognegic's Postgres SQL Support for Linux or PostgreSQL Relational Database Service. By physically you can likewise settle this issue yet it requires great learning and abilities to handle this Postgres issue. Be that as it may, if not then we at Cognegic settle your issues inside the assessed day and age.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
Unfit to Start Postgres After Changing to Nologin? Contact to Postgres SQL Support for Windows
ReplyDeleteThere might be where you find that your Postgres database can't begin as a result of some specialized issues. Be that as it may, with Cognegic's PostgreSQL Remote Database Service or Postgres SQL Support for Linux. In the event that you found that you Postgres Database is running gradually or neglecting to serve questions for any reason at that point don't squander your valuable time in scanning for another help supplier. Basically contact our specialized specialists and get the most confided in help which you never found in some other help.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801
hamiiiiiiiiiiiiii
ReplyDelete