Administration BDD

🗄️ Fiche Administrateur PostgreSQL / PostGIS (SIG) – Version complète structurée

Cette fiche présente les étapes logiques d’administration PostgreSQL/PostGIS : installation → configuration → gestion → stockage → import SIG → sauvegarde → automatisation → monitoring.

---

1. Vérification du serveur PostgreSQL

Vérifier l’état du service :

sudo systemctl status postgresql.service

Démarrer le service :

sudo systemctl start postgresql.service

Arrêter :

sudo systemctl stop postgresql.service

Redémarrer :

sudo systemctl restart postgresql.service

Activer au démarrage :

sudo systemctl enable postgresql.service

N.B. Si le fichier de configuration postgresql.conf a été modifié, faire sudo systemctl stop postgresql.service avant de faire sudo systemctl start postgresql.service**

---

2. Connexion à PostgreSQL

Connexion locale :

psql -U postgres -d postgres

Connexion distante :

psql -h host -p port -U user -d database

Exemple :

psql -h 192.168.10.1 -p 15432 -U editeur -d mardochee

Quitter psql :

\q

---

3. Gestion des bases de données

Lister les bases :

\l

Créer une base :

CREATE DATABASE ma_base;

Se connecter à ma base de donnée "mardochee" :

\c mardochee

ou

\connect mardochee

une fois connectée , lister toutes les tables dans le schema "qualite"

\dt qualite.*

lister les utilisateurs de la base de données :

\du

Supprimer une base :

DROP DATABASE ma_base;

---

4. Gestion des rôles et utilisateurs

Créer un rôle

CREATE ROLE nom_utilisateur LOGIN;

Un rôle permet de gérer les utilisateurs et leurs permissions dans la base de données Ses pouvoirs : - contrôler qui peut se connecter - définir ce que la personne peut faire - appliquer une gestion de sécurité et des privilèges

Exemple création d'un rôle "idgeo" :

-- Role: idgeo (ici idgeo c'est un groupe )
-- DROP ROLE IF EXISTS idgeo;
CREATE ROLE idgeo WITH
    LOGIN -- se connecter à PostgreSQL, Sans LOGIN, ne peut pas ouvrir de session
    NOSUPERUSER --pas es privilèges d'admin global, ne peut pas modifier toute la base ni accéder à toutes les données
    INHERIT --hérite automatiquement des privilèges des rôles auxquels il appartient
    CREATEDB --peut créer de nouvelles bases de données
    NOCREATEROLE -- Interdit au rôle de créer ou modifier d'autres rôles, donc ne peut donc pas gérer les utilisateurs
    NOREPLICATION --ne peut pas être utilisé pour la réplication
    NOBYPASSRLS --ne peut pas contourner les politiques de sécurité au niveau des lignes (Row Level Security)
;

Créer un utilisateur :

CREATE USER user1 WITH PASSWORD 'password';

Donner les droits :

GRANT ALL PRIVILEGES ON DATABASE ma_base TO user1;

Revoquer les droits :

REVOKE  PRIVILEGES ON DATABASE ma_base TO user2;

Modifier mot de passe :

ALTER USER user1 WITH PASSWORD 'new_password';

Supprimer utilisateur :

DROP USER user1;

Lister les rôles :

\du

---

5. Installation de PostGIS

Activer PostGIS :

CREATE EXTENSION postgis;

Vérifier la version :

SELECT PostGIS_version();

Lister les extensions :

\dx

---

6. Gestion des schémas

Créer un schéma :

CREATE SCHEMA admin;

Lister les schémas :

\dn

Supprimer :

DROP SCHEMA admin CASCADE;

---

7. Création de tables géographiques

CREATE TABLE communes (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    geom geometry(MULTIPOLYGON,2154)
);

---

8. Index spatial

CREATE INDEX communes_geom_idx
ON communes
USING GIST (geom);

---

9. Fonctions PostGIS essentielles

ST_Intersects()
ST_Contains()
ST_Distance()
ST_Buffer()
ST_Union()
ST_Area()
ST_Length()
ST_Transform()

Exemple :

SELECT *
FROM communes
WHERE ST_Intersects(geom, autre_geom);

---

10. Transformation de projection

SELECT ST_Transform(geom,2154)
FROM table;

---

11. Tablespaces PostgreSQL

Créer le dossier du tablespace :

sudo mkdir -p /srv/tablespace/

Donner les droits :

sudo chown postgres:postgres /srv/tablespace/

Créer le tablespace :

CREATE TABLESPACE mine
LOCATION '/srv/tablespace';

Lister les tablespaces :

\db

---

12. Vérification du stockage physique

Lister le contenu :

sudo ls -la /srv/tablespace

Explorer la structure interne :

sudo ls -la /srv/tablespace/PG_*

Voir les liens PostgreSQL :

ls -la /var/lib/postgresql/16/main/pg_tblspc

---

13. Import de données SIG

Importer un shapefile :

ogr2ogr -f "PostgreSQL" PG:"dbname=ma_base user=postgres" -nln admin.communes communes.shp

Importer avec reprojection :

ogr2ogr -f "PostgreSQL" PG:"dbname=ma_base user=postgres" -s_srs EPSG:4326 -t_srs EPSG:2154 communes.shp

Importer un département spécifique :

ogr2ogr -f "PostgreSQL" PG:"dbname=hg user=postgres" departement_31.shp -nln dept_31 -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=geom

---

14. Export de données

ogr2ogr communes.shp PG:"dbname=ma_base user=postgres" -nln admin.communes

---

15. Sauvegarde PostgreSQL

Sauvegarde simple :

pg_dump ma_base > sauvegarde.sql

Sauvegarde complète :

pg_dumpall > all_db.sql

Sauvegarde avec options :

pg_dump -U postgres -p 5432 -d hg -f /mnt/d/admin_bd/hg_backup.sql

---

16. Restauration d'une base

Créer une base :

psql -U postgres -p 5432 -c "CREATE DATABASE hg_restore;"

Restaurer :

psql -U postgres -p 5432 -d hg_restore < hg_backup.sql

---

17. Script bash de sauvegarde automatisée

Créer le script :

nano script_sauv_bdd.sh

Script :

#!/bin/bash

DATE=$(date +%Y-%m-%d_%H-%M)

pg_dump -U postgres -p 5432 -d hg -f /mnt/d/admin_bd/hg_backup_$DATE.sql

psql -U postgres -c "DROP DATABASE IF EXISTS hg_restore;"
psql -U postgres -c "CREATE DATABASE hg_restore;"

psql -U postgres -p 5432 -d hg_restore < /mnt/d/admin_bd/hg_backup_$DATE.sql

Rendre exécutable :

chmod +x script_sauv_bdd.sh

Tester :

./script_sauv_bdd.sh

---

18. Automatisation avec CRON

Modifier la crontab :

crontab -e

Ajouter :

20 3 * * * /home/ofbpnmba/script_sauv_bdd.sh >> /home/ofbpnmba/backup.log 2>&1

Vérifier :

crontab -l

---

19. Monitoring PostgreSQL

Tester si PostgreSQL répond :

pg_isready

Lister les clusters :

pg_lsclusters

Voir les processus :

ps -aux | grep postgres

Surveiller les ressources :

htop

Connexions actives :

SELECT * FROM pg_stat_activity;

---

20. Logs PostgreSQL

Consulter les logs :

sudo less /var/log/postgresql/postgresql-16-main.log

Suivre en temps réel :

tail -f /var/log/postgresql/postgresql-16-main.log

---

21. Export de l'historique Linux

Afficher l’historique avec date :

HISTTIMEFORMAT="%F %T " history

Exporter dans un fichier CSV :

HISTTIMEFORMAT="%F;%T " history | grep "$(date +%F)" > history_2026.csv

---

22. Architecture SIG typique

Linux
 ├─ PostgreSQL
 │   └─ PostGIS
 ├─ GDAL / OGR
 ├─ QGIS
 └─ WebSIG
     ├─ GeoServer
     ├─ Leaflet
     └─ OpenLayers

---

23. Bonnes pratiques DBA SIG

✔ utiliser des tablespaces pour les gros volumes
✔ créer des index spatiaux
✔ vérifier les projections EPSG
✔ sauvegarder régulièrement la base
✔ automatiser les sauvegardes avec cron
✔ surveiller les logs PostgreSQL

🔗 Connexions distantes avec Foreign Data Wrappers (FDW)

1. Principe

Les Foreign Data Wrappers (FDW) permettent à PostgreSQL d'accéder à des données externes comme si elles étaient des tables locales.

Cela permet de :

• interroger des bases PostgreSQL distantes
• accéder à des services web (WFS)
• lire des fichiers externes
• accéder à d'autres bases de données (Oracle, MySQL, etc.)

Les données externes apparaissent sous forme de foreign tables.

---

2. Avantages des FDW

✔ accès transparent aux données distantes
✔ possibilité de requêtes SQL directes
✔ intégration avec PostGIS
✔ évite la duplication des données

Exemple :

SELECT *
FROM table_distante
WHERE id = 10;

---

3. Types de sources supportées

Le FDW permet d'accéder à différents types de sources :

Source	Mode
PostgreSQL	lecture / écriture
Oracle	lecture / écriture
MySQL	lecture / écriture
CSV	lecture
ODBC / JDBC	dépend du driver
NoSQL (CouchDB, Redis)	dépend du driver
WFS / fichiers géographiques	lecture

---

4. Activation des extensions FDW

Activer le FDW PostgreSQL :

CREATE EXTENSION postgres_fdw;

Activer le FDW GDAL :

CREATE EXTENSION ogr_fdw;

Activer le FDW FILE GDAL :

CREATE EXTENSION file_fdw;

---

5. Connexion à une base PostgreSQL distante

Création du serveur distant

CREATE SERVER foreign_bd
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
    host 'localhost',
    port '5434',
    dbname 'bd_test_postgis'
);

Paramètre	Description
host	adresse du serveur distant
port	port PostgreSQL
dbname	base distante

---

6. Association des utilisateurs

Associer un utilisateur local à un utilisateur distant.

CREATE USER MAPPING FOR postgres
SERVER foreign_bd
OPTIONS (
    user 'postgres',
    password 'postgres'
);

élément	description
postgres	utilisateur local
user	utilisateur distant
password	mot de passe distant

---

7. Création d'une table distante

Une foreign table représente une table distante.

CREATE FOREIGN TABLE foreign_parkings (
    osm_id integer NOT NULL,
    date_heure timestamp without time zone,
    nom character varying,
    type_obj character varying,
    xcoord double precision,
    ycoord double precision,
    geom geometry(Point,2154)
)
SERVER foreign_bd
OPTIONS (
    schema_name 'public',
    table_name 'parkings'
);

Interroger la table distante :

SELECT *
FROM foreign_parkings
LIMIT 10;

---

8. Accès à un service WFS avec ogr_fdw

Créer un serveur WFS :

CREATE SERVER fdw_ogr_inpn_metropole
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
    datasource 'WFS:http://ws.carmencarto.fr/WFS/119/fxx_inpn?',
    format 'WFS'
);

---

9. Création du schéma d'import

CREATE SCHEMA IF NOT EXISTS inpn_metropole;

---

10. Import des tables du service WFS

IMPORT FOREIGN SCHEMA ogr_all
FROM SERVER fdw_ogr_inpn_metropole
INTO inpn_metropole
OPTIONS (
    launder_table_names 'true',
    launder_column_names 'true'
);

option	rôle
launder_table_names	nettoie les noms de tables
launder_column_names	nettoie les noms de colonnes

---

11. Vérification des tables importées

SELECT foreign_table_schema, foreign_table_name
FROM information_schema.foreign_tables
WHERE foreign_table_schema = 'inpn_metropole'
ORDER BY foreign_table_schema, foreign_table_name;

---

12. Exemple d'interrogation

SELECT *
FROM inpn_metropole.zico
LIMIT 3;

---

13. Architecture FDW

PostgreSQL local
        │
        │
        ├── postgres_fdw
        │       │
        │       └── PostgreSQL distant
        │
        └── ogr_fdw
                │
                └── WFS / fichiers géographiques

---

14. Bonnes pratiques

✔ utiliser FDW pour éviter la duplication des données
✔ limiter les requêtes lourdes sur les serveurs distants
✔ créer des vues locales pour simplifier l'accès
✔ surveiller les performances réseau

---

15. Exemple d'utilisation SIG

Serveur SIG
│
├── Base principale
│
├── FDW vers base cadastre
│
├── FDW vers base biodiversité
│
└── FDW vers service WFS INPN

Les données peuvent être utilisées directement dans QGIS.