Postgresql

POSTGRESQL

# Info postgresql

# —————————————————————————–

# en local a la base avec user postgres
psql -f
psql -f database-create.sql
psql -d visiovote -f schema-create.sql
psql -d visiovote -f script.sql -U user

psql -f database-create.sql; psql -d visiovote -f schema-create.sql

# depuis tomcat psql (postgres client)
psql -h IP_BDD -p 5432 -U visiovoteapp
psql -h IP_BDD -p 5432 -U visiovoteflyway

# —————————————————————————–

\c visiovote
\dt
copy ( select * from schema_version ) to ‘/tmp/visiovote_selecte_schema_version.csv’;

su – postgres
psql
\c visiovote
copy ( select distinct login from utilisateur where code_profil=’UTILISATEUR.PRESIDENT’ ) to ‘/tmp/visiovote_select_login_prez.csv’;
copy ( select distinct email from utilisateur where code_profil=’UTILISATEUR.PRESIDENT’ ) to ‘/tmp/visiovote_select_email_prez.csv’;
copy ( select distinct email from elu where code_titulaire=’ELU.PRE’ ) to ‘/tmp/visiovote_select_email_elu.csv’;

alter user visiovoteflyway with password ‘5NFFb7MhF$d6rN8u’;
alter user visiovoteapp with password ‘Cne3SPMRXg$7qc]$’;
drop schema public cascade;
create schema public authorization visiovoteflyway;
select * from utilisateur;

# pour visivote
select * from utilisateur ;
visiovote=# select * from reunion ;
visiovote=# select * from elu;

# —————————————————————————–

# Si conf pg_hba.conf
pg_ctl reload

# —————————————————————————–

become: true
become_user: postgres

# —————————————————————————–

co a la base, pour faire des requetes SQL :
– drop extention
– alter user

# Ex commandes
alter user visiovoteflyway with password ‘MDP’;
drop database visioviote ;

# change mdp pour recette
postgres@visiovote-poc:~$ psql
psql (13.2 (Ubuntu 13.2-1.pgdg20.04+1))
Type « help » for help.

postgres=# alter user visiovoteflyway with password ‘5NFFb7MhF$d6rN8u’;
ALTER ROLE
postgres=# alter user visiovoteapp with password ‘Cne3SPMRXg$7qc]$’;
ALTER ROLE
postgres=# \q
postgres@visiovote-poc:~$

# —————————————————————————–

# Création de la base de données visioviote:
root@visiovote-poc-front:~# su – postgres
postgres@visiovote-poc-front:~$ cd /application/scripts/
postgres@visiovote-poc-front:/application/scripts$ psql -f database-create.sql; psql -d visiovote -f schema-create.sql
CREATE ROLE
CREATE DATABASE
CREATE ROLE
GRANT
postgres@visiovote-poc-front:/application/scripts$

# —————————————————————————–

# Se co a la db et vérifier les table
postgres@visiovote-poc:~$ psql
psql (13.3 (Ubuntu 13.3-1.pgdg20.04+1))
Type « help » for help.

postgres=# \c visiovote
You are now connected to database « visiovote » as user « postgres ».
visiovote=# \dt
List of relations
Schema | Name | Type | Owner
——–+———————————-+——-+—————–
public | acces | table | visiovoteflyway
public | archive_deliberation | table | visiovoteflyway
public | archive_participant | table | visiovoteflyway
public | archive_participant_deliberation | table | visiovoteflyway
public | archive_reunion | table | visiovoteflyway
public | choix | table | visiovoteflyway
public | commun | table | visiovoteflyway
public | cse | table | visiovoteflyway
public | delegation | table | visiovoteflyway
public | deliberation | table | visiovoteflyway
public | elu | table | visiovoteflyway
public | emargement | table | visiovoteflyway
public | entreprise | table | visiovoteflyway
public | participant | table | visiovoteflyway
public | participant_deliberation | table | visiovoteflyway
public | representation | table | visiovoteflyway
public | reunion | table | visiovoteflyway
public | schema_version | table | visiovoteflyway
public | suffrage | table | visiovoteflyway
public | utilisateur | table | visiovoteflyway
(20 rows)

visiovote=#

# —————————————————————————–

# faire un drop de schema sur visiovote

Stop tomcat

drop schema
root@visiovote-poc-bdd:~# su – postgres
postgres@visiovote-poc-bdd:~$ psql
psql (13.4 (Ubuntu 13.4-1.pgdg20.04+1))
Type « help » for help.

postgres=# \c visiovote
You are now connected to database « visiovote » as user « postgres ».
visiovote=# \dt
List of relations
Schema | Name | Type | Owner
——–+———————————-+——-+—————–
public | acces | table | visiovoteflyway
public | agcopro | table | visiovoteflyway
public | archive_deliberation | table | visiovoteflyway
public | archive_participant | table | visiovoteflyway
public | archive_participant_deliberation | table | visiovoteflyway
public | archive_reunion | table | visiovoteflyway
public | choix | table | visiovoteflyway
public | commun | table | visiovoteflyway
public | cse | table | visiovoteflyway
public | delegation | table | visiovoteflyway
public | deliberation | table | visiovoteflyway
public | elu | table | visiovoteflyway
public | emargement | table | visiovoteflyway
public | entreprise | table | visiovoteflyway
public | participant | table | visiovoteflyway
public | participant_deliberation | table | visiovoteflyway
public | representation | table | visiovoteflyway
public | reunion | table | visiovoteflyway
public | schema_version | table | visiovoteflyway
public | suffrage | table | visiovoteflyway
public | tantieme | table | visiovoteflyway
public | tantieme_elu | table | visiovoteflyway
public | utilisateur | table | visiovoteflyway
(23 rows)

visiovote=# drop schema public cascade;
NOTICE: drop cascades to 25 other objects
DETAIL: drop cascades to table schema_version
drop cascades to table commun
drop cascades to table entreprise
drop cascades to table cse
drop cascades to table reunion
drop cascades to table deliberation
drop cascades to table suffrage

\dt
create schema public authorization visiovoteflyway;

Start tomcat

\dt
select * from utilisateur;

# —————————————————————————–

select * from reunion where id = (select id from cse where nom = ‘COOPANAME : Assemblée Générale ordinaire’);

mchevrot@PONA095: ~ (master) $ ssh visiovote-prod
admin_voxaly@vps601752:~$ sudo -i
root@vps601752:~# su – postgres
postgres@vps601752:~$ psql
psql (9.6.20)
Saisissez « help » pour l’aide.

postgres=# \c visiovote
Vous êtes maintenant connecté à la base de données « visiovote » en tant qu’utilisateur « postgres ».
visiovote=# select * from reunion where id = (select id from cse where nom = ‘COOPANAME : Assemblée Générale ordinaire’);
visiovote=# update reunion set workflow_etape=’WF.REU.NOUVEAU’ where id = (select id from cse where nom = ‘COOPANAME : Assemblée Générale ordinaire’);
UPDATE 1
visiovote=# select * from reunion where id = (select id from cse where nom = ‘COOPANAME : Assemblée Générale ordinaire’);
visiovote=#
nom | description | id | date_ouverture | workflow_etape | workflow_date_maj | nb_mandats_max | ventilation_mandat
———————————————————-+————-+—–+———————+—————-+————————-+—————-+——————–
COOPANAME : Assemblée Générale Ordinaire du 25 juin 2021 | | 672 | 2021-06-25 09:30:00 | WF.REU.NOUVEAU | 2021-06-24 14:47:12.566 | 19 | f
(1 ligne)

(END)

# —————————————————————————–

http://redmine.voxatool.com/issues/41667
[Visiovote] restauration de dump de la prod vers la preprod (avec Anomymisation des données)

# Création du script d’anonymisation
root@visiovote-preprod-bdd:/application/archive# vim anomymisation.sql
root@visiovote-preprod-bdd:/application/archive# ll anomymisation.sql
rw-r–r- 1 root voxaly 177 Sep 27 10:59 anomymisation.sql
root@visiovote-preprod-bdd:/application/archive# cat anomymisation.sql
update elu set nom = ‘nom’||id, prenom =’prenom_’||id, email = ’email’||id, telephone = null;
update utilisateur set login = ‘login’||id||’@test.com’ where login <> ‘VoxAdmin’;
root@visiovote-preprod-bdd:/application/archive#

# anonymisation lancer
postgres@visiovote-preprod-bdd:/application/archive$ psql -d visiovote -f anomymisation.sql
UPDATE 16894
UPDATE 584
postgres@visiovote-preprod-bdd:/application/archive$

# —————————————————————————–

postgres=# \c visiovote
You are now connected to database « visiovote » as user « postgres ».
visiovote=# \dt
visiovote=# ALTER DATABASE visiovote SET search_path = public;
ALTER DATABASE
visiovote=# ALTER ROLE visiovoteflyway SET search_path = public;
ALTER ROLE
visiovote=# ALTER ROLE visiovoteapp SET search_path = public;
ALTER ROLE

# —————————————————————————–

# Exemple méga requete: pour compter nombre de ligne ? par colonne ?

select table_schema, table_name, (xpath(‘/row/cnt/text()’, xml_count))[1]::text::int as row_count
from (
select table_name, table_schema, query_to_xml(format(‘select count(*) as cnt from %I.%I’, table_schema, table_name), false, true,  ») as xml_count
from information_schema.tables
where table_schema = ‘sh_voxalyvoxcore_meae’ –<< change here for the schema you want
) t order by row_count desc
# —————————————————————————–

# —————————————————————————–
pg_dump -F c -E UTF8 -d voxalyprod > /tmp/voxalyprodrecette1904.dump

pg_restore -d voxalyprod /tmp/pg_dump_voxalyprod_20220415_1747.pgdmp
# —————————————————————————–