2022-01-06

TableAU Backup and Restore

#!/bin/bash
#############################################
# @Author: TinhCX
# @Email : TinhCX@gmail.com
# @CreatedDate: #13:39 2022.01.04
# @LastUpdate : #12:46 2022.01.06
# @Last Modified by: iadmin
#############################################
#cat /opt/script/tableau-backup.sh | grep LastUpdate
#############################################
#__________TIME_START:BEGIN
time_start=`date +%s.%3N`
#__________TIME_START:END
#############################################
#############################################
#__________TIME_CALCULATE:BEGIN
#now1: 2022.01.06-11.55.40.168
now1="$(date +'%Y.%m.%d-%H.%M.%S.%3N')"

CURRENT_YEAR=$(date +'%Y'); CURRENT_MONTH=$(date +'%m'); CURRENT_DATE=$(date +'%d')
CURRENT_HOUR=$(date +'%H'); CURRENT_MINUTE=$(date +'%M'); CURRENT_SECOND=$(date +'%S'); CURRENT_MILISECOND=$(date +'%3N');
#YYYY_MM: 2022-01
YYYY_MM="$CURRENT_YEAR-$CURRENT_MONTH"

#YYYY_MM_DD: 2022-01-06
#YYYY_MM_DD="$CURRENT_YEAR-$CURRENT_MONTH-$CURRENT_DATE"

#YYYY_MM_DD: 20220106
YYYY_MM_DD="$CURRENT_YEAR""$CURRENT_MONTH""$CURRENT_DATE"

#HH_MM_SS: 11:55:40.206
#HH_MM_SS="$CURRENT_HOUR:$CURRENT_MINUTE:$CURRENT_SECOND.$CURRENT_MILISECOND"

#HH_MM_SS: 11.55.40.206
HH_MM_SS="$CURRENT_HOUR.""$CURRENT_MINUTE.""$CURRENT_SECOND.""$CURRENT_MILISECOND"

# echo "YYYY_MM: $YYYY_MM"
# echo "YYYY_MM_DD: $YYYY_MM_DD"
# echo "HH_MM_SS: $HH_MM_SS"
# echo "now1: $now1"
#__________TIME_CALCULATE:END

(
#__________BACKUP_PROCESSING:BEGIN
TableAU_Backup_LOC="/opt/bk/TableAU";
mkdir -p $TableAU_Backup_LOC; cd $TableAU_Backup_LOC

echo ""
echo "Go to backup date folder: [$TableAU_Backup_LOC/$YYYY_MM_DD]"
mkdir -p $YYYY_MM_DD; cd $YYYY_MM_DD

Config_File="config-$YYYY_MM_DD-$HH_MM_SS.json"
Data_File="data-$YYYY_MM_DD-$HH_MM_SS.tsbak"

echo ""
echo "Backup Config: [tsm settings export -f config $Config_File]"
#tsm settings export --output-config-file <path/to/output_file.json> [global options]
#tsm settings export -f config "$Config_File"
tsm settings export --output-config-file "$Config_File"

echo ""
echo "Backup Data: [tsm maintenance backup -f $Data_File]"
#tsm maintenance backup --file <backup_file> [options] [global options]
tsm maintenance backup --file "$Data_File"

echo ""
echo "Moving backup data to Backup Folder [$TableAU_Backup_LOC/$YYYY_MM_DD]"
mv /var/opt/tableau/tableau_server/data/tabsvc/files/backups/* $TableAU_Backup_LOC/$YYYY_MM_DD

md5sum "$Data_File" > "$Data_File".md5
#__________BACKUP_PROCESSING:END


#______________________DELETE_OLD_DATA:BEGIN
cd $PATH_DST
NumberOfFileWantToKeep=14
#Giu lai [14] file moi nhat trong thu muc [$FOLDER_DST]:
#XOA FILE:
#/bin/rm -rf `ls -t "20"* | awk 'NR>7'`

#XOA FOLDER:
cd $TableAU_Backup_LOC
/bin/rm -rf `ls -td */ | awk 'NR>14'`
#______________________DELETE_OLD_DATA:END

#______________________FREE_RAM:BEGIN
free && sync && echo 3 > /proc/sys/vm/drop_caches && free
free
# printf "RAM FREE: \n $(free -h)\n" >> $DB_RESULT.log
#______________________FREE_RAM:END

#############################################
#############################################
#__________TIME_END:BEGIN
time_end=`date +%s.%3N`
execution_time=$( echo "$time_end - $time_start" | bc -l -l );
echo "time_start: [$time_start = $(date -d@$time_start +'%Y.%m.%d-%H.%M.%S.%3N')]"
echo "time_end : [$time_end = $(date -d@$time_end +'%Y.%m.%d-%H.%M.%S.%3N')]"
echo "runtime : [$execution_time] (second.milliseconds)"
#__________TIME_END:END
) 2>&1 | tee backup-job-$YYYY_MM_DD-$HH_MM_SS.log


#THE_END:
# #Restore Config:
# #https://help.tableau.com/current/server/en-us/cli_settings_tsm.htm#TSMSet
# tsm settings import --import-config-file config-2022.01.04-17h40.json

# #Restore Data:
# #https://help.tableau.com/current/server/en-us/upgrade_migrate.htm
# chown -R tableau.tableau /opt/temp/bi-2021.01.04-17h40-2022-01-04.tsbak
# tsm maintenance restore -f /opt/temp/bi-2021.01.04-17h40-2022-01-04.tsbak


#####################
# root@srv032:/opt/bk/TableAU/20220106# /opt/script/tableau-backup.sh

# Go to backup date folder: [/opt/bk/TableAU/20220106]

# Backup Config: [tsm settings export -f config config-20220106-12.33.43.768.json]
# Configuration file written to /opt/bk/TableAU/20220106/config-20220106-12.33.43.768.json.

# Backup Data: [tsm maintenance backup -f data-20220106-12.33.43.768.tsbak]
# Job id is '25', timeout is 1440 minutes.
# 6% - Starting the Active Repository instance, File Store, and Cluster Controller.
# 13% - Waiting for the Active Repository, File Store, and Cluster Controller to start.
# 20% - Installing backup services.
# 26% - Estimating required disk space.
# 33% - Gathering disk space information from all nodes.
# 40% - Analyzing disk space information.
# 46% - Checking if sufficient disk space is available on all nodes.
# 53% - Backing up configuration.
# 60% - Backing up object storage data.
# 66% - Backing up database.
# 73% - Backing up asset keys.
# 80% - Assembling the tsbak archive.
# 86% - Stopping the Active Repository if necessary.
# 93% - Waiting for the Active Repository to stop if necessary.
# 100% - Uninstalling backup services.
# Backup written to '/var/opt/tableau/tableau_server/data/tabsvc/files/backups/data-20220106-12.33.43.768.tsbak' on the controller node.

# Moving backup data to Backup Folder [/opt/bk/TableAU/20220106]
# time_start: [1641447223.762 = 2022.01.06-12.33.43.762]
# time_end : [1641447421.623 = 2022.01.06-12.37.01.623]
# runtime : [197.861] (second.milliseconds)
# root@srv032:/opt/bk/TableAU/20220106#
#####################

2021-11-16

PostgreSQL check all DB SIZE



#CHECK DB SIZE:

postgres=# select t1.datname AS db_name,pg_size_pretty(pg_database_size(t1.datname)) as db_size from pg_database t1 order by pg_database_size(t1.datname) desc;

        db_name         | db_size 
------------------------+---------
 xxx                    | 8109 MB
 xxx_20210622_14h05     | 7520 MB
 xxx_500001_1000000     | 1052 MB
 xxx_000001_500000      | 566 MB
 postgres               | 11 MB
 beta_xxxchat           | 8461 kB
 template1              | 7605 kB
 template0              | 7433 kB
(8 rows)

2021-11-07

[Solved] Percona XtraBackup for MariaDB/MySQL. Binary [HotBackup / HotRestore]

Steps:

A/ FULL BACKUP a DATABASE
A.1/ Backup Full Single Database
A.2/ Backup Schema of Single Database


B/ FULL RESTORE a DATABASE
B.1/ Create new empty Database <New DBName>
B.2/ Import Schema (A.2) to new Empty Database
B.3/ Discard Tablespace
B.4/ Copy all file of Backuped Database (A.1) to new Database
B.5/ 
chown -R mysql.mysql /opt/mariadb/data/<New DBName>
B.6/ Import Tablespace for all new Table
B.7/ DONE

P.S:
1/ Backup full a MariaDB/MySQL Database in Binary mode
2/ Backup full all 
MariaDB/MySQL Database in Binary mode
3/ Backup Incremental a or all database in Binary mode

4/ Restore full a or all 
database in Binary mode

For detail script, contact to me



2021-10-22

Upgrade "Ubuntu 16.04.LTS.x64" to "Ubuntu 18.04.LTS.x64"

#A/ Upgrade "Ubuntu 16.04.LTS.x64" to "Ubuntu 18.04.LTS.x64"
sudo apt-get update -y && sudo apt-get upgrade -y && sudo apt-get dist-upgrade -y && sudo do-release-upgrade


#B/Upgrade Ubuntu 16.04 -> 18.04, Fix lỗi ERROR Curl_Open_SSL cho PHP:
sudo apt-get -y remove --auto-remove libcurl4-openssl-dev && sudo apt-get -y install libcurl3


#C/ Fixed "LetsEncrypt Error":
sudo apt-get -y install libgnutls-openssl27 libgnutls30 ubuntu-advantage-tools


#D/REBOOT OS


#E/ Result: DONE




2021-09-30

PostgreSQL Checking Current config (loaded on RAM)

#!/bin/bash
#/postgres/pgscript/postgres-pg_settings.sh
#LastUpdate: #14:30 2021.08.02
################################################
#su - postgres -c 'psql -h 127.0.0.1 -p 5432'
################################################
echo "===================="
echo "Checking Current config (loaded on RAM):"
su - postgres -c "psql -h 127.0.0.1 -d postgres -c 'select name,setting from pg_settings order by name asc;'"

#THE_END


#RESULT:
root@srv145:/postgres/pgscript# ./postgres-pg_settings.sh
====================
Checking Current config (loaded on RAM):
                  name                  |             setting              
----------------------------------------+----------------------------------
 allow_system_table_mods                | off
 application_name                       | psql
 archive_command                        | (disabled)
 archive_mode                           | off
 archive_timeout                        | 0
 array_nulls                            | on
 authentication_timeout                 | 60
 autovacuum                             | on
 autovacuum_analyze_scale_factor        | 0.1
 autovacuum_analyze_threshold           | 50
 autovacuum_freeze_max_age              | 200000000
 autovacuum_max_workers                 | 3
 autovacuum_multixact_freeze_max_age    | 400000000
 autovacuum_naptime                     | 60
 autovacuum_vacuum_cost_delay           | 20
 autovacuum_vacuum_cost_limit           | -1
 autovacuum_vacuum_scale_factor         | 0.2
 autovacuum_vacuum_threshold            | 50
 autovacuum_work_mem                    | -1
 backend_flush_after                    | 0
 backslash_quote                        | safe_encoding
 bgwriter_delay                         | 200
 bgwriter_flush_after                   | 64
 bgwriter_lru_maxpages                  | 100
 bgwriter_lru_multiplier                | 2
 block_size                             | 8192
 bonjour                                | off
 bonjour_name                           | 
 bytea_output                           | hex
 check_function_bodies                  | on
 checkpoint_completion_target           | 0.9
 checkpoint_flush_after                 | 32
 checkpoint_timeout                     | 300
 checkpoint_warning                     | 30
 client_encoding                        | UTF8
 client_min_messages                    | notice
 cluster_name                           | postgres
 commit_delay                           | 0
 commit_siblings                        | 5
 config_file                            | /postgres/pgdata/postgresql.conf
 constraint_exclusion                   | partition
 cpu_index_tuple_cost                   | 0.005
 cpu_operator_cost                      | 0.0025
 cpu_tuple_cost                         | 0.01
 cursor_tuple_fraction                  | 0.1
 data_checksums                         | on
 data_directory                         | /postgres/pgdata
 data_directory_mode                    | 0700
 data_sync_retry                        | off
 DateStyle                              | ISO, MDY
 db_user_namespace                      | off
 deadlock_timeout                       | 1000
 debug_assertions                       | off
 debug_pretty_print                     | on
 debug_print_parse                      | off
 debug_print_plan                       | off
 debug_print_rewritten                  | off
 default_statistics_target              | 500
 default_tablespace                     | 
root@srv145:/postgres/pgscript# 

PostgreSQL check config file location

#!/bin/bash
#/postgres/pgscript/postgres-pgdata-LOC.sh
#LastUpdate: #11:35 2021.09.18
################################################
#su - postgres -c 'psql -h 127.0.0.1 -p 5432'
################################################

echo "===================="
echo "PostgreSQL [pgdata] location:..."
su - postgres -c "psql -h 127.0.0.1 -d postgres -c 'show data_directory;'"


echo "===================="
echo "PostgreSQL [config file] location:..."
#SELECT name,setting,category FROM pg_settings WHERE category like '%Locations%';
su - postgres -c "psql -h 127.0.0.1 -d postgres -c \"SELECT name,setting,category FROM pg_settings WHERE category like '%Locations%';\""
#THE_END

#RESULT:
root@srv145:/postgres/pgscript# ./postgres-pgdata-LOC.sh
====================
PostgreSQL [pgdata] location:...
  data_directory  
------------------
 /postgres/pgdata
(1 row)

====================
PostgreSQL [config file] location:...
       name        |             setting              |    category    
-------------------+----------------------------------+----------------
 config_file       | /postgres/pgdata/postgresql.conf | File Locations
 data_directory    | /postgres/pgdata                 | File Locations
 external_pid_file |                                  | File Locations
 hba_file          | /postgres/pgdata/pg_hba.conf     | File Locations
 ident_file        | /postgres/pgdata/pg_ident.conf   | File Locations
(5 rows)

How to Run PostgreSQL Query Using Bash Script and Command-Line

#!/bin/bash
#/opt/script/postgres-query-from-shell.sh
#LastUpdate: #11:54 2021.08.02
#----------------------------------------
#su - postgres -c 'psql -h 127.0.0.1'
#su -c "psql -d database_name -c \"SELECT c_defaults  FROM user_info WHERE c_uid = 'testuser'\"" postgres
su - postgres -c "psql -h 127.0.0.1 -d dvdrental -c 'select film_id,title,description from film order by film_id asc limit 5'"



#!/bin/bash
#/opt/script/postgres-query-from-shell-update-load-test.sh
#LastUpdate: #14:13 2021.08.02
#----------------------------------------
#su - postgres -c "psql -h 127.0.0.1 -d dvdrental -c 'select film_id,title,description from film order by film_id asc limit 5'"
#update film set title='Ace Goldfinger-201' where film_id=2
for var_temp in {1..1000}
do
   echo "[DATE: $(date +'%H:%M:%S_%6N')]--------------VALUE: $var_temp"
   su - postgres -c "psql -h 127.0.0.1 -d dvdrental -c \"update film set title='Ace Goldfinger-$var_temp' where film_id=2\""
done




#REF:
for var01 in {1..1000}
do
   echo "Welcome $var01 times"
done

DO
$do$
BEGIN 
    FOR i IN 1..25 LOOP
        -- COMMAND
    END LOOP;
END
$do$;