Une façon plus rapide de copier une base de données postgresql (ou la meilleure façon)

Question

J'ai effectué un pg_dump d'une base de données et j'essaie maintenant d'installer le fichier .sql résultant sur un autre serveur.

J'utilise la commande suivante.

psql -f databasedump.sql

J'ai lancé l'installation de la base de données plus tôt dans la journée et 7 heures plus tard, la base de données est toujours en cours de remplissage. Je ne sais pas si c'est le temps qu'il faut, mais je continue à surveiller la situation. Jusqu'à présent, j'ai vu plus de 12 millions d'insertions et ça continue. Je pense qu'il y a un moyen plus rapide de faire cela.

Answer 1

Créez vos dumps avec

pg_dump -Fc -Z 9  --file=file.dump myDb

Fc

Produit une archive personnalisée qui peut être introduite dans pg_restore. C'est le format le plus flexible car il permet de réordonner les données de chargement ainsi que les définitions d'objets. Ce format est également compressé par défaut.

Z 9: --compress=0..9

Indique le niveau de compression à utiliser. Zéro signifie qu'il n'y a pas de compression. Pour le format d'archive personnalisé, cela spécifie la compression de segments individuels de données de table, et la valeur par défaut est de compresser à un niveau modéré. Pour le texte brut, un niveau de compression différent de zéro entraîne la compression de l'ensemble du fichier de sortie, comme s'il était passé par gzip ; mais la valeur par défaut est de ne pas compresser. Le format d'archive tar ne prend actuellement pas en charge la compression.

et le restaurer avec

pg_restore -Fc -j 8  file.dump

-j: --jobs=number-of-jobs

Exécutez les parties de pg_restore qui prennent le plus de temps - celles qui chargent les données, créent des index ou des contraintes - en utilisant plusieurs tâches simultanées. Cette option permet de réduire considérablement le temps nécessaire à la restauration d'une base de données volumineuse sur un serveur fonctionnant sur une machine multiprocesseur.

Chaque travail est un processus ou un thread, selon le système d'exploitation, et utilise une connexion distincte au serveur.

La valeur optimale de cette option dépend de la configuration matérielle du serveur, du client et du réseau. Le nombre de cœurs de l'unité centrale et la configuration du disque sont autant de facteurs à prendre en compte. Le nombre de cœurs de CPU sur le serveur est un bon point de départ, mais des valeurs plus élevées peuvent également conduire à des temps de restauration plus rapides dans de nombreux cas. Bien entendu, des valeurs trop élevées entraîneront une baisse des performances en raison de l'effet de balayage.

Seuls les formats d'archives personnalisées et de répertoires sont pris en charge par cette option. L'entrée doit être un fichier ou un répertoire normal (pas, par exemple, un tuyau). Cette option est ignorée lorsque l'on émet un script au lieu de se connecter directement à un serveur de base de données. De même, il n'est pas possible d'utiliser plusieurs tâches en même temps que l'option --single-transaction.

Liens :

pg_dump

pg_restore

Answer 2

Améliorer pg dump&restore

PG_DUMP | toujours utiliser le répertoire de format avec -j option

time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external

PG_RESTORE | toujours utiliser le tuning pour postgres.conf avec le répertoire de format With -j option

work_mem = 32MB
shared_buffers = 4GB
maintenance_work_mem = 2GB
full_page_writes = off
autovacuum = off
wal_buffers = -1

time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/`

Pour plus d'informations

https://gitlab.com/yanar/Tuning/wikis/improve-pg-dump&restore

Answer 3

Pourquoi produisez-vous un fichier .sql brut ? La description initiale de pg_dump recommande le format "personnalisé -Fc .

Vous pouvez ensuite utiliser pg_restore qui restaurera vos données (ou des parties sélectionnées de celles-ci). Il existe une option "nombre de tâches" -j qui peut utiliser plusieurs cœurs (en supposant que vos disques ne soient pas déjà le facteur limitant). Dans la plupart des cas, sur une machine moderne, vous pouvez vous attendre à au moins quelques gains.

Vous dites maintenant : "Je ne sais pas combien de temps cela est censé prendre". Eh bien, tant que vous n'aurez pas effectué quelques restaurations, vous ne le saurez pas. Surveillez ce que fait votre système et vérifiez si vous êtes limité par le processeur ou les entrées/sorties du disque.

Enfin, les paramètres de configuration que vous souhaitez utiliser pour restaurer une base de données ne sont pas ceux que vous souhaitez utiliser pour l'exécuter. Quelques exemples utiles :

1. Augmentation maintenance_work_mem afin de pouvoir construire des index par tranches plus importantes
2. Désactiver fsync pendant la restauration. Si votre machine tombe en panne, vous repartirez de toute façon à zéro.

N'oubliez pas de les réinitialiser après la restauration.

Answer 4

L'utilisation de pg_dump Il est généralement recommandé de l'associer à pg_restore au lieu de psql . Cette méthode peut être répartie entre les cœurs afin d'accélérer le processus de chargement en passant la commande --jobs comme tel :

$ pg_restore --jobs=8 dump.sql

Postgres a lui-même une fonction guide sur le chargement en masse des données.

Je vous recommande également d'accorder une attention particulière à votre postgresql.conf et définissez des valeurs suffisamment élevées pour le paramètre maintenance_work_mem y checkpoint_segments des valeurs plus élevées peuvent augmenter considérablement les performances d'écriture.