![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a><\/p>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2020\/10\/pool_billiard_ball_13-512-300x300.png\")
<\/p>\n
<\/p>\n
<\/p>\n
La nouvelle release officielle de PostgreSQL est sorti le 24 septembre 2020. Place \u00e0 la version 13.<\/p>\n
Celle ci est bien entendu, t\u00e9l\u00e9chargeable sur le site PostgreSQL.org<\/a> sur lequel vous pourrez retrouver les sources, les derniers packages. <\/p>\n Comme \u00e0 chaque version, PostgreSQL nous apporte son lot de nouveaut\u00e9s pour cette release 13. <\/p>\n <\/p>\n Regardons de plus pr\u00e8s les quelques avanc\u00e9es de cette nouvelle release. Voyons si celles ci sont r\u00e9ellement pertinentes !<\/p>\n Nous pourrons, avec la release 13, cr\u00e9er des tables partitionn\u00e9es via “pgbench”. L’option est la suivante<\/p>\n <\/p>\n <\/p>\n Pour notre article, partons sur un partitionnement de type range avec 10 partitions pour le moment.<\/p>\n <\/p>\n S’il l’on liste les objets cr\u00e9\u00e9s dans notre base ‘manuelo’<\/p>\n <\/p>\n <\/p>\n Nous retrouvons nos tables ‘pgbench’ cr\u00e9\u00e9es sur le sch\u00e9ma public, dont le propri\u00e9taire est ‘postgres’.<\/p>\n Nous remarquons la cr\u00e9ation de “pgbench_accounts” qui est partitionn\u00e9e.<\/p>\n Afin d’effectuer les tests de comparaison n\u00e9cessaires pour la suite de l’article, on copie les donn\u00e9es de la base “manuelo” sur un moteur PostgreSQL 12. La m\u00e9thode “pg_dump\/pg_restore” sera utilis\u00e9e pour cette copie<\/p>\n <\/p>\n Dans certaines op\u00e9rations de tris sur une requ\u00eate, il est parfois utile de regarder quelles sont les n valeurs les plus fortes, en prenant en compte \u00e9galement leurs ex\u00e6quos. Cela est possible en passant par un “ranking” via une sous requ\u00eate d’un ensemble donn\u00e9e. Avec PostgreSQL 13, ceci est simplifier avec l’\u00e9criture “WITH TIES”. Reprenons nos donn\u00e9es charg\u00e9es via PgBench auparavant.<\/p>\n <\/p>\n On sait que notre table “pgbench_accounts” est partitionn\u00e9e m\u00e9thode\u00a0 RANGE sur le champ “aid”.<\/p>\n <\/p>\n Chaque partition contient donc 100 000 lignes.<\/p>\n Voyons donc s’il l’on interroge le TOP 10 des plus grands “account id” et leurs “branche id” attach\u00e9s avec tri descendant sur branche id.<\/p>\n le plan d’ex\u00e9cution est le suivant :<\/p>\n <\/p>\n Il nous faut environ 2 secondes pour ramener le r\u00e9sultat de l’analyse. Rappelons que l’optimiseur parcourt l’ensemble des partitions dans la mesure on nous n’avons pas de clause WHERE.<\/p>\n Voyons maintenant le plan d’ex\u00e9cution si l’on souhaite ramener le TOP 10 des plus grands “account id” et leurs “branche id” attach\u00e9s tri\u00e9s par “branche id” descendant, ainsi que les ex\u00e6quos. Au vu de la conception de notre champs “bid”(valeurs \u00e9gales par partition de la table “pgbench_accounts”), nous devrions ramener toutes les lignes de la partition 10 soit 100 000 lignes<\/p>\n <\/p>\n Nous mettons un peu plus de 9 secondes pour cette op\u00e9ration, avec du tri sur disque (un peu plus de 17 Mo).<\/p>\n Voyons maintenant comment cela se comporte en PostgreSQL 13<\/p>\n Ex\u00e9cutons les m\u00eames requ\u00eates<\/p>\n <\/p>\n la 1ere requ\u00eate avec le TOP 10 des plus grands “account id” et leurs “branche id” attach\u00e9s tri\u00e9s par “branche id” descendant.<\/p>\n <\/p>\n <\/p>\n Pour le plan d’ex\u00e9cution c’est le m\u00eame qu’en 12 avec quasiment les m\u00eames perfs, soit \u00e0 peu pr\u00e8s 2 secondes d’analyse.<\/p>\n <\/p>\n Puis le TOP 10 des plus grands “account id” et leurs “branche id” attach\u00e9s ainsi que les ex\u00e6quos avec la m\u00e9thode via la fonction ‘ranking’.<\/p>\n <\/p>\n Les performances sont identiques \u00e0 la version 12. Nous ramenons \u00e9galement 100 000 lignes, mais avec une ex\u00e9cution en 3 secondes pour ce EXPLAIN ANALYZE au lieu des 6 secondes avec le mode ranking. Nous optimisons donc les IO et le temps d’ex\u00e9cution avec cette fonction.<\/p>\n <\/p>\n Autre nouveaut\u00e9 de la version 13 de PostgreSQL , le tri incr\u00e9mental. Tr\u00e8s int\u00e9ressant pour des requ\u00eates de type “TOP n” avec “order by” et “limit”, pour l’optimiseur, il n’est pas n\u00e9cessaire de rev\u00e9rifier les ensembles de tris pr\u00e9c\u00e9dents lorsque vous effectuez un tri complet de donn\u00e9es. Soit 2 colonnes A et B, nous pourrions avoir les valeurs suivantes :<\/p>\n Avec le “incremental sort”, l’optimiseur PostgreSQL va alors classer les donn\u00e9es en fonction de la colonne A pour faire des sous ensembles avec les valeurs de B, soit :<\/p>\n <\/p>\n On constatera alors un gain en terme de co\u00fbt, et en IO car l’optimiseur effectuera des tris ensemblistes sur bien moins de lignes .<\/p>\n PostgreSQL 12\u00a0<\/span><\/strong><\/p>\n Prenons un exemple avec une jointure entre la table “pgbench_accounts” et “pgbench_branches”.<\/p>\n 400ms<\/strong> pour l’ex\u00e9cution de cette requ\u00eate. Regardons le plan.<\/p>\n <\/p>\n Pour ramener les 10 lignes de r\u00e9sultat, nous effectuons un tri sur plus de 416000 lignes.<\/p>\n PostgreSQL 13<\/span><\/strong><\/p>\n Voyons maintenant cette m\u00eame requ\u00eate en version 13. Le plan d’ex\u00e9cution sera le suivant<\/p>\n <\/p>\n <\/p>\n On ram\u00e8ne ces 10 lignes en effectuant du tri incr\u00e9mental sur seulement 100 000 lignes avec un temps de parsing de 0,2400ms.<\/p>\n A l’ex\u00e9cution, le r\u00e9sultat est sans appel :<\/p>\n <\/p>\n Moins de 1ms alors que sur la version 12, nous \u00e9tions \u00e0 plus de 400ms.<\/p>\n <\/p>\n <\/p>\n Une nouvelle fonctionnalit\u00e9 \u00e9galement apparu avec la version 13, c’est l’agr\u00e9gation des valeurs en doublons dans les index. En quelque sorte, nous ne r\u00e9p\u00e9tons pas, dans l’index, chacune des valeurs en double, nous faisons de la d\u00e9duplication ! Prenons notre champs “bid” de notre table “pgbench_accounts”. Nous savons que celui ci comporte le num\u00e9ro de branche pour chaque “account id” de notre table. Nous aurons donc des valeurs qui vont se r\u00e9p\u00e9ter sur chaque partition. Dans notre exemple, nous travaillerons par partition.<\/p>\n PostgreSQL 12.<\/span><\/strong><\/p>\n Cr\u00e9ons 2 index sur la partition 1 de notre table “pgbench_accounts”. <\/p>\n <\/p>\n On sait que pour “bid”, la valeur 1 se r\u00e9p\u00e8te pour cette partition 1<\/p>\n <\/p>\n On passe les stats sur cette partition<\/p>\n Voyons maintenant les tailles de chacun de ces index<\/p>\n Nous avons donc 2 index sur cette table, champs “aid” et “bid”, qui font \u00e0 peu pr\u00e8s la m\u00eame taille, soit 2Mo.<\/p>\n <\/p>\n PostgreSQL 13<\/span><\/strong><\/p>\n Effectuons la m\u00eame op\u00e9ration sur PostgreSQL 13<\/p>\n <\/p>\n Regardons les tailles de chacun des index<\/p>\n <\/p>\n On voit que l’index sur “aid” fait toujours environ 2Mo, en effet, celui ci travaille sur des valeurs distinctes car c’est une contrainte unique sur ce champs.<\/p>\n En revanche, pour “bid”, comme nous n’avons que des 1 en valeur, celui ci ne fait plus que 600ko, PostgreSQL sait agr\u00e9ger la valeur 1 et la d\u00e9duplique afin d’optimiser la place disque.<\/p>\n <\/p>\n <\/p>\n Dans vos scripts de maintenance PostgreSQL, vous utilisez tr\u00e8s certainement des appels aux outils tels “pg_basebackup” et “pg_archivecleanup” pour g\u00e9rer les sauvegardes de bases et des WALs associ\u00e9s. L’outil “pg_verifybackup” va effectuer des op\u00e9rations de checksum dans le backup \u00e0 la mani\u00e8re d’un RESTORE VERIFY ONLY pour SQL Server. C’est pour cela qu’il est n\u00e9cessaire de faire un backup via “pg_basebackup” en mode “plain” et non pas tar.<\/p>\n Effectuons un backup de notre instance PostgreSQL 13, mode “plain”, avec les WALs en mode fetch.<\/p>\n <\/p>\n S’il l’on regarde le contenu du r\u00e9pertoire, nous avons les fichiers PostgreSQL classiques comme nous savons le trouver dans un backup en mode “plain”. Ce fichier est \u00e9ditable, nous avons toutes les informations sur les diff\u00e9rents fichiers composants notre instance. Le nom du fichier, la date de modification, l’algorithme de checksum, sa taille, et la valeur du checksum. <\/p>\n Ceci ressemble fortement \u00e0 un formatage JSON qui sera exploit\u00e9 par “pg_verifybackup”, afin de valider notre sauvegarde. <\/p>\n Attention, il est possible que vous ayez une erreur lors de la v\u00e9rification des WALs, en effet, “pg_verifybackup” s’appuie sur l’outil “pg_waldump” pour chercher les WALs dans la sauvegarde, selon la Timeline en cours. <\/p>\n L’erreur que vous pourriez rencontr\u00e9 est la suivante :<\/p>\n D’autant plus, si vous \u00eates en mode “archive_mode” avec un “archive_command” renseign\u00e9 qui envoie les WALs vers un r\u00e9pertoire d’archive. <\/p>\n <\/p>\n Merci \u00e0 vous !<\/p>\n N’h\u00e9sitez pas \u00e0 laisser un commentaire.<\/p>\n La nouvelle release officielle de PostgreSQL est sorti le 24 septembre 2020. Place \u00e0 la version 13. Celle ci est bien entendu, t\u00e9l\u00e9chargeable sur le site PostgreSQL.org sur lequel vous pourrez retrouver les sources, les derniers packages.… Continuer la lecture
\nIl sera aussi, comme pour les autres versions, possible de mettre \u00e0 jour vos fichiers “sources” pour les utilisateurs Linux et profiter de cette nouvelle version.<\/p>\nLes nouveaut\u00e9s<\/h2>\n
\nNotons cette liste, non exhaustive, des quelques am\u00e9liorations dans les domaines suivants :<\/p>\n\n
\n
\n
\n
Quelques exemples d’am\u00e9liorations<\/h2>\n
Le paritionning g\u00e9n\u00e9r\u00e9 par PgBench<\/span><\/h3>\n
[postgres@pg13]$ pgbench --help | grep -i partition<\/span>\r\n--partition-method<\/span>=(range|hash)\r\npartition pgbench_accounts with this method (default: range)\r\n--partitions<\/span>=NUM partition pgbench_accounts into NUM parts (default: 0)<\/pre>\n[postgres@pg13]$ pgbench -i -s 10 --partitions=10 --partition-method=range --foreign-keys -p 5433 manuelo<\/span>\r\ndropping old tables...\r\nNOTICE: table \"pgbench_accounts\" does not exist, skipping\r\nNOTICE: table \"pgbench_branches\" does not exist, skipping\r\nNOTICE: table \"pgbench_history\" does not exist, skipping\r\nNOTICE: table \"pgbench_tellers\" does not exist, skipping\r\ncreating tables...\r\ncreating 10 partitions...\r\ngenerating data (client-side)...\r\n1000000 of 1000000 tuples (100%) done (elapsed 5.75 s, remaining 0.00 s)\r\nvacuuming...\r\ncreating primary keys...\r\ncreating foreign keys...\r\ndone in 9.51 s (drop tables 0.00 s, create tables 0.04 s, client-side generate 5.80 s, vacuum 0.49 s, primary keys 2.80 s, foreign keys 0.37 s).<\/pre>\nmanuelo=# \\dt+<\/span>\r\nList of relations\r\nSchema | Name | Type | Owner | Persistence | Size | Description\r\n--------+---------------------+-------------------+----------+-------------+------------+-------------\r\npublic | pgbench_accounts | partitioned table | postgres | permanent | 0 bytes |\r\npublic | pgbench_accounts_1 | table | postgres | permanent | 13 MB |\r\npublic | pgbench_accounts_10 | table | postgres | permanent | 13 MB |\r\npublic | pgbench_accounts_2 | table | postgres | permanent | 13 MB |\r\npublic | pgbench_accounts_3 | table | postgres | permanent | 13 MB |\r\npublic | pgbench_accounts_4 | table | postgres | permanent | 13 MB |\r\npublic | pgbench_accounts_5 | table | postgres | permanent | 13 MB |\r\npublic | pgbench_accounts_6 | table | postgres | permanent | 13 MB |\r\npublic | pgbench_accounts_7 | table | postgres | permanent | 13 MB |\r\npublic | pgbench_accounts_8 | table | postgres | permanent | 13 MB |\r\npublic | pgbench_accounts_9 | table | postgres | permanent | 13 MB |\r\npublic | pgbench_branches | table | postgres | permanent | 40 kB |\r\npublic | pgbench_history | table | postgres | permanent | 0 bytes |\r\npublic | pgbench_tellers | table | postgres | permanent | 40 kB |<\/pre>\n[postgres@pg13<\/strong><\/span>]$ pg_dump -v -f manuelo.dmp -n public manuelo\r\n[postgres@pg12<\/strong><\/span>]$ psql -d manuelo -f manuelo.dmp\r\n\r\n<\/pre>\nLe FETCH FIRST WITH TIES \u00e7a apporte quoi au juste ?<\/span><\/h3>\n
\nEn version postgreSQL 12, on connaissait d\u00e9j\u00e0 le FETCH FIRST n ROWS ONLY. Voyons ce que peut apporter le WITH TIES.<\/p>\nPostgreSQL 12<\/span><\/h4>\n
manuelo=# show server_version;\r\nserver_version\r\n----------------\r\n12.0\r\n(1 row)<\/pre>\n
manuelo=# \\d+ pgbench_accounts\r\nPartitioned table \"public.pgbench_accounts\"\r\nColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description\r\n----------+---------------+-----------+----------+---------+----------+--------------+-------------\r\naid | integer | | not null | | plain | |\r\nbid | integer | | | | plain | |\r\nabalance | integer | | | | plain | |\r\nfiller | character(84) | | | | extended | |\r\nPartition key: RANGE (aid)\r\nIndexes:\r\n\"pgbench_accounts_pkey\" PRIMARY KEY, btree (aid)\r\nForeign-key constraints:\r\n\"pgbench_accounts_bid_fkey\" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)\r\nReferenced by:\r\nTABLE \"pgbench_history\" CONSTRAINT \"pgbench_history_aid_fkey\" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)\r\nPartitions: pgbench_accounts_1 FOR VALUES FROM (MINVALUE) TO (100001),\r\npgbench_accounts_10 FOR VALUES FROM (900001) TO (MAXVALUE),\r\npgbench_accounts_2 FOR VALUES FROM (100001) TO (200001),\r\npgbench_accounts_3 FOR VALUES FROM (200001) TO (300001),\r\npgbench_accounts_4 FOR VALUES FROM (300001) TO (400001),\r\npgbench_accounts_5 FOR VALUES FROM (400001) TO (500001),\r\npgbench_accounts_6 FOR VALUES FROM (500001) TO (600001),\r\npgbench_accounts_7 FOR VALUES FROM (600001) TO (700001),\r\npgbench_accounts_8 FOR VALUES FROM (700001) TO (800001),\r\npgbench_accounts_9 FOR VALUES FROM (800001) TO (900001)<\/pre>\n
manuelo=# select aid , bid from pgbench_accounts order by bid desc fetch first 10 rows only;\r\naid | bid\r\n--------+-----\r\n900002 | 10\r\n900003 | 10\r\n900004 | 10\r\n900005 | 10\r\n900006 | 10\r\n900007 | 10\r\n900008 | 10\r\n900009 | 10\r\n900010 | 10\r\n900001 | 10\r\n(10 rows)<\/pre>\n
manuelo=# explain analyze select aid , bid from pgbench_accounts order by bid desc fetch first 10 rows only;\r\nQUERY PLAN\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nLimit (cost=34369.82..34370.98 rows=10 width=8) (actual time=2736.423..2736.528 rows=10 loops=1)\r\n-> Gather Merge (cost=34369.82..131599.60 rows=833340 width=8) (actual time=2736.421..2736.495 rows=10 loops=1)\r\nWorkers Planned: 2\r\nWorkers Launched: 2\r\n-> Sort (cost=33369.79..34411.47 rows=416670 width=8) (actual time=2681.387..2681.394 rows=10 loops=3)\r\nSort Key: pgbench_accounts_1.bid DESC\r\nSort Method: top-N heapsort Memory: 25kB\r\nWorker 0: Sort Method: top-N heapsort Memory: 25kB\r\nWorker 1: Sort Method: top-N heapsort Memory: 25kB\r\n-> Parallel Append (cost=0.00..24365.70 rows=416670 width=8) (actual time=0.012..1945.815 rows=333333 loops=3)\r\n-> Parallel Seq Scan on pgbench_accounts_1 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.006..240.764 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_2 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.013..296.642 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_3 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.032..220.039 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_4 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.012..254.346 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_5 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.025..202.572 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_6 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.010..296.606 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_7 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.014..69.118 rows=33333 loops=3)\r\n-> Parallel Seq Scan on pgbench_accounts_8 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.006..184.851 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_9 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.007..250.804 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_10 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.010..231.848 rows=100000 loops=1)\r\nPlanning Time: 0.186 ms\r\nExecution Time: 2736.588 ms\r\n(22 rows)\r\n\r\nTime: 2737.157 ms (00:02.737)<\/pre>\n
manuelo=# explain analyze select aid , bid from (select aid , bid , rank() over (order by bid desc) from pgbench_accounts) as acc where rank <= 10 order by bid desc;\r\nQUERY PLAN\r\n---------------------------------------------------------------------------------------------------------------------------------------------------\r\nSubquery Scan on acc (cost=144732.34..174732.34 rows=333333 width=8) (actual time=4288.950..9857.908 rows=100000 loops=1)\r\nFilter: (acc.rank <= 10)\r\nRows Removed by Filter: 900000\r\n-> WindowAgg (cost=144732.34..162232.34 rows=1000000 width=16) (actual time=4288.946..9109.634 rows=1000000 loops=1)\r\n-> Sort (cost=144732.34..147232.34 rows=1000000 width=8) (actual time=2834.834..3555.152 rows=1000000 loops=1)\r\nSort Key: pgbench_accounts_1.bid DESC\r\nSort Method: external merge Disk: 17696kB<\/strong>\r\n-> Append (cost=0.00..31400.00 rows=1000000 width=8) (actual time=0.014..2052.294 rows=1000000 loops=1)\r\n-> Seq Scan on pgbench_accounts_1 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.012..75.332 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_2 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.012..75.270 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_3 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.012..77.936 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_4 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.024..76.067 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_5 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.010..78.318 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_6 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.019..81.886 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_7 (cost=0.00..2640.00 rows=100000 width=8) (actual time=1.330..82.675 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_8 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.010..77.019 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_9 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.011..76.075 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_10 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.011..76.073 rows=100000 loops=1)\r\nPlanning Time: 0.232 ms\r\nExecution Time: 9924.785 ms\r\n(20 rows)\r\n\r\nTime: 9925.463 ms (00:09.925)<\/pre>\n
PostgreSQL 13<\/span><\/h4>\n
manuelo=# show server_version;\r\nserver_version\r\n----------------\r\n13.0\r\n(1 row)<\/pre>\n
manuelo=# select aid , bid from pgbench_accounts order by bid desc fetch first 10 rows only;\r\naid | bid\r\n--------+-----\r\n900002 | 10\r\n900003 | 10\r\n900004 | 10\r\n900005 | 10\r\n900006 | 10\r\n900007 | 10\r\n900008 | 10\r\n900009 | 10\r\n900010 | 10\r\n900001 | 10\r\n(10 rows)<\/pre>\n
manuelo=# explain analyze select aid , bid from pgbench_accounts order by bid desc fetch first 10 rows only;\r\nQUERY PLAN\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nLimit (cost=34369.82..34370.98 rows=10 width=8) (actual time=2713.365..2713.483 rows=10 loops=1)\r\n-> Gather Merge (cost=34369.82..131599.60 rows=833340 width=8) (actual time=2713.362..2713.465 rows=10 loops=1)\r\nWorkers Planned: 2\r\nWorkers Launched: 2\r\n-> Sort (cost=33369.79..34411.47 rows=416670 width=8) (actual time=2691.259..2691.275 rows=10 loops=3)\r\nSort Key: pgbench_accounts.bid DESC\r\nSort Method: top-N heapsort Memory: 25kB\r\nWorker 0: Sort Method: top-N heapsort Memory: 25kB\r\nWorker 1: Sort Method: top-N heapsort Memory: 25kB\r\n-> Parallel Append (cost=0.00..24365.70 rows=416670 width=8) (actual time=0.010..1921.334 rows=333333 loops=3)\r\n-> Parallel Seq Scan on pgbench_accounts_1 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.006..275.988 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_2 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.009..187.432 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_3 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.006..186.142 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_4 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.005..206.952 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_5 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.010..164.090 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_6 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.007..163.891 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_7 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.007..91.653 rows=33333 loops=3)\r\n-> Parallel Seq Scan on pgbench_accounts_8 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.004..140.797 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_9 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.005..184.767 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_10 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.008..165.512 rows=100000 loops=1)\r\nPlanning Time: 0.201 ms\r\nExecution Time: 2713.549 ms\r\n(22 rows)<\/pre>\n
manuelo=# explain analyze select aid , bid from (select aid , bid , rank() over (order by bid desc) from pgbench_accounts) as acc where rank <= 10 order by bid desc;\r\nQUERY PLAN\r\n---------------------------------------------------------------------------------------------------------------------------------------------------\r\nSubquery Scan on acc (cost=131057.84..161057.84 rows=333333 width=8) (actual time=2962.088..5939.797 rows=100000 loops=1)\r\nFilter: (acc.rank <= 10)\r\nRows Removed by Filter: 900000\r\n-> WindowAgg (cost=131057.84..148557.84 rows=1000000 width=16) (actual time=2962.085..5234.609 rows=1000000 loops=1)\r\n-> Sort (cost=131057.84..133557.84 rows=1000000 width=8) (actual time=2816.923..3508.142 rows=1000000 loops=1)\r\nSort Key: pgbench_accounts.bid DESC\r\nSort Method: external merge Disk: 17624kB<\/strong>\r\n-> Append (cost=0.00..31400.00 rows=1000000 width=8) (actual time=0.011..2029.690 rows=1000000 loops=1)\r\n-> Seq Scan on pgbench_accounts_1 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.009..78.495 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_2 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.009..77.523 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_3 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.008..82.124 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_4 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.009..76.659 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_5 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.032..82.713 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_6 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.006..76.210 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_7 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.012..75.231 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_8 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.011..74.480 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_9 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.010..74.386 rows=100000 loops=1)\r\n-> Seq Scan on pgbench_accounts_10 (cost=0.00..2640.00 rows=100000 width=8) (actual time=0.011..77.242 rows=100000 loops=1)\r\nPlanning Time: 0.216 ms\r\nExecution Time: 6006.397 ms\r\n(20 rows)<\/pre>\n
\nMaintenant, utilisons la nouvelle m\u00e9thode FETCH FIRST WITH TIES pour nous retourner le m\u00eame r\u00e9sultat de donn\u00e9es.<\/p>\nmanuelo=# explain analyze select aid , bid from pgbench_accounts order by bid desc fetch first 10 rows with ties;\r\nQUERY PLAN\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nLimit (cost=34369.82..34370.98 rows=10 width=8) (actual time=2741.817..3067.730 rows=100000 loops=1)\r\n-> Gather Merge (cost=34369.82..131599.60 rows=833340 width=8) (actual time=2741.814..2941.271 rows=100001 loops=1)\r\nWorkers Planned: 2\r\nWorkers Launched: 2\r\n-> Sort (cost=33369.79..34411.47 rows=416670 width=8) (actual time=2714.034..2736.566 rows=34426 loops=3)\r\nSort Key: pgbench_accounts.bid DESC\r\nSort Method: quicksort Memory: 28076kB\r\nWorker 0: Sort Method: quicksort Memory: 27841kB\r\nWorker 1: Sort Method: quicksort Memory: 27824kB<\/strong>\r\n-> Parallel Append (cost=0.00..24365.70 rows=416670 width=8) (actual time=0.010..1973.108 rows=333333 loops=3)\r\n-> Parallel Seq Scan on pgbench_accounts_1 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.006..254.568 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_2 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.011..208.208 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_3 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.007..207.545 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_4 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.007..207.043 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_5 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.005..229.658 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_6 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.005..163.504 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_7 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.008..80.356 rows=33333 loops=3)\r\n-> Parallel Seq Scan on pgbench_accounts_8 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.004..273.048 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_9 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.004..173.430 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_10 (cost=0.00..2228.24 rows=58824 width=8) (actual time=0.008..162.951 rows=100000 loops=1)\r\nPlanning Time: 0.196 ms\r\nExecution Time: 3129.996 ms\r\n(22 rows)<\/pre>\n
\nSur la version PostgreSQL 12, nous avions m\u00eame un temps d’ex\u00e9cution d’environ 9 secondes.
\nDe plus, on voit que tous nos tris se font avec 2 workers, et en m\u00e9moire\u00a0 — > Sort Method: quicksort Memory: 28076kB<\/span><\/p>\nLe tri incr\u00e9mental (incremental sort), optimiser les tris dans vos jointures !<\/span><\/h3>\n
\nAvec un exemple concret, ce sera bien mieux :<\/p>\nA\u00a0 \u00a0B\r\n--\u00a0 --\r\n25\u00a0 65\r\n25\u00a0 77678\r\n25\u00a0 90\r\n32\u00a0 89\r\n32 \u00a0176889\r\n32 7\r\n55 89\r\n55 779<\/pre>\n
25,65 25,77678 25,90\r\n32,89 32,176889 32,7\r\n55,89 55,779<\/pre>\n
manuelo=# select a.aid, b.bid, a.abalance from pgbench_accounts a,pgbench_branches b \r\nwhere a.bid=b.bid order by a.aid desc,b.bid desc fetch first 10 rows only;\r\naid | bid | abalance\r\n---------+-----+----------\r\n1000000 | 10 | 0\r\n999999 | 10 | 0\r\n999998 | 10 | 0\r\n999997 | 10 | 0\r\n999996 | 10 | 0\r\n999995 | 10 | 0\r\n999994 | 10 | 0\r\n999993 | 10 | 0\r\n999992 | 10 | 0\r\n999991 | 10 | 0\r\n(10 rows)\r\n\r\nTime: 429.901 ms<\/pre>\n
manuelo=# explain analyze select a.aid, b.bid, a.abalance from pgbench_accounts a,pgbench_branches b \r\nwhere a.bid=b.bid order by a.aid desc,b.bid desc fetch first 10 rows only;<\/pre>\n
QUERY PLAN\r\n-----------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nLimit (cost=35928.28..35929.45 rows=10 width=12) (actual time=4207.405..4207.509 rows=10 loops=1)\r\n-> Gather Merge (cost=35928.28..133157.37 rows=833334 width=12) (actual time=4207.402..4207.479 rows=10 loops=1)\r\nWorkers Planned: 2\r\nWorkers Launched: 2\r\n-> Sort (cost=34928.26..35969.92 rows=416667 width=12) (actual time=4186.212..4186.219 rows=10 loops=3)<\/strong>\r\nSort Key: a.aid DESC, b.bid DESC\r\nSort Method: top-N heapsort Memory: 25kB\r\nWorker 0: Sort Method: top-N heapsort Memory: 25kB\r\nWorker 1: Sort Method: top-N heapsort Memory: 25kB\r\n-> Hash Join (cost=1.23..25924.23 rows=416667 width=12) (actual time=3.593..3388.957 rows=333333 loops=3)\r\nHash Cond: (a.bid = b.bid)\r\n-> Parallel Append (cost=0.00..24365.70 rows=416670 width=12) (actual time=3.483..1976.369 rows=333333 loops=3)\r\n-> Parallel Seq Scan on pgbench_accounts_1 a (cost=0.00..2228.24 rows=58824 width=12) (actual time=0.007..187.719 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_2 a_1 (cost=0.00..2228.24 rows=58824 width=12) (actual time=10.429..277.525 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_3 a_2 (cost=0.00..2228.24 rows=58824 width=12) (actual time=10.436..232.550 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_4 a_3 (cost=0.00..2228.24 rows=58824 width=12) (actual time=0.005..253.731 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_5 a_4 (cost=0.00..2228.24 rows=58824 width=12) (actual time=0.012..187.681 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_6 a_5 (cost=0.00..2228.24 rows=58824 width=12) (actual time=10.865..234.133 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_7 a_6 (cost=0.00..2228.24 rows=58824 width=12) (actual time=3.713..88.257 rows=33333 loops=3)\r\n-> Parallel Seq Scan on pgbench_accounts_8 a_7 (cost=0.00..2228.24 rows=58824 width=12) (actual time=0.004..253.161 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_9 a_8 (cost=0.00..2228.24 rows=58824 width=12) (actual time=0.005..230.784 rows=100000 loops=1)\r\n-> Parallel Seq Scan on pgbench_accounts_10 a_9 (cost=0.00..2228.24 rows=58824 width=12) (actual time=0.007..120.950 rows=100000 loops=1)\r\n-> Hash (cost=1.10..1.10 rows=10 width=4) (actual time=0.029..0.030 rows=10 loops=3)\r\nBuckets: 1024 Batches: 1 Memory Usage: 9kB\r\n-> Seq Scan on pgbench_branches b (cost=0.00..1.10 rows=10 width=4) (actual time=0.009..0.017 rows=10 loops=3)\r\nPlanning Time: 0.335 ms\r\nExecution Time: 4207.612 ms\r\n(27 rows)<\/pre>\n
manuelo=# explain analyze select a.aid, b.bid, a.abalance from pgbench_accounts a,pgbench_branches b \r\nwhere a.bid=b.bid order by a.aid desc,b.bid desc fetch first 10 rows only;\r\nQUERY PLAN\r\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nLimit (cost=3.12..5.42 rows=10 width=12) (actual time=0.229..0.256 rows=10 loops=1)\r\n-> Incremental Sort (cost=3.12..230201.32 rows=1000000 width=12) (actual time=0.227..0.241 rows=10 loops=1)<\/strong>\r\nSort Key: a.aid DESC, b.bid DESC\r\nPresorted Key: a.aid\r\nFull-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB\r\n-> Nested Loop (cost=2.92..185201.32 rows=1000000 width=12) (actual time=0.050..0.218 rows=11 loops=1)\r\nJoin Filter: (a.bid = b.bid)\r\nRows Removed by Join Filter: 99\r\n-> Append (cost=2.92..47472.92 rows=1000000 width=12) (actual time=0.013..0.036 rows=11 loops=1)\r\n-> Index Scan Backward using pgbench_accounts_10_pkey on pgbench_accounts_10 a_10 (cost=0.29..4247.29 rows=100000 width=12) (actual time=0.011..0.020 rows=11 loops=1)\r\n-> Index Scan Backward using pgbench_accounts_9_pkey on pgbench_accounts_9 a_9 (cost=0.29..4247.29 rows=100000 width=12) (never executed)\r\n-> Index Scan Backward using pgbench_accounts_8_pkey on pgbench_accounts_8 a_8 (cost=0.29..4247.29 rows=100000 width=12) (never executed)\r\n-> Index Scan Backward using pgbench_accounts_7_pkey on pgbench_accounts_7 a_7 (cost=0.29..4247.29 rows=100000 width=12) (never executed)\r\n-> Index Scan Backward using pgbench_accounts_6_pkey on pgbench_accounts_6 a_6 (cost=0.29..4247.29 rows=100000 width=12) (never executed)\r\n-> Index Scan Backward using pgbench_accounts_5_pkey on pgbench_accounts_5 a_5 (cost=0.29..4247.29 rows=100000 width=12) (never executed)\r\n-> Index Scan Backward using pgbench_accounts_4_pkey on pgbench_accounts_4 a_4 (cost=0.29..4247.29 rows=100000 width=12) (never executed)\r\n-> Index Scan Backward using pgbench_accounts_3_pkey on pgbench_accounts_3 a_3 (cost=0.29..4247.29 rows=100000 width=12) (never executed)\r\n-> Index Scan Backward using pgbench_accounts_2_pkey on pgbench_accounts_2 a_2 (cost=0.29..4247.29 rows=100000 width=12) (never executed)\r\n-> Index Scan Backward using pgbench_accounts_1_pkey on pgbench_accounts_1 a_1 (cost=0.29..4247.29 rows=100000 width=12) (never executed)\r\n-> Materialize (cost=0.00..1.15 rows=10 width=4) (actual time=0.001..0.008 rows=10 loops=11)\r\n-> Seq Scan on pgbench_branches b (cost=0.00..1.10 rows=10 width=4) (actual time=0.005..0.012 rows=10 loops=1)\r\nPlanning Time: 0.328 ms\r\nExecution Time: 0.308 ms\r\n(23 rows)<\/pre>\n
manuelo=# select a.aid, b.bid, a.abalance from pgbench_accounts a,pgbench_branches b \r\nwhere a.bid=b.bid order by a.aid desc,b.bid desc fetch first 10 rows only;\r\naid | bid | abalance\r\n---------+-----+----------\r\n1000000 | 10 | 0\r\n999999 | 10 | 0\r\n999998 | 10 | 0\r\n999997 | 10 | 0\r\n999996 | 10 | 0\r\n999995 | 10 | 0\r\n999994 | 10 | 0\r\n999993 | 10 | 0\r\n999992 | 10 | 0\r\n999991 | 10 | 0\r\n(10 rows)\r\n\r\nTime: 0.895 ms<\/pre>\n
Pourquoi certains de mes index B-Tree prennent moins de place en PostgreSQL 13 ?<\/span><\/h4>\n
\nVoyons comment ca marche.<\/p>\n
\n– 1 index pour le champs “aid”
\n– 1 index pour le champs “bid”<\/p>\nmanuelo=# \\d pgbench_accounts\r\nPartitioned table \"public.pgbench_accounts\"\r\nColumn | Type | Collation | Nullable | Default\r\n----------+---------------+-----------+----------+---------\r\naid | integer | | not null |\r\nbid | integer | | |\r\nabalance | integer | | |\r\nfiller | character(84) | | |\r\nPartition key: RANGE (aid)<\/pre>\n
manuelo=# select * from pgbench_accounts_1 limit 10;\r\naid | bid | abalance | filler\r\n-----+-----+----------+--------------------------------------------------------------------------------------\r\n1 | 1 | 0 |\r\n2 | 1 | 0 |\r\n3 | 1 | 0 |\r\n4 | 1 | 0 |\r\n5 | 1 | 0 |\r\n6 | 1 | 0 |\r\n7 | 1 | 0 |\r\n8 | 1 | 0 |\r\n9 | 1 | 0 |\r\n10 | 1 | 0 |\r\n(10 rows)<\/pre>\n
manuelo=# create index account_id_1 on pgbench_accounts_1(aid);\r\nCREATE INDEX\r\n\r\n\r\nmanuelo=# create index account_bid_1 on pgbench_accounts_1(bid);\r\nCREATE INDEX<\/pre>\n
manuelo=# vacuum analyse pgbench_accounts_1;\r\nVACUUM<\/pre>\n
manuelo=# select pg_table_size('account_id_1')\/1024 as \"Taille aid Ko\";\r\nTaille aid Ko\r\n-----------\r\n2208\r\n\r\n\r\nmanuelo=# select pg_table_size('account_bid_1')\/1024 as \"Taille bid Ko\";\r\nTaille bid Ko\r\n-------------\r\n2224<\/pre>\nmanuelo=# create index account_bid_1 on pgbench_accounts_1(bid);\r\nCREATE INDEX\r\n\r\n\r\nmanuelo=# create index account_id_1 on pgbench_accounts_1(aid);\r\nCREATE INDEX\r\n\r\n\r\nmanuelo=# vacuum analyse pgbench_accounts_1;\r\nVACUUM<\/pre>\n
manuelo=# select pg_table_size('account_id_1')\/1024 as \"Taille aid Ko\";\r\nTaille aid Ko\r\n---------------\r\n2208\r\n(1 row)\r\n\r\nmanuelo=# select pg_table_size('account_bid_1')\/1024 as \"Taille bid Ko\";\r\nTaille bid Ko\r\n---------------\r\n696\r\n(1 row)<\/pre>\nV\u00e9rifier l’int\u00e9grit\u00e9 de ses backup<\/span><\/h4>\n
\nIl faudra maintenant compter sur un nouvel outil pour v\u00e9rifier l’int\u00e9grit\u00e9 de vos sauvegardes, qui est “pg_verifybackup”.<\/p>\n[postgres@pg13]$ mkdir -p \/data\/postgres\/backup\/bkp_PG13\r\n[postgres@pg13]$ pg_basebackup -D \/data\/postgres\/backup\/bkp_PG13 -Fp -Xf -v -P\r\npg_basebackup: initiating base backup, waiting for checkpoint to complete\r\npg_basebackup: checkpoint completed\r\npg_basebackup: write-ahead log start point: 0\/B4000028 on timeline 2\r\n204392\/204392 kB (100%), 1\/1 tablespace\r\npg_basebackup: write-ahead log end point: 0\/B4000100\r\npg_basebackup: syncing data to disk ...\r\npg_basebackup: renaming backup_manifest.tmp to backup_manifest\r\npg_basebackup: base backup completed\r\n\r\n<\/pre>\n
\nNous trouvons maintenant un fichier nomm\u00e9 “backup_manifest”.<\/p>\n
\nVoici un extrait de celui ci :<\/p>\n[postgres@PG13]$ head -n 15 backup_manifest\r\n{ \"PostgreSQL-Backup-Manifest-Version\": 1,\r\n\"Files\": [\r\n{ \"Path\": \"backup_label\", \"Size\": 226, \"Last-Modified\": \"2020-10-16 15:59:48 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"ea4e7ad9\" },\r\n{ \"Path\": \"current_logfiles\", \"Size\": 30, \"Last-Modified\": \"2020-10-16 09:19:39 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"f6838adf\" },\r\n{ \"Path\": \"pg_multixact\/members\/0000\", \"Size\": 8192, \"Last-Modified\": \"2020-10-12 16:20:35 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"23464490\" },\r\n{ \"Path\": \"pg_multixact\/offsets\/0000\", \"Size\": 8192, \"Last-Modified\": \"2020-10-16 09:24:39 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"23464490\" },\r\n{ \"Path\": \"base\/16403\/16576\", \"Size\": 8192, \"Last-Modified\": \"2020-10-12 16:56:22 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"4fbeb31c\" },\r\n{ \"Path\": \"base\/16403\/16659\", \"Size\": 2260992, \"Last-Modified\": \"2020-10-13 15:41:00 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"0fc9222c\" },\r\n{ \"Path\": \"base\/16403\/16461\", \"Size\": 16384, \"Last-Modified\": \"2020-10-13 15:44:15 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"0e627e6f\" },\r\n{ \"Path\": \"base\/16403\/3381\", \"Size\": 0, \"Last-Modified\": \"2020-10-12 16:20:35 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"00000000\" },\r\n{ \"Path\": \"base\/16403\/16475\", \"Size\": 16384, \"Last-Modified\": \"2020-10-12 16:55:58 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"ccdbb572\" },\r\n{ \"Path\": \"base\/16403\/16489\", \"Size\": 8192, \"Last-Modified\": \"2020-10-12 16:56:01 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"82a0e924\" },\r\n{ \"Path\": \"base\/16403\/2602_fsm\", \"Size\": 24576, \"Last-Modified\": \"2020-10-12 16:20:35 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"5786bbba\" },\r\n{ \"Path\": \"base\/16403\/16648_fsm\", \"Size\": 24576, \"Last-Modified\": \"2020-10-13 15:41:02 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"9e571b03\" },\r\n{ \"Path\": \"base\/16403\/16497\", \"Size\": 16384, \"Last-Modified\": \"2020-10-13 15:44:16 GMT\", \"Checksum-Algorithm\": \"CRC32C\", \"Checksum\": \"bb227b8f\" },<\/pre>\n
\nOn lance la commande :<\/p>\n[postgres@pg13]$ pg_verifybackup -e \/data\/postgres\/backup\/bkp_PG13\r\n\r\n<\/pre>\n
\nCeci pourra se v\u00e9rifier dans le fichier manifest :<\/p>\n\"WAL-Ranges\": [\r\n{ \"Timeline\": 2, \"Start-LSN\": \"0\/9B0000A0\", \"End-LSN\": \"0\/C1000100\" }\r\n],<\/pre>\npg_waldump: fatal: could not find file \"00000002000000000000009B\": No such file or directory\r\npg_verifybackup: error: WAL parsing failed for timeline 2<\/pre>\n
\nEn effet, “pg_basebackup” ne sauvegarde pas les archives envoy\u00e9s vers un r\u00e9pertoire autre que ce qu’il y’a dans “pg_wal”.
\nSi vous souhaitez \u00e9viter de contr\u00f4ler tous les WALs li\u00e9s \u00e0 cette ‘timeline’ courante, il sera possible de passer la commande avec l’option “-n” :<\/p>\n[postgres@ip-172-44-2-98 pg_wal]$ pg_verifybackup --help | grep \"wal\"\r\n-n, --no-parse-wal do not try to parse WAL files<\/pre>\n
[postgres@ip-172-44-2-98 pg_wal]$ pg_verifybackup -e \/data\/postgres\/backup\/bkp_PG13 -n\r\nbackup successfully verified<\/span><\/pre>\n<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"