Setting up PGCluster: Difference between revisions

From Alpine Linux
(Created page with 'PGCluster is a fork of PostgreSQL that allows multi-master replication. This document explains how to set it up. == Notes == * All hosts in conf files are referenced by name - ...')
 
(replace /etc/init.d with rc-service)
 
(11 intermediate revisions by 5 users not shown)
Line 1: Line 1:
{{Obsolete|This package is no longer a part of alpine, upstream is NOT actively maintained since early 2013}}
PGCluster is a fork of PostgreSQL that allows multi-master replication. This document explains how to set it up.
PGCluster is a fork of PostgreSQL that allows multi-master replication. This document explains how to set it up.


Line 10: Line 12:


First install the packages
First install the packages
  apk add pgcluster pgcluster-client
 
{{Cmd|apk add pgcluster pgcluster-client}}


For recovery, you'll need ssh and rsync
For recovery, you'll need ssh and rsync
  apk add openssh rsync
 
{{Cmd|apk add openssh rsync}}


== Configuration ==
== Configuration ==
Line 22: Line 26:


Create the database files (same as for postgresql)
Create the database files (same as for postgresql)
  /etc/init.d/pgcluster setup
{{Cmd|rc-service pgcluster setup}}


Edit the config files in the data path found above (usually /var/lib/posgresql/pgcluster)
Edit the config files in the data path found above (usually /var/lib/posgresql/pgcluster)
Line 31: Line 35:


To start it manually:
To start it manually:
  su postgres
{{Cmd|su postgres
  cd /var/lib/postgresql/pgcluster
cd /var/lib/postgresql/pgcluster
  pg_ctl -D . start
pg_ctl -D . start}}
or  
 
  postgres -D .
or
{{Cmd|postgres -D .}}


==== postgresql.conf ====
==== postgresql.conf ====


This is the standard postgresql config file. Just make sure of this:
This is the standard postgresql config file. Just make sure of this:
  listen_addresses='*'
listen_addresses='*'


You might also want to changed the logging settings.
You might also want to changed the logging settings.


==== pg_hba.conf ====
==== pg_hba.conf ====
This is also a standard postgresql config file. Make sure all the hosts involved trust each other. Here's an example line you can use to get a test cluster running (it's a bit permissive for production use):
This is also a standard postgresql config file. Make sure all the hosts involved trust each other. Here's an example line you can use to get a test cluster running (it's a bit permissive for production use):
  host    all        all        192.168.0.0/16        trust
host    all        all        192.168.0.0/16        trust


==== cluster.conf ====
==== cluster.conf ====


This file is new with PGCLuster - it configures the patched database instance.
This file is new with PGCLuster - it configures the patched database instance.


Add a block like this for each replication server:
Add a block like this for each replication server:
  <Replicate_Server_Info>
<Replicate_Server_Info>
  <Host_Name>    pgrep1  </Host_Name>
<Host_Name>    pgrep1  </Host_Name>
  <Port>          8001    </Port>
<Port>          8001    </Port>
  <Recovery_Port> 8101    </Recovery_Port>
<Recovery_Port> 8101    </Recovery_Port>
  </Replicate_Server_Info>
</Replicate_Server_Info>


Also add the following:
Also add the following:
  <Host_Name> pgdb1 </Host_Name>
<Host_Name> pgdb1 </Host_Name>
  <Recovery_Port> 7001 </Recovery_Port>
<Recovery_Port> 7001 </Recovery_Port>
  <Rsync_Path> /usr/bin/rsync </Rsync_Path>
<Rsync_Path> /usr/bin/rsync </Rsync_Path>
  <Rsync_Option> ssh -1 </Rsync_Option>
<Rsync_Option>         ssh -1 </Rsync_Option>
  <Rsync_Compress> yes </Rsync_Compress>
<Rsync_Compress> yes </Rsync_Compress>
  <Rsync_Timeout> 10min </Rsync_Timeout>
<Rsync_Timeout> 10min </Rsync_Timeout>
  <Rsync_Bwlimit> 0KB </Rsync_Bwlimit>
<Rsync_Bwlimit> 0KB </Rsync_Bwlimit>
  <Pg_Dump_Path> /var/lib/pgcluster/pg_dump </Pg_Dump_Path>
<Pg_Dump_Path>         /usr/bin/pg_dump         </Pg_Dump_Path>
  <Ping_Path> /bin/ping </Ping_Path>
<Ping_Path> /bin/ping </Ping_Path>
  <When_Stand_Alone> read_only </When_Stand_Alone>
<When_Stand_Alone> read_only </When_Stand_Alone>
  <Replication_Timeout> 1min </Replication_Timeout>
<Replication_Timeout> 1min </Replication_Timeout>
  <LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
  <LifeCheck_Interval> 11s </LifeCheck_Interval>
<LifeCheck_Interval> 11s </LifeCheck_Interval>
And this bit is optional:
And this bit is optional:
  <Not_Replicate_Info>
<Not_Replicate_Info>
  <DB_Name> test_db </DB_Name>
<DB_Name> test_db </DB_Name>
  # <Table_Name> log_table </Table_Name>
# <Table_Name> log_table </Table_Name>
  </Not_Replicate_Info>
</Not_Replicate_Info>
 


=== pgreplicate ===
=== pgreplicate ===
Line 87: Line 90:


To start it manually:
To start it manually:
  su postgres
{{Cmd|su postgres
  cd /var/lib/postgresql/pgcluster
cd /var/lib/postgresql/pgcluster
  pgreplicate -D .  
pgreplicate -D .}}


If you want debug output change the last line to:
If you want debug output change the last line to:
  pgreplicate -D . -n -v
{{Cmd|pgreplicate -D . -n -v}}


==== pgreplicate.conf ====
==== pgreplicate.conf ====


Add a block like this for each database (also called cluster) instance:
Add a block like this for each database (also called cluster) instance:
  <Cluster_Server_Info>
<Cluster_Server_Info>
    <Host_Name>pgdb1</Host_Name>
    <Host_Name>pgdb1</Host_Name>
    <Port>5432</Port>
    <Port>5432</Port>
    <Recovery_Port>7001</Recovery_Port>
    <Recovery_Port>7001</Recovery_Port>
  </Cluster_Server_Info>
</Cluster_Server_Info>
Add this block for your load balancer instance:
Add this block for your load balancer instance:
  <LoadBalance_Server_Info>
<LoadBalance_Server_Info>
    <Host_Name>pglb1</Host_Name>
    <Host_Name>pglb1</Host_Name>
    <Recovery_Port>6001</Recovery_Port>
    <Recovery_Port>6001</Recovery_Port>
  </LoadBalance_Server_Info>
</LoadBalance_Server_Info>
And add this to describe the replicator instance you're configuring:
And add this to describe the replicator instance you're configuring:
  <Host_Name>pgrep1</Host_Name>
<Host_Name>pgrep1</Host_Name>
  <Replication_Port>8002</Replication_Port>
<Replication_Port>8002</Replication_Port>
  <Recovery_Port>8101</Recovery_Port>
<Recovery_Port>8101</Recovery_Port>
  <RLog_Port>8301</RLog_Port>
<RLog_Port>8301</RLog_Port>
  <Response_Mode>normal</Response_Mode>
<Response_Mode>normal</Response_Mode>
  <Use_Replication_Log>no</Use_Replication_Log>
<Use_Replication_Log>no</Use_Replication_Log>
Optional Logging info:
Optional Logging info:
  <Log_File_Info>
<Log_File_Info>
          <File_Name>pg_log/pgreplicate.log</File_Name>
        <File_Name>pg_log/pgreplicate.log</File_Name>
  </Log_File_Info>
</Log_File_Info>


=== pgreplicate ===
=== pglb ===


This process provides load balancing.
This process provides load balancing.


To start it manually:
To start it manually:
  su postgres
{{Cmd|su postgres
  cd /var/lib/postgresql/pgcluster
cd /var/lib/postgresql/pgcluster
  pglb -D .  
pglb -D .}}


If you want debug output change the last line to:
If you want debug output change the last line to:
  pglb -D . -n -v


{{Cmd|pglb -D . -n -v}}


==== pglb.conf ====
==== pglb.conf ====
Similar to pgreplicate, add the following block for each database instance:
Similar to pgreplicate, add the following block for each database instance:
  <Cluster_Server_Info>
<Cluster_Server_Info>
    <Host_name>pgdb1</Host_Name>
    <Host_name>pgdb1</Host_Name>
    <Port>5432</Port>
    <Port>5432</Port>
    <Max_Connect>30</Max_Connect>
    <Max_Connect>30</Max_Connect>
  </Cluster_Server_Info>
</Cluster_Server_Info>
Add the following for the load balancer:
Add the following for the load balancer:
  <Host_Name>pglb1</Host_Name>
<Host_Name>pglb1</Host_Name>
  <Backend_Socket_Dir>/tmp</backend_socket_dir>
<Backend_Socket_Dir>/tmp</backend_socket_dir>
  <Receive_Port>5433</Receive_Port>
<Receive_Port>5433</Receive_Port>
  <Recovery_Port>6001</Recovery_Port>
<Recovery_Port>6001</Recovery_Port>
  <Max_Cluster_Num>128</Max_Cluster_Num>
<Max_Cluster_Num>128</Max_Cluster_Num>
  <Use_Connection_Pooling>no</Use_Connection_Pooling>
<Use_Connection_Pooling>no</Use_Connection_Pooling>
Optional Logging info:
Optional Logging info:
  <Log_File_Info>
<Log_File_Info>
          <File_Name>pg_log/pgreplicate.log</File_Name>
        <File_Name>pg_log/pgreplicate.log</File_Name>
  </Log_File_Info>
</Log_File_Info>
 


== Recovery ==
== Recovery ==
Line 157: Line 159:


== References ==
== References ==
http://pgfoundry.org/projects/pgcluster/
* https://wiki.postgresql.org/wiki/PgCluster
http://pgcluster.i-logic.hu/cgi-bin/trac.cgi
* [https://web.archive.org/web/20090830041417/http://odyssi.blogspot.com/2007/08/postgresql-replication-with-pgcluster.html PostgreSQL Replication with PGCluster (via archive.org)]
http://pgcluster.projects.postgresql.org/
 
http://odyssi.blogspot.com/2007/08/postgresql-replication-with-pgcluster.html
[[Category:SQL]]

Latest revision as of 10:41, 17 November 2023

This material is obsolete ...

This package is no longer a part of alpine, upstream is NOT actively maintained since early 2013 (Discuss)

PGCluster is a fork of PostgreSQL that allows multi-master replication. This document explains how to set it up.

Notes

  • All hosts in conf files are referenced by name - these must resolve properly to thier IPs.
  • PGCluster xml conf files are case sensitive
  • PGCluster 1.9.0 is based off PostgreSQL 8.3

Installation

First install the packages

apk add pgcluster pgcluster-client

For recovery, you'll need ssh and rsync

apk add openssh rsync

Configuration

Edit /etc/hosts file if necessary so all names can be resolved.

Check the data path in /etc/conf.d/pgcluster. Also check here to see which processes the init script will start on this host.

Create the database files (same as for postgresql)

rc-service pgcluster setup

Edit the config files in the data path found above (usually /var/lib/posgresql/pgcluster)

postgres (postmaster)

postgres has 3 configuration files of interest here: postgresql.conf, pg_hba.conf, cluster.conf.

To start it manually:

su postgres cd /var/lib/postgresql/pgcluster pg_ctl -D . start

or

postgres -D .

postgresql.conf

This is the standard postgresql config file. Just make sure of this:

listen_addresses='*'

You might also want to changed the logging settings.

pg_hba.conf

This is also a standard postgresql config file. Make sure all the hosts involved trust each other. Here's an example line you can use to get a test cluster running (it's a bit permissive for production use):

host    all         all         192.168.0.0/16        trust

cluster.conf

This file is new with PGCLuster - it configures the patched database instance.

Add a block like this for each replication server:

<Replicate_Server_Info>
	<Host_Name>     pgrep1  </Host_Name>
	<Port>          8001    </Port>
	<Recovery_Port> 8101    </Recovery_Port>
</Replicate_Server_Info>

Also add the following:

<Host_Name>		pgdb1				</Host_Name>
<Recovery_Port>	7001				</Recovery_Port>
<Rsync_Path>		/usr/bin/rsync			</Rsync_Path>
<Rsync_Option>	        ssh -1				</Rsync_Option>
<Rsync_Compress>	yes				</Rsync_Compress>
<Rsync_Timeout>	10min				</Rsync_Timeout>
<Rsync_Bwlimit>	0KB				</Rsync_Bwlimit>
<Pg_Dump_Path>	        /usr/bin/pg_dump	        </Pg_Dump_Path>
<Ping_Path>		/bin/ping			</Ping_Path>
<When_Stand_Alone>	read_only			</When_Stand_Alone>
<Replication_Timeout>	1min				</Replication_Timeout>
<LifeCheck_Timeout>	3s				</LifeCheck_Timeout>
<LifeCheck_Interval>	11s				</LifeCheck_Interval>

And this bit is optional:

<Not_Replicate_Info>
	<DB_Name>		test_db		</DB_Name>
#	<Table_Name>		log_table	</Table_Name>
</Not_Replicate_Info>

pgreplicate

This process replicates to the other servers.

To start it manually:

su postgres cd /var/lib/postgresql/pgcluster pgreplicate -D .

If you want debug output change the last line to:

pgreplicate -D . -n -v

pgreplicate.conf

Add a block like this for each database (also called cluster) instance:

<Cluster_Server_Info>
   <Host_Name>pgdb1</Host_Name>
   <Port>5432</Port>
   <Recovery_Port>7001</Recovery_Port>
</Cluster_Server_Info>

Add this block for your load balancer instance:

<LoadBalance_Server_Info>
   <Host_Name>pglb1</Host_Name>
   <Recovery_Port>6001</Recovery_Port>
</LoadBalance_Server_Info>

And add this to describe the replicator instance you're configuring:

<Host_Name>pgrep1</Host_Name>
<Replication_Port>8002</Replication_Port>
<Recovery_Port>8101</Recovery_Port>
<RLog_Port>8301</RLog_Port>
<Response_Mode>normal</Response_Mode>
<Use_Replication_Log>no</Use_Replication_Log>

Optional Logging info:

<Log_File_Info>
        <File_Name>pg_log/pgreplicate.log</File_Name>
</Log_File_Info>

pglb

This process provides load balancing.

To start it manually:

su postgres cd /var/lib/postgresql/pgcluster pglb -D .

If you want debug output change the last line to:

pglb -D . -n -v

pglb.conf

Similar to pgreplicate, add the following block for each database instance:

<Cluster_Server_Info>
   <Host_name>pgdb1</Host_Name>
   <Port>5432</Port>
   <Max_Connect>30</Max_Connect>
</Cluster_Server_Info>

Add the following for the load balancer:

<Host_Name>pglb1</Host_Name>
<Backend_Socket_Dir>/tmp</backend_socket_dir>
<Receive_Port>5433</Receive_Port>
<Recovery_Port>6001</Recovery_Port>
<Max_Cluster_Num>128</Max_Cluster_Num>
<Use_Connection_Pooling>no</Use_Connection_Pooling>

Optional Logging info:

<Log_File_Info>
        <File_Name>pg_log/pgreplicate.log</File_Name>
</Log_File_Info>

Recovery

...

References