Databases tips & tricks

Système

Postgresql - Dump

1
2
Dump all without Repmgr (nexus):
sudo -i -u postgres pg_dumpall --exclude-database=repmgr --data-only --column-inserts --inserts --on-conflict-do-nothing --role=nexus_user -f /pgsql_backup/nexus_data_transfert/db_nexus.dump

Postgresql - Retrait Tables

1
sudo sed -i -e '/public.batch_/d' -e '/public.download_count/d' /pgsql_backup/nexus_data_transfert/db_nexus.dump

Postgresql - Update Tables

1
2
sudo -u postgres /usr/pgsql-16/bin/psql -tAc "update repository set attributes = replace(attributes::TEXT,': 18444.0',': 8081.0')::jsonb where  name='docker-private';" nexus
sudo -u postgres /usr/pgsql-16/bin/psql -tAc "SELECT attributes from repository where name='docker-private';" nexus

Postgresql - Restore Dump with Errors Catch

1
sudo -i -u postgres psql -d nexus -f /bind_pg_backup/nexus_data_transfert/db_nexus.dump 2>log_errors.txt

Postgresql - Drop with Force

1
sudo -i -u postgres psql -tAc "DROP DATABASE IF EXISTS nexus WITH (FORCE);"

Postgresql - Reset Admin Password

1
2
3
4
sudo -u postgres /usr/pgsql-16/bin/psql nexus 
update security_user SET password='$shiro1$SHA-512$1024$NE+wqQq/TmjZMvfI7ENh/g==$V4yPw8T64UQ6GfJfxYq2hLsVrBY8D1v+bktfOxGdt4b/9BthpWPNUy/CBk6V9iA0nHpzYzJFWO8v/tZFtES8CA==', status='active' WHERE id='admin';
select * from security_user WHERE id='admin'; -> Verifier la présence de $shiro1
mdp = admin123

Import propre sql

1
mysql -u root -e "source /db.sql;"

Repmgr Cluster

En Vrac:

1
2
3
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf cluster show
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf standby switchover
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf node rejoin -d 'host=WQYD3762 user=repmgr dbname=repmgr connect_timeout=2' --force-rewind

Repmgr Simple Sync

1
2
3
sudo systemctl stop postgresql-16
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf node rejoin -d 'host={{ install_repmgr_standby_host }} user=repmgr dbname=repmgr connect_timeout=2' --force-rewind
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf standby switchover

Repmgr Hard Sync

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sudo systemctl start postgresql-16
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf cluster show
sudo systemctl stop postgresql-16
sudo mv /var/lib/pgsql/16/data /var/lib/pgsql/16/data_$DATE_BACKUP
sudo mv /pgsql_wal/16/wals /pgsql_wal/16/wals_$DATE_BACKUP
echo "Sync data"
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf -h {{ install_repmgr_standby_host }} -U repmgr -d repmgr standby clone --force
sudo systemctl start postgresql-16
echo "Join Cluster as standby"
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf -h {{ install_repmgr_standby_host }} -U repmgr -d repmgr standby register --force
echo "Promoting to primary via switchover"
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf standby switchover
sudo -u postgres /usr/pgsql-16/bin/repmgr -f /etc/repmgr/16/repmgr.conf cluster show

echo "Echo if restoring success you can delete backups: /var/lib/pgsql/16/data_$DATE_BACKUP & /pgsql_wal/16/wals_$DATE_BACKUP"
postgresql repmgrd mysql

Commands tips & tricks

Système

Infos Reseau

1
2
IP=$(ip -4 -o a | sed -e '/^2:/!d;s/^.*inet //;s/\/.*$//g')
HOSTNAME=$(hostname -s)

Infos Appli/Ports

1
sudo netstat -nltp

Infos Usb Devices / Port serie

1
lsusb -cui

Modification des hosts et dns

1
[[ $(grep -E "$IP(.*)$(hostname -s)(.*)" /etc/hosts) ]] || echo -e "$IP    $(hostname -s)" >> /etc/hosts

Verification execution en root

1
[[ $(whoami) != root ]] && { echo "ERREUR : Veuillez exécuter ce script en tant que root avec la commande sudo !"; exit 1; }

Proxy

APT:

1
echo -e "Acquire::http::Proxy \"${PROXY_URL}/\";" > /etc/apt/apt.conf.d/proxy.conf

System:

1
2
3
4
5
PROXY_URL="http://USER:PASS@HOST:PORT"
echo -e "export http_proxy='${PROXY_URL}'\n
	     export https_proxy='${PROXY_URL}'\n
	     export no_proxy=EXCLUDED_DOMAIN.fr,localhost,127.0.0.1,${HOSTNAME}" > /etc/profile.d/proxy.sh
source /etc/profile.d/proxy.sh

Maven:

1
2
mkdir -p ~/.m2
echo ${http_proxy} | tr "/:@" " " | awk '{print "<settings>\n<proxies>\n<proxy>\n<id>proxy</id>\n<active>true</active>\n<protocol>"$1"</protocol>\n<host>"$4"</host>\n<port>"$5"</port>\n<username>"$2"</username>\n<password>"$3"</password>\n</proxy>\n</proxies>\n</settings>"}' > ~/.m2/settings.xml

Curl:

1
echo "proxy = ${http_proxy}" > ~/.curlrc

Decompresser à la volée

1
2
curl -s $PACKAGE_REGISTRY/stedolan/jq/1.6/jq-1.6.tar.gz | tar xz -C /usr/bin/ && chmod +x /usr/bin/jq

Add Iptables

1
iptables -A INPUT -p tcp -m tcp --dport 443 -j ACCEPT && iptables -A INPUT -p tcp -m tcp --dport 80 -j ACCEPT && iptables-save 2> /dev/null > /etc/sysconfig/iptables

Optimisation memoire Java (java heap size)

1
-Xmx2048m -Xms512m -XX:MaxPermSize=2048m -XX:+UseConcMarkSweepGC -XX:ReservedCodeCacheSize=128m -XX:+HeapDumpOnOutOfMemoryError

Changement de cible binaire

1
2
3
4
5
update-alternatives --set php /usr/bin/php5.6

Alias alternative from vars:
echo "[[ \${php}  ]] && alias php='update-alternatives --set php /usr/bin/php\${php} > /dev/null; php' \
	\n[[ \${node} ]] && alias node='nvm use \${node} >/dev/null; node'" >> ~/.bashrc

Manipulation de variables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Variable avec nom dynamique (shell): 
varname="toto"
varcontent="content"
$varname=$varcontent

name=${varname}
data=${!varname}

Variable avec nom dynamique (Groovy): 
def OC_CONFIG_PATH="OC_CONFIG_AA"
evaluate("env.${OC_CONFIG_PATH} = \"--config='/tmp/$OC_CONFIG_PATH/config'\" ")
env.OC_CONFIG_PATH="$OC_CONFIG_PATH"
OC_CONFIG_PATH=\${!OC_CONFIG_PATH}
env.KUBECONFIG="/tmp/AA/oc_config_${JOB_BASE_NAME}"

Sed tricks

1
2
3
4
5
6
7
change maven version
sed -i -r -e 's#<source>(.*)</source>#<source>1.8</source>#g' -e 's#<target>(.*)</target>#<target>1.8</target>#g' pom.xml

# Export 'exist*' groovy vars outside this shell
echo "\$(env | grep -E '_exist|_version' |sed 's/^/env./')" | tee ${WORKSPACE}/vars.groovy


Find tricks

1
2
3
find ${builder.buildDir}/ -name "*.sh" -exec chmod +x {} \\;
find ${builder.buildDir}/ -name "*.sh" -exec chmod 755 {} \\;

Windows Startup applications

1
%AppData%\Microsoft\Windows\Start Menu\Programs\Startup

Systemd Authorize execution from alternative PATH

1
2
semanage fcontext -a -t bin_t "/data/pgsql/bin(/.*)?"
restorecon -RF /data/pgsql/bin

Tester la presence d’un executable:

1
2
command -v <BIN>

Pousser une commande en Background et la récupérer:

1
2
3
4
5
tail -f /dev/null 	#exemple
ctrl + z 			#stop le process
bg 					# le pousse en Background
fg 					# pour récupere le Process

shell show ip hostname proxy tips & tricks

GIT- Appliquer des changements d'une branche autonome à une autre

Système

Pour appliquer les changement d’une branche à une autre :

Comparer la liste des fichiers de master par rapport à une autre branche (demo) avec exclusion de fichier

1
git diff master..demo --stat ':(exclude)fichier1' ':(exclude)README.md'

Comparer le contenu des fichiers de master par rapport à demo

1
git diff master..demo

Appliquer les changement de master sur metro pour le fichier fichier_2.txt

1
2
git checkout demo
git checkout --patch master fichier_2.txt

-> Options:
y: accepter les changements
n: refuser les changements
a: accepter tous les changements
e: editer manuellement le fichier (si la ligne à garder commence par - il faut juste le remplacer par un espace et supprimer la ligne correspondante qui commence par un +)

Cherrypick entre plusieurs repos

  • Preparation
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    git clone https://repoA.git repoA
    git clone https://repoB.git repoB
    git clone https://repoC.git repoC

    cd repoB/
    git remote add repoA ../repoA
    git remote add repoC ../repoC

    cd ..

    cd repoC/
    git remote add repoA ../repoA
    git remote add repoB ../repoB

  • Utilisation
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    Recupération de un ou plusieurs commits de repoA vers repoB

    cd repoB/
    git fetch repoA

    -> pour un seul commit:
    git cherry-pick <commit_id>

    -> pour un groupe de commits:
    git cherry-pick <first_commit>..<last_commit>

    git push

Application à grande échelle

1
for br in $(git branch --list | tr -d '*' | grep -v <SOURCE BRANCH>); do git checkout $br; git checkout --patch <SOURCE BRANCH> <FICHIER> ; done

Authentification

1
2
3
4
5
6
7
8
9
10
11
Personal/project/group access tokens

curl "https://gitlab.example.com/api/v4/projects?private_token=<your_access_token>"

curl --header "PRIVATE-TOKEN: <your_access_token>" "https://gitlab.example.com/api/v4/projects"

OAuth-compliant headers:

curl --header "Authorization: Bearer <your_access_token>" "https://gitlab.example.com/api/v4/projects"

curl "https://oauth2:${CI_JOB_TOKEN}@${CI_SERVER_HOST}/${CI_PROJECT_PATH}.git"

Pull and push back in Gitlab-ci

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
update_tree_on_git:
  stage: update_tree_on_git
  tags:
    - <target runner>
  variables:
    # Save Git token in CI/CD variables as GIT_PUSH_TOKEN
    CI_JOB_TOKEN: $GIT_PUSH_TOKEN
  before_script:
    # Set Git info
    - git config user.email "${GITLAB_USER_EMAIL}"
    - git config user.name "${GITLAB_USER_NAME}"
    - git remote add origin_push https://oauth2:${CI_JOB_TOKEN}@${CI_SERVER_HOST}/${CI_PROJECT_PATH}.git
  script:
    - **make some stuff**
  after_script:
    # Save changes
    - git add <files> && git commit -m "<commit message>"
    - git push origin_push HEAD:${CI_COMMIT_BRANCH} -o ci.skip # Prevent triggering pipeline again

Save git credential

1
git config --global credential.helper store
Git Branch

Docker tips & tricks

Système

Install latest docker & docker-compose

1
2
curl -sSL https://get.docker.com/ | sh
curl -L "https://github.com/docker/compose/releases/download/1.24.0/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose

Configure proxy for docker

1
2
3
4
mkdir -p /etc/systemd/system/docker.service.d
PROXY_URL='http:/USER:PASS@PROXY:PORT'
echo -e "[Service] \nEnvironment=\"HTTPS_PROXY=${PROXY_URL}\"\nEnvironment=\"NO_PROXY=localhost,127.0.0.1,${HOSTNAME}\"" > /etc/systemd/system/docker.service.d/http-proxy.conf
systemctl daemon-reload && systemctl restart docker

Change data directory & activate logrotate

1
2
3
mkdir -p /etc/docker/ /data
echo -e "{\"data-root\": \"/data/docker\",\"log-driver\": \"json-file\", \"log-opts\": { \"max-size\": \"100m\",\"max-file\": \"5\" }}}" | tee /etc/docker/daemon.json
systemctl daemon-reload && systemctl enable docker && systemctl restart docker

Clean by filter orphan images or regex container

1
2
docker rmi $(docker images -q -f 'dangling=true') 2> /dev/null || true
docker rm -f $(docker ps -aq -f 'name=NAME*') 2> /dev/null || true

Import many images from directory, apply tag by date and latest, and push to registry

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Livraisons="2018-10-10 2019-01-21 2019-03-21 2019-03-26 2019-04-10 2019-04-11"

for livraison in $Livraisons;do
	for image in $(ls $IMAGES_PATH/$livraison/*.tar); do 
		name=$(docker load -q -i $image | awk '{print $3}');
		newtag=$(basename $name | sed -e "s/:.*$/:$(basename $(dirname $image))/g")
		latesttag=$(basename $name | sed -e 's/:.*$/:latest/g')
		docker tag $name $DOCKER_REGISTRY/$newtag; 
		docker tag $name $DOCKER_REGISTRY/$latesttag
		docker push $DOCKER_REGISTRY/$newtag 
		docker push $DOCKER_REGISTRY/$latesttag
		docker rmi $name >/dev/null; 
		echo -e "\n==> image $DOCKER_REGISTRY/$newtag imported\n"; 
	done
done

Dockerfile exemple multi-tool container

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
### Input variables ##
ARG http_proxy
ARG https_proxy
ARG no_proxy 

## put arg as env for futur use
ENV http_proxy=$http_proxy
ENV https_proxy=$https_proxy
ENV no_proxy=$no_proxy

# System Env
ENV TERM=xterm
ENV LC_ALL=C.UTF-8
ENV DEBIAN_FRONTEND=noninteractive

# Package Env
ENV INSTALL="apt-utils software-properties-common vim dos2unix sqlite3 mysql-server mysql-client libpcre3-dev apache2 php-pear php-pecl-http maven"
ENV INSTALL_PHP="5.5 5.6 7.0 7.2"
ENV INSTALL_PHP_MODULES="xml mysql http mcrypt sqlite3 mbstring curl cli gd dev intl xsl memcache memcached zip apc"
ENV INSTALL_JAVA="openjdk-8-jdk openjdk-8-jre"
ENV INSTALL_NODEJS="8 9 10 11 12"

# Download others packages
RUN apt-get update -y && apt-get install -y software-properties-common apt-transport-https lsb-release

# Download the signing key for php repo
RUN wget -O /etc/apt/trusted.gpg.d/php.gpg https://packages.sury.org/php/apt.gpg

# Add repo for php packages
RUN echo "deb https://packages.sury.org/php/ $(lsb_release -sc) main" > /etc/apt/sources.list.d/php.list && apt-get update -y

# Add nvm for nodejs managing
RUN wget -O install.sh  https://raw.githubusercontent.com/creationix/nvm/v0.33.11/install.sh; bash install.sh

# Installing packages
# System packages
RUN /bin/bash -c 'for packages in ${INSTALL[@]};do apt-get install -y ${packages} ;done'

# Php & php modules packages
RUN /bin/bash -c 'for php_version in ${INSTALL_PHP[@]};do apt-get install -y php${php_version} ; for php_modules in ${INSTALL_PHP_MODULES[@]};do apt-get install -y php${php_version}-${php_modules} ;done ;done'

# Java packages
RUN /bin/bash -c 'for java_version in ${INSTALL_JAVA[@]};do apt-get install -y ${java_version} ;done'

# Nodejs packages
RUN /bin/bash -c 'source ~/.profile 2>/dev/null; for node_version in ${INSTALL_NODEJS[@]};do nvm install ${node_version} ;done'


tips & tricks docker