pg_restore examples ¶

Sauvegarde du schéma (–clean –if-exists –schema-only) ¶

           pg_dump -U USER --clean --if-exists --schema-only --create -f /opt/USER/scripts/db.dump_schema_only.sql db_XXX

          

Sauvegarde des données (–clean –if-exists –schema-only) ¶

           pg_dump -U USER --data-only --inserts --column-inserts  -f /opt/USER/scripts/db.dump_data_only.sql db_XXX

          

Autre exemple ¶

           #export postgre en local
pg_dump -U USER --format=custom db_XXX > /root/scripts/output_backup/db_XXX_$(date +%F).sql
pg_dump -U USER --format=custom db_USER > /root/scripts/output_backup/db_USER_$(date +%F).sql

#export mysql en local
mysqldump -u root db_id3_programs > /root/scripts/output_backup/db_id3_programs_$(date +%F).sql


#export pour le staging
pg_restore -h X.X.X.X -p AAAA -d db_XXX -U USER --if-exists -c /root/scripts/output_backup/db_XXX_$(date +%F).sql
pg_restore -h X.X.X.X -p AAAA -d db_USER -U USER --if-exists -c /root/scripts/output_backup/db_USER_$(date +%F).sql

#delete files older than 5 days
cd /root/scripts/output_backup/
find . -type f -mtime +5 -exec rm -f {} \;

          

cat /root/scripts/backup_db.bash ¶

Prérequis ¶

existence de la base db_intranet:

              CREATE DATABASE db_intranet WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'fr_FR.utf8' LC_CTYPE = 'fr_FR.utf8';

             

existence du role intranet:

              CREATE ROLE intranet;
ALTER ROLE intranet WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
ALTER ROLE intranet WITH LOGIN PASSWORD 'password';

             

            #!/bin/bash

#export postgre en local
pg_dump -U intranet --format=custom db_intranet > /root/scripts/output_backup/db_intranet_$(date +%F).sql

#export pour le staging
pg_restore -h x.y.z.t -p 5432 -U intranet -d db_intranet -c /root/scripts/output_backup/db_intranet_$(date +%F).sql

#delete files older than 5 days
cd /root/scripts/output_backup/
find . -type f -mtime +5 -exec rm -f {} \;

           

restore example ¶

See also

Le fichier pg_hba.conf (hba host-based authentication)

           pg_restore -d db_intranet -U intranet --if-exists -c db_intranet.sql

          

           pvergain@pvergain-MS-7721 psql -d db_intranet -U intranet

          

           L'affichage étendu est utilisé automatiquement.
L'affichage de null est « ¤ ».
Le style de ligne est unicode.
Le style de bordure Unicode est « single ».
Le style de ligne Unicode est « single ».
Le style d'en-tête Unicode est « double ».
SET
psql (12.2 (Ubuntu 12.2-2.pgdg18.04+1))
Saisissez « help » pour l'aide.

intranet@db_intranet=#  \dt
                          Liste des relations
 Schéma │                  Nom                   │ Type  │ Propriétaire
════════╪════════════════════════════════════════╪═══════╪══════════════
 public │ article                                │ table │ intranet
 public │ article_achat                          │ table │ intranet

          