// Join BDR Group
func (r *RDPG) bdrGroupJoin() (err error) {
log.Trace(fmt.Sprintf(`rdpg.RDPG<%s>#bdrGroupJoin() Joining BDR Group rdpg for cluster...`, ClusterID))
p := pg.NewPG(globals.MyIP, pgPort, `rdpg`, `rdpg`, pgPass)
joinPG := pg.NewPG(bdrJoinIP, pgPort, `rdpg`, `rdpg`, pgPass)
re := regexp.MustCompile(`[^0-9]+`)
ip := strings.ToLower(string(re.ReplaceAll([]byte(globals.MyIP), []byte("_"))))
localNodeName := fmt.Sprintf(`rdpg_%s`, ip)
err = p.BDRGroupJoin(localNodeName, `rdpg`, *joinPG)
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#bdrGroupJoin(%s,rdpg) {HINT: Check pgbdr logs and pg_hba.conf} ! %s`, ClusterID, localNodeName, err))
return
}
return
}
func (c *Credentials) Find() (err error) {
log.Trace(fmt.Sprintf(`cfsb.Credentials#Find(%s) ... `, c.BindingID))
if c.BindingID == "" {
return errors.New("Credentials ID is empty, can not Credentials#Find()")
}
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("cfsb.Credentials#Find(%s) PG#Connect() ! %s", c.BindingID, err))
return
}
defer db.Close()
sq := fmt.Sprintf(`SELECT id,instance_id,binding_id FROM cfsb.credentials WHERE binding_id=lower('%s') LIMIT 1`, c.BindingID)
log.Trace(fmt.Sprintf(`cfsb.Credentials#Find(%s) SQL > %s`, c.BindingID, sq))
err = db.Get(c, sq)
if err != nil {
if err == sql.ErrNoRows {
log.Error(fmt.Sprintf("cfsb.Credentials#Find(%s) ! Could not find binding with given Credentials ID", c.BindingID))
} else {
log.Error(fmt.Sprintf("cfsb.Credentials#Find(%s) ! %s", c.BindingID, err))
}
}
return
}
// Create Credentials in the data store
func (c *Credentials) Create() (err error) {
log.Trace(fmt.Sprintf(`cfsb.Credentials#Create(%s,%s) ... `, c.InstanceID, c.BindingID))
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("cfsb.Credentials#Create(%s) ! %s", c.BindingID, err))
return
}
defer db.Close()
err = c.Find()
if err != nil { // Does not yet exist, insert the credentials.
if err == sql.ErrNoRows { // Does not yet exist, insert the credentials.
sq := fmt.Sprintf(`INSERT INTO cfsb.credentials (instance_id,binding_id,host,port,dbuser,dbpass,dbname) VALUES (lower('%s'),lower('%s'),'%s','%s','%s','%s','%s');`, c.InstanceID, c.BindingID, c.Host, c.Port, c.UserName, c.Password, c.Database)
log.Trace(fmt.Sprintf(`cfsb.Credentials#Create() > %s`, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf(`cfsb.Credentials#Create() %s ! %s`, sq, err))
}
} else {
log.Error(fmt.Sprintf(`cfsb.Credentials#Create() c.Find() binding %s ! %s`, c.BindingID, err))
}
return
} else { // Credentials already exists, return.
log.Trace(fmt.Sprintf(`cfsb.Credentials#Create() Credentials already exist for binding %s, returning`, c.BindingID))
return
}
}
func getLockCountByDatabase(dbname string) (locks []databaseLocks, err error) {
locks = []databaseLocks{}
sql := `SELECT mode::text as mode, count(mode) as mode_count, datname::text as dbname FROM pg_locks, pg_database WHERE database=oid`
if dbname != "" {
sql += fmt.Sprintf(` AND datname = '%s'`, dbname)
}
sql += ` GROUP BY mode, datname;`
pgPort := `7432`
address := `127.0.0.1`
pgPass = os.Getenv(`RDPGD_PG_PASS`)
p := pg.NewPG(address, pgPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
return nil, err
}
defer db.Close()
err = db.Select(&locks, sql)
if err != nil {
log.Error(fmt.Sprintf("admin.getLockCountByDatabase ! db.Select(&locks, %s) erred : %s", sql, err.Error()))
return nil, err
}
return locks, nil
}
// This is called on the management cluster when it is running the scheduled task
// which reconciles the databases comparing against the service clusters lists.
func (i *Instance) Reconcile() (err error) {
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("instances.Register() p.Connect(%s) ! %s", p.URI, err))
return err
}
defer db.Close()
err = i.Lock()
if err != nil {
log.Error(fmt.Sprintf("instances.Instance#Reconcile(%s) Failed Locking instance %s ! %s", i.Database, i.Database, err))
return
}
ei, err := FindByDatabase(i.Database)
if err != nil {
log.Error(fmt.Sprintf("instances.Instance#Reconcile() ! %s", err))
} else if ei == nil {
log.Trace(fmt.Sprintf(`instances.Instance#Reconcile() Reconciling database %s for cluster %s`, i.Database, i.ClusterID))
sq := fmt.Sprintf(`INSERT INTO cfsb.instances (cluster_id,service_id ,plan_id ,instance_id ,organization_id ,space_id,dbname, dbuser, dbpass,effective_at) VALUES ('%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s',CURRENT_TIMESTAMP)`, i.ClusterID, i.ServiceID, i.PlanID, i.InstanceID, i.OrganizationID, i.SpaceID, i.Database, i.User, i.Pass)
log.Trace(fmt.Sprintf(`instances.Instance#Reconcile(%s) > %s`, i.Database, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf("instances.Instance#Reconcile(%s) ! %s", i.Database, err))
}
}
err = i.Unlock()
if err != nil {
log.Error(fmt.Sprintf(`instances.Instance#Reconcile(%s) Unlocking ! %s`, i.InstanceID, err))
}
return
}
// General Boostrapping that should occur on every node irrespective of role/leader.
func (r *RDPG) initialBootstrap() (err error) {
log.Trace(fmt.Sprintf(`rdpg.RDPG<%s>#initialBootstrap() Beginning general Bootstrapping...`, ClusterID))
// TODO: Record somehow that general bootstrap was completed and do not re-run.
p := pg.NewPG(`127.0.0.1`, pgPort, `postgres`, `postgres`, ``)
err = p.CreateUser(`rdpg`, pgPass)
if err != nil {
log.Error(fmt.Sprintf(`r.RDPG<%s>#initialBootstrap() CreateUser(rdpg) ! %s`, ClusterID, err))
return
}
err = p.CreateUser(`health`, `check`)
if err != nil {
log.Error(fmt.Sprintf(`r.RDPG<%s>#initialBootstrap() CreateUser(health) ! %s`, ClusterID, err))
return
}
// TODO: ALTER USER health SET default_transaction_read_only=on;
priviliges := []string{`SUPERUSER`, `CREATEDB`, `CREATEROLE`, `INHERIT`}
err = p.GrantUserPrivileges(`rdpg`, priviliges)
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#initialBootstrap() p.GrantUserPrivileges(rdpg,...) ! %s`, ClusterID, err))
return
}
err = p.CreateDatabase(`rdpg`, `rdpg`)
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#initialBootstrapping() CreateDatabase() ! %s`, ClusterID, err))
return
}
err = p.CreateDatabase(`health`, `health`)
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#initialBootstrapping() CreateDatabase() ! %s`, ClusterID, err))
return
}
exts := []string{`btree_gist`, `bdr`, `pgcrypto`}
err = p.CreateExtensions(`rdpg`, exts)
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#initialBootstrap() CreateExtensions() ! %s`, ClusterID, err))
return
}
err = r.Register()
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#initialBootstrap() Register() ! %s`, ClusterID, err))
return
}
err = r.waitForClusterNodes()
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#initialBootstrap() r.waitForClusterNodes() ! %s`, ClusterID, err))
return
}
// TODO: Record somehow that general bootstrap was completed and do not re-run.
return
}
func (c *Catalog) Fetch() (err error) {
log.Trace(`cfsb.Catalog#Fetch()...`)
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("cfsb.Catalog#Fetch() ! %s", err))
return
}
defer db.Close()
sq := `SELECT service_id,name,description,bindable FROM cfsb.services;`
log.Trace(fmt.Sprintf(`cfsb.Catalog#Fetch() > %s`, sq))
err = db.Select(&c.Services, sq)
if err != nil {
log.Error(fmt.Sprintf("cfsb.Catalog#Fetch() db.Select() ! %s", err.Error()))
return
}
// TODO: Account for plans being associated with a service.
for i, _ := range c.Services {
service := &c.Services[i]
sq := `SELECT plan_id,name,description FROM cfsb.plans;`
log.Trace(fmt.Sprintf(`cfsb.Catalog#Fetch() > %s`, sq))
err = db.Select(&service.Plans, sq)
if err != nil {
log.Error(fmt.Sprintf("cfsb.Catalog#Fetch() db.Select() ! %s", err.Error()))
return
}
c.Services[i].Tags = []string{"rdpg", "postgresql"}
// c.Services[i].Dashboard = DashboardClient{}
}
return
}
/*
columnMigrations migrates columns testing for conditions,
eg. handle the migration of pre-existing environments.
*/
func columnMigrations() (err error) {
p := pg.NewPG(`127.0.0.1`, pgPort, `rdpg`, `rdpg`, pgPass)
p.Set(`database`, `rdpg`)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf(`rdpg.columnMigration() Could not open connection ! %s`, err))
return
}
defer db.Close()
sq := fmt.Sprintf(`SELECT constraint_name FROM information_schema.table_constraints WHERE table_name='instances' AND constraint_type='UNIQUE';`)
log.Trace(fmt.Sprintf("rdpg.columnMigrations() %s", sq))
var constraintName string
if err = db.QueryRow(sq).Scan(&constraintName); err != nil {
if err == sql.ErrNoRows {
log.Trace(fmt.Sprintf("The instance table db name is not set UNIQUE constraints"))
_, err = db.Exec(`ALTER TABLE cfsb.instances ADD CONSTRAINT instances_dbname_key UNIQUE (dbname)`)
if err != nil {
log.Error(fmt.Sprintf("rdpg.columnMigrations()%s", err))
return
}
} else {
log.Error(fmt.Sprintf("rdpg.columnMigrations() ! %s", err))
return
}
}
return
}
// This is called on the management cluster when the service cluster has created
// a new database and is registering it's avialabilityh with the management cluster.
func (i *Instance) Register() (err error) {
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("instances.Register() p.Connect(%s) ! %s", p.URI, err))
return err
}
defer db.Close()
err = i.Lock()
if err != nil {
log.Error(fmt.Sprintf("instances.Instance#Register(%s) Failed Locking instance %s ! %s", i.Database, i.Database, err))
return
}
sq := fmt.Sprintf(`INSERT INTO cfsb.instances (cluster_id,dbname, dbuser, dbpass,effective_at) VALUES ('%s','%s','%s','%s',CURRENT_TIMESTAMP)`, i.ClusterID, i.Database, i.User, i.Pass)
log.Trace(fmt.Sprintf(`instances.Instance#Register(%s) > %s`, i.Database, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf("instances.Instance#Register(%s) ! %s", i.Database, err))
}
err = i.Unlock()
if err != nil {
log.Error(fmt.Sprintf(`instances.Instance#Register(%s) Unlocking ! %s`, i.InstanceID, err))
}
return
}
func (b *Binding) Remove() (err error) {
log.Trace(fmt.Sprintf(`cfsb.Binding#Remove(%s) ... `, b.BindingID))
err = b.Find()
if err != nil {
log.Error(fmt.Sprintf(`cfsb.Binding#Remove(%s) ! %s`, b.BindingID, err))
return
}
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("cfsb.Binding#Remove(%s) ! %s", b.BindingID, err))
return
}
defer db.Close()
// TODO: Scheduled background task that does any cleanup necessary for an
// unbinding (remove credentials?)
sq := fmt.Sprintf(`UPDATE cfsb.bindings SET ineffective_at=CURRENT_TIMESTAMP WHERE binding_id=lower('%s')`, b.BindingID)
log.Trace(fmt.Sprintf(`cfsb.Binding#Remove(%s) SQL > %s`, b.BindingID, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf(`cfsb.Binding#Remove(%s) ! %s`, b.BindingID, err))
}
b.Creds = &Credentials{
InstanceID: b.InstanceID,
BindingID: b.BindingID,
}
err = b.Creds.Remove()
if err != nil {
log.Error(fmt.Sprintf(`cfsb.Binding#Remove(%s) b.Creds.Remove() ! %s`, b.BindingID, err))
}
return
}
func (i *Instance) Decommission() (err error) {
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("instances.Decommission() p.Connect(%s) ! %s", p.URI, err))
return
}
defer db.Close()
// TODO: i.SetIneffective()
sq := fmt.Sprintf(`UPDATE cfsb.instances SET ineffective_at=CURRENT_TIMESTAMP WHERE dbname='%s'`, i.Database)
log.Trace(fmt.Sprintf(`instances.Instance<%s>#Decommission() SQL > %s`, i.InstanceID, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf("Instance#Decommission(%s) setting inefective_at ! %s", i.InstanceID, err))
return
}
// TODO: tasks.Task{ClusterID: ,Node: ,Role: ,Action:, Data: }.Enqueue()
// Question is how to do this without an import cycle? Some tasks require instances.
sq = fmt.Sprintf(`INSERT INTO tasks.tasks (cluster_id,role,action,data, cluster_service) VALUES ('%s','all','DecommissionDatabase','%s', '%s')`, i.ClusterID, i.Database, i.ClusterService)
log.Trace(fmt.Sprintf(`instances.Instance#Decommission(%s) Scheduling Instance Removal > %s`, i.InstanceID, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf(`instances.Instance#Decommission(%s) ! %s`, i.InstanceID, err))
}
return
}
// Non-Leader specifc bootstrapping.
func (r *RDPG) bdrNonLeaderBootstrap() (err error) {
log.Trace(fmt.Sprintf(`rdpg.RDPG<%s>#bdrNonLeaderBootstrap() bootstrapping non-leader...`, ClusterID))
err = r.bdrGroupJoin()
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#bdrNonLeaderBootstrap() bdrGroupJoin() ! %s`, ClusterID, err))
r.bootstrapUnlock()
return err // BDR join during bootstrap is critical path, unlock and exit.
}
r.bootstrapUnlock()
err = r.waitForBDRNodes()
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#bdrNonLeaderBootstrap() r.waitForBDRNodes() ! %s`, ClusterID, err))
}
p := pg.NewPG(`127.0.0.1`, pgPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("rdpg.RDPG<%s>#bdrNonLeaderBootstrap() ! %s", ClusterID, err))
return
}
defer db.Close()
err = p.WaitForRegClass("cfsb.instances")
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#bdrNonLeaderBootstrap() p.WaitForRegClass() ! %s`, ClusterID, err))
}
err = r.waitForWriteMasterIP()
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#bdrNonLeaderBootstrap() p.waitForWriteMasterIP() ! %s`, ClusterID, err))
}
return
}
func (r *RDPG) bootstrapSystem() (err error) {
p := pg.NewPG(`127.0.0.1`, pgPort, `rdpg`, `rdpg`, pgPass)
exts := []string{`pgcrypto`, `pg_stat_statements`, `uuid-ossp`, `hstore`, `pg_trgm`}
err = p.CreateExtensions(`rdpg`, exts)
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#initialBootstrap() CreateExtensions() ! %s`, ClusterID, err))
return
}
err = r.InitSchema()
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#bootstrapSystem() r.InitSchema(%s) ! %s`, ClusterID, globals.ServiceRole, err))
return
}
cluster, err := NewCluster(ClusterID, r.ConsulClient)
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#bootstrapSystem(%s) NewCluster() ! %s`, ClusterID, globals.ServiceRole, err))
return err
}
err = cluster.SetWriteMaster(globals.MyIP)
if err != nil {
log.Error(fmt.Sprintf(`rdpg.RDPG<%s>#bootstrapSystem() SetWriteMaster() ! %s`, ClusterID, err))
return
}
return
}
func (b *Binding) Find() (err error) {
log.Trace(fmt.Sprintf(`cfsb.Binding#Find(%s) ... `, b.BindingID))
if b.BindingID == "" {
return errors.New("Binding ID is empty, can not Binding#Find()")
}
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("cfsb.Binding#Find(%s) ! %s", b.BindingID, err))
return
}
defer db.Close()
sq := fmt.Sprintf(`SELECT id,instance_id FROM cfsb.bindings WHERE binding_id=lower('%s') LIMIT 1`, b.BindingID)
log.Trace(fmt.Sprintf(`cfsb.Binding#Find(%s) > %s`, b.BindingID, sq))
err = db.Get(b, sq)
if err != nil {
if err == sql.ErrNoRows {
log.Error(fmt.Sprintf("cfsb.Binding#Find(%s) ! Could not find binding with given Binding ID", b.BindingID))
} else {
log.Error(fmt.Sprintf("cfsb.Binding#Find(%s) ! %s", b.BindingID, err))
}
} else {
// TODO: Load creds: b.Creds := Credentials{} ... b.Creds.Find()
}
return
}
// A wrapper for ImportSqlFile which handles the additional process of
// finding the backup whereever it is stored (local or remote) and putting it in
// the correct place, and then restoring it from that location.
func RestoreInPlace(dbname, basefile string) (err error) {
if strings.Contains(basefile, "/") {
errorMessage := fmt.Sprintf("utils/backup.RestoreInPlace ! '%s' is not a file base name.", basefile, err.Error())
log.Error(errorMessage)
return errors.New(errorMessage)
}
err = StageRestoreInPlace(dbname, basefile)
if err != nil {
log.Error(fmt.Sprintf("utils/backup.RestoreInPlace ! utils/backup.StageRestoreInPlace(%s, %s) erred : %s", dbname, basefile, err.Error()))
return err
}
p := pg.NewPG("127.0.0.1", globals.PBPort, "rdpg", "rdpg", globals.PGPass)
if err != nil {
log.Error(fmt.Sprintf(`utils/backup.RestoreInPlace ! pg.NewPG("127.0.0.1", %s, "rdpg", "rdpg", %s) erred : %s`, globals.PBPort, globals.PGPass, err.Error()))
return err
}
exists, err := DatabaseExists(dbname)
if err != nil {
log.Error(fmt.Sprintf("utils/backup.RestoreInPlace ! utils/backup.DatabaseExists(%s) erred : %s", dbname, err.Error()))
return err
}
if exists {
err = p.DisableDatabase(dbname)
if err != nil {
log.Error(fmt.Sprintf("utils.backup.RestoreInPlace ! pg.DisableDatabase(%s) erred : %s", dbname, err.Error()))
return err
}
err = p.DropDatabase(dbname)
if err != nil {
log.Error(fmt.Sprintf("utils/backup.RestoreInPlace ! pg.DropDatabase(%s) erred : %s", dbname, err.Error()))
return err
}
} else {
errorMessage := fmt.Sprintf("utils/backup.RestoreInPlace ! Restoring database %s doesn't currently exist.", dbname)
log.Warn(errorMessage)
}
username := "******" + strings.TrimPrefix(dbname, "d")
err = p.CreateDatabase(dbname, username)
if err != nil {
log.Error(fmt.Sprintf("utils/backup.StageRestoreInPlace ! pg.CreateDatabase(%s, %s) erred : %s", dbname, username, err.Error()))
return err
}
err = ImportSqlFile(dbname, RestoreLocation(dbname, basefile))
if err != nil {
log.Error(fmt.Sprintf("utils/backup.RestoreInPlace ! utils/backup.ImportSqlFile(%s, %s) erred : %s", dbname, RestoreLocation(dbname, basefile), err.Error()))
return err
}
err = UnstageRestore(dbname, basefile)
if err != nil {
log.Error(fmt.Sprintf("utils/backup.RestoreInPlace ! UnstageRestore(%s, %s) erred : %s", dbname, basefile, err.Error()))
return err
}
return nil
}
/*
ConfigurePostgreSQL on the current system.
*/
func (s *Service) ConfigurePostgreSQL() (err error) {
// TODO: Adjust for cluster role...
clusterID := os.Getenv("RDPGD_CLUSTER")
if clusterID == "" {
matrixName := os.Getenv(`RDPGD_MATRIX`)
matrixNameSplit := strings.SplitAfterN(matrixName, `-`, -1)
matrixColumn := os.Getenv(`RDPGD_MATRIX_COLUMN`)
for i := 0; i < len(matrixNameSplit)-1; i++ {
clusterID = clusterID + matrixNameSplit[i]
}
clusterID = clusterID + "c" + matrixColumn
}
ips, err := s.ClusterIPs(clusterID)
if err != nil {
log.Error(fmt.Sprintf("services#Service.ConfigurePostgreSQL() ! %s", err))
return err
}
hbaHeader, err := ioutil.ReadFile(`/var/vcap/jobs/postgresql/config/pg_hba.conf`)
if err != nil {
log.Error(fmt.Sprintf("services#Service.ConfigurePostgreSQL() ! %s", err))
return err
}
hba := []string{string(hbaHeader)}
for _, ip := range ips {
hba = append(hba, fmt.Sprintf(`host replication postgres %s/32 trust`, ip))
hba = append(hba, fmt.Sprintf(`host all postgres %s/32 trust`, ip))
hba = append(hba, fmt.Sprintf(`host all rdpg %s/32 trust`, ip))
}
hba = append(hba, "")
err = ioutil.WriteFile(`/var/vcap/store/postgresql/data/pg_hba.conf`, []byte(strings.Join(hba, "\n")), 0640)
if err != nil {
log.Error(fmt.Sprintf("services#Service.ConfigurePostgresql() ! %s", err))
return err
}
p := pg.NewPG(`127.0.0.1`, pgPort, `postgres`, `postgres`, ``)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("tasks.ConfigurePostgresql() Failed connecting to %s err: %s", p.URI, err))
return
}
defer db.Close()
var successful bool
err = db.Get(&successful, `SELECT pg_reload_conf()`)
if err != nil {
log.Error(fmt.Sprintf("services.ConfigurePostgresql(postgresql) pg_reload_conf() ! %s", err))
return
}
if !successful {
log.Error("services.ConfigurePostgresql(postgresql) ! ERROR pg_reload_conf() was unsuccessful!")
return
}
return
}
//Scheduler - Entry point for executing the long running tasks scheduler
func Scheduler() {
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
p.Set(`database`, `rdpg`)
err := p.WaitForRegClass("tasks.schedules")
if err != nil {
log.Error(fmt.Sprintf(`tasks.Scheduler() p.WaitForRegClass() ! %s`, err))
}
scheduleDB, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf(`tasks.Scheduler() p.Connect() Failed connecting to %s ! %s`, p.URI, err))
proc, _ := os.FindProcess(os.Getpid())
proc.Signal(syscall.SIGTERM)
}
defer scheduleDB.Close()
for {
err = SchedulerLock()
if err != nil {
time.Sleep(10 * time.Second)
continue
}
schedules := []Schedule{}
sq := fmt.Sprintf(`SELECT id,cluster_id, role, action, data, ttl, node_type, cluster_service FROM tasks.schedules WHERE enabled = true AND CURRENT_TIMESTAMP >= (last_scheduled_at + frequency::interval) AND role IN ('all','%s')`, globals.ServiceRole)
log.Trace(fmt.Sprintf(`tasks#Scheduler() Selecting Schedules > %s`, sq))
err = scheduleDB.Select(&schedules, sq)
if err != nil {
log.Error(fmt.Sprintf(`tasks.Scheduler() Selecting Schedules ! %s`, err))
SchedulerUnlock()
time.Sleep(10 * time.Second)
continue
}
for index := range schedules {
sq = fmt.Sprintf(`UPDATE tasks.schedules SET last_scheduled_at = CURRENT_TIMESTAMP WHERE id=%d`, schedules[index].ID)
log.Trace(fmt.Sprintf(`tasks#Scheduler() %+v > %s`, schedules[index], sq))
_, err = scheduleDB.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf(`tasks.Scheduler() Schedule: %+v ! %s`, schedules[index], err))
continue
}
task := NewTask()
task.ClusterID = schedules[index].ClusterID
task.ClusterService = schedules[index].ClusterService
task.Role = schedules[index].Role
task.Action = schedules[index].Action
task.Data = schedules[index].Data
task.TTL = schedules[index].TTL
task.NodeType = schedules[index].NodeType
err = task.Enqueue()
if err != nil {
log.Error(fmt.Sprintf(`tasks.Scheduler() Task.Enqueue() %+v ! %s`, task, err))
}
}
SchedulerUnlock()
time.Sleep(10 * time.Second)
}
}
func (t *Task) DecommissionDatabase(workRole string) (err error) {
log.Trace(fmt.Sprintf(`tasks.DecommissionDatabase(%s)...`, t.Data))
i, err := instances.FindByDatabase(t.Data)
if err != nil {
log.Error(fmt.Sprintf("tasks.DecommissionDatabase(%s) instances.FindByDatabase() ! %s", i.Database, err))
return err
}
ips, err := i.ClusterIPs()
if err != nil {
log.Error(fmt.Sprintf(`tasks.Task#DecommissionDatabase(%s) i.ClusterIPs() ! %s`, i.Database, err))
return err
}
if len(ips) == 0 {
log.Error(fmt.Sprintf("tasks.Task#DecommissionDatabase(%s) ! No service cluster nodes found in Consul?!", i.Database))
return
}
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("tasks.Task#DecommissionDatabase(%s) p.Connect(%s) ! %s", t.Data, p.URI, err))
return err
}
defer db.Close()
switch workRole {
case "manager":
path := fmt.Sprintf(`databases/decommission/%s`, t.Data)
url := fmt.Sprintf("http://%s:%s/%s", ips[0], os.Getenv("RDPGD_ADMIN_PORT"), path)
req, err := http.NewRequest("DELETE", url, bytes.NewBuffer([]byte("{}")))
log.Trace(fmt.Sprintf(`tasks.Task#Decommission() > DELETE %s`, url))
//req.Header.Set("Content-Type", "application/json")
// TODO: Retrieve from configuration in database.
req.SetBasicAuth(os.Getenv("RDPGD_ADMIN_USER"), os.Getenv("RDPGD_ADMIN_PASS"))
httpClient := &http.Client{}
_, err = httpClient.Do(req)
if err != nil {
log.Error(fmt.Sprintf(`tasks.Task#DecommissionDatabase(%s) httpClient.Do() %s ! %s`, i.Database, url, err))
return err
}
// TODO: Is there anything we want to do on successful request?
case "service":
for _, ip := range ips {
newTask := Task{ClusterID: ClusterID, Node: ip, Role: "all", Action: "Reconfigure", Data: "pgbouncer"}
err = newTask.Enqueue()
if err != nil {
log.Error(fmt.Sprintf(`tasks.Task#DecommissionDatabase(%s) ! %s`, i.Database, err))
}
}
log.Trace(fmt.Sprintf(`tasks.DecommissionDatabase(%s) TODO: Here is where we finally decommission on the service cluster...`, i.Database))
return nil
default:
log.Error(fmt.Sprintf(`tasks.Task#DecommissionDatabase(%s) ! Unknown work role: '%s' -> BUG!!!`, i.Database, workRole))
return nil
}
return
}
func AddBackupPathConfig(dc *config.DefaultConfig) (err error) {
log.Trace("Entering AddBackupPathConfig")
if dc.Key != "BackupsPath" {
errorMessage := fmt.Sprintf("utils/backup.AddBackupPathConfig ! Key specified: %s != 'BackupsPath'", dc.Key)
log.Error(errorMessage)
return errors.New(errorMessage)
}
p := pg.NewPG(`127.0.0.1`, globals.PGPort, `rdpg`, `rdpg`, globals.PGPass)
p.Set(`database`, `rdpg`)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf(`config.DefaultConfig() Could not open connection ! %s`, err))
}
defer db.Close()
oldConfigs := []config.DefaultConfig{}
sql := fmt.Sprintf("SELECT key, cluster_id, value FROM rdpg.config WHERE key = 'BackupsPath' AND cluster_id = '%s';", dc.ClusterID)
err = db.Select(&oldConfigs, sql)
//If there is no preexisting config, then just insert this...
if len(oldConfigs) == 0 {
sq := fmt.Sprintf(`INSERT INTO rdpg.config (key,cluster_id,value) SELECT '%s', '%s', '%s' WHERE NOT EXISTS (SELECT key FROM rdpg.config WHERE key = '%s' AND cluster_id = '%s')`, dc.Key, dc.ClusterID, dc.Value, dc.Key, dc.ClusterID)
log.Trace(fmt.Sprintf(`config.DefaultConfig.Add(): %s`, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf(`config.DefaultConfig.Add():%s`, err))
return err
}
} else { //Otherwise, need to check if we need to move the backup files.
if oldConfigs[0].Value != dc.Value {
//If the path has changed, move the files.
sq := fmt.Sprintf(`UPDATE rdpg.config SET value = '%s' WHERE key = '%s' AND cluster_id = '%s';`, dc.Value, dc.Key, dc.ClusterID)
log.Trace(fmt.Sprintf(`config.DefaultConfig.Add(): %s`, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf(`config.DefaultConfig.Add():%s`, err))
return err
}
err = MoveBackupFiles(oldConfigs[0].Value, dc.Value)
var localError error = nil
if err != nil {
log.Error(fmt.Sprintf("utils/backup.AddBackupPathConfig() ! utils/backup.MoveBackupFiles erred: %s", err.Error()))
//Still want to try remote move. Don't just explode now. Return this error later if necessary.
localError = err
err = nil
}
if rdpgs3.Configured {
err = MoveRemoteBackupFiles(oldConfigs[0].Value, dc.Value)
if err != nil {
log.Error(fmt.Sprintf("utils/backup.AddBackupPath() ! utils/backup.MoveRemoteBackupFiles erred: %s", err.Error()))
return err
}
}
return localError //is nil by default. Only returns error if MoveBackupFiles erred.
}
}
return nil
}
func Check(check string) (status int, err error) {
p := pg.NewPG(`127.0.0.1`, pbPort, `health`, `health`, `check`)
status = http.StatusOK
switch check {
case "ha_pb_pg":
p.Set(`port`, `5432`)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("admin.Check(%s) %s ! %s", check, p.URI, err))
status = http.StatusInternalServerError
return status, err
}
defer db.Close()
_, err = db.Exec(`SELECT CURRENT_TIMESTAMP`)
if err != nil {
log.Error(fmt.Sprintf(`admin.Check(%s) ! %s`, check, err))
status = http.StatusInternalServerError
return status, err
}
case "pb":
p.Set(`port`, pbPort)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("admin.Check(%s) %s ! %s", check, p.URI, err))
status = http.StatusInternalServerError
return status, err
}
defer db.Close()
_, err = db.Exec(`SELECT CURRENT_TIMESTAMP`)
if err != nil {
log.Error(fmt.Sprintf(`admin.Check(%s) ! %s`, check, err))
status = http.StatusInternalServerError
return status, err
}
case "pg":
p.Set(`port`, pgPort)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("admin.Check(%s) %s ! %s", check, p.URI, err))
status = http.StatusInternalServerError
return status, err
}
defer db.Close()
_, err = db.Exec(`SELECT CURRENT_TIMESTAMP`)
if err != nil {
log.Error(fmt.Sprintf(`admin.Check(%s) ! %s`, check, err))
status = http.StatusInternalServerError
return status, err
}
default:
status = http.StatusInternalServerError
return status, err
}
return status, err
}
// Assign() is called when the master cluster tells the service cluster about
// an assignment.
func (i *Instance) Assign() (err error) {
p := pg.NewPG(`127.0.0.1`, pbPort, `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("instances.Assign() p.Connect(%s) ! %s", p.URI, err))
return err
}
defer db.Close()
for {
err = i.Lock()
if err != nil {
log.Error(fmt.Sprintf("instances.Instance#Assign(%s) Failed Locking instance ! %s", i.Database, err))
continue
}
sq := fmt.Sprintf(`UPDATE cfsb.instances SET service_id='%s',plan_id='%s',instance_id='%s',organization_id='%s',space_id='%s' WHERE dbname='%s'`, i.ServiceID, i.PlanID, i.InstanceID, i.OrganizationID, i.SpaceID, i.Database)
log.Trace(fmt.Sprintf(`instances.Instance#Assign(%s) > %s`, i.Database, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf("instances.Instance#Assign(%s) ! %s", i.Database, err))
err = i.Unlock()
if err != nil {
log.Error(fmt.Sprintf(`instances.Instance#Assign(%s) Unlocking ! %s`, i.InstanceID, err))
}
err = i.Unlock()
if err != nil {
log.Error(fmt.Sprintf(`instances.Instance#Assign(%s) Unlocking ! %s`, i.InstanceID, err))
}
continue
} else {
err = i.Unlock()
if err != nil {
log.Error(fmt.Sprintf(`instances.Instance#Assign(%s) Unlocking ! %s`, i.InstanceID, err))
}
break
}
}
ips, err := i.ClusterIPs()
if err != nil {
log.Error(fmt.Sprintf(`instances.Instance#Assign(%s) i.ClusterIPs() ! %s`, i.InstanceID, err))
return
}
for _, ip := range ips {
// TODO: tasks.Task{ClusterID: ,Node: ,Role: ,Action:, Data: }.Enqueue()
// Question is how to do this without an import cycle? Smoe tasks require instances.
sq := fmt.Sprintf(`INSERT INTO tasks.tasks (cluster_id,node,role,action,data) VALUES ('%s','%s','service','Reconfigure','pgbouncer')`, ClusterID, ip)
log.Trace(fmt.Sprintf(`instances.Instance#Assign(%s) Enqueue Reconfigure of pgbouncer > %s`, i.InstanceID, sq))
_, err = db.Exec(sq)
if err != nil {
log.Error(fmt.Sprintf(`instances.Instance#Assign(%s) Unlocking ! %s`, i.InstanceID, err))
}
}
return
}
//Consider merging this with the version in admin/db.go. Would need to add
// row count variable and adjust calls to this function accordingly.
func ExecQuery(address string, sq string) (err error) {
p := pg.NewPG(address, globals.PBPort, `rdpg`, `rdpg`, globals.PGPass)
db, err := p.Connect()
if err != nil {
return err
}
defer db.Close()
_, err = db.Exec(sq)
return err
}
//ExecQuery - Run a query against the rdpg database
func ExecQuery(address string, sq string) (err error) {
p := pg.NewPG(address, "7432", `rdpg`, `rdpg`, "admin")
db, err := p.Connect()
if err != nil {
return err
}
defer db.Close()
_, err = db.Exec(sq)
return err
}
//GetRowCountUserDB returns the number of rows generated by a give query to a user database
func GetRowCountUserDB(address string, sq string, dbname string) (queryRowCount int, err error) {
p := pg.NewPG(address, "7432", `rdpg`, dbname, "admin")
db, err := p.Connect()
if err != nil {
return 0, err
}
defer db.Close()
var rowCount []int
err = db.Select(&rowCount, sq)
if err != nil {
return 0, err
}
return rowCount[0], nil
}
func execQuery(address string, sq string) (queryRowCount int, err error) {
p := pg.NewPG(address, "7432", `rdpg`, `rdpg`, pgPass)
db, err := p.Connect()
if err != nil {
return -1, err
}
defer db.Close()
var rowCount []int
err = db.Select(&rowCount, sq)
if err != nil {
return -1, err
}
return rowCount[0], nil
}
//Returns the local and remote retention policies for the database requested
// If no custom local/remote rule is found, the default is put in its place.
func GetRetentionPolicy(dbname string) (ret RetentionPolicy, err error) {
p := pg.NewPG(`127.0.0.1`, globals.PGPort, `rdpg`, `rdpg`, globals.PGPass)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("utils/backup.GetRetentionPolicy ! pg.Connect() : %s", err.Error()))
return RetentionPolicy{}, err
}
defer db.Close()
/* Maybe this isn't the retention system's responsibility after all...
dbExists, err := DatabaseExists(dbname)
if err != nil {
log.Error(fmt.Sprintf("utils/backup.GetRetentionPolicy ! Error when trying to retrieve list of databases: %s # %s", dbname, err.Error()))
return RetentionPolicy{}, err
}
if !dbExists {
errorMessage := fmt.Sprintf("utils/backup.GetRetentionPolicy ! Requested retention policy for non-existant database: %s", dbname)
log.Warn(errorMessage)
return RetentionPolicy{}, errors.New(errorMessage)
}
*/
//Get custom retention rules
response := []RetentionRuleRow{}
sql := fmt.Sprintf("SELECT * FROM backups.retention_rules WHERE dbname='%s'", dbname)
err = db.Select(&response, sql)
if err != nil {
log.Error(fmt.Sprintf("utils/backup.GetRetentionPolicy ! sqlx.db.Select(%s) : %s", sql, err))
return RetentionPolicy{}, err
}
//Theres only local and remote, so if theres more than two rules, something is very wrong.
if len(response) > 2 {
errorMessage := "utils/backup.GetRetentionPolicy: More than two rules for a database?!"
log.Error(errorMessage)
return RetentionPolicy{}, errors.New(errorMessage)
}
//Establish defaults
ret = RetentionPolicy{
DBName: dbname,
LocalHours: globals.LocalRetentionTime,
RemoteHours: globals.RemoteRetentionTime,
}
//Look for a local and remote retention rule
for _, v := range response {
if v.IsRemoteRule {
ret.RemoteHours = v.Hours
} else {
ret.LocalHours = v.Hours
}
}
return
}
func (s *Service) ConfigurePGBDR() (err error) {
// TODO: Adjust for cluster role...
clusterID := os.Getenv("RDPGD_CLUSTER")
ips, err := s.ClusterIPs(clusterID)
if err != nil {
log.Error(fmt.Sprintf("services#Service.ConfigurePGBDR() ! %s", err))
return err
}
hbaHeader, err := ioutil.ReadFile(`/var/vcap/jobs/pgbdr/config/pg_hba.conf`)
if err != nil {
log.Error(fmt.Sprintf("services#Service.ConfigurePGBDR() ! %s", err))
return err
}
hba := []string{string(hbaHeader)}
for _, ip := range ips {
hba = append(hba, fmt.Sprintf(`host replication postgres %s/32 trust`, ip))
hba = append(hba, fmt.Sprintf(`host all postgres %s/32 trust`, ip))
hba = append(hba, fmt.Sprintf(`host all rdpg %s/32 trust`, ip))
}
hba = append(hba, "")
err = ioutil.WriteFile(`/var/vcap/store/pgbdr/data/pg_hba.conf`, []byte(strings.Join(hba, "\n")), 0640)
if err != nil {
log.Error(fmt.Sprintf("services#Service.ConfigurePGBDR() ! %s", err))
return err
}
p := pg.NewPG(`127.0.0.1`, pgPort, `postgres`, `postgres`, ``)
db, err := p.Connect()
if err != nil {
log.Error(fmt.Sprintf("tasks.ConfigurePGBDR() Failed connecting to %s err: %s", p.URI, err))
return
}
defer db.Close()
var successful bool
err = db.Get(&successful, `SELECT pg_reload_conf()`)
if err != nil {
log.Error(fmt.Sprintf("services.ConfigurePGBDR(pgbdr) pg_reload_conf() ! %s", err))
return
}
if !successful {
log.Error("services.ConfigurePGBDR(pgbdr) ! ERROR pg_reload_conf() was unsuccessful!")
return
}
return
}
func (r *RDPG) waitForBDRNodes() (err error) {
log.Trace(fmt.Sprintf(`rdpg.RDPG<%s>#waitForBDRNodes() waiting for all BDR nodes to be joined...`, ClusterID))
p := pg.NewPG(`127.0.0.1`, pgPort, `rdpg`, `rdpg`, pgPass)
var db *sqlx.DB
for {
db, err = p.Connect()
if err != nil {
re := regexp.MustCompile("canceling statement due to conflict with recovery")
if re.MatchString(err.Error()) {
log.Error(fmt.Sprintf("rdpg.RDPG<%s>#waitForBDRNodes() p.Connect() (sleeping 2 seconds and trying again) ! %s", ClusterID, err))
time.Sleep(2 * time.Second)
continue // Sleep 2 seconds and try again...
} else {
log.Error(fmt.Sprintf("rdpg.RDPG<%s>#waitForBDRNodes() p.Connect() ! %s", ClusterID, err))
return err
}
} else {
break
}
}
defer db.Close()
for {
nodes := []string{}
sq := `SELECT node_name FROM bdr.bdr_nodes;`
err = db.Select(&nodes, sq)
if err != nil {
if err == sql.ErrNoRows {
log.Error(fmt.Sprintf("rdpg.RDPG<%s>#waitForBDRNodes() db.Select() %sq ! Sleeping 2 seconds and trying again.", ClusterID, sq))
time.Sleep(2 * time.Second)
continue
}
log.Error(fmt.Sprintf("rdpg.RDPG<%s>#waitForBDRNodes() db.Select() ! %s", ClusterID, err))
return err
}
log.Trace(fmt.Sprintf(`rdpg.RDPG<%s>#waitForBDRNodes() nodes %+v`, ClusterID, nodes))
switch ClusterID {
case "rdpgmc":
if len(nodes) > 2 {
return nil
}
default: // rdpgsc*
if len(nodes) > 1 {
return nil
}
}
time.Sleep(2 * time.Second)
}
}
func GetList(address string, sq string) (list []string, err error) {
p := pg.NewPG(address, globals.PBPort, `rdpg`, `rdpg`, globals.PGPass)
db, err := p.Connect()
if err != nil {
return nil, err
}
defer db.Close()
rows := []string{}
err = db.Select(&rows, sq)
if err != nil {
return nil, err
}
return rows, nil
}
//GetList returns 1 column of data for a given sql query
func GetList(address string, sq string) (list []string, err error) {
p := pg.NewPG(address, "7432", `rdpg`, `rdpg`, "admin")
db, err := p.Connect()
if err != nil {
return nil, err
}
defer db.Close()
rows := []string{}
err = db.Select(&rows, sq)
if err != nil {
return nil, err
}
return rows, nil
}