class Seira::Db

Constants

SUMMARY
VALID_ACTIONS

Attributes

action[R]
app[R]
args[R]
context[R]

Public Class Methods

new(app:, action:, args:, context:) click to toggle source
# File lib/seira/db.rb, line 22
def initialize(app:, action:, args:, context:)
  @app = app
  @action = action
  @args = args
  @context = context
end

Public Instance Methods

primary_instance() click to toggle source
# File lib/seira/db.rb, line 78
def primary_instance
  Seira::Helpers.get_seira_app_config(context: context)['primary_sql_instance']
end
run() click to toggle source
# File lib/seira/db.rb, line 29
def run
  case action
  when 'help'
    run_help
  when 'create'
    run_create
  when 'add'
    run_add
  when 'configure'
    run_configure
  when 'delete'
    run_delete
  when 'list'
    run_list
  when 'restart'
    run_restart
  when 'connect'
    run_connect
  when 'ps'
    run_ps
  when 'kill'
    run_kill
  when 'analyze'
    run_analyze
  when 'create-readonly-user'
    run_create_readonly_user
  when 'psql'
    run_psql
  when 'table-sizes'
    run_table_sizes
  when 'index-sizes'
    run_index_sizes
  when 'vacuum'
    run_vacuum
  when 'unused-indexes', 'unused-indices'
    run_unused_indexes
  when 'user-connections'
    run_user_connections
  when 'info'
    run_info
  when 'alter-proxyuser-roles'
    run_alter_proxyuser_roles
  when 'write-pgbouncer-yaml'
    run_write_pgbouncer_yaml
  else
    fail "Unknown command encountered"
  end
end

Private Instance Methods

execute_db_command(sql_command, instance_name: nil, pgbouncer_tier: nil, as_admin: false, interactive: false, print: true) click to toggle source
# File lib/seira/db.rb, line 482
def execute_db_command(sql_command, instance_name: nil, pgbouncer_tier: nil, as_admin: false, interactive: false, print: true)
  # TODO(josh): move pgbouncer naming logic here and in Create to a common location
  instance_name ||= primary_instance
  private_ip = Helpers.sql_ips(instance_name, context: context)[:private]

  # NOTE: Relies on the pgbouncer instance being named based on the db name, as is done in create command
  pgbouncer_tier ||= instance_name.gsub("#{app}-", '')
  matching_pods = Helpers.fetch_pods(context: context, filters: { tier: pgbouncer_tier })
  if matching_pods.empty?
    puts 'Could not find pgbouncer pod to connect to'
    exit 1
  end
  pod_name = matching_pods.first['metadata']['name']
  psql_command =
    if as_admin
      root_password = Helpers.get_secret(context: context, key: "#{instance_name.tr('-', '_').upcase}_ROOT_PASSWORD")
      "psql postgres://postgres:#{root_password}@#{private_ip}:5432"
    else
      "psql"
    end
  system_command = "kubectl exec #{pod_name} --namespace #{app}"
  system_command += ' -ti' if interactive
  system_command += " -- #{psql_command}"
  system_command += " -c \"#{sql_command}\"" unless sql_command.nil?
  if interactive
    exit(1) unless system(system_command)
  else
    output = `#{system_command}`
    success = $CHILD_STATUS.success?
    puts output if print || !success
    exit(1) unless success
    output
  end
end
existing_instances(remove_app_prefix: true) click to toggle source
# File lib/seira/db.rb, line 517
def existing_instances(remove_app_prefix: true)
  plain_list = `gcloud sql instances list --uri`.split("\n").map { |uri| uri.split('/').last }.select { |name| name.start_with? "#{app}-" }

  if remove_app_prefix
    plain_list.map { |name| name.gsub(/^#{app}-/, '') }
  else
    plain_list
  end
end
run_add() click to toggle source
# File lib/seira/db.rb, line 115
def run_add
  Seira::Db::Create.new(app: app, action: action, args: args, context: context).add(existing_instances)
end
run_alter_proxyuser_roles() click to toggle source
# File lib/seira/db.rb, line 139
def run_alter_proxyuser_roles
  instance_name = nil
  args.each do |arg|
    if arg.start_with? '--instance='
      instance_name = arg.split('=')[1]
    else
      puts "Warning: Unrecognized argument '#{arg}'"
    end
  end

  command = "ALTER ROLE proxyuser NOCREATEDB NOCREATEROLE;"
  execute_db_command(command, instance_name: instance_name, as_admin: true)
  puts "Removed Create role and Create DB roles from proxyuser"
end
run_analyze() click to toggle source
# File lib/seira/db.rb, line 243
    def run_analyze
      puts 'Cache Hit Rates'.bold
      execute_db_command(
        <<~SQL
          SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
          FROM pg_statio_user_tables;
        SQL
      )

      puts 'Index Usage Rates'.bold
      execute_db_command(
        <<~SQL
          SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
          FROM pg_stat_user_tables
          WHERE (seq_scan + idx_scan) > 0
          ORDER BY n_live_tup DESC;
        SQL
      )
    end
run_configure() click to toggle source
# File lib/seira/db.rb, line 119
def run_configure
  instance_name = nil
  master_name = nil
  args.each do |arg|
    if arg.start_with? '--instance='
      instance_name = arg.split('=')[1]
    elsif arg.start_with? '--master='
      master_name = arg.split('=')[1]
    else
      puts "Warning: Unrecognized argument '#{arg}'"
    end
  end

  if instance_name.nil? || instance_name.strip.chomp == ''
    puts "Please specify the db instance name, like --instance=name"
    exit(1)
  end
  Seira::Db::Create.new(app: app, action: action, args: args, context: context).configure(instance_name, master_name)
end
run_connect() click to toggle source
# File lib/seira/db.rb, line 183
def run_connect
  name = args[0] || primary_instance
  puts "Connecting to #{name}..."
  root_password = Helpers.get_secret(context: context, key: "#{name.tr('-', '_').upcase}_ROOT_PASSWORD") || "Not found in secrets"
  puts "Your root password for 'postgres' user is: #{root_password}"
  system("gcloud sql connect #{name}")
end
run_create() click to toggle source
# File lib/seira/db.rb, line 111
def run_create
  Seira::Db::Create.new(app: app, action: action, args: args, context: context).run(existing_instances)
end
run_create_readonly_user() click to toggle source

Example: seira staging app-name db create-readonly-user –username=readonlyuser

# File lib/seira/db.rb, line 264
    def run_create_readonly_user
      instance_name = primary_instance # Always make user changes to primary instance, and they will propogate to replicas
      pgbouncer_tier = nil
      user_name = nil

      args.each do |arg|
        if arg.start_with? '--username='
          user_name = arg.split('=')[1]
        elsif arg.start_with? '--instance='
          instance_name = arg.split('=')[1]
        elsif arg.start_with? '--pgbouncer-tier='
          pgbouncer_tier = arg.split('=')[1]
        else
          puts "Warning: Unrecognized argument '#{arg}'"
        end
      end

      if user_name.nil? || user_name.strip.chomp == ''
        puts "Please specify the name of the read-only user to create, such as --username=testuser"
        exit(1)
      end

      # Require that the name be alpha only for simplicity and strict but basic validation
      if user_name.match(/\A[a-zA-Z]*\z/).nil?
        puts "Username must be characters only"
        exit(1)
      end

      valid_instance_names = existing_instances(remove_app_prefix: false)
      if instance_name.nil? || instance_name.strip.chomp == '' || !valid_instance_names.include?(instance_name)
        puts "Could not find a valid instance name - does the DATABASE_URL have a value? Must be one of: #{valid_instance_names}"
        exit(1)
      end

      password = SecureRandom.urlsafe_base64(32)
      if gcloud("sql users create #{user_name} --instance=#{instance_name} --password=#{password}", context: context, format: :boolean)
        puts "Created user '#{user_name}' with password #{password}"
      else
        puts "Failed to create user '#{user_name}'"
        exit(1)
      end

      puts 'Setting permissions...'
      admin_commands =
        <<~SQL
          REVOKE cloudsqlsuperuser FROM #{user_name};
          ALTER ROLE #{user_name} NOCREATEDB NOCREATEROLE;
        SQL
      database_commands =
        <<~SQL
          REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM #{user_name};
          GRANT SELECT ON ALL TABLES IN SCHEMA public TO #{user_name};
          ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO #{user_name};
        SQL

      puts "Connecting"
      execute_db_command(admin_commands, instance_name: instance_name, pgbouncer_tier: pgbouncer_tier, as_admin: true)
      execute_db_command(database_commands, instance_name: instance_name, pgbouncer_tier: pgbouncer_tier)
    end
run_delete() click to toggle source
# File lib/seira/db.rb, line 158
def run_delete
  name = "#{app}-#{args[0]}"
  if gcloud("sql instances delete #{name}", context: context, format: :boolean)
    puts "Successfully deleted sql instance #{name}"

    # TODO: Automate the below
    puts "Don't forget to delete the deployment, configmap, secret, and service for the pgbouncer instance."
  else
    puts "Failed to delete sql instance #{name}"
  end
end
run_help() click to toggle source
# File lib/seira/db.rb, line 84
    def run_help
      puts SUMMARY
      puts "\n"
      puts <<~HELPTEXT
        analyze:                Display database performance information
        connect:                Open a psql command prompt via gcloud connect. You will be shown the password needed before the prompt opens.
        create:                 Create a new postgres instance in cloud sql. Supports creating replicas and other numerous flags.
        configure:              Configure users and related secrets for existing db instance --instance= with optional --master= for read replica.
        add:                    Adds a new database to the given project. Requires --prefix=my-prefix to prefix the random name
        create-readonly-user:   Create a database user named by --username=<name> with only SELECT access privileges
        delete:                 Delete a postgres instance from cloud sql. Use with caution, and remove all kubernetes configs first.
        index-sizes:            List sizes of all indexes in the database
        info:                   Summarize all database instances for the app
        kill:                   Kill a query
        list:                   List all postgres instances.
        ps:                     List running queries
        psql:                   Open a psql prompt via kubectl exec into a pgbouncer pod.
        restart:                Fully restart the database.
        table-sizes:            List sizes of all tables in the database
        unused-indexes:         Show indexes with zero or low usage
        user-connections:       List number of connections per user
        vacuum:                 Run a VACUUM ANALYZE
        alter-proxyuser-roles:  Update NOCREATEDB and NOCREATEROLE roles for proxyuser in cloud sql.
        write-pbouncer-yaml:    Produces a Kubernetes Deployment yaml to run Pgbouncer for specified database.
      HELPTEXT
    end
run_index_sizes() click to toggle source
# File lib/seira/db.rb, line 373
    def run_index_sizes
      # https://wiki.postgresql.org/wiki/Disk_Usage
      execute_db_command(
        <<~SQL
          SELECT relname AS index
            , c.reltuples AS row_estimate
            , pg_size_pretty(pg_relation_size(c.oid)) AS "size"
          FROM pg_class c
          LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
          WHERE relkind = 'i'
          AND n.nspname = 'public'
          ORDER BY pg_relation_size(c.oid) DESC;
        SQL
      )
    end
run_info() click to toggle source
# File lib/seira/db.rb, line 423
    def run_info
      instances = JSON.parse(gcloud("sql instances list --filter='name~\\A#{app}-'", context: context, format: :json))
      instances.each do |instance|
        db_info_command =
          <<~SQL
            COPY (SELECT pg_size_pretty(sum(pg_database_size(datname))) FROM pg_database) TO stdout;
            COPY (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE') TO stdout;
            COPY (SELECT count(*) FROM pg_stat_activity) TO stdout;
          SQL
        db_info = execute_db_command(db_info_command, print: false)
        data_size, table_count, connection_count = db_info.split("\n")
        instance['data_size'] = data_size
        instance['table_count'] = table_count
        instance['connection_count'] = connection_count
      end
      instances.each do |instance|
        # https://cloud.google.com/sql/faq
        disk_size = instance['settings']['dataDiskSizeGb'].to_f
        connection_limit =
          if disk_size <= 0.6
            25
          elsif disk_size <= 3.75
            50
          elsif disk_size <= 6
            100
          elsif disk_size <= 7.5
            150
          elsif disk_size <= 15
            200
          elsif disk_size <= 30
            250
          elsif disk_size <= 60
            300
          elsif disk_size <= 120
            400
          else
            500
          end

        backup_info = instance['settings']['backupConfiguration']['enabled'] == 'true' ? instance['settings']['backupConfiguration']['startTime'] : 'false'

        puts "\n"
        puts instance['name'].bold
        puts <<~INFOTEXT
          State:        #{instance['state']}
          Tables:       #{instance['table_count']}
          Disk Size:    #{disk_size} GB
          Data Size:    #{instance['data_size']}
          Auto Resize:  #{instance['settings']['storageAutoResize']}
          Disk Type:    #{instance['settings']['dataDiskType']}
          Tier:         #{instance['settings']['tier']}
          Availability: #{instance['settings']['availabilityType']}
          Version:      #{instance['databaseVersion']}
          Backups:      #{backup_info}
          Connections:  #{instance['connection_count']}/#{connection_limit}(?)
        INFOTEXT
      end
    end
run_kill() click to toggle source
# File lib/seira/db.rb, line 221
def run_kill
  force = false
  pid = nil

  args.each do |arg|
    if %w[--force -f].include? arg
      force = true
    elsif /^\d+$/.match? arg
      if pid.nil?
        pid = arg
      else
        puts 'Must specify only one PID'
        exit 1
      end
    else
      puts "Warning: unrecognized argument #{arg}"
    end
  end

  execute_db_command("SELECT #{force ? 'pg_terminate_backend' : 'pg_cancel_backend'}(#{pid})")
end
run_list() click to toggle source
# File lib/seira/db.rb, line 170
def run_list
  puts existing_instances
end
run_ps() click to toggle source
# File lib/seira/db.rb, line 191
    def run_ps
      verbose = false
      args.each do |arg|
        if %w[--verbose -v].include? arg
          verbose = true
        else
          puts "Warning: unrecognized argument #{arg}"
        end
      end

      execute_db_command(
        <<~SQL
          SELECT
            pid,
            state,
            application_name AS source,
            age(now(),query_start) AS running_for,
            query_start,
            wait_event IS NOT NULL AS waiting,
            query
          FROM pg_stat_activity
          WHERE
            query <> '<insufficient privilege>'
            #{verbose ? '' : "AND state <> 'idle'"}
            AND pid <> pg_backend_pid()
          ORDER BY query_start DESC
        SQL
      )
    end
run_psql() click to toggle source
# File lib/seira/db.rb, line 324
def run_psql
  as_admin = false
  instance_name = nil

  args.each do |arg|
    if arg == '--as-root-user'
      as_admin = true
    elsif arg.start_with? '--instance='
      instance_name = arg.split('=')[1]
    else
      puts "Warning: Unrecognized argument '#{arg}'"
    end
  end

  if as_admin
    puts "!! Warning !!! You are running as root PSQL user `postgres`. This super user account has full admin priveleges. Use with extreme care."
  end

  execute_db_command(nil, interactive: true, instance_name: instance_name, as_admin: as_admin)
end
run_restart() click to toggle source
# File lib/seira/db.rb, line 174
def run_restart
  name = "#{app}-#{args[0]}"
  if gcloud("sql instances restart #{name}", context: context, format: :boolean)
    puts "Successfully restarted sql instance #{name}"
  else
    puts "Failed to restart sql instance #{name}"
  end
end
run_table_sizes() click to toggle source
# File lib/seira/db.rb, line 345
    def run_table_sizes
      # https://wiki.postgresql.org/wiki/Disk_Usage
      execute_db_command(
        <<~SQL
          SELECT table_name
            , row_estimate
            , pg_size_pretty(table_bytes) AS table
            , pg_size_pretty(index_bytes) AS index
            , pg_size_pretty(toast_bytes) AS toast
            , pg_size_pretty(total_bytes) AS total
          FROM (
            SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
              SELECT relname AS table_name
                  , c.reltuples AS row_estimate
                  , pg_total_relation_size(c.oid) AS total_bytes
                  , pg_indexes_size(c.oid) AS index_bytes
                  , pg_total_relation_size(reltoastrelid) AS toast_bytes
                FROM pg_class c
                LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
                WHERE relkind = 'r'
                AND n.nspname = 'public'
            ) a
          ) a
          ORDER BY total_bytes DESC;
        SQL
      )
    end
run_unused_indexes() click to toggle source
# File lib/seira/db.rb, line 397
    def run_unused_indexes
      # https://github.com/heroku/heroku-pg-extras/blob/master/commands/unused_indexes.js
      execute_db_command(
        <<~SQL
          SELECT
            schemaname || '.' || relname AS table,
            indexrelname AS index,
            pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
            idx_scan as index_scans
          FROM pg_stat_user_indexes ui
          JOIN pg_index i ON ui.indexrelid = i.indexrelid
          WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
          ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
          pg_relation_size(i.indexrelid) DESC;
        SQL
      )
    end
run_user_connections() click to toggle source
# File lib/seira/db.rb, line 415
    def run_user_connections
      execute_db_command(
        <<~SQL
          SELECT usename AS user, count(pid) FROM pg_stat_activity GROUP BY usename;
        SQL
      )
    end
run_vacuum() click to toggle source
# File lib/seira/db.rb, line 389
    def run_vacuum
      execute_db_command(
        <<~SQL
          VACUUM VERBOSE ANALYZE;
        SQL
      )
    end
run_write_pgbouncer_yaml() click to toggle source
# File lib/seira/db.rb, line 154
def run_write_pgbouncer_yaml
  Seira::Db::WritePgbouncerYaml.new(app: app, args: args, context: context).run
end