Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

Le dernier des DBAs

  • Backup Cassandra

    Pour sauvegarde incrémentale: 

     

    incremental_backups à true dans le fichier .yaml
     
    https://docs.datastax.com/en/cassandra-oss/2.2/cassandra/configuration/configCassandra_yaml.html#configCassandra_yaml__incremental_backups
     
     
    backup : 
    nodetool -h localhost -p 7199 snapshot mykeyspace

    https://docs.datastax.com/en/cassandra-oss/2.2/cassandra/operations/opsBackupTakesSnapshot.html


    supprimer backup :
    nodetool -h localhost -p 7199 clearsnapshot

    restaurer :


    cp snapshots/1535397029191/* /path/to/keyspace1/standard1/

    sstableloader --nodes 172.17.0.2 /var/lib/cassandra/loadme/keyspace1/standard1-f8a4fa30aa2a11e8af27091830ac5256/


    https://docs.datastax.com/en/cassandra-oss/3.0/cassandra/operations/opsBackupSnapshotRestore.html

    https://docs.datastax.com/en/cassandra-oss/2.2/cassandra/tools/toolsBulkloader.html

  • DEPLOY HA postgres

    deploy_HA.sh

    #!/bin/bash
    #script de deploiement de solution HA pour postgres
    # JJY
    # 2021
    ######################################################
    HA_LIST_SERVER_PRIM=(toto)

    HA_LIST_SERVER_STBY=(titi)

    HA_LIST_SERVER_RO=(tata)

    SRC=$HOME/adm/dba/sh

    #Deploiement sur serveur primaire

    for ((i = 0; i < ${#HA_LIST_SERVER_PRIM[@]}; i++)); do
    ssh ${HA_LIST_SERVER_PRIM[i]} "mkdir -p $HOME/adm/dba/sh;mkdir -p $HOME/adm/dba/sql;mkdir -p $HOME/adm/dba/log"
    scp pg_install_HA_primary.sh ${HA_LIST_SERVER_PRIM[i]}:$HOME/adm/dba/sh/install.sh
    state=${HA_LIST_SERVER_PRIM[i]:0:1}

    if [ $state = "v" ]
    then
    scp ../sql/config_val.sql ${HA_LIST_SERVER_PRIM[i]}:$HOME/adm/dba/sql/config.sql
    echo "Environnement de VAL"
    else
    scp ../sql/config_prd.sql ${HA_LIST_SERVER_PRIM[i]}:$HOME/adm/dba/sql/config.sql
    echo "Environnement de PROD"
    fi
    done

    for ((i = 0; i < ${#HA_LIST_SERVER_PRIM[@]}; i++)); do
    ssh ${HA_LIST_SERVER_PRIM[i]} << EOFSSH1
    cd $SRC
    echo " "
    echo "Configuration du serveur ${HA_LIST_SERVER_PRIM[i]}."
    echo " "
    ./install.sh
    EOFSSH1
    done

    #Deploiement sur serveur secondaire

    for ((i = 0; i < ${#HA_LIST_SERVER_STBY[@]}; i++)); do
    ssh ${HA_LIST_SERVER_STBY[i]} "mkdir -p $HOME/adm/dba/sh;mkdir -p $HOME/adm/dba/sql;mkdir -p $HOME/adm/dba/log"
    scp pg_install_HA_standby.sh ${HA_LIST_SERVER_STBY[i]}:$HOME/adm/dba/sh/install.sh
    done

    for ((i = 0; i < ${#HA_LIST_SERVER_STBY[@]}; i++)); do
    ssh  ${HA_LIST_SERVER_STBY[i]} <<  EOFSSH2
    cd $SRC
    echo " "
    echo "Configuration du serveur ${HA_LIST_SERVER_STBY[i]}."
    echo " "
    ./install.sh ${HA_LIST_SERVER_PRIM[i]}
    EOFSSH2
    echo "finscr"
    done

    #Deploiement sur serveur read/only

    for ((i = 0; i < ${#HA_LIST_SERVER_RO[@]}; i++)); do
    ssh ${HA_LIST_SERVER_RO[i]} "mkdir -p $HOME/adm/dba/sh;mkdir -p $HOME/adm/dba/sql;mkdir -p $HOME/adm/dba/log"
    scp pg_install_HA_readonly.sh ${HA_LIST_SERVER_RO[i]}:$HOME/adm/dba/sh/install.sh
    done

    for ((i = 0; i < ${#HA_LIST_SERVER_RO[@]}; i++)); do
    ssh ${HA_LIST_SERVER_RO[i]} << EOFSSH3
    cd $SRC
    echo "Configuration du serveur ${HA_LIST_SERVER_RO[i]}."
    ./install.sh ${HA_LIST_SERVER_STBY[i]} ${HA_LIST_SERVER_RO[i]}
    EOFSSH3
    done
    exit;
    ______________________________________________________________________________
    pg_install_HA_primary.sh

    #!/bin/bash
    # script de configuration
    # pour une installation en HA postgres
    # JJY
    # 2021
    ########################################
    . $HOME/.bash_profile
    export CLIENT_ENCODING=UTF8
    export LANG=fr_FR.UTF-8
    export NLS_LANG=${LANG}

    cd $(dirname $0)

    DB=${HOSTNAME:5:3}
    pg_ctl stop
    cp $PGDATA/*.conf $HOME
    rm -rf /pgdata/*
    rm -rf /pgindx/*
    rm -rf /pgtmp/*
    rm -rf /pgwal/pg_wal/*
    rm -rf /pgarchive/*
    rm -rf /pgcluster/data/*


    mkdir -p /pgdata/tbs_repmgr_data
    mkdir -p /pgdata/tbs_${DB}_data
    mkdir -p /pgindx/tbs_${DB}_indx
    mkdir -p /pgtmp/pgsql_tmp

    initdb -D /pgcluster/data --wal-segsize=512 --waldir=/pgwal/pg_wal

    cp $HOME/pg_hba.conf $PGDATA
    echo "max_connections=400" >>$HOME/postgresql.conf
    echo "min_wal_size=1GB" >>$HOME/postgresql.conf
    echo "max_wal_size=2GB" >>$HOME/postgresql.conf
    cp $HOME/postgresql.conf $PGDATA
    cp $HOME/postgresql.replication.conf $PGDATA
    #cp $HOME/pg_ident.conf $PGDATA

    pg_ctl -D /pgcluster/data -l /pglog/postgresql-logfile_start.log start -o "--log_duration=off --log_line_prefix='%t:%r:%u@%d:[%p]: ' --log_directory='/pglog' --log_filename='postgresql-%Y-%m-%d_%H%M%S.log' --shared_buffers=10GB --max_connections=400 --maintenance_work_mem=2GB --effective_cache_size=4GB --checkpoint_completion_target=0.9  --wal_buffers=32MB --max_wal_size=2GB --min_wal_size=1GB"

    psql < ../sql/config.sql

    repmgr -f /etc/repmgr.conf primary register

    repmgr cluster show

    ________________________________________________________________________________________________________

    pg_install_HA_standby.sh

    #!/bin/bash
    # script de configuration
    # pour une installation en HA postgres
    # JJY
    # 2021
    ########################################

    . $HOME/.bash_profile
    export CLIENT_ENCODING=UTF8
    export LANG=fr_FR.UTF-8
    export NLS_LANG=${LANG}

    cd $(dirname $0)

    DB=${HOSTNAME:5:3}

    pg_ctl stop

    rm -rf /pgdata/*
    rm -rf /pgindx/*
    rm -rf /pgtmp/*
    rm -rf /pgwal/pg_wal/*
    rm -rf /pgarchive/*
    rm -rf /pgcluster/data/*

    mkdir -p /pgdata/tbs_repmgr_data
    mkdir -p /pgdata/tbs_${DB}_data
    mkdir -p /pgindx/tbs_${DB}_indx
    mkdir -p /pgtmp/pgsql_tmp

    repmgr -h $1 -U repuser -d repmgr -f /etc/repmgr.conf standby clone

    /usr/lib/postgresql/11/bin/pg_ctl start -D /pgcluster/data

    repmgr -f /etc/repmgr.conf standby register
    sleep 2
    repmgr cluster show

    # Wait for 2 seconds
    sleep 2

    echo "Faire ctrl-C si le script de rend pas la main!"

    ________________________________________________________________

    pg_install_HA_readonly.sh

    #!/bin/bash
    # script de configuration
    # pour une installation en HA postgres
    # JJY
    # 2021
    ########################################

    . $HOME/.bash_profile
    export CLIENT_ENCODING=UTF8
    export LANG=fr_FR.UTF-8
    export NLS_LANG=${LANG}

    cd $(dirname $0)

    DB=${HOSTNAME:5:3}

    # Get its PID
    PID=$!

    pg_ctl stop

    rm -rf /pgdata/*
    rm -rf /pgindx/*
    rm -rf /pgtmp/*
    rm -rf /pgwal/pg_wal/*
    rm -rf /pgarchive/*
    rm -rf /pgcluster/data/*

    mkdir -p /pgdata/tbs_repmgr_data
    mkdir -p /pgdata/tbs_${DB}_data
    mkdir -p /pgindx/tbs_${DB}_indx
    mkdir -p /pgtmp/pgsql_tmp

    $PGHOME/bin/pg_basebackup -D /pgcluster/data -h $1 -U repuser -X stream --waldir=/pgwal/pg_wal -v

    cat /dev/null > $PGDATA/recovery.conf

    echo "standby_mode = 'on'" > $PGDATA/recovery.conf
    echo "primary_conninfo = 'host=$1 user=repuser application_name=$2 port=5432 passfile=''/home/postgres/.pgpass'''" >> $PGDATA/recovery.conf
    echo "recovery_target_timeline = 'latest'" >> $PGDATA/recovery.conf
    echo "archive_cleanup_command = 'pg_archivecleanup /pgarchive %r'" >> $PGDATA/recovery.conf

    mkdir -p /home/postgres/adm/pg_conf
    cp $PGDATA/*.conf /home/postgres/adm/pg_conf
    ln -f -s  /home/postgres/adm/pg_conf/postgresql.conf /pgcluster/data/postgresql.conf
    ln -f -s /home/postgres/adm/pg_conf/postgresql.replication.conf /pgcluster/data/postgresql.replication.conf
    ln -f -s  /home/postgres/adm/pg_conf/pg_hba.conf /pgcluster/data/pg_hba.conf
    ln -f -s  /home/postgres/adm/pg_conf/recovery.conf /pgcluster/data/recovery.conf

    echo "shared_preload_libraries='pg_stat_statements' ">> $PGDATA/postgresql.replication.conf

    pg_ctl start
    echo "Faire ctrl-C si le script de rend pas la main!"
    exit

     

    _________________________________________________

    config_val.sql ou config_val.sql (seul le mot de passe change!)

    o configHA.log
    CREATE ROLE repuser;
    ALTER ROLE repuser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION NOBYPASSRLS;
    ALTER ROLE repuser WITH PASSWORD 'lemotdepasse';

    CREATE TABLESPACE temp OWNER postgres LOCATION '<temp_dir>';
    CREATE TABLESPACE tbs_repmgr_data OWNER postgres LOCATION '<repmgr_dir_data>';

    GRANT ALL ON TABLESPACE tbs_repmgr_data TO repuser;
    CREATE DATABASE repmgr WITH TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = 'tbs_repmgr_data';
    ALTER DATABASE repmgr SET temp_tablespaces TO 'temp';
    ALTER DATABASE repmgr OWNER TO repuser;
    c repmgr
    CREATE SCHEMA repmgr;
    ALTER SCHEMA repmgr OWNER TO repuser;
    CREATE EXTENSION IF NOT EXISTS repmgr WITH SCHEMA repmgr;
    c postgres
    ALTER ROLE repuser SET search_path TO repuser,repmgr, "$user", public;

  • Status instance postgres

    POSTGRES_IS_RUNNING=$(export PGPASSWORD=${PG_PWD}; psql --host=${MACH_DEST} --port=${PORT} --user=${PG_USER} --dbname=postgres --quiet --tuples-only --no-psqlrc --command "SELECT 1;" 2>/dev/null)

     

    INSTANCE_STATUS=$(export PGPASSWORD=${PG_PWD}; psql --host=${MACH_DEST} --port=${PORT} --user=${PG_USER} --dbname=postgres --quiet --tuples-only --no-psqlrc --command="SELECT CASE pg_is_in_recovery() WHEN true THEN 'IN RECOVERY' ELSE 'OPEN' END;" 2>/dev/null)

  • Référencement

    Déjà il faut contacter le responsable des achats. 

    Ensuite il faut demander un dossier pour être éligible au referencement.

     

    Critère : la taille de la masse salariale. 

    Répondre à un nombre de lots

    Être accrédité CIR : crédit impôt recherche 

    Avoir une RSE : responsabilité sociétale des entreprises 

     

     

     

  • [PostgreSQL] Voir les tables partitionnées

    WITH RECURSIVE partition_info(relid, relname, relsize, relispartition, relkind)
    AS (SELECT oid AS relid, relname, pg_relation_size(oid) AS relsize, relispartition, relkind
          FROM pg_catalog.pg_class
         WHERE relkind = 'p' AND relname = 'mof_ple_part' -- Pour une table donnee.
         UNION ALL
        SELECT c.oid AS relid, c.relname AS relname, pg_relation_size(c.oid) AS relsize,
               c.relispartition AS relispartition, c.relkind AS relkind
          FROM partition_info AS p, pg_catalog.pg_inherits AS i, pg_catalog.pg_class AS c
         WHERE p.relid = i.inhparent AND c.oid = i.inhrelid AND c.relispartition)

    SELECT * FROM partition_info;
     

    select relnamespace::regnamespace::text schema_name, oid::regclass::text table_name from pg_class
    where relkind = 'p' and oid in (select distinct inhparent from pg_inherits)
    order by schema_name, table_name;

  • MAJ Sequence

    sqlplus -s user_admin/<mdp>@BDDREC << EOF > upd_seq.sql
    SELECT 'SELECT setval('''||sequence_name||''','||last_number||', true);'
    FROM user_sequences;
    exit;
    EOF
    psql --single-transaction  -h $2 -U postgres -d $1 -f ./upd_seq.sql