PI - database description

This file describes tables in pi database , currently used database is postgres, several features of other DB are compared here, database
structure is available here ( or full zip pack ) :

Initializing new pi database
Tables Frame , Event and Monitoring tables ( MountStatus and PimanCommand )
Table FrameDet
Star Catalogue
1. Table Stars
2. Table Measurements
3. Table ObsFieldStat
4. Table SuperStar
5. Table InterestingObject
6. Usage
7. Cataloging Procedure Description
External GRB tables
1. Table Alert
2. Table GRB
3. Table SN
Setup Tables
1. Table alert_source_type
2. Table alert_instruments
3. Table alert_status
4. Table field_def
Database Backups
Currently filled production databases
Database Links
Optimizing postgres database
Disussion on final version of database
Database disk space usage
Configuration hints
Usfull sql statements
Database log files
Database synchronization
Database pgsql functions usfull for analysis
Optimization of pi database
Handling of problems with database
Starting new star catalog
Starting new star catalog from the old one

1. Initializing new pi database :

    a/ login as root goto :
             cd /etc/sysconfig/pgsql/
             echo "export PGDATA=/disk01/pidb/" > postgresql
        where instead of /disk01/pidb/ path to place where database will be stored should be put
        Restart postgres server :
             /etc/init.d/postgresql restart
    b/ login as postgres user
    c/ go to directory where database files are stored (probably /disk01/pidb/ , but default instalation places it in /var/lib/pgsql/ )
        edit postgres.conf file to have best settings for pi database , this file is stored in svn source control system in :
               /opt/pi/dev/pisys/daq/src/ccd/cfg/postgres/postgresql_heplx48.conf
               cd /disk01/pidb/
               cp postgresql.conf postgresql.conf.backup
               cp /opt/pi/dev/pisys/daq/src/ccd/cfg/postgres/postgresql_heplx48.conf postgresql.conf
       before restarting postgres pi service must be setup which will increase limit of shared memory every time system starts , as root do :
              cd /etc/init.d/
              cp /opt/pi/dev/pisys/daq/src/ccd/scripts/init.d/pi .
              in GUI add starting of pi service at runlevels 3 and 5 and start pi service
               restart postgres :
                    /etc/init.d/postgresql restart
    d/ run :
         cp /opt/pi/dev/pisys/daq/src/ccd/scripts/shell/dodb! .
         cp -r /opt/pi/dev/pisys/daq/src/ccd/scripts/pidb/init_db scripts
   dodb! pidb

         NOTE : database1 stands for new database name and should be changed according to needs

        Allow user pidb_user to login to databases on this machine as postgres user :
             cd /disk01/pidb/
             use as template file : /opt/pi/dev/pisys/daq/src/ccd/cfg/postgres/pg_hba.conf.heplx48
             and edit file pg_hba.conf , put computers allowed to use database
    e/ login as pi user and run :
         init_pidb! pidb

Starting / Shutting down database :

    a/ starting database :
       - login as root
       - /etc/init.d/postgres start
    b/ shuttdown :
          su -
          /etc/init.d/postgres stop

2. Table Frame, Event and monitoring tables MountStatus and PimanCommand:

Table Frame description :

              id_frm - internal serial number of record ( serial )
              iframeno - frame number for single date ( integer )
          iframesecondno - NOT USED ( integer )
          idaynight - night date in format YYYYMMDD ( integer )
      spathtofile - path to frame file ( character varying(100) )
            icamid - camera identifier ( 0 = k2a, 1 = k2b ) integer
      scamfilter - used filter ( character varying(10) )
      inaxis1 - x size of frame ( integer )
      inaxis2 - y size of frame ( integer )
sobject - observed object name ( character varying(20) )
frotate - picture rotation ( double precision )
      fra - right ascension [ hours ] double precision
      fha - hour angle [ hours ] double precision
      fdec - declination [ degrees ] double precision
      falt - altitude [ degrees ] double precision
      fzenith_d - zenital distance [ degrees ] double precision
      fazim - azimuth [ degrees ] double precision
sdate_obs - date
      ttime_ut - unix time ( integer )
      sloctime - local time ( time without time zone )
      slocdate - local date ( date )
      fjd - julian date ( double precision )
      fhjd - heliocentric julian data ( double precision )

COLUMN NAME	TYPE	DESCRIPTION	VALUES
id_frm	integer	internal serial number of record
iframeno	integer	frame number for single date
iframesecondno	integer	NOT USED
idaynight	integer	night date in format YYYYMMDD
spathtofile	character varying(100)	path to frame file
icamid	integer	camera identifier ( 2 = k2a, 3 = k2b )
scamfilter	character varying(10)	user filter
inaxis1	integer	x size of frame
inaxis2	integer	y size of frame
sobject	character varying(20)	observed object name
frotate	double precision	picture rotation
fra	double precision	right ascension [ hours ] of frame center
fha	double precision	hour angle [ hours ] of frame center
fdec	double precision	declination [degrees] of frame center
falt	double precision	altitude [ degrees ] of frame center
fzenith_d	double precision	zenital distance [ degrees ] of frame center
fazim	double precision	azimuth [ degrees ] of frame center
sdate_obs	date	date of frame
ttime_ut	integer	unix time of frame starting time
sloctime	time	local time of frame start
slocdate	date	local date of frame start
fjd	double precision	julian date of frame start time
fhjd	double precision	heliocentric julian date of frame start time
istarcount	integer	number of stars on frame
ifitssize	integer	size of compressed fits file
bhas_jpg	boolean	flag indicating if jpg image is available on WWW server
posangle	double precision	astrometry - rotation angle
ast_ord	integer	astrometry - order of fitted transformation polynomial
asttime	integer	astrometry - time of astrometry
ast_ver	character varying(16)	astrometry - version of astrometry algortihm
ast_err	double precision	astrometry - error of astrometry
ra2000	double precision	astrometry - fitted right ascension of center [hours]
dec2000	double precision	astrometry - fitted declination of center [degrees]
par_x_?	double precision	astrometry - parameter of transformation
par_y_?	double precision	astrometry - parameter of transformation
flip	integer	astrometry - if frame was fliped before photometry : 0 - no flip 1 - eReverseImageFull 2 - horizontal flip 3 - vertical flip 4 - horizontal and vertical
errcode	integer	0 - OK >0 skiped in cataloging , number if error code as defined in table ErrorDef
matchedstars	integer	number of stars matched to catalog stars ( only in star catalog databases aver20 and scan , not in pidb )
shutter_mode	integer	shutter mode 1-permanently opened, 2-normal open/close mode
fwhm_aver	double precision	average value of FWHM in ast file
avg	double precision	average value of pixel on image
rms	double precision	rms of pixel value on image
star_fraction	double precision	ratio of (#image stars)/(#stars in catalog on this field)
cat_stars	integer	number of catalog stars on this field
astrook	integer	astrometry OK/FAILED flag : 0 - FAILED , 1 - OK
avg_shape	double precision	average value of SHAPE ( one of the columns in ast file ) , indicating average shape of stars on image
pixscale	double precision	pixscale of image , as fitted by astrometry
quality	int2	quality flag , if >0 means that something wrong is with the image , bad values : 1 - mount movemenet detected 2 - temporary system problem ( for example voltage strips in 2007-XI etc )
last_checked	integer	nightdate when frame was last checked by deep-purple-FRAME criteria
pktmiss	integer	number of missing packets which required to be retransmitted (only for the case of ETH cameras)
phottype	integer	type of photometric program used	NULL-none in case of raw image, 1-asas, 2-piphoto
mountra	real	RA in hours, passed from the mount	0-24
mountdec	real	DEC in degrees, passed from the mount	-90 - 90
mounttrk	integer	flag if mount is tracking	0-TRACKING , 1-NOT TRACKING
mountdw	integer	DEEP/WIDE flag	0-DEEP,1-WIDE,
mountfha	real	MOUNT FULL STEPS HA
mountfdec	real	MOUNT FULL STEPS DEC
mounteha	real	MOUNT ENCODER HA STEPS
mountedec	real	MOUNT ENCODER DEC STEPS
mountccdxra	real	CCDX axis orientation RA[h]
mountccdxdec	real	CCDX axis orientation DEC[deg]
mountdtm	double precision	unix time of mount information with decimal precision

In case image was obtained as sum of many single images , this information is stored in table frame_averaged :

COLUMN NAME	TYPE	DESCRIPTION	VALUES
id_frm	integer	link to table Frame.id_frm, frame which was a result of summing this frame
spathtofile	character varying(1000)	path to fitc file of single frame used in summ

Table BadFrame :

Lists bad frames ( id_frm field values ) :

COLUMN NAME	TYPE	DESCRIPTION	VALUES
bf_id	serial	internal id of record ( automatically filled )
bf_idfrm	integer	link to Frame.id_frm , id_frm of bad frame
bf_comment	character varying(256)	descriptive reason why image is bad

Table Event :

id_event	integer	Event ID ( internal number )
id_frm	integer	Link to Frame.id_frm
frame_no	integer	Frame number ( during night )
event_no	integer	Event number of frame
x	integer	chip coordinate X
y	integer	chip coordinate Y
ra	double precision	Right Ascension [deg]
dec	double precision	Delinaction [deg]
evt_time	integer	unix time of event
evt_night	integer	night number
evt_type	integer	event type type 0-verif,1-final ( default ),3-allevents.log
evt_glon	double precision	Galactic longitude [deg]
evt_glat	double precision	Galactic latitude [deg]
evt_runtype	integer	algorithm type
evt_camid	integer	camera ID
sphericity	double precision	value of shape indicator

TABLE HotPixel

hp_id serial,
hp_x double precision,
hp_y double precision,
hp_cam integer,
hp_radius integer, -- radius of CCD defect
hp_dx double precision, -- for rectangle
hp_dy double precision, -- for rectangle
hp_type integer DEFAULT 0, -- same as enum enum eCCDDefectType { eCCDDefectSinglePixel=0, eCCDDefectRectangle=1, eCCDDefectCircle=2 };
hp_night integer -- in case it is only for given night ( ICE CRYSTALS )

Table MountStatus :

File mount.status copied to the database :

mst_id	Serial integer	Record internal ID
mst_mountid	Integer	Mount ID
mst_uxtime	Integer	Unix time of mount.status
mst_last_pos_uxtime	Integer	Unix time of last position value
mst_ra	double precision	RA [deg]
mst_dec	double precision	DEC [deg]
mst_ha	double precision	HA [deg]
mst_az	double precision	AZIM [deg]
mst_alt	double precision	ALT [deg]
mst_full_steps_ha	double precision	number of full steps in HA
mst_full_steps_dec	double precision	number of full steps in DEC
mst_adc_counts_ha	double precision	number of ADC counts in HA
mst_adc_counts_dec	double precision	number of ADC counts in DEC
mst_left_switch_ha	integer	status of LEFT switch HA (1-at switch,0-off)
mst_right_switch_ha	integer	status of RIGHT switch HA (1-at switch,0-off)
mst_left_switch_dec	integer	status of LEFT switch DEC (1-at switch,0-off)
mst_right_switch_dec	integer	status of RIGHT switch DEC (1-at switch,0-off)
mst_last_command	character varying(256)	last command executed by mount
mst_last_cmd_start_ux	integer	unix time of last command start
mst_exp_adc_counts_ha	double precision	number of EXPECTED ADC counts in HA
mst_exp_adc_coun number of EXPECTED ADC counts in DECts_dec	double precision	number of EXPECTED ADC counts in DEC

Table DomeStatus :

DomeStatus inserted into database :

dst_id serial,
dst_domeid integer, -- DOME ID
dst_uxtime integer, -- unix time stamp
dst_status integer -- dome status : -1 - unknown, 0 - closed, 1 - opened

Table UpsStatus :

ups_id integer, -- ID of ups UPS-2 at LCO jaws = 2
ups_unixtime integer, -- unixtime of measurement
ups_date integer, -- date in format YYYYMMDD
ups_dtm character varying(64), -- ups date time string
ups_voltage double precision, -- input voltage in Volts
ups_outvolt double precision, -- output voltage in Volts
ups_bcharge double precision, -- battery charge in [%]
ups_loadpct double precision -- current load ( obciazenie ) in %

Table PiPointingInfo :

ppt_id serial, -- internal record number
ppt_syscode integer, -- code of pi instrument - Reference to System table
ppt_mountid integer, -- ID of mount References PiMount(id) field , 0 - LCO
ppt_start_time integer, -- time of poiting start
ppt_interval integer, -- estimated time of staying on this field
ppt_target_no integer, -- code of observed target
ppt_object character varying(24), -- observed object
ppt_ra double precision, -- RA in degrees
ppt_dec double precision, -- DEC in degrees
ppt_min_ra double precision, -- not obligatory
ppt_max_ra double precision, -- not obligatory
ppt_min_dec double precision, -- not obligatory
ppt_max_dec double precision, -- not obligatory
ppt_comment character varying(128),
ppt_sent_count integer DEFAULT 0 -- counter of sending to external services

TABLE InterestingImages

ii_id serial not null, -- internal id
ii_object_type character varying(16), -- GRB/SN/NOVA/...etc...
ii_url character varying(1024), -- www link to jpg/html files
ii_object_name character varying(128), -- name of object
ii_other_table character varying(64), -- table in which detailed info is stored GRB/SN
ii_other_table_id integer, -- link to object id in other table
ii_ra double precision, -- right ascension [ hours decimal ]
ii_dec double precision,-- declination [degrees decimal ]
ii_night integer -- night number

TABLE NightStat

ns_night integer,
ns_camid integer,
ns_frame_count integer DEFAULT 0,
ns_cat_done integer DEFAULT 0,
ns_opt_done integer DEFAULT 0,
ns_comment character varying(512), -- shift person comment
ns_quality integer, -- in scale from 0 to 5 , 0-no data,1-usless data,2-poor data, 3-medium quality data, 4-good data, 5 - very good data
ns_moon_ra double precision, -- MOON RightAscension in hours decimal
ns_moon_dec double precision, -- MOON Declination in degrees decimal
ns_moon_illum double precision, -- MOON illum ( decimal < 1 )
ns_numgen integer, -- number of generated events
ns_numgenok integer, -- number of generated and found events
ns_numbkg integer, -- number of background events
ns_online_eff double precision, -- efficiency ns_numgenok/ns_numgen
ns_sysstarted integer, -- 0 - if system was NOT started , 1 - if was started ( this means dome opened and programs were started daq/piman/mount etc )
ns_anal_done integer DEFAULT 0, -- 0 - if off-line analysis was not done yet , 1 - if done
ns_synch_done integer DEFAULT 0

Table PimanCommand :

Commands executed by piman, parsed from piman.logfile and inserted to the database.

pmc_name	character varying(64)	Name of command
pmc_param1	double precision	Double precision parameter 1 ( for example goto_ra_dec(RA,DEC)
pmc_param2	double precision	Double precision parameter 2 ( for example goto_ra_dec(RA,DEC)
pmc_time	integer	unix time of command
pmc_night	integer	Night value
pmc_module	character varying(16)	Module : daq , mount, piman, internal etc
pmc_par1	character varying(128)	string paramter 1
pmc_par2	character varying(128)	string paramter 2
pmc_par3	character varying(128)	string paramter 3
pmc_par4	character varying(128)	string paramter 4
pmc_par5	character varying(128)	string paramter 5
pmc_par6	character varying(128)	string paramter 6
pmc_par7	character varying(128)	string paramter 7
pmc_par8	character varying(128)	string paramter 8
pmc_site	character varying(16)	SITE ID
pmc_pimanid	character varying(16)	piman ID

3. Table FrameDet description :

    id_frm - frame ID , link to Frame(id_frm) (integer , not null)
    sobserver - observer ( character varying(30) )
    ssoftware - software description ( character varying(30) )
    sbuild - software build ( character varying(50) )
    sdrvtype - driver type ( character varying(30) )
    icamiidx - camera internal index ( integer )
    fexptime - exposition time [sec] ( double precision )
    frexptime - measured expostion time [sec] ( double precision )
    bshutter - shuter OPENED/CLOSED ( boolean )
    fadcgain - ADC gain value ( double precision )
    fadcbias - ADC bias ( double precision )
    fadcgset - ADC gain setting ( double precision )
    flnagain - LNA gain ( double precision )
    fadcbset - ADC bias setting ( double precision )
    fadcrange - ADC range ( double precision )
    fadcclamp - ADC clamping ( double precision )
    felecgain - electron gain ( double precision )
    bcooling - cooling ON/OFF ( boolean )
    fabinn - analog binning ( double precision )
    fsbinn - software binning ( double precision )
    fspeed - chip readout speed ( double precision )
    sspeedmh - chip readout speed description ( character varying(20) )
    bmpp_bc - mpp / bcc ( boolean )
    fro_time - USB transfer time [sec] ( double precision )
    fcrotime - chip readout time ( double precision )
    ifocus - focus [mm] ( integer )
    bhitlens - lans hitting on/off ( boolean )
    ssavearea - save area ( character varying(50) )
    susbmode - USB mode USB1.1 / USB2.0 ( character varying(20) )
    sfpgaver - FPG software version ( character varying(50) )
    scprsver - CYPRESS software version ( character varying(50) )
    sverdesc - version description ( character varying(50) )
    frnoise - readout noise ( double precision )
    frelnoise - rel. noise ( double precision )
    fchiptset - chip temerature setting ( double precision )
    fchiptemp - chip temperature measured ( double precision )
    fcastemp - case temperature ( double precision )
    fambtemp - ambient temperature ( double precision )
    fcamhumid - camera humidity ( double precision )
    fambhumid - ambient humidity ( double precision )
    fintrtemp - internal temperature ( double precision )
    fairmass - air mass ( double precision )

4. Star catalogue :

Basics on usage of pi-catalog can be found here.
The structure of the star catalogs tables is shown on the image below :

a/ table STARS :

COLUMN NAME	TYPE	DESCRIPTION	VALUES
id	integer	internal id of star in database
ra	double	Right Ascension in hours - decimal , example 4.5454
dec	double	declination in degrees - decimal, example -43.4343
magnitude	double	average magnitude of star measurements
sigma_mag	double	rms of magnitude
name	varchar	name of star : HHMM+DDMM.M , name of star are hours and minutes of RA and degrees with minutes ( with one digit after coma ) , example : (RA,DEC ) = (12h20m23.2s,-12deg 12.2' ) = 1220-1212.2
min_mag	double	minimum magnitude of star
max_mag	double	maximum magnitude of star
no_measurements	double	number of measurements of star
mag_cat	double	ASAS catalogue magnitudo, if found in ASAS catalogue
magsum	double	sum of star magnitudes
mag2sum	double	sum of star magnitudes squared
sigma_ra	double	rms of ra
sigma_dec	double	rms of dec
camid	integer	ID of camera	2 - k2a 3 - k2b 102 - fast photometry k2a 103 - fast photometry k2b 1002 - ASAS-2 1003 - ASAS-3
sstar_id	integer	link to SuperStar record, or to star on other camera in table Stars NOTE : currently I am changing this so that this field is link to SuperStar only, link to star on second camera will be stored in field cam2_sstar_id
field_id	integer	link to so called best field on which star was measuremed most times
sigma_field	double	rms of magnitude on best field
sigma_night	double	minumum rms on single night
tmp_value	real	field for temporary calculations if nessesary to store some intermediate values
amp	real	Amp field in ASAS table of var-stars
period	real	Period in case of variables
hjd_t0	real	HJD when measurements were started
cam2_sstar_id	integer	link to star.id of same star on second camera
quality	int2	bit flag with information on results of purple criteria check
max_id_frm	integer	maximum id_frm number ( frame number ) on which star was observed
last_checked	integer	nightdate of last purple criteria check
candtype	integer	type of object according to lightcurve analysis	0-NOTHING INTERESTING, 1-VARSTAR, 2-???
lc_quality	integer	quality of the full lightcurve	0-unknown, 1-poor, 2-medium, 3-good/very good
is_ref_star	integer	flag to mark best stars to be reference stars, same field is also in SUPERSTAR table but the current one is more important than SuperStar.is_red_star field value ( filled on 2006_2009, see here )	NULL/0 -not a ref-star, 1 - good reference star
source_catalog	integer	ID of source catalog, if external SEED catalog was used	0/null-none, 1-TYCHO, 2-GSC

b/ table MEASUREMENTS :

COLUMN NAME	TYPE	DESCRIPTION	VALUES
star	integer	link to table Stars field Stars.id ( see above )	>0
time_hjd	double	heliocentric julian date	>0
magnitude	double	star magnitude ( after normalization to star catalogue )
error	double	magnitude error
id_frm	integer	link to table Frame where FITS header is stored ( and Frame_det )	>0
ra	double	Right Ascension
dec	double	declination
mag_piphoto	double	measured pi-magnitude ( aparature magnitude ) - average of all measured apertures
grade	text	grade of measurement
ccdx	double	X position on chip ( after flip - if needed ) - see value in Frame.flip
ccdy	double	Y position on chip ( after flip - if needed ) - see value in Frame.flip
mag_ap0	double	magnitude in aperture 0
mag_ap1	double	magnitude in aperture 1
mag_ap2	double	magnitude in aperture 2
mag_ap3	double	magnitude in aperture 3
new_star	boolean	true if this is first measurement of star, false if not	true-the measurements record is the first one for this star,false-not the first one
quality	int2	bit flag for results of purple criteria check
mag_calib0	real	Magnitude obtained by any kind of special calibration procedure, possibily by AFZ procedure of color correction, it will probably filled only for selected objects for deeper studies.
mag_cal0qual	real	quality of calibrated measurement, in case of AFZ correction it is a sum of distances from fitted surface to ref-stars
mag_cat	real	Brigthness of star in reference catalog used by piaddast2 program in calibration/cataloging procedure. Typically it is equal to Stars.mag_cat, but in case reference catalog changes it can be different than that one. It is the brightness of star in catalog used for calibration of this particular image
mag_r_cat	real	Brigthness in R-filter of star in reference catalog used by piaddast2 program in calibration/cataloging procedure ( option -post_simple_corr first normalizes to V-filter and later finds average correction for whole image to R-filter, it assumes that corr_V(x,y)~=corr_R(x,y) )
magnitude_r	double	star magnitude normalized to R filter ( according to mag_r_cat values )

c/ Table ObsFieldStat

COLUMN NAME	TYPE	DESCRIPTION	VALUES
ofs_field	character varying(16)	name of field , as defined in table Field_Def
ofs_night	integer	night
ofs_ra	real	field right acsension
ofs_dec	real	field declination
ofs_night_count	integer	number of field observation for current night ( ofs_night value )
ofs_count	integer	number of all observation of this field up to current night ( ofs_night value )

d/ Table SuperStar

This table is used as super-star table, all records in table Stars should be linked to some record of superstar table , which is beasically created
on relation or between Stars with different camid values, if star exists in any of the catalog - pi-k2a, pi-k2b, asas-var, asas-astrometry, gscv etc
SuperStar record is created and all Star records representing this star are linked to this record

COLUMN NAME	TYPE	DESCRIPTION	VALUES
id	serial	id of superstar record
name	text	name of star
ra	double precision	right ascension [h] decimal
dec	double precision	declination [deg] decimal
v_mag	double precision	visual magnitude, average of Stars.magnitude, which means average of averages magnitudes in all pi cameras which observed it ( in this particular database - not globally ! )
b_mag	double precision	B magnitude ( blue filter )
i_mag	double precision	I band magnitude
period	double precision	period [days], filled for variable stars by Marek's Biskup program ( ask Gosia Siudek )
star_type	integer	link to table StarType
star_class	varchar(64)	class of shape : EC/RRC/DSCT/ESD
other_id	varchar(64)	Id in other catalog
other_class	varchar(64)	class in other catalog
gcvs_id	integer	link to internal ID of star in GCVS catalog, can have interpratation of flat when <0
tycho_id	integer	link to internal ID of star in TYCHO catalog, can have interpratation of flat when <0
asas_id	integer	link to internal ID of star in ASAS catalog
gcvs_match_count	integer	number of matched objects in GCVS star catalog
asas_match_count	integer	number of matched objects in ASAS star catalog
tycho_match_count	integer	number of matched objects in TYCHO star catalog
simbad_id	integer	link to internal ID of star in TYCHO catalog
simbad_match_count	integer	number of matched objects in SIMBAD star catalog
filter	integer	in which photometric system in was observed ( CURRENTLY NOT USED )
pi_class	integer	class according to PI classification ( PROBABLY ALWAYS EMPTY )
sigma_mag	real	error of magnitude measurement, it is the smallest value on all cameras ( MIN(Stars.sigma_mag) )
no_measurements	integer	Number photometric measurements on all cameras ( SUM(Stars.no_measurements) )
amp	real	amplitude of Stars.magnitude changes - mainly for variable stars ( not sure if filled )
pi_id	integer	link to internal ID of star in other PI catalog
mag	real	better not to use it now, I am not sure what the difference between v_mag and this is, probably it must be clarified. In future mag should be average of Stars.magnitude ( AVG(Stars.magnitude) ), but I am not sure if this is the case at the moment ( maybe in 2006_2009 database yes)
j_mag	real	J magnitude from 2MASS ( script cross_correlate_catalogs.pl must be used to fill it - on 2006_2009 it is filled )
h_mag	real	H magnitude from 2MASS ( script cross_correlate_catalogs.pl must be used to fill it - on 2006_2009 it is filled )
k_mag	real	K magnitude from 2MASS ( script cross_correlate_catalogs.pl must be used to fill it - on 2006_2009 it is filled )
twomass_match_count	integer	number of counterparts in 2MASS catalog, depends on search radius ( script cross_correlate_catalogs.pl must be used to fill it - on 2006_2009 it is filled )
twomass_id	integer	link to internal ID of star in 2MASS database for example 2mass@heplx61 ( script cross_correlate_catalogs.pl must be used to fill it - on 2006_2009 it is filled )
is_ref_star	integer	flag to mark best stars to be reference stars, same field is also in STARS table and that one takes priority and is more important ( filled on 2006_2009, see here )	NULL/0 -not a ref-star, 1 - good reference star
source_catalog	integer	ID of source catalog, if external SEED catalog was used	0/null-none, 1-TYCHO, 2-GSC

e/ Table InterestingObjects

This table is created in order to keep objects which we want to follow and analyse their lightcurves. If one would like to follow a given object
it should be added to table InterestingObjects on remote machine pi1 or pi2 with new io_type value.
Then synchronization of new io_type should be added to synchronizing script get_io.sh on heplx40 machine.
Definition of table :

COLUMN NAME	TYPE	DESCRIPTION	VALUES
io_id	serial	internal id of record
io_name	character varying(64)	name of object
io_ra	double precision	right ascension [hours decimal]	0 - 24 example : 3.34354554
io_dec	double precision	declination [degrees]	0 - 360 example : -23.43454554
io_star_k2a	integer	link to STAR.id field - record star in pi catalog in camera k2a	>=0 or null
io_star_k2b	integer	link to STAR.id field - record star in pi catalog in camera k2b	>=0 or null
io_type	integer	type of interesting object - new types can be added , currently [0,11] , but new values will be added soon	>=0
io_vmag	double precision	visual magnitudo
io_comment	character varying(128)	user comment
io_quality	integer default 0	value of object - can be changed manually by user	>=0
io_run	integer default 0	run number - currently not used	>=0
io_bmag	double precision	B - magnitudo
io_star_type	character varying(8)	star type - as in SIMBAD
io_no_measurements	integer default 0	number of measurements of object in pi-catalog	>=0 or null
io_match_distance	double precision	distance from pi star to interesting object in arcsec, resulting from matching procedure	>=0 or null

f/ Usage

Description of database examples and usage can be found in the following document.

g/ Cataloging procedure

Detail description of pi-cataloging procedure is described in separate document.

5. External GRB tables :

a/ Table Alert :

COLUMN NAME	TYPE	DESCRIPTION	VALUES
alert_id	serial	internal id of alert record
source_id	integer	id of source - possible values can be found here, definition table alert_source_type
alert_type	integer	alert type - possible can be found here
trg_num	integer	trigger number - internal number of experiment ( is not unique )
seq_num	integer	trigger sequence number
instrument_id	integer	ID of instrument, instruments are defined in table alert_instruments, values can be found here
alert_status	integer	List of allowed values for this field can be found here , link to field alert_status.type_id , check definition of table Alert_Status, below
datetjd	integer	TJD - Truncated Julian Date
date	date	date
time	float	time in : SOD - Seconds Of Day in 1/100 of second ( hundreds )
rays	string	keV band
band	string	keV band
flax	integer	??? - possible this is flax we should as Michal Zaczek if this is the same ...
duration	integer	T90 in mili seconds
gamma_rate	integer	number of photons per integration time - this can depend on experiment and should be described in comment field
coordra	double	RA - Right Ascension in degrees decimal
coorddec	double	DEC - declination in degrees decimal
coord1	double	RA error [arcsec]
coord2	double	DEC error [arcsec]
fname	string	ALERT message filename
grb_id	integer	link to GRB record
unix_time	integer	unix time of event
sigma	double	sigma above background
validity	integer	trigger validity flag, possible values are listed here , NOTE : possible this is duplication of field alert_status and does not need to be used !
coorderr	double	error of position [arcsec]
gal_long	double	galactic longitude in decimal degrees
gal_lat	double	galactic latitude in decimal degrees
ecl_long	double	ecliptic longitude in decimal degrees
ecl_lat	double	ecliptic latitude in decimal degrees
flux1	double	flux in band 1 - units ???
flux2	double	flux in band 2 - units ???
flux3	double	flux in band 3 - units ???
flux4	double	flux in band 4 - units ???
flux	double	total flux - units ???
comment	string	experiment operators comment from WWW, or our pi-people comment

b/ Table GRB :

COLUMN NAME	TYPE	DESCRIPTION	VALUES
grb_local_id	serial	internal ID of grb record
trg_num	integer	trigger number
name	string	GRB name
date	date	grb date
time	integer	time in SOD - Seconds Of Day
grb_status	integer	status of GRB , same as for alert_status, list of allowed values for this field can be found here
is_hete	boolean	seen by HETE ?	true/false
is_integral	boolean	seen by INTEGRAL ?	true/false
is_ipn	boolean	is from IPN ?	true/false
is_gcn	boolean	is from GCN ?	true/false
is_konus	boolean	seen by KONUS ?	true/false
is_swift	boolean	seen by SWIFT ?	true/false
z	string	measured redshift , or limit
has_ot	boolean	has Optical Transient	true/false
final_status	string	final status

In order to load external data the following script shoud be run in this order :
                load_batse_catalog.pl
              load_all_konus!
                load_all_spiacs!
                update_gcn_db.sh /opt/pi/dev/pisys/pidb/downloads/

c/ Table SN :

COLUMN NAME	TYPE	DESCRIPTION	VALUES
sn_id	serial	internal ID - automatic incement
sn_name	character varying(10)	name of supernova
sn_host_glx	character varying(16)	name of host galaxy
sn_date	date	discovery date
sn_ra	double precision	right ascension in degrees
sn_dec	double precision	declination in degrees
sn_offset	character varying(16)
sn_mag	double precision	brightness in maximum
sn_type	character varying(4)	type of SN
sn_discoverer	character varying(64)	name of discoverer
sn_create_dtm	date	record creation date and time

6. Setup Tables :

a/ Table alert_source_type - Sources definitions :

COLUMN NAME	TYPE	DESCRIPTION	VALUES
source_id	integer	flag defining source, see here also
description	string	source description / name
additional_info	string	additional info
www_links	string	link to information on WWW, coma separated

b/ Table alert_instruments - Instruments definitions :

COLUMN NAME	TYPE	DESCRIPTION	VALUES
instr_id	integer	flag defining instrument, see here also
source_id	integer	satellite id, link to table alert_source_type.source_id
descr	string	name / description
low_energy	double	low limit of energy detector
up_energy	double	upper limit of energy detector
full_name	string	full name of instrument
www_links	string	links to information on WWW, coma separated
energy_units	string	energy units : keV, nm

c/ Table alert_status

COLUMN NAME	TYPE	DESCRIPTION	VALUES
type_id	integer	flag value
descr	string	meaning of flag

d/ Table Field_def

This table stors known field, by default they are load from initialization script init_fields.sql , but later in case frame with unknown field comes
this field is added to table automaticaly but it has flag fd_original set to false

COLUMN NAME	TYPE	DESCRIPTION	VALUES
fd_id	serial	internal ID of field definition record
fd_name	character varying(16)	name of field for example : 0000+00.0
fd_ra	double precision	right ascension of the field [deg] decimal
fd_dec	double precision	declination of the field [deg] decimal
fd_ra_h	double precision	right ascension of the field [h] decimal
fd_original	boolean	flag if this is original field ( initialized from script init_fields.sql ) or added later

7. Database Backups :

Pi-database is stored in two places on pi2.lco.cl and heplx40. Backups of heplx40 database are performed weakly from crontab using custom written script pidb_backup_heplx40.sh.
Backups are stored stored in directory : /lhome/piwww/www/pi0/pidb/backup/YYYYMMDD and old one's are cleaned after a month.
Backups on pi2 are stored in /opt/pi/dev/pisys/pidb/backup/YYYYMMDD.
Backups of scan2005, scan_single and 2004_2005 databases are stored on heplx49:/disk02/backup/pidb/ ( but only stars, superstar, frame and frame_det ,
measurements table is not backuped now )
This table lists location of database backups at hoza , NOTE : table measurements is not backuped in most cases as it takes a lot of disk space ! :

DATABASE NAME	DESCRIPTION	LOCATION
2004_2005	catalog of asas-pipeline , 20 frames averaged, only tables Stars,SuperStar backup, database originaly on heplx49	/disk02/backup/pidb/asas20042005_heplx49
scan	catalog of scan images, on heplx47, only Stars,SuperStar	/opt/pi/dev/pisys/daq/ndir/data/backup/scan
scan	novaevents table backup	heplx43:/disk01/backup/novaevents

8. Currently filled production databases :

Public databases :

COMPUTER	DATABASE NAME	DESCRIPTION	STATUS
heplx40	2004_2005	2004-2005 aver20 data ( backup on heplx61 )	READY
heplx40	2006_2007	2006-2007 aver20 data ( backup on heplx61 )	READY

a/ Star catalogs

COMPUTER	DATABASE NAME	DESCRIPTION	STATUS
heplx46	scan2004	all scans (20040911-200505) , cataloged frames resulting from averaging of 3 single scan frames	READY
heplx46 (N EW)	scan2005	all scans (20040911-200505) , cataloged frames resulting from averaging of 3 single scan frames. Some corrections in cataloging procedure where done : 1/ matching radius enlarged to 120 arcsec 2/ camera identification corrected ( matching to stars from proper camers fixed ) NOTE : no double measurements skiping , k2a <-> k2b matching with radius 5 arcmin	READY
heplx46	scan_single	catalog of single scan frames (200409-200505)	READY
heplx46	scan_small	same as scan@heplx47 - small scan dataase 200501-200503	READY
heplx46	S0448_60	database of single scan field S0448-60	READY
heplx48	2004_2005	Copy of 2004_2005 database on heplx47 but with new_star field added and filled !	READY
heplx48	S0448_60	database of single scan field S0448-60 ( copy of heplx46 db ) , however SuperStar table is filled - good for new interface testing NOTE - link to second camera not working properly on this interace - M.Biskup new interface ,ust be used instead
heplx48	20050301_asas	test dabase catalogued with ast files from asas-pipeline
heplx48	20050301_asas2	same as above - for comparison
heplx48	20050301_pi	test database catalogued with ast files from pi-pipeline
heplx48	20050301_test	run_cat_parallel! test - cataloging of ast files
heplx48	20050301_test2	pi-pipeline , on-line - averaging of images ( 20 ) and cataloging in the same time
heplx48	20050301_test3	te same asty co w 20050301_test2 ale skatalogowane normalnym synchronicznym skryptem do katalogowania - w celu porownania bo test2 rozni sie mocno od asas i szukam przyczyny
heplx49 ( NEW)	2004_2005@h eplx49 2004_2005 ( OLD)	asas-pipeline cataloged after some corrections : 1/ matching radius changed to 120 arcsec 2/ camera identification corrected ( matching to stars from proper camers fixed ) 3/ option -skip_dups enabled, rejecting in case more then 1 measurements on single frame NOTE : no double measurements skiping , k2a <-> k2b matching with radius 5 arcmin	READY
heplx47	2004_2005	asas-pipeline catalog ( 20 images summed ) , all available data 200407-200505 catalog, field new_star is missing on this database due to lack of disk space on /disk01 , database must be moved before adding new_star field and filling it	READY
heplx47	scan	test database with scans from period 200501-200503 cataloged - 3 frames averaged It will be removed soon due to lack of space on heplx47:/disk01	READY to be removed
heplx47 (NEW)	scan_single	database of single scan frames NOTE : no double measurements skiping , k2a <-> k2b matching with radius 5 arcmin	READY
heplx43	pidb_test	test database of period 20050201-20050210 prepared for testing purposes for group of people from W.Ogloza	READY
heplx43	20040814_jf	database for testing shadow of shutter ( border cut = 3 pixels only ) , to be compared with same night catalogued with ASAS algorithm kasia1 For J.Falzmann tests	READY
heplx43	20050511_jf	AutoGuiding on , border=3, also for shadow tests by JF	READY
heplx43	20041201_jf	No AG , border=3, for shadow tests by JF	READY
heplx43	fast1	catalog of 20050301 asas-pipeline , but with cataloging option -skip_dups enabled, this means in case star has more then 1 measurment on single frame all are skiped	READY
heplx43	fast2	same as fast1 , but also requirement for minimum number of averaged frames is enabled on cataloging level	loading ...
heplx48	special_data_aver20 , special_data_single	databases for special data from LCO	to be filled
heplx48	200710	NEW : database to be dumped and stored on CD/DVD for student developing migration PostgreSQL -> DB2 , nights : 20071001 , 20071002, 20071003, 20071004, 20071005, 20071006, 20071007, 20071008, 20071009, 20071010 Dane skatalogowane na heplx48:/disk02/results/cat/200710 dnia 2007-11-08 przez msok , pg_dump dump on DVD	READY

Fast photometry databases :

fast_photometry	2004/2005		loading ... ( some are READY )
heplx43	20050511		READY
heplx43	20050203		READY
fast ( NEW )	2004/2005	database storing all available pi data ( fits ) cataloged with fast photometry	loading ...

some selected asas-astrometry nights catalogued to separate databases :

20040918			READY
20040921			READY
20040922			READY
20041201			READY
20041203			READY
20050202			READY
20050301			READY
20050302			READY

Test databases ( which can be removed without big loss ):

COMPUTER	DATABASE NAME	DESCRIPTION	STATUS
heplx48	Border size tests : 20050401_100 - border=100 pixels 20050401_50 - border=50 pixels 20050401_20 - border=20 pixels	Border size tests :	READY

external star catalogs gcvs , tycho etc ... :

heplx48	tycho	database storing gcvs and tycho catalogs , they are stored in separate tables gcvs and tycho, this database structure is not compatible with our other databases so there is currently no gui interface to it	READY

New interface is used in database available here

To check other databases see here.
Some nice lightcurves are listed in file varstars.html

b/ data (frames, events ) databases :

COMPUTER	DATABASE NAME	DESCRIPTION	STATUS
heplx40	pidb	MAIN DATABASE	WORKING
heplx40	pidb_bacup	BACKUP DATABAE - copy of pi2 database done on 2005-11-17	TO BE DONE

c/ databases in LCO

DATABASE NAME	COMPUTER	DESCRIPTION	STATUS
pidb	pi2 ( /data2/pidb/ )	database of all images collected by system	READY
scan	pi2 ( /data2/pidb/ )	scan database data 200605-200704	FILLED
scan2007	pi2 ( /data2/pidb/ )	scan database 200705-	NOT READY
scan_single	pi2 ( /data2/pidb/ )	database of single image from scan, only several previous nights and clear	READY
aver20_2006	pi3 ( /data3/pidb )	database of pi-pipeline , images 20 averaged images	READY

d/ external databases

DATABASE NAME	COMPUTER	DESCRIPTION	STATUS
gsc	heplx60.fuw.edu.pl	full GSC star catalog	READY

9. Usfull links

Postgres links :
- transaction limits : http://pgsqld.active-venture.com/datatype-oid.html
- loging database information : http://www.postgresql.org/docs/8.1/interactive/runtime-config-logging.html
     It is very nice option to enable logging of statements :
                 log_min_messages = notice
                 log_statement = true

10. Optimizing postgres database

NOTE : be cerfull with units , here I have everything in bytes , but in new versions of postgres MB, GB kB etc are used

PARAMETER	DESCRIPTION	DEFAULT	OPTIMIZED
shared_buffers	memory size for shared buffers maximum size of shared memory buffer must be enlarged : cat /proc/sys/kernel/shmmax echo 128000000 > /proc/sys/kernel/shmmax	100	10000
sort_mem	memory size allocated for sorting operations	not set : # sort_mem=1024	32168
fsync	turns forced synchronization on or off	not set : # fsync = true	false
wal_sync_method	the default varies across platforms	not set : #wal_sync_method = fsync	fsync
wal_buffers	min 4, 8KB each	not set : #wal_buffers = 8	10
commit_delay	range 0-100000, in microseconds	not set : #commit_delay = 0	5
commit_siblings	range 1-1000	not set : #commit_siblings = 5	30
checkpoint_segments	in logfile segments, min 1, 16MB each	not set : #checkpoint_segments = 3	5
stats_start_collector	domyslnie wylaczona, ale dobra do sprawdzania statystyk	not set : #stats_start_collector = true	true
max_fsm_pages	paramter sluzacy do czyszczenia bazy przy pomocy komendy VACUUM ANALYZE jak jest za maly to sie nie chce wyczyscic . Defaultowo byl za maly i na bazie scan @ pi2 mialem : WARNING: relation "public.measurements" contains more than "max_fsm_pages" pages with useful free space HINT: Consider compacting this relation or increasing the configuration parameter "max_fsm_pages". zmianilem na 310000 , moze czasem trzeba nawet wiecej , ale tutaj tyle wytarczylo	153600	310000

Links usfull for optimizations :
    1/ postgres doc page : http://www.postgresql.org/docs/8.1/interactive/performance-tips.html

In older version of postgres ( 7.1 ) which is installed on pi1, pi2 and pi3 computers at LCO , it is nessesary to set the following paramter
in order to improve usage on indexes :

    set enable_hashjoin = false;
or better :
   SET enable_seqscan = false;

or set this paramters permanently in postgresql configuration file , for more details see here or here

11. Discussion on final version on database

First sentence in discussion is here.

12. Database diskspace usage :

DATABASE NAME	DESCRIPTION	NIGHTS	# NIGHTS	# CAMERA	TOTAL USAGE	USAGE PER NIGHT
2004_2005@heplx49	asas pipeline ( 20 averaged )	20040901 - 20050528	235	2	117GB	~0.5GB ( 2 cameras -> 0.25 GB per camera)
scan2005@heplx46	scan averaged frames	20040901 - 20050528	234	2	42GB	~180MB ( 2 cameras -> 90MB per camera )
scan_single@heplx46	scan single frames	20040901 - 20050528	234	2	?	?
scan_single@heplx47	scan single frames	20040901 - 20050528	212	2	30GB	~150 MB ( 2 cameras -> 75MB per camera )
20050509@heplx48	fast photometry of 20050509, 2451 frames	20050509 - 2451x2 frames	1	2	~4GB	2451 frames , czyli na miesiac ~30x4GB = 120GB rok - 365x4GB = 1.4 TB ( ~3.5 dysku 400 GB ) jeszcze to potwierdzic dla kolejnych nocys

13. Configuration hints

in order to open connection to remote host with choosen user ( with limited grants - only for selecting for example ) , the
following line should be added to file pg_hba.conf ( example on heplx42 : /var/lib/pgsql/data/pg_hba.conf ) :
host pidb_test pidb_remote 193.0.84.112 255.255.255.255 password
this will require identification with password from user pidb_remote. Now it will be tested on pi2 so that synchronization will be
performed from heplx40
enabling/disabling TCP/IP connection , edit postgresql.conf file and set :
tcpip_socket = true
In newer version there is no tcpip_socket paramter , but you must change :
listen_addresses = 'localhost'
to :
listen_addresses = '*'
NOTE : it must be uncommented !!!
enabling loging of important messages :
       1/ edit file postgresql.conf
       2/ set the following variables and values :
            postgres 8.x :
           log_min_messages = notice
           log_statement = 'all'
            postgres 7.x :
               server_min_messages = notice
               log_statement = true
database configuration description can be found here
enabling access from other computer in iptables ( opening firewall for 5432 port ) :
# db synchronization
-A RH-Firewall-1-INPUT -s 193.0.84.134 -m tcp -p tcp --dport 5432 -j ACCEPT

14. Usfull sql statements :

checking what indexes are used , example :
EXPLAIN select id,ra,dec,camid from stars where dec>=(-12.8400-300.00/3600) and dec<=(-12.8400+300.00/3600) and calcdistradec(ra,dec,23.0403,-12.8400)*(180.0/pi())*3600.00<300;
                                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using stars_dec_index on stars (cost=0.00..48016.06 rows=4079 width=24)
   Index Cond: (("dec" >= -12.9233333333333::double precision) AND ("dec" <= -12.7566666666667::double precision))
   Filter: (((calcdistradec(ra, "dec", 23.0403::double precision, -12.84::double precision) * 57.2957795130823::double precision) * 3600::double precision) < 300::double precision)
(3 rows)

15. Database log files

Table above shows were database log files can be found on pi machines at hoza , this file can be viewed as postgres or root user , description how
to enable logging database information can be found in previous chapter :

Computer	Log files location
heplx49 ( sum20 database - big database )	/disk01/pidb/pg_log
heplx46	/pidb/postgres/pg_log
heplx47 ( scan )	/disk01/pidb/pg_log
heplx40 ( asas database , frames )	/tmp/postgres_start.log

16. Database synchronization

In order to be able to synchronize database on heplx40 with remote database on pi2 ( SMOK ) and have at least frame and events information localy
perl script synch_db.pl was created, it was tested on SMOK at hoza from heplx40 machine in location :
heplx40:/opt/pi/dev/pisys/daq/ndir/data/db/
synch_db.pl -remote_db=pidb_test -remote_host=heplx45 -local_db=pidb_smok_test -local_host=heplx40 -night=20040929 -do_insert -verb
It is working very well

17. Database pgsql functions usfull for analysis

FUNCTION NAME	PARAMTERS	RETURNS	DESCRIPTION
CalcDistRADEC	CalcDistRADEC( ra1 ,dec1, ra2, dec2 ) ra1,dec1 - coordinates of first position ra1 [decimal hours] , dec1 [ decimal degrees ] ra2,dec2 - coordinates of first position ra2 [decimal hours] , dec2 [ decimal degrees ]	Distance in RADIANS of 2 points (ra1,dec1) and (ra2,dec2)	result in RADIANS
CountNearStars	CountNearStars(ra_hours,dec_degrees,radius_arcsec,camera,mag_limit,other_then_star) camera - 2 for k2a, 3 - for k2b	number of stars near to given postion (ra_hours,dec_degrees) in radius_arcsec on given camera brighter then mag_limit and different then given star (other_then_star)	count
CountCloseToStarSameNight

18. Optimization of pi database

heplx40 :
- it was nessasry to drop trigger :
DROP TRIGGER tr_new_frame ON frame;
in this database it is not very important to have ObsFieldStat table filled, as there is now analysis running on this database currently

19. Handling of problems with database

CLUSTER command canceled due to computer crash or kill or anything else :
Problem polega na tym ze po padzie komendy CLUSTER nie czyszcza sie tymczasowe pliki przez niego tworzone i nie odzyskuje sie automatycznie miejsce na dysku.
Probowalem to odzyskac komenda : vacuumdb -af , ale to praktycznie nic nie daje. Dlatego trzeba recznie identyfikowac te pliki po czasach ich utworzenia , porownaniu
z logiem /var/log/messages kiedy byl pad systemu oraz z datami i timestampami plikow make_cluster.out i recalc_night.log. I da sie po tym praktycznie na 100% ustalic
ktore pliki mozna spokojnie usunac , a ktorych nie. Nastepnie trzeba je movnac w jakies inne miejsce i sprawdzic czy wszystko dziala skryptem testdb! , ktory robi
pewne podstawowe selecty i \d opisy tabel.
Oto czesc moich zapisek z tych napraw , ktore robilem 2007-02-01 po kilku crashach pi2 :



cluster po padzie, mozna chyba usnac pi2:/data1/pidb/base/26590548/*
                                           /data2/backup/pidb/base/26590548/*
      588817269
      588809187
zrobic cluster na jakiejs innej malej bazie i co bedzie , jakie pliki
przybywaja , sprawdzic :
    vacuumdb -af
napisac skrypcik ktory przeszuka tego co zwraca ten :
   select relfilenode from pg_class;
PLAN :
   1/ vacuumdb -af
   2/ backup stars i superstar : /data2/backup/pidb
   3/ backup plikow ktore bede usuwal ... usuniecie plikow,
      test # recordow w bazie itd ...

zrobic dumpa ze stars i superstar , ale nie measurements i
zrobic po prostu kopie tych plikow ktore usuwam i probowac ..

Chyba nauczylem sie naprawiac padniety cluster , trzeba po czasach
powstania plikow w pidb/base/ dojsc ktore to sa te z padnietgo clustra
( porowanc czasy padu systemu z /var/log/messages oraz czasy w logach
   od katalogowania i timestamp make_cluster.out oznacza kiedy dokladnie
   zaczal sie robic CLUSTER po tym mozna dosc ktore to pliki powstaly
w ramach tego padnietgo CLUSTER i to je trzeba usunac !
Teraz wyglad aze jests ok, na razie nie usuwam zadnych innych plikow
tylko te 2 : 588809187* 588817269*
More on problems on piwiki-problems (there is handling of failed cluster there ) and piwiki-problems-db

20. Starting new star catalog

It can be done using TYCHO-2 star catalog, take it from :
cd heplx43:/opt/pi/dev/pisys/daq/ndir/data/cat/tycho_new
tycho.sql.gz
Load and enjoy ( there is a copy on pi3:/data1/results/cat/tycho )

21. Starting new star catalog from old one

In order to start new star catalog as continuation of the old one the follwoing steps should be done :

create new catalog : loging as postgres goto /data1/pidb/ and execute , for example : ./dodb! scan2007
create users by runnuing script create_user.sql
make dump of the current database :
- cd /backup/scan
- pidb_dump_catalog! scan
load dumped database to new database :
pidb_load_catalog! scan2007
Now there are 2 possibilities :
1) you want to continue old catalog ( stars with same numbers etc ) , in this case you don't need to do anything
2) you want to continue only Frames,Frame_det, ObsFieldStat etc , but start new star catalog, then execute :
             drop table stars;
             \i create
      and in case you want to start from TYCHO-2 catalog :
             cd $DATADIR/cat/tycho2_new/
             psql scan2007 -U pidb_user < tycho2.sql
             DELETE FROM Stars WHERE magnitude>13; ( to have nova algorithm working fine )
fix sequences : select fix_seq_star_id( MAX_ID_FRM )
clean links in Interesting objects ( only in option 2) ) : UPDATE InterestingObjects SET io_star_k2a=-1,io_star_k2b=-1 WHERE io_star_k2a>0 OR io_star_k2b>0;

This scenario was done ( with loading TYCHO-2 catalog ) with database scan -> scan2007.

22. Calling C procedures from pg/sql

It is working, it is possible to write shared library ( libastsql.so ) and call C functions from pg/sql procedures. I did it in order to have ad2xy functions in pg/sql it was quite easy. Library is $SRCDIR/ccd/fitslib/astsql and functions are also in pidb_ast.sql Interesting information concenring this subject can be found here Notes :

napisac ad2xy - skompilowac jako C i tutaj : http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html#AEN37037 i mam to w postgreSQL !
/pi20/msok/badania/postgres_c
Dziala !!!