Forums PostgreSQL.fr

LECARROU

Membre

Administration base Postgresql via SSH

Bonjour,

je suis newbie en dev, et en postgresql particulièrement, et je maitrise très peu les aspects administration base de données postgresql.
j'ai développé une application Django que je vais déployer sur un serveur distant via ssh.

une fois déployée et ma base de données créée, je souhaite écrire un script qui me permettra de créer les utilisateurs, les tables thésaurus, etc...

j'ai écris les instructions SQL mais je sais pas trop comment je dois procéder pour pouvoir lancer ce script via ssh...
il faudra également que j'importe des données de fichiers csv

j'ai commencé à lire la doc concernant postgresql (mais elle est bien dense) et j'ai commencé à me familiariser avec des commandes psql en SQL Shell

en résumé, je vais me connecter en ssh à mon serveur sur lequel sera déployée mon appli et sur lequel sera donc installé postgresql

comment dois-je procéder pour utiliser SQL Shell via ssh ?
comment je peut lancer un script qui initialiserait ma base de données, avec des insert d'une part et des import csv d'autre part ?

d'avance merci pour votre aide,

gleu

Administrateur

Re : Administration base Postgresql via SSH

Vous devez passer par l'outil psql. Il vous permettra de vous connecter à PostgreSQL et d'exécuter des requêtes manuellement ou via un script SQL.

---

Guillaume.

rjuju

Administrateur

Re : Administration base Postgresql via SSH

Au passage psql dispose aussi d'une fonctionnalité pour importer des fichiers CSV, cf https://www.postgresql.org/docs/current/app-psql.html et la partie \copy.

---

Julien.
https://rjuju.github.io/

pifor

Membre

Re : Administration base Postgresql via SSH

Vous pouvez aussi utiliser psql sur une machine locale - sans accès ssh à distance - pour une connexion à distance vers l'instance base de données Postgresql (ce qui suppose que le serveur Postgresql est correctement configuré ET sécurisé dans pg_hba.conf et que le port de l'instance est ouvert dans le pare-feu réseau). Ce type de fonctionnement est possible avec des hébergeurs gratuits mais limités comme https://www.elephantsql.com (mais pas chez Free qui ne donne que l'accès à la base PostgreSQL par une interface web).

Dernière modification par pifor (12/03/2020 20:20:05)

---

Pierre

LECARROU

Membre

Re : Administration base Postgresql via SSH

merci pour vos réponses.
je vais voir avec l'admin sys pour correctement configurer mon accès ssh sur notre serveur
je suis en train de suivre un tuto sur l'utilisation de psql (SQL Shell sous Windows)

LECARROU

Membre

Re : Administration base Postgresql via SSH

bon j'avance bien dans mes tests en local
par contre, lorsque je créé mes utilisateurs avec script sql, il faut que je créé des mot de passe crypté PBKDF2/SHA256

comment je peux intégrer cela dans mon script sql sans devoir créé manuellement les mots de passe via la fonction make_password de Django ?

pifor

Membre

Re : Administration base Postgresql via SSH

A priori il faut
1. modifier le paramètre de l'instance password_encryption à la valeur

scram-sha-256

2. modifier pg_hba.conf en conséquence.
3. Ensuite il doit suffire de créer un compte avec CREATE ROLE ou CREATE USER.

Voir https://docs.postgresql.fr/11/auth-password.html.

---

Pierre

LECARROU

Membre

Re : Administration base Postgresql via SSH

Bonjour,

je galère sur un point d'encodage

donc pour précision, je travaille sous Windows avec pslq

j'ai donc un problème d'accent que je pensais avoir solutionné avec SET NAMES 'utf8'; au début de mon script mais aujourd'hui j'ai de nouveau des erreurs.

je lance 2 scripts :
- le premier me créé la base en précisant l'encodage utf8 (ensuite je créé ma base dans mon projet Django: py manage.py migrate)
- le deuxième doit initialiser ma base avec des données de paramétrage

je n'ai pas de message d'erreur mais les accents ne sont pas correctement stockés en base

du coup, j'ai voulu tester directement un INSERT INTO depuis psql

INSERT INTO adm_pay (pay_nom_eng,pay_nom_fra,pay_abr) VALUES ('Ivory Coast','Côte d Ivoire','CIV');

et là j'ai un message d'erreur :

ERREUR:  séquence d'octets invalide pour l'encodage « UTF8 » : 0x93

la commande \encoding dans psql m'indique UTF8 pourtant les message d'erreur de la console présentent aussi des problème d'accents...

script 1 (fichier .txt créé dans blocnote):

CREATE DATABASE intensetbm;
CREATE USER intensetbm_admin WITH PASSWORD 'admin';
ALTER ROLE intensetbm_admin SET client_encoding TO 'utf8';
ALTER ROLE intensetbm_admin SET default_transaction_isolation TO 'read committed';
ALTER ROLE intensetbm_admin SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE intensetbm TO intensetbm_admin;

script 2 (fichier .txt créé dans blocnote):

SET NAMES 'utf8';
INSERT INTO adm_pay (pay_nom_eng,pay_nom_fra,pay_abr) VALUES ('Ivory Coast','Côte d Ivoire','CIV');

comment dois-je/puis-je procéder ?

pifor

Membre

Re : Administration base Postgresql via SSH

Avec le client psql sur Windows, essayez de configurer l'encodage du client par rapport à la "code page" WIndows retournée par:

chcp

Si vous avez 1252, essayer de configurer client_encoding avec:

set client_encoding to 'win1252';

CLIENT_ENCODING définit le jeu du caractères du client connecté à PG.Je ne crois pas qu'il influe sur la création de la base.
Merci de nous donner le résultat de:

\l intensetbm;

NB: SET NAMES est bien pris en compte par PG mais il est plus habituel d'utiliser SET CLIENT_ENCODING:

postgres=# set names 'SQL_ASCII';
SET
postgres=# show names;
ERROR:  unrecognized configuration parameter "names"
postgres=# show client_encoding;
 client_encoding 
-----------------
 SQL_ASCII
(1 row)

postgres=# 

Dernière modification par pifor (17/03/2020 16:12:05)

---

Pierre

LECARROU

Membre

Re : Administration base Postgresql via SSH

chcp ne me retourne rien
par contre j'ai effectivement un message d'avertissement au lancement de la console psql

Attention : l'encodage console (850) diffère de l'encodage Windows (1252).
            Les caractères 8 bits peuvent ne pas fonctionner correctement.
            Voir la section « Notes aux utilisateurs de Windows » de la page
            référence de psql pour les détails.
Saisissez « help » pour l'aide.

postgres=# \l intensetbm;
                                           Liste des bases de donnÚes
    Nom     | PropriÚtaire | Encodage |  Collationnement   |    Type caract.    |        Droits d'accÞs
------------+--------------+----------+--------------------+--------------------+-------------------------------
 intensetbm | postgres     | UTF8     | French_France.1252 | French_France.1252 | =Tc/postgres                 +
            |              |          |                    |                    | postgres=CTc/postgres        +
            |              |          |                    |                    | intensetbm_admin=CTc/postgres
(1 ligne)

Dernière modification par LECARROU (17/03/2020 16:16:37)

jmarsac

Membre

Re : Administration base Postgresql via SSH

Bonjour,

L'encodage de la console Windows est 850.

Faites :
chcp 1252
avant de lancer psql et tout devrait rentrer dans l'ordre

pifor

Membre

Re : Administration base Postgresql via SSH

Avant le lancer psql sur Windows, essayez d'exécuter:

chcp 1252

Vérfiez ensuite:
1. l'avertissement de psql sur l'encodage console est-il toujours affiché ?
2. est-ce que le INSERT avec des caractères accentués fonctionne ?

---

Pierre

LECARROU

Membre

Re : Administration base Postgresql via SSH

pardon mais qu'entendez-vous par exécuter chcp AVANT de lancer psql ?
je début en postgres et ce que je fait c'est que je recherche l'application SQL Shell (psql) et le lance pour utiliser psql

du coup, ou est-ce que je dois lancer chcp?

dans mes scripts, c'est SET client_encoding to 'UTF8' ou 'win1252' du coup ?

Dernière modification par LECARROU (17/03/2020 16:32:39)

jmarsac

Membre

Re : Administration base Postgresql via SSH

chcp 1252
psql

pifor

Membre

Re : Administration base Postgresql via SSH

Il faut lancer chcp dans une fenêtre interprête de commande comme cmd.exe et lancer psql dans la même fenêtre juste après.
Dans un premier temps  vérifiez que CLIENT_ENCODING est bien positionné à 'WIN1252' par défaut avec:

show client_encoding

Dernière modification par pifor (17/03/2020 16:37:34)

---

Pierre

LECARROU

Membre

Re : Administration base Postgresql via SSH

mais le problème c'est que je ne lance pas psql avec la commande psql... j'ouvre la console SQL Shell, renseigne les paramètre de connexion serveur et psql se lance automatiquement... donc à aucun moment je ne peut faire chcp 1252 avant psql

pifor

Membre

Re : Administration base Postgresql via SSH

Je ne connais pas la "console SQL Shell": est-ce qu'il s'agit d'un logiciel connu ou d'une application maison ?

Si vous ne pouvez pas lancer cmd.exe directement il faut trouver une solution pour que votre console SQL Shell démarre avec l'instruction "chcp 1252" avant de lancer psql.exe ou une configuration équivalente dans Windows: à avoir avec votre administrateur côté poste de travail (Windows 10 sans doute) ?

Dernière modification par pifor (17/03/2020 16:47:56)

---

Pierre

jmarsac

Membre

Re : Administration base Postgresql via SSH

regardez ce post https://forums.postgresql.fr/viewtopic.php?id=1060
vous aurez la solution

LECARROU

Membre

Re : Administration base Postgresql via SSH

je vois qu'il faut éditer le fichier C:\Program Files\PostgreSQL\10\scripts\runpsql.bat en ajoutant la ligne chcp 1252

LECARROU

Membre

Re : Administration base Postgresql via SSH

j'ai modifier runpsql.bat et je n'ai plus l'avertissement au lancement de psql (cool)

si je fait

INSERT INTO adm_pay (pay_nom_eng,pay_nom_fra,pay_abr) VALUES ('Ivory Coast','Côte d Ivoire','CIV');

directement dans la console pslq pas de problème, il m'insère correctement les accents

mais si je lance le script depuis la console : \i 'D:/Users/jl3/intensetbm_test/intensetbm-etool/script2.txt' toujours un problème sur les accents...

script1.txt

CREATE DATABASE intensetbm;
CREATE USER intensetbm_admin WITH PASSWORD 'admin';
ALTER ROLE intensetbm_admin SET client_encoding TO 'utf8';
ALTER ROLE intensetbm_admin SET default_transaction_isolation TO 'read committed';
ALTER ROLE intensetbm_admin SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE intensetbm TO intensetbm_admin;

script2.txt

SET client_encoding TO 'utf8';
INSERT INTO adm_pay (pay_nom_eng,pay_nom_fra,pay_abr) VALUES ('Ivory Coast','Côte d Ivoire','CIV');

je vais craquer... ; )

pifor

Membre

Re : Administration base Postgresql via SSH

Essayer de supprimer toutes les instructions pour CLIENT_ENCODING (ALTER ROLE intensetbm_admin SET client_encoding TO 'utf8';  et SET client_encoding TO 'utf8';) dans les scripts et retestez.

---

Pierre

LECARROU

Membre

Re : Administration base Postgresql via SSH

je viens de le faire et toujours pas...

pifor

Membre

Re : Administration base Postgresql via SSH

Pouvez-vous nous donner la sortie complète  du script suivant exécuté dans le même environnement que script2.txt:

\encoding
show client_encoding
\l intensetbm
INSERT INTO adm_pay (pay_nom_eng,pay_nom_fra,pay_abr) VALUES ('Ivory Coast','Côte d Ivoire','CIV');
select * from_adm_pay where pay_abr='CIV';

---

Pierre

jmarsac

Membre

Re : Administration base Postgresql via SSH

j'ai modifier runpsql.bat et je n'ai plus l'avertissement au lancement de psql (cool)

si je fait

INSERT INTO adm_pay (pay_nom_eng,pay_nom_fra,pay_abr) VALUES ('Ivory Coast','Côte d Ivoire','CIV');

directement dans la console pslq pas de problème, il m'insère correctement les accents

mais si je lance le script depuis la console : \i 'D:/Users/jl3/intensetbm_test/intensetbm-etool/script2.txt' toujours un problème sur les accents...

Il faut vérifier/corriger l'encodage du fichier .txt contenant le script (D:/Users/jl3/intensetbm_test/intensetbm-etool/script2.txt par exemple)

Dernière modification par jmarsac (17/03/2020 18:24:14)

LECARROU

Membre

Re : Administration base Postgresql via SSH

euh, pas sûr d'avoir compris ! ; )

je me suis connecté via SQL Shell et j'obtiens le message suivant :

Server [localhost]:
Database [postgres]:
Port [5433]:
Username [postgres]: postgres
Page de codes active : 1252
Mot de passe pour l'utilisateur postgres :
psql (10.10)
Saisissez « help » pour l'aide.

postgres=#

à partir de là, je lance donc le script que vous m'avez donné (aucun SET de l'encoding):

\encoding
show client_encoding
\l intensetbm
INSERT INTO adm_pay (pay_nom_eng,pay_nom_fra,pay_abr) VALUES ('Ivory Coast','Côte d Ivoire','CIV');
select * from adm_pay where pay_abr='CIV';

et j'obtiens la sortie suivante :

WIN1252
                                           Liste des bases de données
    Nom     | Propriétaire | Encodage |  Collationnement   |    Type caract.    |        Droits d'accès
------------+--------------+----------+--------------------+--------------------+-------------------------------
 intensetbm | postgres     | UTF8     | French_France.1252 | French_France.1252 | =Tc/postgres                 +
            |              |          |                    |                    | postgres=CTc/postgres        +
            |              |          |                    |                    | intensetbm_admin=CTc/postgres
(1 ligne)

les instructions SQL sortent en erreur

psql:D:/Users/jl3/intensetbm_test/intensetbm-etool/test.txt:4: ERREUR:  erreur de syntaxe sur ou près de « INSERT »
LIGNE 2 : INSERT INTO adm_pay (pay_nom_eng,pay_nom_fra,pay_abr) VALUES...
          ^
psql:D:/Users/jl3/intensetbm_test/intensetbm-etool/test.txt:5: ERREUR:  la relation « adm_pay » n'existe pas
LIGNE 1 : select * from adm_pay where pay_abr='CIV';