POSTGRES Backup / restore

Petites bases :

#backup

-C : ajouter le create database.

pg_dump -d $DB > $DB.sql
#restore
psql < $DB.sql

 

Grosses bases :

#backup
on exporte la structure :

 pg_dump -s $DB> $DBstruct.sql

 psql $DB < $DBstruct.sql

on exporte les données : 
pg_dump -Fc -a -d $DB > $DB.dmp
pg_restore -Fc -d $DB $DB.dmp

Ajout du GZIP pour le dump : 
pg_dump -U <user> <database> | gzip -c > backup.gz

Ajout du GZIP pour l'import : 
gzip -d backup.gz | psql -d <database> -U <user>

You might need to be logged in as postgres in order to have full privileges on databases.

su - postgres
psql -l                      # will list all databases on Postgres cluster

pg_dump/pg_restore

  pg_dump -U username -f backup.dump database_name -Fc 

switch -F specify format of backup file:

• c will use custom PostgreSQL format which is compressed and results in smallest backup file size
• d for directory where each file is one table
• t for TAR archive (bigger than custom format)
• -h/--host Specifies the host name of the machine on which the server is running
• -W/--password Force pg_dump to prompt for a password before connecting to a database

restore backup:

   pg_restore -d database_name -U username -C backup.dump

[Postgres] VACUUM

Comprendre : autovacuum_freeze_max_age

 

Quand le vacuum ne suffit plus pour réinitialiser le XID d'une table, il faut faire un VACUUM FULL de cette table.