Copie de la base de données PostgreSQL sur un autre serveur

Question

Je cherche à copier une base de données PostgreSQL de production sur un serveur de développement. Quel est le moyen le plus rapide et le plus simple d'y parvenir ?

Answer 1

Vous n'avez pas besoin de créer un fichier intermédiaire. Vous pouvez faire

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

ou

pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

en utilisant psql ou pg_dump pour se connecter à un hôte distant.

Avec une grande base de données ou une connexion lente, le vidage d'un fichier et le transfert du fichier compressé peuvent être plus rapides.

Comme Kornel l'a dit, il n'est pas nécessaire d'effectuer un dumping vers un fichier intermédiaire. Si vous voulez travailler en mode compressé, vous pouvez utiliser un tunnel compressé.

pg_dump -C dbname | bzip2 | ssh  remoteuser@remotehost "bunzip2 | psql dbname"

ou

pg_dump -C dbname | ssh -C remoteuser@remotehost "psql dbname"

mais cette solution nécessite également d'obtenir une session aux deux extrémités.

Note : pg_dump sert à sauvegarder et psql est pour la restauration. Ainsi, le La première commande dans cette réponse est pour copier de local à distant et la seconde est de distant à local. . Plus -> https://www.postgresql.org/docs/9.6/app-pgdump.html

Answer 2

pg_dump the_db_name > the_backup.sql

Ensuite, copiez la sauvegarde sur votre serveur de développement, restaurez avec :

psql the_new_dev_db < the_backup.sql

Answer 3

Utilisez pg_dump et plus tard psql ou pg_restore - selon que vous choisissez les options -Fp ou -Fc de pg_dump.

Exemple d'utilisation :

ssh production
pg_dump -C -Fp -f dump.sql -U postgres some_database_name
scp dump.sql development:
rm dump.sql
ssh development
psql -U postgres -f dump.sql

Answer 4

Si vous cherchez à migrer d'une version à l'autre (par exemple, vous avez mis à jour postgres et vous avez la 9.1 qui tourne sur localhost:5432 et la 9.3 sur localhost:5434), vous pouvez exécuter :

pg_dumpall -p 5432 -U myuser91 | psql -U myuser94 -d postgres -p 5434

Consultez le documents sur la migration .

Answer 5

pg_basebackup semble être la meilleure façon de procéder maintenant, surtout pour les grandes bases de données.

Vous pouvez copier une base de données à partir d'un serveur dont la version majeure est identique ou antérieure. Ou bien plus précisément :

pg_basebackup fonctionne avec des serveurs de la même version majeure ou d'une version antérieure, jusqu'à la 9.1. Cependant, le mode streaming de WAL ( -X stream ) ne fonctionne qu'avec la version 9.3 ou ultérieure du serveur, et le mode de formatage tar ( --format=tar ) de la version actuelle ne fonctionne qu'avec la version 9.5 ou ultérieure du serveur.

Pour cela, vous avez besoin du serveur source :

1. listen_addresses = '*' pour pouvoir se connecter depuis le serveur cible. Assurez-vous que le port 5432 est ouvert à cet effet.
2. Au moins une connexion de réplication disponible : max_wal_senders = 1 ( -X fetch ), 2 pour -X stream (la valeur par défaut dans le cas de PostgreSQL 12), ou plus.
3. wal_level = replica ou plus pour pouvoir définir max_wal_senders > 0 .
4. host replication postgres DST_IP/32 trust sur pg_hba.conf . Cela permet d'accéder à la pg à toute personne du DST_IP machine. Vous pourriez vouloir recourir à une option plus sûre.

Les changements 1, 2, 3 nécessitent un redémarrage du serveur, le changement 4 nécessite un rechargement.

Sur le serveur cible :

# systemctl stop postgresql@VERSION-NAME
postgres$ pg_basebackup -h SRC_IP -U postgres -D VERSION/NAME --progress
# systemctl start postgresql@VERSION-NAME