Catégories
Expert SGBD

PostgreSQL : sauvegarde avec pg_dump et pg_dumpall

On avait vu comment sauvegarder une base PostgreSQL avec l’outil graphique pgAdmin, voici la version en ligne de commande. Une alternative efficace et plus adaptée aux traitements réguliers, par exemple utilisés par un développeur d’application, un administrateur système et bien sûr un DBA (administrateur de bases de données). Les DBA de production et DBA de développement PostgreSQL doivent déjà connaitre tous ces paramètres.

Ce tutoriel explique comment sauvegarder (exporter) et restaurer (importer) une base de données sur un serveur PostgreSQL avec les commandes pg_dump et pg_dumpall (dump all). Une méthode simple et conseillée par le SGBD pour créer des dumps en quelques actions. On peut aussi scripter cette opération pour l’automatiser afin de créer des sauvegardes automatiques des bases PG SQL.

La principale différence entre pg_dump et pg_dumpall est la suivante : pg_dump sauvegarde une base, pg_dumpall fait une sauvegarde complète de l’instance avec la BDD, les objets, etc.

 

Options de sauvegarde pg_dump et pg_dumpall pour PostgreSQL

pg_dump -F p | t | c | d : choisir le format entre :

  • p : plain, SQL
  • t : tar
  • c : custom (format spécifique PGSQL)
  • d : directory

pg_dump -f : spécifie le fichier où sera créée la sauvegarde (sans -f, pgdump utilise le dossier standard de sortie)

pg_dump -s (pour –schema-only) : exporte uniquement la structure de la base

pg_dump -a (pour –data-only) : sauvegarde uniquement les données de la BDD

pg_dump -n <schema> : sélectionne uniquement le schéma

pg_dump -N <schema> : prend tous les schémas sauf celui indiqué

pg_dump -C : intègre la commande pour créer la base lors de la restauration

pg_dump -t <table> : sauvegarde juste cette table

pg_dump -T <table> : sauvegarde toutes les tables sauf celle indiquée

pg_dump -j <nombre> : parallélisation du job, indiquer un nombre de threads

pg_dump -O : ignorer le propriétaire

pg_dump -x : ignorer les droits

pg_dump –no-role-passwords : ne sauvegarde pas les mots de passe

pg_dump –no-tablespaces : ignorer les tablespaces

pg_dump –inserts : remplace COPY par INSERT

pg_dump -v : voir la progression de l’action

 

Exemples de commandes pg_dump et pg_dumpall

1. Ouvrir un Invite de commandes (cmd).

2. Aller dans le dossier \bin de PGSQL : cd D:\pgsql\bin ou cd /opt/pgsql/bin

Ou, en Linux, indiquer la variable dans le PATH : export PATH=/usr/local/pgsql/bin:$PATH

3. Exemples de commandes Windows. Pour Linux, retirer le « .exe » :

pg_dumpall.exe -U username > D:\Backup\dumpall

–> Va sauvegarder toutes les bases et tous les objets (utilisateurs, tablespaces…) dans le fichier dumpall.

pg_dump.exe -U username -C -F c -f D:\Backup\NomBase.dump NomBase

–> Va sauvegarder la base NomBase dans le dossier D:\Backup dans le format propriétaire PGSQL (bien compressé et rapide à exécuter).

Catégories
Expert SGBD

PostgreSQL : voir la taille des bases

De nombreuses bases de données sur un serveur Postgre et impossible de savoir laquelle prend trop d’espace ? Quel volume occupe une base bien précise ? Est-ce que mon traitement a fait grossir la base ? Pour toutes ces questions et bien d’autres, ce tutoriel indique comment lister les bases de données et leur taille sur un serveur PostgreSQL. Une information intéressante et essentielle pour les administrateurs, système et DBA. On utilise une commande psql, l’interface en mode texte pour PostgreSQL, et un simple raccourci de commande agrémenté d’une variable pour afficher des informations supplémentaires à la simple liste de BDD.

Au contraire de Microsoft SQL Server qui propose un fichier MDF par base de données (ainsi qu’un LDF pour les logs), PostgreSQL met en place une multitude de fichiers dans un répertoire dédié que l’on retrouve dans \data\base.

Ce guide fonctionne avec toutes les versions récentes de PGSQL : 9, 10, 11, 12.

 

Afficher la taille des bases d’un serveur PostgreSQL

1. Ouvrir un Invite de commandes (cmd).

2. Aller dans le dossier « bin » de PGSQL, par exemple :

cd D:\pgsql11\bin

3. Se connecter au serveur Postgre en PSQL :

psql -U username

Si le serveur PG n’est pas sur le port par défaut 5432 ou qu’il y a plusieurs versions du SGBD sur la machine, préciser le port dans la commande : psql -U username -p 5433

4. Indiquer le mot de passe de l’utilisateur.

5. Taper la commande « \l+ » (anti slash, L minuscule – comme list – et symbole plus). Patienter quelques instants pour obtenir un résultat de ce type :

La capture ci-dessus montre la différence entre les commandes raccourcies « \l » et « \l+ » et les colonnes qui se rajoutent à l’écran : Taille, Tablespace, Description.

Il est ainsi très facile d’identifier les BDD les plus volumineuses sur le serveur PostgreSQL. On voit aussi dans quel tablespace sont installées les bases de données, utile que si on les utilise pour cloisonner les bases.

Catégories
Expert SGBD

PostgreSQL ne démarre plus : PANIC could not locate a valid checkpoint record

Un message d’erreur qui peut arriver après avoir fait un ménage trop important dans les logs, un espace disque insuffisant ou un simple plantage du SGBD. Le service postgresql ne démarre plus correctement, que ce soit sur Linux ou sur Windows. En fouillant dans les logs du serveur PostgreSQL, on trouve ce message d’erreur :

PANIC: could not locate a valid checkpoint record

ou en français :

PANIC: n’a pas pu localiser un enregistrement d’un point de vérification valide

Ce tutoriel est basé sur un exemple concret : le service PostgreSQL Server ne démarre pas sur Windows et aucun message graphique n’explique le problème. Il explique comment résoudre l’erreur « PANIC: could not locate a valid checkpoint record » de PostgreSQL toutes versions, que l’on peut lire dans le fichier log courant. La réponse est un peu dans la question mais un accompagnement ne fait pas de mal pour corriger cette erreur bloquante. Ce guide montre la solution pour les versions Postgre inférieures à 10 (7, 8, 9) et supérieures/égales à 10 (10, 11…).

 

Solution à l’erreur Postgre : PANIC: could not locate a valid checkpoint record

Serveur PostreSQL 10 et 11

1. Ouvrir un Invite de commandes (cmd) en tant qu’Administrateur.

2. Naviguer dans le dossier d’installation « bin » de PGSQL :

cd C:\Program Files\PostgreSQL\10\bin

3. Exécuter la commande suivante pour vérifier, en indiquant le chemin des bases de données :

pg_resetwal DATADIR

soit par exemple :

pg_resetwal "D:\BasesPostgreSQL10"

4. Et celle-ci pour forter le reset :

pg_resetwal -f DATADIR

soit :

pg_resetwal -f "D:\BasesPostgreSQL10"

5. Le service PostgreSQL Server peut à nouveau démarrer.

 

Serveur PostreSQL 9

1. Ouvrir un Invite de commandes (cmd) en tant qu’Administrateur.

2. Naviguer dans le dossier d’installation « bin » de PGSQL :

cd C:\Program Files\PostgreSQL\9.6\bin

3. Exécuter la commande suivante pour vérifier, en indiquant le chemin des bases de données :

pg_resetxlog DATADIR

soit par exemple :

pg_resetxlog "D:\BasesPostgreSQL9"

4. Et celle-ci pour forter le reset :

pg_resetxlog -f DATADIR

soit :

pg_resetxlog -f "D:\BasesPostgreSQL9"

5. Le service PostgreSQL Server peut à nouveau démarrer correctement.

Catégories
Expert SGBD

Importer et exporter une base de données PostgreSQL avec pgAdmin

PostreSQL est un outil libre pour gérer ses bases de données. Le plus avancé des SGBD (world’s most advanced open source database, d’après eux) est compatible avec les systèmes d’exploitation Solaris, Linux, Unix, AIX, *BSD, macOS et Windows. Si la partie serveur est ainsi multiplateforme, la gestion peut se faire à distance en ligne de commande (psql) ou avec un client pgAdmin, outil d’administration graphique spécifique à Postgre. Il existe également les interfaces phpPgAdmin et Adminer pour accéder à un serveur PGSQL.

Ce tutoriel explique comment sauvegarder (exporter) et restaurer (importer) une base de données sur un serveur PostgreSQL avec l’outil graphique pgAdmin. Une méthode par interface graphique qui nécessite un compte avec les droits adéquats pour ces opérations de maintenance et d’administration. L’utilitaire pgAdmin4 permet d’accéder à un serveur PostgreSQL 9.x, 10.x, 11.x et sûrement aussi les versions ultérieures.

 

Import de base Postgre avec pgAdmin

1. Se connecter au serveur PGSQL avec pgAdmin (ici en version 4).

2. Créer une base de données vierge (clic droit, Create, Database) sauf si elle existe déjà.

3. Faire un clic droit sur la base de données, Restore.

4. A l’onglet General, dans le champ Filename, charger le fichier depuis le disque du serveur local ou un partage du réseau.

Si le fichier n’apparait pas dans la liste, modifier le Format de fichiers recherché en bas à droite.

5. Cliquer sur Restore pour importer la base. Un message « Successfully completed » indique que l’opération s’est bien déroulée.

 

Export de base PostgreSQL avec pgAdmin

1. Se connecter au serveur PG SQL avec pgAdmin4.

2. Faire un clic droit sur la base de données, Backup.

3. Indiquer où sauvegarder la base dans le champ Filename et préciser le nom du fichier avec son extension.

Par défaut, le format de sauvegarde est un .sql : modifier en format .backup si nécessaire.

4. Cliquer sur le bouton Backup pour lancer l’export.

Catégories
Expert SGBD

Gérer PostgreSQL avec Oracle SQL Developer

L’outil SQL Developer a été développé par Oracle pour exécuter des requêtes sur son SGBD maison. Les bases Oracle 11g ou 12c sont donc nativement supportées par le logiciel SQL Developper mais il est également possible de se connecter à des bases montées sur un serveur PostgreSQL pour les gérer de la même manière.

Ce tutoriel explique comment utiliser l’application Oracle SQL Developer pour se connecter à une base PostgreSQL, hébergée sur un serveur Postgre. Tout passe par l’ajout d’un pilote tiers JDBC (Java DataBase Connectivity, même principe qu’un driver ODBC) dans SQL Developer pour supporter les bases PG.

La transition sur PostgreSQL sera ainsi facilitée pour les DBA qui travaillent sur Oracle, dans le cadre d’une migration ou d’une nouvelle application basée sur le système de gestion de base de données libre. PostgreSQL JDBC Driver (PgJDBC) autorise des programmes en Java à se connecter à des bases PostgreSQL, des outils tels que SQL Developer par exemple.

Le téléchargement du logiciel SQL Developer est gratuit.

 

Ajouter le support PostgreSQL dans SQL Developer

1. Télécharger le pilote sur la page JDBC Driver pour PostgreSQL.

2. Déplacer le fichier téléchargé dans un dossier dédié aux pilotes JDBC, par exemple dans le répertoire d’installation de sqldeveloper, sous-dossier « jdbc » ou dans un nouveau dossier créé pour l’occasion.

3. Ouvrir le logiciel Oracle SQL Developer.

4. Aller dans le menu Outils, Préférences. Dérouler Base de données, Pilotes JDBC tiers.

5. Cliquer sur « Ajouter une entrée » et charger le chemin du dossier des pilotes JDBC où se trouve le fichier téléchargé.

6. Valider par OK pour quitter l’écran de configuration des paramètres.

7. Créer une Nouvelle connexion. Un onglet « PostgreSQL » est apparu à côté de Oracle. Cliquer dessus et configurer la connexion au serveur PGSQL.

L’outil Oracle SQL Developer est maintenant capable de se connecter à des serveurs PostgreSQL pour effectuer des requêtes grâce à un plugin JDBC ajouté dans le programme Oracle.

Catégories
Expert SGBD

Installer un serveur PostgreSQL 9 sur Windows

PostgreSQL est un SGBDRO (système de gestion de base de données relationnel-objet, ou ORDBMS en anglais), au même titre que le système Oracle. Avec une première version sortie en 1996, la licence gratuite et open source PostgreSQL est de plus en plus utilisée en entreprise, en tant qu’alternative sérieuse à Microsoft SQL Server ou Oracle (dont le coût des licences a explosé avec la version 12).

La version 9.0 de PostgreSQL est sorti en septembre 2010 et les release de mise à jour ont apporté de nouvelles fonctionnalités, jusuq’à la 9.6 qui a été publiée en septembre 2016 avec le dernier update mineur en novembre 2017 (9.6.6) et sera maintenue jusqu’en septembre 2021.

Dans la logique open source du SGBD, Postgre est compatible avec la plupart des systèmes Unix (BSD, Solaris, AIX, HP-UX et bien sûr Linux). Parmi les OS les plus courants, citons Ubuntu, Debian, SuSE, Red Hat, macOS, Solaris, FreeBSD et OpenBSD. Mais en tant que système de gestion de BDD populaire, PGSQL est aussi compatible avec les systèmes d’exploitation Microsoft Windows. L’édition 9.6.x est compatible avec Windows 7, 8, 10, Server 2008 / R2, Server 2012 / R2 (mais pas Windows Server 2016 pour lequel il faudra passer en PostgreSQL 10).

Ce tutoriel explique comment installer un serveur PostgreSQL 9 sur un système Windows. Le paquet fourni par EnterpriseDB est utilisé puisqu’il intègre le serveur Postgre, un outil graphique de gestion pgAdmin et un gestionnaire de paquets StackBuilder. L’alternative pour Windows est le setup de BigSQL qui embarque des outils de développement et d’intégration avec d’autres SGBD (Cassandra, Oracle, SQL Server, Hadoop).

 

Télécharger PostgreSQL 9 pour Windows

1. Aller sur la page Download for Windows : https://www.postgresql.org/download/windows/

2. A la partie « Interactive installer by EnterpriseDB » : cliquer sur « Download the installer » :

3. Sur la page de téléchargement, choisir PostgreSQL 9.6.6 et le système d’exploitation cible :

  • Windows x86-64 pour Windows 64 bits
  • Windows x86-32 pour Windows 32 bit

Puis cliquer sur « Download now » :

 

Installer PostgreSQL 9.6 sur Windows

1. Copier le setup d’installation sur le serveur PG.

2. Ouvrir le fichier postgresql-9.6.6-3-windows-x64.exe par exemple.

3. L’assistant peut télécharger le Runtime Microsoft Visual C++ 2013 si cela est nécessaire (demande une connexion internet).

4. Définir un dossier d’installation pour PostgreSQL9, par exemple D:\PostgreSQL9.

5. Renseigner le répertoire où seront enregistrées les données (bases), par défaut dans le dossier d’installation \data mais cela peut être une partition dédiée aux bases de données pgsql.

6. Indiquer un mot de passe superutilisateur (administrateur du serveur PostgreSQL).

8. Régler le port de connexion TCP à utiliser, par défaut 5432.

9. Spécifier la « locale utilisée » (langue et pays). On peut laisser « Locale par défaut » sauf si par exemple le système d’exploitation est en anglais mais que les bases de données seront en français (encodage).

10. Un récapitulatif résume ce qui sera installé et configuré. Cliquer sur Suivant pour démarrer l’installation du serveur PostgreSQL.

11. Un message indique que tout s’est bien passé et demande à ouvrir Stack Builder (optionnel). Cet outil permet notamment d’installer d’autres versions de PostgreSQL en parallèle de l’existante, pour par exemple avoir un PGSQL 10 à côté du 9, sur un autre port TCP.

12. Ouvrir pgAdmin4 ou un autre outil pour gérer le serveur PostgreSQL9.