Commandes Postgres

Backup

Au format "plain text" :

pg_dump -f fichier.sql -U <user> <dbname>
pg_dumpall -U postgres -f full.sql

Backup de toutes vos bases postgres au format custom (pour utilisation de pg_restore)

#! /bin/sh

# Author : Nicolas Steinmetz
# Date : 2011/02/22
# Desc : backup all postgres db in /var/backup using the custom format
#       so that we can use pg_restore to restore the whole or part of the
#       DB
#       For more details :
#               http://www.postgresql.org/docs/8.4/static/app-pgdump.html
#               http://www.postgresql.org/docs/8.4/static/app-pgrestore.html
#
# Requires that ~/.pgpass is defined as follow :
#       host:port:dbname (use * for all db):user:password
#
# Be sure that ~/.pgpass is "chmoded" 600
#

psql -Atw -U postgres -c "SELECT datname FROM pg_database \
                      WHERE NOT datistemplate"| \
while read f;
    do pg_dump -U postgres -w --format=c --file=/var/backup/$f.sql $f;
done;

Restauration

Si votre backup est au format "plain text" :

psql -U <user> -d <base> -f fichier.sql

ou s'il est au format tar ou custom :

pg_restore -U <user> -d <base> -f /path/to/file.sql

Autre intérêt de pg_restore, il est possible, à partir d'un dump complet, de ne demander la restauration que d'une table, fonction, trigger, etc.

Learn more »

Initialisation d'une instance Postgres

Note

Version de postgres utilisée : 8.4.2 sur Slackware 13.0 64 bits

Sécurisation de l'instance Postgres

Commencez par donner un mot de passe à votre compte postgres :

su postgres
psql -d postgres -c "ALTER USER postgres WITH PASSWORD '<password>'"

Puis éditer /var/lib/pgsql/data/pg_hba.conf pour remplacer les occurences de trust par md5 :

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

S'il vous faut un accès depuis votre réseau à votre instance postgres :

# All local network
host    all         all         192.168.56.0/24        md5

Il vous faut aussi éditer le fichier /var/lib/pgsql/data/postgresql.conf et ajuster la valeur de listen_addresses.

Et redémarrer votre instance Postgres :

/etc/rc.d/rc.postgresql reload

Support UTF-8

Pour avoir le support de l'UTF-8 :

su postgres -c "initdb --locale=C --encoding=UTF-8 -D /var/lib/pgsql/data"

Création d'un compte utilisateur et d'une base

create user <user>
psql -d template1 -c "alter user <user> with password '<password>'"
createdb -O <user> -E UTF8 <dbname>

Redémarrer votre instance pour prise en compte du compte :

/etc/rc.d/rc.postgresql reload
View details »