Quelques raccourcis sur le client psql
\l : lister toutes les bases
\c <base> : se connecter à la base <base>
\dt : lister les tables de la base courante
\d : quitter le client
\du : lister les roles
Créer une base et son utilisateur
CREATE DATABASE my_db;
CREATE USER my_username WITH encrypted password 'my_pass';
GRANT ALL ON DATABASE my_db to my_username;
Requête dynamique en plsql
Le but est de requêter une table dont le nom est récupéré depuis la colonne d’une autre table.
CREATE OR REPLACE FUNCTION ressource.getRessource(integer)
RETURNS character varying AS
$BODY$
DECLARE
ressource RECORD;
name_list RECORD;
BEGIN
BEGIN
FOR name_list IN (SELECT id, name FROM table_index)
LOOP
EXECUTE 'SELECT * FROM ressource.' || quote_ident(name_list.name) || ' as t where t.id = '|| $1
INTO ressource;
EXIT WHEN FOUND;
END LOOP;
END;
RETURN ressource;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Get rid of « error PANIC: could not locate a valid checkpoint record »
Sur Postgresql 9.6, il est possible de forcer la suppression des xlogs (avec une possible perte des transactions) :
pg_resetxlog -f DATADIR
En s’assurant que DATADIR est le chemin vers le repertoire de la bdd.
Sur Debian Strech :
- il faut lancer
pg_resetxlog en étant connecté avec le user « postgres » pg_resetxlog n’est pas dans le PATH du userpg_resetxlog se trouve dans /usr/lib/postgresql/9.6/bin
Pour plus de détails : http://www.postgresql.org/docs/current/interactive/app-pgresetxlog.html
Faire un full backup sans interruption client sur un serveur standalone
Il est important de comprendre que postgresql bufferise les transactions et qu’une copie brute force des données du disque (avec un dd ou autre) ne suffit pas à garantir la cohérence d’un backup. Ces buffers sont liés à la notion de checkpoint, ceux sont eux qui permettent d’identifier quand un ensemble de transaction a été effectivement écrit sur le disque. Avec l’activation de l’archivage dans des WAL, on peut récupérer l’ensemble des transactions qui sont en attente dans un fichier (le WAL).
Donc un backup complet doit se composer des données brutes et des WAL qui se sont accumulées pendant la copie des données brutes. Avec une réplication sur un standby on reste sur la même philosophie.
Cette solution est basée sur un export des backups sur une machine distante via rsync, il faut donc avoir préparé les accès ssh pour le user postgres entre les machines pour que tout fonctionne.
Voici les paramètres du fichier de conf postgresql.conf qui sont nécessaires :
wal_level = replica # minimal, replica, or logical
checkpoint_timeout = 1min # à règler en fonction de la charge de la base
max_wal_size = 1GB
min_wal_size = 80MB
checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
checkpoint_flush_after = 256kB # à règler en fonction du type de disque (SAN, disque virtuel...)
checkpoint_warning = 30s
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/backup_in_progress || (test ! -f /data/db_backup/archive/%f && cp %p /data/db_backup/archive/%f)'
# si vous voulez gzipper les WAL: (attention a bien dezipper lors du restore sinon postgres ne verra pas les WAL
# archive_command = 'test ! -f /var/lib/postgresql/backup_in_progress || (test ! -f /data/db_backup/archive/%f && gzip < %p > /data/db_backup/archive/%f)'
Ensuite voici un script qui lance le backup à bas niveau et la copie les WAL en attente. Voir les commentaires pour les explications :
#!/bin/bash
set -x
SYNCUSER=postgres
TARGET=backup_server
TARGET_BACKUP_FOLDER_NAME=backup_b
OLD_PWD=`pwd`
# fichier drapeau qui indique a postgres si il doit archiver ou non les WAL, fichier defini dans le parametre archive_command lui meme defini dans postgresql.conf
FLAG_FILE=/var/lib/postgresql/backup_in_progress
BACKUP_PATH=/data/db
DATA_FOLDER_NAME=main
DATA_PATH=/var/lib/postgresql/9.6/$DATA_FOLDER_NAME
# idem le repertoire d'archive est defini dans le archive_command
ARCHIVE_FOLDER_NAME=archive
DATE=`date +%F%T`
echo "Démarrage backup $DATE"
# demarre l'archivage des fichiers WAL
touch $FLAG_FILE
# demarre le buffering des transactions dans des WAL
psql -c "select pg_start_backup('backup_$DATE');"
echo "Copie des données"
# copie bas niveau de tous les fichiers de données de postgresql sur le distant
cd $DATA_PATH/..
rsync -aivz --delete --exclude postmaster.pid --exclude postmaster.opts --exclude pg_xlog $DATA_FOLDER_NAME/ $SYNCUSER@$TARGET:$BACKUP_PATH/$TARGET_BACKUP_FOLDER_NAME/
# Decommentez la ligne ci-dessous pour simuler un temps d'attente (cela permet de tester que l'injection d'une transaction pendant le backup est bien stocké dans le WAL et effectivement restauré par le script de restoration
# read
# arrete le buffering des transactions
psql -c "select pg_stop_backup();"
echo "copie terminée"
# arrete l'archivage des WAL
rm $FLAG_FILE
echo "Sauvegarde des transactions en attentes"
# ajout des WAL archivés lors du backup dans le repertoire pg_xlog du backup distant
cd $BACKUP_PATH
rsync -aivz $ARCHIVE_FOLDER_NAME/ $SYNCUSER@$TARGET:$BACKUP_PATH/$TARGET_BACKUP_FOLDER_NAME/pg_xlog/
echo "Base de donnée et WAL sauvegardées sur $TARGET dans $BACKUP_PATH"
DATE=`date +%F%T`
echo "Fin de backup $DATE"
cd $OLD_PWD
Restaurer le backup
Avec une version 9.6 et un standalone, il suffit de lancer ce script pour restaurer la base sauvegardée.
Procédure :
- arrêter le service postgresql (il devrait l’être vu que vous voulez faire une restauration)
- Lancer le script en tant que user postgres
- relancer le service postgresql
#!/bin/bash
#set -x
echo "Make sure postgresql server is down, if not press CTRL+C"
read
SYNCUSER=postgres
TARGET=backup_server
TARGET_BACKUP_FOLDER_NAME=backup_b
OLD_PWD=`pwd`
# fichier drapeau qui indique a postgres si il doit archiver ou non les WAL, fichier defini dans le parametre archive_command lui meme defini dans postgresql.conf
FLAG_FILE=/var/lib/postgresql/backup_in_progress
BACKUP_PATH=/data/db
DATA_FOLDER_NAME=main
DATA_PATH=/var/lib/postgresql/9.6/$DATA_FOLDER_NAME
DATE=`date +%F%T`
echo "Restauration du dernier backup"
echo "Déplacement du repertoire data"
cd $DATA_PATH/..
tar -c $DATA_FOLDER_NAME | gzip > $DATA_FOLDER_NAME_$DATE.tar.gz
echo "Restauration des données"
# copie bas niveau de tous les fichiers de données de postgresql sauvegardés sur le local (inlcus les WAL, cf. script de backup)
rsync -aivz --delete $SYNCUSER@$TARGET:$BACKUP_PATH/$TARGET_BACKUP_FOLDER_NAME/ $DATA_FOLDER_NAME/
DATE=`date +%F%T`
echo "Base de donnée restaurée $DATE, vous pouvez redémarrer le service"
cd $OLD_PWD
Erreur stupide : s’assurer que les WAL ne sont pas compressés quand vous les restaurez dans le répertoire pg_xlog, sinon postgres vous indiquera que le backup n’est pas valide. En effet le WAL pointé par le backup_label porte bien l’id du WAL mais il sera illisible pour postgresql.
