popovnv

Install MySQL 8 on CentOS 8 in VirtualBox

** Please use the root user to edit the files and execute the commands unless further notice. **

Prerequisite

1. Install the latest VirtualBox Platform Package and the VirtualBox Extension Pack (Oracle_VM_VirtualBox_Extension_Pack-VERSION.vbox-extpack).
2. Download the latest VirtualBox Guest Additions (VBoxGuestAdditions_VERSION.iso).
3. Download the latest CentOS Linux release 8.
4. Create a new virtual machine and install the CentOS to the virtual machine. During the CentOS installation, select Workstation as Base Environment, select Container Management, Development Tools and Graphical Administration Tools as Additional software for Selected Environment.

popovnv

Install Oracle Database 19c on CentOS 8 in VirtualBox

** Please use the root user to edit the files and execute the commands unless further notice. **

Prerequisite

1. Install the latest VirtualBox Platform Package and the VirtualBox Extension Pack (Oracle_VM_VirtualBox_Extension_Pack-VERSION.vbox-extpack).
2. Download the latest VirtualBox Guest Additions (VBoxGuestAdditions_VERSION.iso).
3. Download the latest CentOS Stream 8.
4. Create a new virtual machine and install the CentOS to the virtual machine. During the CentOS installation, select Workstation as Base Environment, select Container Management, Development Tools and Graphical Administration Tools as Additional software for Selected Environment. Use http://mirror.centos.org/centos/8/BaseOS/x86_64/os/ as the installation source.

popovnv

Как максимально быстро и с минимальным расходом дискового пространства получать копии баз для разработки.

Предположим, есть сервер баз данных под управлением postgres, назовем его srv-db
И есть сервер разработки, на котором тоже есть postgres, назовем его srv-dev.
Требуется по мере надобности создавать в онлайне за несколько минут получать копии для тестирования и разработки.
Для этого проводим подготовительные работы:
1. Размещаем кластер postgres на zfs
2. Настраиваем потоковую асинхронную репликацию srv-db -> srv-dev
Делаем копиии:
3. Делаем снимок zfs, затем клон на основе снимка

popovnv

select pg_size_pretty(pg_current_wal_lsn()- '3C2F/0000001F'::pg_lsn);
pg_size_pretty
----------------
33 GB
(1 row)

popovnv

#!/bin/bash
#
# 
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# on Oracle Linux
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it

popovnv

#!/bin/sh
 
ORACLE_BASE=/u/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/grid_1
 
$ORACLE_HOME/bin/crsctl stat res -t \
  | awk -v t="$t" '$0 !~ "Cluster Resources" && $0 !~ "Local Resources" \
      { \
        if ($0 ~ "Name") { \
          printf "%-45s %-10s %-15s %-14s %s %s\n", $1, $2, $3, $4, $5, $6

popovnv

Introduction

• C-a == Ctrl-a
• M-a == Alt-a

General

:q        close
:w        write/saves
:wa[!]    write/save all windows [force]
:wq       write/save and close

popovnv

-- Create a group
CREATE ROLE readaccess;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

popovnv

-- Completely unused indexes:
SELECT relid::regclass as table, indexrelid::regclass as index
     , pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes
  JOIN pg_index
 USING (indexrelid)
 WHERE idx_scan = 0
   AND indisunique IS FALSE order by pg_relation_size(indexrelid);

popovnv

-- A method to have RETURNING work if you are partitioning data using trigger. 
-- The method to this madness is:
--
-- 1) Use the normal trigger mechanism to insert the data into the child tables, but
--    Instead of the trigger function returning NULL so that the row does not get⋅
--    inserted into the master table, it returns the row inserted into the child
--    table
--
-- 2) Postgres will insert the new row from the trigger into the master table
--