pg_dump 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 {} \;