Setting up Slony-I with pgAdmin

pgAdmin has had the ability to create and manage Slony-I replication clusters for some time, however it is designed to allow the user to work directly with the lower-level Slony concepts such as listens and paths. This is pretty flexible, but isn't particularly user-friendly. We are hoping to include some wizards in the future to make it much easier to setup and modify clusters, but in the meantime here's a long overdue walkthrough using the existing features to create a 3 node replication cluster.

In this example, a master server is setup with two direct slaves. This example was written and tested using Slony-I v1.2.11 and PostgreSQL 8.2.5, running on a single Windows XP machine. The PostgreSQL pgbench utility is used to generate the test schema and workload.

1) Create 3 databases, master, slave1 and slave2 and ensure pl/pgsql is setup in each.

2) Create a pgbench schema in the master database:

> pgbench -i -U postgres master

3) Add a primary key called history_pkey to the history table on the tid, bid and aid columns.

4) Create a schema-only dump of the master database, and load it into slave1 and slave2:

> pg_dump -s -U postgres master > schema.sql
> psql -U postgres slave1 < psql -U postgres slave2

5) Create Slony config files for each slon engine (daemon on *nix). The files should contain just the following two lines:

cluster_name='pgbench'
conn_info='host=127.0.0.1 port=5432 user=postgres dbname=master'

Create a file for each database, adjusting the dbname parameter as required and adding any other connection options that may be needed. (Windows only) Install the Slony-I service: > slon -regservice Slony-I

6) Register each of the engines (this is only necessary on Windows - on *nix the slon daemons may be started individually and given the path to the config file on the command line using the -f option):

> slon -addengine Slony-I C:\slony\master.conf
> slon -addengine Slony-I C:\slony\slave1.conf
> slon -addengine Slony-I C:\slony\slave2.conf

7) In pgAdmin under the Replication node in the master database, create a new Slony-I cluster using the following options:

Join existing cluster: Unchecked
Cluster name: pgbench
Local node: 1 Master node
Admin node: 99 Admin node

8) Under the Replication node, create a Slony-I cluster in each of the slave databases using the following options:

Join existing cluster: Checked
Server: <Select the server containing the master database>
Database: master
Cluster name: pgbench
Local node: 10 Slave node 1
Admin node: 99 - Admin node

Join existing cluster: Checked
Server: <Select the server containing the master database>
Database: master
Cluster name: pgbench
Local node: 20 Slave node 2
Admin node: 99 - Admin node

9) Create Paths on the master to both slaves, and on each slave back to the master. Create the paths under each node on the master, using the connection strings specified in the slon config files. Note that future restructuring of the cluster may require additional paths to be defined.

10) Create a Replication Set on the master using the following settings:

ID: 1
Comment: pgbench set

11) Add the tables to the replication set using the following settings:

Table: public.accounts
ID: 1
Index: accounts_pkey

Table: public.branches
ID: 2
Index: branches_pkey

Table: public.history
ID: 3
Index: history_pkey

Table: public.tellers
ID: 4
Index: tellers_pkey

12) On the master node, create a new subscription for each slave using the following options:

Origin: 1
Provider: 1 - Master node
Receiver: 10 - Slave node 1

Origin: 1
Provider: 1 - Master node
Receiver: 20 - Slave node 2

13) Start the slon service (or daemons on *nix):

> net start Slony-I

14) Initial replication should begin and can be monitored on the statistics tab in pgAdmin for each node. The pgbench utility may be run against the master database to generate a test workload.

OpenSSL on Windows

OK, to get the usual intro over and done with - blog, neglected, back now, very sorry, won't do it again.

And now onto business... Ben West reported a problem with pgAdmin III v.1.8.0 Beta 2 on Windows which seems to have gained a dependency on MSVCR71.DLL (that's the Visual C++ runtime library for VC++ 7.1). pgAdmin is built with VC++ 8 these days so we really don't want to ship the 7.1 runtimes. After a little investigation, it seems that the latest builds of OpenSSL from Shining Light Productions are built using VC++ 7.1 

So, it seems we need to build OpenSSL ourselves now to avoid this issue. I struggled to find the details of how to do that, so after finding the relevant info elsewhere, I'll repeat it here for future reference...

So, here's how it's done. You will need something to unpack the OpenSSL source code (such as tar with gzip support), and a perl installation, as well as VC++.

1. Download the OpenSSL Source.


2. Start a Visual Studio command prompt for the version of Visual C++ you need to compile against (8.0 for us, otherwise known as 2005).


3. Unpack the OpenSSL source, and CD into the source directory.


4. Configure the source code:
   
   C:\openssl-0.9.8e> perl configure VC-WIN32
   


5. Generate assembler code for speed:
   
   C:\openssl-0.9.8e> ms\do_masm
   


6. Finally, the libraries can be built:
   
   C:\openssl-0.9.8e> nmake -f ms\ntdll.mak

And that's it! The OpenSSL DLLs should be found in the out32dll directory.

Joining EnterpriseDB

Once again I've been neglecting my blog - this time it's been months and months since my last entry which looking back I see was a drunken rambling following a conversation with (and about) Jim Nasby in the company of Magnus and an assortment of people from EnterpriseDB. Which is quite a coincidence really as the point of this blog entry is to announce to anyone that's interested that after more than 11 years at Vale Housing, I'll be joining EnterpriseDB in January.

So, from the new year I'll be braving the A34 on a daily basis to work primarily from the EnterpriseDB Performance Center in Bicester, near Oxford. PostgreSQL regulars may already know that means I'll be sharing an office with well known PostgreSQL hackers Simon Riggs, Heikki Linnakangas and Greg Stark.

As for pgAdmin and my other community work, EnterpriseDB have kindly agreed to allow me time to continue contributing to all the PostgreSQL projects I work on so I expect to dedicate as much time to pgAdmin development and the Windows PostgreSQL distribution as I've been able to do at Vale.

Anyway, enough rambling - if you want to know more about EnterpriseDB, checkout the website, or (for reasons that I still find somewhat baffling), the marketing guys decided to mention me in a press release.

Thought for the night...

It seems I have been remise in writing up my experiences in Toronto as a number of the other hackers have been doing during the PostgreSQL 10th Anniversary Summit. So, to that end and in my current slightly drunken state I offer you this thought for the night that arose fromm a conversation with Jim Nasby, Denis Lussier, Simon Riggs, Magnus Hagander and Greg Stark:

What's the use-case for a Jim Nasby?

And though I'll probably think "what the hell did I write that for?" in the morning, I'll just hit 'Publish' and tootle off to bed...

pgAdmin gets plastic surgery

Well, given the complete lack of activity on planetpostgresql/dpage you'd be forgiven for assuming I was dead or had been kidnapped by the Lizard Alliance to be used as a crash test dummy, but no, I've just been lazy and haven't written anything recently.

So, to put that to rights, here's a pgAdmin update. For the last few days I've been working on cosmetic and usability issues in the user interface, and consequently, 1.6.0 (when released) will sport a fancy new docking user interface on the main windows, allowing you to arrange elements as you like, detach panes, move toolbars around and display or hide individual elements. There are also popup docking hints to make it easy to re-dock elements exactly where you want them...

Other recent improvements include a host of tweaks to the Query Tool including regexp search and replace, error word highlighting and brace matching, as well as enhancements elsewhere such as the ability to display and set boolean columns to NULL in the Data Editor, an XML/XHTML reporting engine with a host of schema and data reporting options and initial support for the PostgreSQL 8.2-dev.

Anyhoo, that's enough for now. I'm leaving for the PostgreSQL Anniversary Conference early tomorrow morning so need to go and pack...

Random Ramblings

Some random noise for the old blog...

First off, an article by Jason Gilmore discusses 5 outdated reasons why people don't choose PostgreSQL. pgAdmin gets a mention under the No professional development and administration tools section. The article was picked up and discussed in depth on Slashdot.

Second, (in a shameless plug) a small site I've been developing in spare five minute sessions for my wife Jo went live the other day.www.jo-jos-jewellery.co.uk is powered by PostgreSQL 8.1 (of course), running with Apache and PHP on FreeBSD. The more interesting part of the work was interfacing the site with Paypal who provide some great resources for developers, including an extremely useful sandbox version of their site for developers to test and experiment with.

'Nothing spectacular' I hear some of you cry - well, perhaps not. Certainly technically there's nothing particularly difficult in the site code, but coming from a decidedly non-commercial work environment as I do it made a very refreshing and welcome change to do something completely different. Now if only I could get my head round Google Adwords...

pgAdmin III v1.4.2 Released

The pgAdmin Development Team are pleased to announce the release of version 1.4.2 of pgAdmin, the Open Source administration and development platform for PostgreSQL 7.3 and above. pgAdmin can be run on Linux, FreeBSD, Mac OSX, Solaris and Windows. For more information, please see the website at:

http://www.pgadmin.org/

pgAdmin can be downloaded free of charge from:

http://www.pgadmin.org/download/

This version is a bug fix release containing the following changes:

• Fix a typo in the admin pack README [August Zajonc].
• Fix serial column detection for 8.1 per Kris Jurka.
• Mask the password when logging the connection string.
• Properly escape single quotes in connection strings.
• Re-arrange the Win32 dependencies so that iconv, libxml2 and wxwidgets are located in $PGADIR/../pgadmin3-deps/.
• Properly enable the redo button in the Query Tool when appropriate, per PromoTrade.
• Add missing files to the Unix tarball, per Andrus - xtra/wxbuild/*, src/utils/precomp.cpp, src/main/pgAdmin3.rc.
• Read config files in the local encoding scheme.
• Display the definition of set returning procedures correctly.
• Fix drop procedure by including IN/OUT parameter flags in the name.
• Fix role SQL - correctly define CREATEROLE/NOCREATEROLE per Andrus.
• Honour the copy quoting setting properly in the SQL results pane.
• Set the initial Unicode/Local charset options correctly in the Export dialogue.
• Fix PK detection in EditGrid per Andrus Moor.
• Fix Bitmap Index explain per Alexander Kirpa.
• Use ELSIF not ELSEIF in pgagent.sql for compatibility with 7.x servers [Glen Sasek].
• Exclude system schemas by name rather than OID, so that 'public' can be safely renamed.
• fix slony node statistics.
• Fix: Unsubscribe set on receiver.
• Another wxComboBox(2.6.2) related fix.

Dave Page
pgAdmin Development Team