PostgreSQL and Project Amber Road (Sun Storage 7000 Series)

The New Sun Microsystems  announced a new line of OpenStorage devices. The new Sun Storage 7000 Unified Storage Systems (code name Amber Road) are Storage appliances which uses ZFS as the core technology and DTrace as the means of "understanding" what's happening with the devices. This now allows systems which DO NOT have ZFS capabilities  (ala Linux, Windows, etc) to use and gain  benefit from the features of ZFS and DTrace (albeit limited to the "Storage" part only).

PostgreSQL (on Linux,Solaris, Windows) can use such systems through one of the standard protocols that these OpenStorage devices support

• NFS


• CIFS (Only on Windows)
  


• iSCSI

PostgreSQL on NFS is already discussed at length (both merits and also the demerits of using NFS with PostgreSQL ) in the PostgreSQL community. Also I haven't met anybody yet who said that they are using PostgreSQL with their database on CIFS mounted devices. So I think one might actually select iSCSI as the favorable protocol for such OpenStorage devices with PostgreSQL. (Correct me via comments if you think otherwise.)

Here are quick steps that I had used to configure the PostgreSQL server (running Solaris 10) to use iSCSI devices exported from Sun Storage 7000 Unified Storage Platform

 In my case I had created two iSCSI LUN devices (one for PGDATA and one for pg_xlog) with file permissions for the postgres user. (This setup is generally done using the BUI - Browser User Interface of the OpenStorage "Amber Road" device.)

Now on the PostgreSQL Server I did the following to setup iSCSI initiators and LUNS:

1. 
   

   Add and display iSCSI target discovery address by giving the
   IP of the "Amber Road" system.

   
   

   # iscsiadm add
   discovery-address 10.9.168.93

   
   

   # iscsiadm list
   discovery-address

   
   

   Discovery Address:
   10.9.168.93:3260

   
   


2. 
   

   Display iSCSI targets discovered from the Amber Road system

   
   

   # iscsiadm list
   discovery-address -v 10.9.168.93

   
   

   Discovery Address: 10.6.140.151:3260

   
   

   Target name:
   iqn.1986-03.com.sun:02:a4602145-85f8-64fa-c0ef-a059394d9a12

   
   

   Target address:
   10.9.168.93:3260, 1

   
   

   Target name:
   iqn.1986-03.com.sun:02:0449398a-486f-4296-9716-bcba3c1be41c

   
   

   Target address:
   10.9.168.93:3260, 1

   
   
   
   


3. 
   

   Enable and display static discovery.

   
   

   # iscsiadm modify discovery
   --static enable

   
   

   # iscsiadm list discovery

   
   

   Discovery:

   
   

   Static: enabled

   
   

   Send Targets:
   disabled

   
   

   iSNS: disabled

   
   


4. 
   

   Adds a target to the list of statically configured targets. A
   connection to the target will not be attempted unless the static
   configuration method of discovery has been enabled.

   
   



# iscsiadm add static-config
iqn.1986-03.com.sun:02:9e0b0e03-8823-eb7e-d449-f9c21930ba15,10.9.168.93






# iscsiadm add static-config
iqn.1986-03.com.sun:02:2cc4fe10-c7ba-697f-d95f-fa75efe50239,10.9.168.93





5. 
   

   Use Solaris devfsadm(1M) to create iSCSI device nodes.

   
   

   # devfsadm -i iscsi

   
   
   
   


6. 
   

   Use format(1M) command to access iSCSI disks. The disk(s) to
   be selected contain /scsi_vhci in their path name. Local disks are
   listed before iSCSI disks in the format command list. The following
   shows disk no. 4 and 5 are iSCSI disks.

   
   

   # format

   
   

   Searching for
   disks...done

   
   

   AVAILABLE DISK
   SELECTIONS:

   
   

   0. c0t0d0
   <DEFAULT cyl 17830 alt 2 hd 255 sec 63>

   
   

   /pci@0,0/pci8086,25f8@4/pci108e,286@0/sd@0,0

   
   

   1. c0t1d0
   <DEFAULT cyl 17830 alt 2 hd 255 sec 63>

   
   

   /pci@0,0/pci8086,25f8@4/pci108e,286@0/sd@1,0

   
   

   2. c0t2d0
   <DEFAULT cyl 17830 alt 2 hd 255 sec 63>

   
   

   /pci@0,0/pci8086,25f8@4/pci108e,286@0/sd@2,0

   
   

   3. c0t3d0
   <DEFAULT cyl 17830 alt 2 hd 255 sec 63>

   
   

   /pci@0,0/pci8086,25f8@4/pci108e,286@0/sd@3,0

   
   

   4.
   c2t600144F04890703F0000144FA6CCAC00d0 <DEFAULT cyl 13051 alt 2 hd
   255 sec 63>

   
   

   /scsi_vhci/disk@g600144f04890703f0000144fa6ccac00

   
   

   5.
   c2t600144F0489070250000144FA6CCAC00d0 <DEFAULT cyl 13051 alt 2 hd
   255 sec 63>

   
   

   /scsi_vhci/disk@g600144f0489070250000144fa6ccac00

   
   


7. 
   

   For UFS Follow the following procedure

   
   
   
   
   1. 
      

      Create file systems on iSCSI disks.

      
      
   
   
   
   

   # newfs
   /dev/rdsk/c2t600144F04890703F0000144FA6CCAC00d0s0

   
   

   # newfs
   /dev/rdsk/c2t600144F0489070250000144FA6CCAC00d0s0

   
   
   
   

2. 
   

   Make the mount points.

   
   





# mkdir /pgdata




# mkdir /pglog

3. 
   

   Mount the iSCSI LUNs

   
   





# mount -F ufs
-o forcedirectio /dev/dsk/c2t600144F04890703F0000144FA6CCAC00d0s
/pgdata




# mount -F ufs
-o forcedirectio /dev/dsk/c2t600144F0489070250000144FA6CCAC00d0s0
/pglog

8. 
   

   For ZFS Create zpool as follows:

   
   

   # zpool create pgdata
   c2t600144F04890703F0000144FA6CCAC00d0s

   
   

   #
   zpool create pglog c2t600144F0489070250000144FA6CCAC00d0s0

   
   

9. 
   

   For ZFS with Read-Cache and Separate Intent Log (OpenSolaris
   2008.05 and later) using local disks

   
   

   # zpool create pgdata
   c2t600144F04890703F0000144FA6CCAC00d0s cache c0t1d0s1
   log c0t1d0s0

   
   

   #
   zpool create pglog c2t600144F0489070250000144FA6CCAC00d0s0
   cache c0t2d0s1 log c0t2d0s0

   
   

PostgreSQL Setup:

Many
times, UFS filesystems have “lost+found” directory which causes
PostgreSQL's initdb command to fail as directory is not empty. Also
major versions of database are incompatible and hence it makes sense
to take a naming convention as follows and create a sub directory
within /pgdata and /pglog depending on the version you are planning
to use:

mkdir
/pgdata/8.3 /pglog/8.3

mkdir
/pgdata/8.2 /pglog/8.2

mkdir
/pgdata/8.1 /pglog/8.1

Then for PostgreSQL 8.3 (Solaris 10 10/08) the step is as follows

/usr/postgres/8.3/bin/initdb
-D /pgdata/8.3 -X /pglog/8.3/pg_xlog

As for 8.2 the steps are as follows:

/usr/postgres/8.2/bin/initdb
-D /pgdata/8.2

mv
/pgdata/8.2/pg_xlog /pglog/8.2/

ln
-s /pglog/8.2/pg_xlog /pgdata/8.2/pg_xlog

And for the default 8.1 the steps are similar:

/usr/bin/initdb
-D /pgdata/8.1

mv
/pgdata/8.1/pg_xlog /pglog/8.1/

ln
-s /pglog/8.1/pg_xlog /pgdata/8.1/pg_xlog

And then use the pg_ctl from the version of PostgreSQL being used to start and stop the server:

pg_ctl
start -D /pgdata/8.3 -l /pgdata/8.3/server.log

pg_ctl
stop -D /pgdata/8.3

In another blog post I will explain on how to take snapshots with PostgreSQL and the OpenStorage devices.