SQL Server Replication (Part 4) : Create Publication

Let's Open SQL Server Management Studio on Server Database Primary, here step by step to create Publication :

1. Expand Folder Replication, right click New Publication

2. Click Next  

3. Choose the database, click next

4. Choose Publication Type : Transactional Publication

5. Choose objects , then click next

6. If we want to filter table rows, clik button add, if don't need, click next

7. Check first option : create a snapshot immediately , click next

8. On Agent Security, click button security settings

9. On Domain or Machine account under which the Snapshot Agent process will run, choose : "Run under the SQL Server Agent service account", on connect Publisher, specify SQL Server Login, click OK

10. Now we can see "SQL Server Agent account" on Snapshot Agent and on Log Reader Agent

11. Check only "Create the publication" and click OK

12. Specify Publication name, then click finish

13. Creating Publication success

SQL Server Replication (Part 3) : Configure Permission Folder

After distribution has been configured, then we need to setup permission folder...

Go to path : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL right click Folder ReplData, click properties, open tab security.

We find path C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\RepData from snapshot folder which has been specified on previous part (configure distribution)

1. Click on SQLServerMSSQLUser, then click edit

 

2. After click edit, then click add

3. Click Advanced

4. Click Object Types, we need only search groups

5. Check Group only, then click OK

6. Click Find Now, then choose SQLServerSQLAgentUser, click OK

7.  Click OK

8. Give Full Access for SQLServerSQLAgentUser

 

After this part, we go ahead to part 4 : Create Publication

SQL Server Replication (Part 2) : Configure Distribution

On this part, we will configure distribution, Open SQL Server Management Studio,

1. Right Click Folder Replication, click Configure Distribution

2. Click Next

3. Choose option first : <hostname> will act its own Distributor, SQL Server will create a distribution database and log, click Next

 4. Browse Location for Snapshot Folder, then click next

5. Specify Distribution Database Name, location log & Database file

6. We will our server will be act as publisher, click next

7. Check configure Distribution,  click next

8.  Verify the result, if our choices already OK, then we can clik Finish

9. Configuration Distribution Success

SQL Server Replication (Part 1) : Preparation

I have 2 servers : (already installed SQL Server 2008R2 Machine on Windows Server 2008R2)

Server Primary : IP 10.10.10.21 hostname WIN-92JP95SLO0R

Server Backup : IP 10.10.10.12 hostname WIN-FRHI57MGALL

On this part 1, before setup replication on SQL Server Management Studio, we need preparation :

1. Config hosts file on path

C:\Windows\System32\drivers\etc\hosts, append this line on both servers

10.10.10.21        WIN-92JP95SLO0R
10.10.10.12        WIN-FRHI57MGALL

Why we need do this ? Because when create subscriber, we need to connect SQL Server Machine by hostname, not IP, when we input IP Address, we will get error message like this : http://willyristanto.blogspot.com/2015/04/verfiy-that-instance-name-is-correct.html

2. Setup SQL Server Agent to start automatically

Open services.msc, right click on SQL Server Agent, click properties

Choose Startup Type : Automatic, then click start

Okay, part 1 finish, then we go to part 2

PostgreSQL : Load Balancing and Replication with PgPool II-92 (Part 3)

Previous : Part 2  http://willyristanto.blogspot.com/2014/10/clustering-postgresql-with-pgpool-ii-92_15.html

12. Testing Replication

Now, we can test replication via application. First, make sure connection to pgpool is OK

Execute query:
CREATE TABLE "public"."test_1" (
    "id"      int NULL primary key,
    "name"    varchar(25) NULL
    )

INSERT INTO "public"."test_1"("id", "name")
VALUES(1, 'one')
;
INSERT INTO "public"."test_1"("id", "name")
VALUES(2, 'two')
;

when we check both on server postgres1 and server postgres2

references :
http://www.pgpool.net/docs/latest/tutorial-en.html#install
http://www.keyup.eu/en/blog/89-replication-and-load-balancing-with-postgresql-and-pgpool2
http://www.milliondollarserver.com/install-postgresql-9-2-on-oracle-enterprise-linux-centos-redhat-6-3/

PostgreSQL : Load Balancing and Replication with PgPool II-92 (Part 2)

Part 1 : http://willyristanto.blogspot.com/2014/10/clustering-postgresql-with-pgpool-ii-92.html

5. Install pgpool
we can download pgpool from here :
http://www.pgpool.net/mediawiki/index.php/Downloads

[root@postgres1 pgp]# ls
pgpool-II-92-3.3.2-1.rhel6.x86_64.rpm
pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm
pgpool-II-92-devel-3.3.2-1.rhel6.x86_64.rpm
[root@postgres1 pgp]#yum install pgpool-II-92-3.3.2-1.rhel6.x86_64.rpm
[root@postgres1 pgp]#yum install pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm
[root@postgres1 pgp]#yum install pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm

6. Edit file pgpool-recovery.sql and pgpool-regclass.sql
[root@postgres1 pgp]# nano /usr/pgsql-9.2/share/extension/pgpool-recovery.sql
CREATE OR REPLACE FUNCTION pgpool_recovery(text, text, text)
RETURNS bool
AS '/usr/pgsql-9.2/lib/pgpool-recovery', 'pgpool_recovery'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION pgpool_remote_start(text, text)
RETURNS bool
AS '/usr/pgsql-9.2/lib/pgpool-recovery', 'pgpool_remote_start'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION pgpool_pgctl(text, text)
RETURNS bool
AS '/usr/pgsql-9.2/lib/pgpool-recovery', 'pgpool_pgctl'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION pgpool_switch_xlog(text)
RETURNS text
AS '/usr/pgsql-9.2/lib/pgpool-recovery', 'pgpool_switch_xlog'
LANGUAGE C STRICT;

make sure file pgpool-recovery.so is exist in the correct path

[root@postgres1 lib]# nano /usr/pgsql-9.2/share/extension/pgpool-regclass.sql
CREATE OR REPLACE FUNCTION pg_catalog.pgpool_regclass(cstring)
RETURNS oid
AS '/usr/pgsql-9.2/lib/pgpool-regclass', 'pgpool_regclass'
LANGUAGE C STRICT;
make sure file pgpool-regclass.so is exist in the correct path

7. Create Database
[root@postgres1 pgp]# su - postgres
-bash-4.1$ psql
psql (9.2.9)
Type "help" for help.

postgres=# create database mydb owner postgres;
CREATE DATABASE
postgres=#

8. Execute function on database
-bash-4.1$ psql -h localhost -U postgres -W -f /usr/pgsql-9.2/share/extension/pgpool-recovery.sql mydb                                                      
Password for user postgres:
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION

-bash-4.1$ psql -h localhost -U postgres -W -f /usr/pgsql-9.2/share/extension/pgpool-regclass.sql mydb
Password for user postgres:
CREATE FUNCTION
-bash-4.1$

9. Install pgpool on pgpool server

[root@pgpserv pgp]# ls
pgpool-II-92-3.3.2-1.rhel6.x86_64.rpm
pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm
pgpool-II-92-devel-3.3.2-1.rhel6.x86_64.rpm
[root@pgpserv pgp]#yum install pgpool-II-92-3.3.2-1.rhel6.x86_64.rpm
[root@pgpserv pgp]#yum install pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm
[root@pgpserv pgp]#yum install pgpool-II-92-debuginfo-3.3.2-1.rhel6.x86_64.rpm

Login to server postgres database, we need to know passwd from pg_shadow from user postgres
mydb=# select passwd from pg_shadow where usename = 'postgres';
               passwd
-------------------------------------
 md53175bce1d3201d16594cebf9d7eb3f9d

back to pgpool server, edit file pcp.conf
#nano /etc/pgpool-II-92/pcp.conf

append line
postgres:3175bce1d3201d16594cebf9d7eb3f9d

please note that we get value 3175bce1d3201d16594cebf9d7eb3f9d from passwd_from pg_shadow (string "md5" is omitted)

10. Edit pgpool.conf
[root@pgpserv pgpool-II-92]# nano /etc/pgpool-II-92/pgpool.conf

listen_addresses = '*'
port = 9999
backend_hostname0 = '192.168.112.142'                              
backend_port0 = 5432                            
backend_weight0 = 1                             
backend_data_directory0 = '/var/lib/pgsql/9.2/data'                              
backend_flag0 = 'ALLOW_TO_FAILOVER'                                  
backend_hostname1 = '192.168.112.140'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.2/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
connection_cache = on
replication_mode = true
load_balance_mode = true

11. Start pgpool
we can start pgpool by simply command
#pgpool

stop pgpool
#pgpool stop
or we can stop immediately
#pgpool -m fast stop

Next Part 3 :  http://willyristanto.blogspot.com/2014/10/clustering-postgresql-with-pgpool-ii-92_94.html

PostgreSQL : Load Balancing and Replication with PgPool II-92 (Part 1)

 Step by step configuration setup load balancing and replication postgresql with pgpool.

1. Install Postgresql 
install both on 192.168.112.142 and 192.168.112.140

# sed -i '7iexclude=postgresql*' /etc/yum.repos.d/public-yum-ol6.repo
# rpm -ivh http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
# yum install postgresql92 postgresql92-libs postgresql92-server postgresql92-devel
# chkconfig postgresql-9.2 on
# service postgresql-9.2 initdb
# service postgresql-9.2 start

2. Edit /etc/hosts on each server
192.168.112.142 postgres1
192.168.112.140 postgres2
192.168.112.141 pgpserv

3. Edit postgresql.conf
nano /var/lib/pgsql/9.2/data/postgresql.conf

modify listen_address and port
listen_addresses = '*'                
port = 5432                            

4. Edit pg_hba.conf

Change all method to trust

Restart service postgresql to take effect
[root@postgres1 ~]# /etc/init.d/postgresql-9.2 restart
Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                             [  OK  ]

Next : Part 2 http://willyristanto.blogspot.com/2014/10/clustering-postgresql-with-pgpool-ii-92_15.html