PostgreSQL Replication II

Some findings around replication following an earlier blog post.

I have a simple streaming replication setup with 1 primary and 1 standby DB server. My reference DB is about 42GB in size.

• A full vacuum will break the replication. Remember, PostgresSQL is recreating every table in the full mode. This wont fit through any default sized xlogs.
  A regular vacuum works fine though.
• In a complete disaster scenario where both primary and standby are corrupted I have to rely on the nightly pg_dump.
  The import on the primary DB server (AWS m2.4xl instance) takes about 3 hours to restore with the default parameters.
  We can significantly speed it up by utilizing more than 1 default CPU for the data and index creation using the j parameter. Constraints cannot be created in parallel though.
  sudo -u postgres pg_restore -j 8 -v -d dbname dumpfilename
  This way I could reducte the restore time to 70 minutes.
  I still have to get the standby in sync with pg_basebackup, takes another 60 minutes. This time I could reduce to 30 minutes by tar and copying the complete DB files over to the standby.
  (But pg_basebackup works while the primary server is in use, provided you have enough xlogs to cover the until you want to link the standby)

PostgreSQL Replication Express Setup

The system I work on we deploy almost solely on the Amazon AWS platform. Even I try to design the architecture in a way not to be locked-in too much into Amazon, I make use of the Amazon tools and products as much as possible (EC2, VPC, S3, SNS). PostgreSQL is our reference DB and the only DB product in production environments, still we run dedicated instances with PostgreSQL. I am quite delighted about AWS recent offering staring RDS with PostgreSQL. While is is still in BETA and I did not started yet with a conclusive test and migration plan, I need to maintain our existing instances.

There are plenty of books and tutorials about setting up PostgreSQL replication with on-board tools, without going into the details I share the express setup in this tutorial based on Streaming Replication which is part of PostgreSQL since version 9.0. I highly recommend to review the parameters and settings from the below tutorial as your project might have different requirements.

References

• http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html
• http://www.postgresql.org/docs/current/static/warm-standby.html
• http://wiki.postgresql.org/wiki/Streaming_Replication

Tutorial

Remarks

• The tutorial is based on PostgreSQL 9.2 running on Ubuntu Server
• Paths and settings are all the PostgreSQL defaults.
• This is async setup, the master will not wait for feedback from the salve and continue to work even the slave is not available

Prerequisite

• 2 Server running the same PostgreSQL version (9.0+)
• Backup your data or use a sandbox environment.
• In the tutorial I refer to
  MASTER (ip: 0.0.0.1) and
  SLAVE (ip: 0.0.0.2)

Configuration

Master

• Create a replicator user

  sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'mypassword';"
  

• Add the slave ip to /etc/postgresql/9.2/main/pg_hba.conf

  host    replication     all      0.0.0.2/32         trust
  

• Modify parameters in /etc/postgresql/9.2/main/postgresql.conf

  wal_level = hot_standby
  max_wal_senders = 3
  checkpoint_segments = 3
  wal_keep_segments = 3
  

  Review these parameters and set them up according to your requirements

• Start the PostgreSQL instance

  service postgresql start
  

Slave

• Modify parameters in /etc/postgresql/9.2/main/postgresql.conf

  wal_level = hot_standby
  max_wal_senders = 3
  checkpoint_segments = 3
  wal_keep_segments = 3
  hot_standby = on
  

• Stop the PostgreSQL instance

  service postgresql stop
  

• Clean up the old data directory

  sudo -u postgres rm -rf /var/lib/postgresql/9.2/main
  

• Copy the database from the master with pg_basebackup

  sudo -u postgres pg_basebackup -h 0.0.0.1 -D /var/lib/postgresql/9.2/main -U replicator -v -P -x
  

  You can see the backup progress and it should result in something likes

  root@:/var/lib/postgresql/9.2/main# transaction log start point: 41/7D000020
  31524952/31524952 kB (100%), 2/2 tablespaces (/var/lib/postgresql/9.2/main/PG_9.)
  transaction log end point: 41/7D0002A8
  pg_basebackup: base backup completed
  

• Create a recovery configuration file /var/lib/postgresql/9.2/main/recovery.conf

  standby_mode = 'on'
  primary_conninfo = 'host=0.0.0.1 port=5432 user=replicator password=mypassword sslmode=require'
  trigger_file = '/tmp/postgresql.trigger'
  

• Start the PostgreSQL instance

  service postgresql start
  

  Check the pglogs.

Test the replication

• Open any table with pgadmin on the master and apply a change, it should be reflected in the slave within short time.
• Try to change data on the slave, it will fail due to the hot-standby mode

Monitor the replication

• The master instance will not alert you when the replication is down. You can check by yourself or create a little cronjob to do it for you with this sql statement.

  sudo -u postgres psql -x -c "select * from pg_stat_replication;"
  

  You get the status back (if it is running, otherwise the statement will return ‘no rows’).

  

  Check replication

Netbeans + Visual Paradigm = EJB Tutorial (Part 4)

Based on the previous parts of this tutorial (1, 2, 3) we will modify the application and Glassfish to connect to PostgreSQL or Oracle as DB.

• Preparing Glassfish libraries
  Download the drivers for Oracle and PostgreSQL and add them to your {GLASSFISHHOME}/domains/{YOURDOMAIN}/lib
  Restart Glassfish.
  

  Glassfish lib folder

Netbeans + Visual Paradigm = EJB Tutorial (Part 3)

In part 1 of this tutorial we created an EJB using Netbeans and Visual Paradigm, in part 2 a little ZK application to read data using the EJB. In part 3 we will move away from the Derby DB to PostgreSQL and Oracle DB and challenge ourselves with identifier more than 30 characters, which is an issue for Oracle (yes, it is 2011). We will add columns with more than 30 characters and play with a few different column types (the ones showing up in a normal DB layout).

• Add new fields to the ERD
  this_is_a_very_long_remark_field
  floatcol
  numbercol
  

  Updated ERD

Installing pgAdminIII for PostgreSQL 9 on Ubuntu

pgAdmin is the best GUI you can use to administrate PostgreSQL, unffortunately the Ubuntu default packages still offer only PostgreSQL 8.4 and an older version of pgAdmin III that does not support PostgreSQL 9.0.x. Thanks to Martin Pitt who maintains the latest packages you can run and maintain the latest PostgreSQL versions.

If you run Maverick:

• sudo apt-get remove pgadmin3 (if any old versions, make sure you dont create trouble with existing PostgreSQL 8.x versions)
• sudo vi /etc/apt/sources.list
  add
  deb http://ppa.launchpad.net/pitti/postgresql/ubuntu maverick main
  deb-src http://ppa.launchpad.net/pitti/postgresql/ubuntu maverick main

• sudo apt-key adv –keyserver keyserver.ubuntu.com –recv-keys 8683D8A2
• sudo apt-get update
• sudo apt-get install pgadmin3

Check here for latest or other releases of Ubuntu.

Installing PostgreSQL 9.0 on Ubuntu

Thanks to Martin Pitt , who maintains the packages for Ubuntu, nstalling the latest version of PostgreSQL on Ubuntu (Lucid) is nothing more than updating your /etc/apt/sources.list with

deb http://ppa.launchpad.net/pitti/postgresql/ubuntu lucid main

deb-src http://ppa.launchpad.net/pitti/postgresql/ubuntu lucid main

plus

apt-get update

and

apt-get installpostgresql-9.0

I recommend reading the release notes and he outstanding good postgresql documentation.

PostgreSQL 9.0 Final released

I didnt notice for 7 days that the latest version of PostgreSQL DB was released on September 20th 2010 ! Congratulations and a big Thank You to the PostgreSQL team ! Is is wonderful to have such a great product out there.

www.postgresql.org

How does Virtualbox help me in hacking with Netbeans, Glassfish and PostgreSQL ? (2)

Did you read yet Part 1 ? If not, I recommend to run through it here.

We started from the scratch with VirtualBox and created a new virtual machine running Ubuntu Server 9.10. The last step was to shutdown the virtual machine. Now it is time to clean up, update our server, finetune the configuration and install JDK and Glassfish.

How does Virtualbox help me in hacking with Netbeans, Glassfish and PostgreSQL ?

Using the Netbeans IDE together with Glassfish as application server is quite easy and comes out-of-the box with the Netbeans installer. To install PostgreSQL is not significantly harder to install on Linux (or Windows). Our team uses Ubuntu 9.10, Netbeans 6.8, Glassfish and PostgreSQL 8.4.2. If you work alone, it is usually no problem to develop and deploy locally but once you are embedded into a team, you better ensure that you deploy and test applications in the same environment. Very quick you will have Ubuntu updating the OS and PostgreSQL and someone might install Glassfish updates or even add optional modules, soon you have as many deployment environments as you have team-members (times 2). The straight forward answer would be to have all working with the same one server running the application server and the DB, but we still need the comfort of a local sandbox playground and in-dependency from being online or in the office. Certainly I do not advocate island development leading to a different codebase and varying DB’s. But after the tutorial you will agree with me our solution is a reasonable approach.

Our approach: We create a virtual server (with VirtualBox) that everyone is running on his/her desktop and also one instance on a central server. One team-member is responsible to create so-called raw virtual images of the server and documents new versions that are deployed to each desktop. Each team-member does nothing but starting the guest Server in VirtualBox (Glassfish and PostreSQL autostarting) and immediately can connect with Netbeans and pgadmin to AS and DB.

Virtual Ubuntu 9.10 Server

Not all developers are familar with setting up Ubuntu Server and VirtualBox, so I compiled this tutorial as a walk-through the necessary steps to get you started witthout knowing too much about  the OS nitty-gritty stuff. You want to concentrate on creating your application, not learning to hack the operating system (even I recommend to know as much as possible on Linux).
A remark on the naming: Your desktop or Notebook running VirtualBox is the HOST and the server or OS you run in the virtual machine is the GUEST.

News: Virtualbox, PostgreSQL

3 more application join the release-fever spreading the last few days:

• Virtualbox
  Now available in release 3.0 offering experimental support for DirectX 8 and 9, supporting Open GL 2.0. Plus quite a number of bugfixes. The continuous train of update releases is really amazing for a freely available product (for private use though). Download and changelog at virtualbox.org (link)
• PostgrSQL
  After 1 year of development the version 8.4 is ready for download. Find all the details and the download at postgresql.org (link)
• pgAdmin
  And along comes the latest version 1.10.0 of the PostgrSQL administration tool pgAdmin. Download and Info at pgadmin.org (link).