Badge WAICF

pdf_39aaf456b8f44ab3265fd5f0e0f841e7004e8f8e2d7f1f427ce1a7c0787618104c70a3cfa1025e61334fa86bf77978ca.pdf (mobicheckin-badges.s3-eu-west-1.amazonaws.com)

pdf_1cae86610290b086ab67333c9e9a994ca8a7069d0490b6b6b4a65587f90e008585f678e713994118897be3a6d2efe987.pdf (mobicheckin-badges.s3-eu-west-1.amazonaws.com)

PostgreSQL Un tour d'horizon !

PostgreSQL around the world

 

PostgreSQL 16 is out.

https://www.postgresql.org/docs/16/release-16.html

 

Digging into PostgreSQL iceberg

https://avestura.dev/blog/explaining-the-postgres-meme

 

PostgreSQL partitioning

https://www.timescale.com/blog/when-to-consider-postgres-partitioning/

 

Who does PostgreSQL

https://momjian.us/main/blogs/pgblog/2023.html#September_11_2023

 

[Postgres] NEWS

There is some new about PostgreSQL world.

 

Some useful tricks and tips about PostgreSQL, like how to get unused index list (but so many more like displaying fun smiley instead of blanks for NULL…)

https://www.crunchydata.com/postgres-tips

 

Some basics about transactions with examples

https://stormatics.tech/salmans-planet-postgresql/how-to-execute-transactions-in-postgresql

 

Huge progress with replication; implement bi-directional replication with upcoming PostgreSQL 16

https://www.postgresql.fastware.com/blog/bi-directional-replication-using-origin-filtering-in-postgresql

Découverte d'une base mongodb

mongo -u <USERNAME> -p <PASSWORD> --eval "var DATABASE_NAME='<DATABASE_NAME>'" Source_Discovery_V2.js

POSTGRES Backup / restore

Petites bases :

#backup

-C : ajouter le create database.

pg_dump -d $DB > $DB.sql
#restore
psql < $DB.sql

 

Grosses bases :

#backup
on exporte la structure :

 pg_dump -s $DB> $DBstruct.sql

 psql $DB < $DBstruct.sql

on exporte les données : 
pg_dump -Fc -a -d $DB > $DB.dmp
pg_restore -Fc -d $DB $DB.dmp

Ajout du GZIP pour le dump : 
pg_dump -U <user> <database> | gzip -c > backup.gz

Ajout du GZIP pour l'import : 
gzip -d backup.gz | psql -d <database> -U <user>

You might need to be logged in as postgres in order to have full privileges on databases.

su - postgres
psql -l                      # will list all databases on Postgres cluster

pg_dump/pg_restore

  pg_dump -U username -f backup.dump database_name -Fc 

switch -F specify format of backup file:

• c will use custom PostgreSQL format which is compressed and results in smallest backup file size
• d for directory where each file is one table
• t for TAR archive (bigger than custom format)
• -h/--host Specifies the host name of the machine on which the server is running
• -W/--password Force pg_dump to prompt for a password before connecting to a database

restore backup:

   pg_restore -d database_name -U username -C backup.dump

[Postgres] VACUUM

Comprendre : autovacuum_freeze_max_age

 

Quand le vacuum ne suffit plus pour réinitialiser le XID d'une table, il faut faire un VACUUM FULL de cette table.

[Postgres] User privs

SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table, 
    CASE 
        WHEN COUNT(privilege_type) = 7 THEN 'ALL'
        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
    END AS grants
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee;

Installation et configuration de Cassandra

en-HTCC-8.5.2-Dep-InitializingCassandra.pdf

en-GMS-8.5.2-Deployment-ConfiguringanExternalCassandra.pdf

 

Qqs prérequis :

Faut désactiver le swap

             Il y des warning lors de démarrage de cassandra : 

            WARN  [main] 2021-04-29 16:18:32,338 SigarLibrary.java:174 - Cassandra server running in degraded mode. Is swap disabled? : false,  Address space adequate? : true,  nofile limit adequate? : true, nproc limit adequate? : true

 

1. Mettre le paramètre system vm.max_map_count a 1048575 ( dans /etc/sysctl.d/ 99-sysctl.conf)

             WARN  [main] 2021-04-29 16:18:32,338 StartupChecks.java:311 - Maximum number of memory map areas per process (vm.max_map_count) 65530 is too low, recommended value: 1048575, you can change it with sysctl.

 

Il est recommander de mettre les commitlogs dans un FS dédié séparer des data.

• Monter le /var/lib/cassandra/commitlog sur sd (lvm) dédié ( 20GB de taille )

 

Changer le home de user cassandra en /home/cassandra au lieu de /var/lib/cassandra

 

Ajouter les lignes suivantes dans fichier .bash_profile

 

PATH=$PATH:$HOME/.local/bin:$HOME/bin

#

export CASSANDRA_HOME=/usr/share/cassandra

export CASSANDRA_INCLUDE=$CASSANDRA_HOME/cassandra.in.sh

export CASSANDRA_CONF=/etc/cassandra/conf

export CASSANDRA_OWNR=cassandra

export CASSANDRA_BIN=/usr/sbin/cassandra

export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.292.b10-1.el7_9.x86_64

#

PATH=$PATH:$CASSANDRA_BIN:$JAVA_HOME/jre/bin

export PATH

export PS1='[$LOGNAME@$HOSTNAME:W]$'

 

Faut installer le package jemalloc.x86_64

Découpage fichiers BDD MSSQL

split_db_mssql.pdf

Check READ ONLY MSSQL

check_read_only_MSSQL.pdf