Archives pour la catégorie ‘PostgreSQL’

PostgreSQL : afficher les droits spécifiques associés à un table

dbname=# SELECT grantee,privilege_type FROM information_schema.role_table_grants WHERE table_name='mytable';

 grantee  | privilege_type 
----------+----------------
 user1    | INSERT
 user1    | SELECT
 user1    | UPDATE
 user1    | DELETE
 user1    | TRUNCATE
 user1    | REFERENCES
 user1    | TRIGGER
 user2    | INSERT
 user2    | SELECT
 user2    | UPDATE
 user2    | DELETE
(11 rows)

PostgreSQL : dump au format custom

# pg_dump -Fc -Z1 -U user db|ssh backup@11.22.33.44 "cat > /home/backup/postgresql/dump.pgdmp"
# pg_restore -j 8 -d db dump.pgdmp

PostgreSQL : script de backup par snapshot LVM

#!/bin/bash
#
# Backup PostgreSQL cluster from LVM partition
#

VG_NAME="vg"
LV_NAME="sql"
LV_SIZE="4G"

MOUNT_DIR="/var/lib/postgresql/tmp"
PG_VERSION="9.3"
PG_CLUSTER="main"

BACKUP_DIR="/home/backup"

#
# script
#

PATH=/usr/sbin:/usr/bin:/sbin:/bin

DATE=`date +%Y%m%d%H%M%S`
SNAPSHOT="${LV_NAME}_${DATE}"

mkdir -p $BACKUP_DIR
mkdir -p $MOUNT_DIR

echo "Backuping PostgreSQL cluster $PG_VERSION/$PG_CLUSTER from LVM partition $VG_NAME/$LV_NAME"
echo ""

echo "- starting PostgreSQL hot backup"
su -c "psql -c \"SELECT pg_start_backup('$SNAPSHOT');\"" -l postgres

echo "- creating LVM snapshot"
lvcreate -s -L$LV_SIZE -n$SNAPSHOT $VG_NAME/$LV_NAME

echo "- stopping PostgreSQL hot backup"
su -c "psql -c \"SELECT pg_stop_backup();\"" -l postgres

echo "- mounting LVM snapshot"
mkdir -p $MOUNT_DIR
mount /dev/mapper/$VG_NAME-$SNAPSHOT $MOUNT_DIR

echo "- archiving files"
tar czf $BACKUP_DIR/$SNAPSHOT.tar.gz -C $MOUNT_DIR/$PG_VERSION/$PG_CLUSTER .

echo "- cleaning"
umount $MOUNT_DIR
lvremove -f $VG_NAME/$SNAPSHOT

echo ""
echo "Backup $SNAPSHOT done."

PostgreSQL : limiter le temps d’exécution des requêtes

Méthode soft, limiter au niveau de la session client :

SET statement_timeout=10000;

Méthode stricte, limiter au niveau du rôle d’exécution :

ALTER ROLE user1 SET statement_timeout = '10000';

Zabbix : partitionnement des tables sous PostgreSQL

Petit résumé sur la configuration du partitionnement des tables Zabbix (version 2.2) avec une base de données PostgreSQL 9.3. Le partionnement des tables est automatique ; il peut être désactivé à tout moment. La suppression des tables est quant à elle à automatiser en cron.

Trigger de gestion du partitionnement journalier

-- Function: trg_daily_partition()

-- DROP FUNCTION trg_daily_partition();

CREATE OR REPLACE FUNCTION trg_daily_partition()
RETURNS trigger AS
$BODY$
DECLARE
tablename text;
partname text;
startdate text;
enddate text;
query text;

BEGIN
tablename := TG_ARGV[0];
partname := tablename || '_' || TO_CHAR(TO_TIMESTAMP(NEW.clock), 'YYYYMMDD');

EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT ($1).*' USING NEW;

RETURN NULL;

EXCEPTION
WHEN undefined_table THEN

startdate := EXTRACT(EPOCH FROM date_trunc('day', TO_TIMESTAMP(NEW.clock)));
enddate := EXTRACT(EPOCH FROM date_trunc('day', TO_TIMESTAMP(NEW.clock) + ('1 day')::interval));

EXECUTE 'CREATE TABLE IF NOT EXISTS ' || 'partitions.' || quote_ident(partname) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS ( ' || tablename || ' )';
EXECUTE 'CREATE INDEX ' || quote_ident(partname) || '_1 on ' || 'partitions.' || quote_ident(partname) || '(itemid,clock)';

EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT($1).*' USING NEW;

RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

ALTER FUNCTION trg_daily_partition()
OWNER TO zabbix;

Trigger de gestion du partitionnement mensuel

-- Function: trg_monthly_partition()

-- DROP FUNCTION trg_monthly_partition();

CREATE OR REPLACE FUNCTION trg_monthly_partition()
RETURNS trigger AS
$BODY$
DECLARE
tablename text;
partname text;
startdate text;
enddate text;
query text;

BEGIN
tablename := TG_ARGV[0];
partname := tablename || '_' || TO_CHAR(TO_TIMESTAMP(NEW.clock), 'YYYYMM');

EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT ($1).*' USING NEW;

RETURN NULL;

EXCEPTION
WHEN undefined_table THEN

startdate := EXTRACT(EPOCH FROM date_trunc('month', TO_TIMESTAMP(NEW.clock)));
enddate := EXTRACT(EPOCH FROM date_trunc('month', TO_TIMESTAMP(NEW.clock) + ('1 month')::interval));

EXECUTE 'CREATE TABLE IF NOT EXISTS ' || 'partitions.' || quote_ident(partname) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS ( ' || tablename || ' )';
EXECUTE 'CREATE INDEX ' || quote_ident(partname) || '_1 on ' || 'partitions.' || quote_ident(partname) || '(itemid,clock)';

EXECUTE 'INSERT INTO ' || 'partitions.' || quote_ident(partname) || ' SELECT($1).*' USING NEW;

RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

ALTER FUNCTION trg_monthly_partition()
OWNER TO zabbix;

Création du schéma

CREATE SCHEMA partitions AUTHORIZATION zabbix

Ajout des triggers sur les tables parentes

CREATE TRIGGER partition_trg BEFORE INSERT ON history FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_sync FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_sync');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_uint FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_uint');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_str_sync FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_str_sync');
CREATE TRIGGER partition_trg BEFORE INSERT ON history_log FOR EACH ROW EXECUTE PROCEDURE trg_daily_partition('history_log');
CREATE TRIGGER partition_trg BEFORE INSERT ON trends FOR EACH ROW EXECUTE PROCEDURE trg_monthly_partition('trends');
CREATE TRIGGER partition_trg BEFORE INSERT ON trends_uint FOR EACH ROW EXECUTE PROCEDURE trg_monthly_partition('trends_uint');

Fonctions de suppression des partitions

-- Function: delete_daily_partitions(integer)

-- DROP FUNCTION delete_daily_partitions(integer);

CREATE OR REPLACE FUNCTION delete_daily_partitions(max integer)
RETURNS text AS
$BODY$
DECLARE
result RECORD;
tmp text;
tmstp TIMESTAMP;
maxdate DATE;
BEGIN
maxdate := date_trunc('day', NOW() - (max || ' day')::interval);

FOR result IN SELECT * FROM pg_tables WHERE schemaname = 'partitions' LOOP
tmp := SUBSTRING(result.tablename FROM '[0-9_]*$');
IF LENGTH(tmp) <> 9 THEN
CONTINUE;
END IF;

tmstp := TO_TIMESTAMP(tmp, '_YYYYMMDD');
IF tmstp <= maxdate THEN
RAISE NOTICE 'Deleting daily table %', result.tablename;
EXECUTE 'DROP TABLE ' || result.schemaname || '.' || quote_ident(result.tablename);
END IF;
END LOOP;

RETURN 1;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

ALTER FUNCTION delete_daily_partitions(integer)
OWNER TO zabbix;

 

-- Function: delete_monthly_partitions(integer)

-- DROP FUNCTION delete_monthly_partitions(integer);

CREATE OR REPLACE FUNCTION delete_monthly_partitions(max integer)
RETURNS text AS
$BODY$
DECLARE
prefix text;
result RECORD;
tmp text;
tmstp TIMESTAMP;
maxdate DATE;
BEGIN
maxdate := date_trunc('day', NOW() - (max || ' month')::interval);

FOR result IN SELECT * FROM pg_tables WHERE schemaname = 'partitions' LOOP
tmp := SUBSTRING(result.tablename FROM '[0-9_]*$');
IF LENGTH(tmp) <> 7 THEN
CONTINUE;
END IF;

tmstp := TO_TIMESTAMP(tmp, '_YYYYMM');
IF tmstp <= maxdate THEN
RAISE NOTICE 'Deleting monthly table %', result.tablename;
EXECUTE 'DROP TABLE ' || result.schemaname || '.' || quote_ident(result.tablename);
END IF;
END LOOP;

RETURN 'OK';
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

ALTER FUNCTION delete_monthly_partitions(integer)
OWNER TO zabbix;

Exemple d’utilisation :

SELECT delete_daily_partitions(31);
SELECT delete_monthly_partitions(12);

PostgreSQL : migration d’un cluster 9.1 vers 9.3

Installation des dépôts

root@zabbix:~# wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add -
root@zabbix:~# add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main'

Mise à jour du serveur

root@zabbix:~# apt-get update && apt-get install postgresql-9.3

Migration du cluster existant

root@zabbix:/var/lib/postgresql# pg_upgradecluster 9.1 main
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster 9.3/main ...
config /etc/postgresql/9.3/main
data /var/lib/postgresql/9.3/main
locale en_US.UTF-8
port 5433
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database zabbix...
Analyzing database zabbix...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Copying old server.crt...
Copying old server.key...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Starting target cluster on the original port...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

pg_dropcluster 9.1 main

Suppression de l’ancien cluster

root@zabbix:~# pg_dropcluster 9.1 main

Désinstallation des anciens packages

root@zabbix:~# apt-get --purge remove postgresql-9.1 postgresql-client-9.1

PostgreSQL : créer un utilisateur avec droits en lecture seule

postgres=# \c db
You are now connected to database "db" as user "postgres".
postgres=# GRANT CONNECT ON DATABASE db TO db_reader;
GRANT
postgres=# GRANT USAGE ON SCHEMA public to db_reader;
GRANT
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_reader;

PostgreSQL : afficher les requêtes SQL en cours d’exécution

L’objectif est de pouvoir afficher à tout instant les requêtes en cours d’exécution sur le serveur afin de diagnostiquer en temps réel son activité.

Em premier lieu, le monitoring des commandes SQL doit être activé dans le fichier de configuration :

root@pgsrv:~# nano /etc/postgresql/9.1/main/postgresql.conf
track_activities = on

Dès lors, il reste à exécuter cette requête pour afficher l’état actuel du serveur :

postgres@pgsrv:~/$ psql
postgres=# SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity;

FreeBSD : jail PostgreSQL

Pour pouvoir exécuter un serveur PostgreSQL sous une jail FreeBSD, il est nécessaire d’autoriser le support de la mémoire partagée (SHM). Il convient d’activer le flag dans /etc/rc.conf :

jail_sysvipc_allow="YES"

Si cela n’est pas fait, l’initialisation de l’espace de stockage échoue de cette façon :

sql# /usr/local/etc/rc.d/postgresql oneinitdb
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.</p>
The database cluster will be initialized with locale C.
The default text search configuration will be set to "english".
creating directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers ... 400kB
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL:  could not create shared memory segment: Function not implemented
DETAIL:  Failed system call was shmget(key=1, size=1622016, 03600).
child process exited with exit code 1
initdb: removing data directory "/usr/local/pgsql/data"

Mac OS X : masquer un utilisateur de la login box

Pour masquer un compte utilisateur de la login box de Mac OS (et également du menu de permutation / Fast Switch User), la commande à saisir est la suivante en spécifiant le compte à la fin :

$ sudo defaults write /Library/Preferences/com.apple.loginwindow HiddenUsersList -array-add postgres

Le compte utilisateur masqué reste évidemment toujours actif. Pour masquer l’élément « Autre… » de la login box (en mode liste), c’est cette commande :

$ sudo defaults write /Library/Preferences/com.apple.loginwindow SHOWOTHERUSERS_MANAGED -bool false
Haut de page