Dump/restore done. Now what? | Postgres.FM 193 | #PostgreSQL #Postgres podcast

pg_restore finished with no errors, and all rows are back. Is the database ready for production traffic? One query on a 31 GiB table (Postgres 18.6, autovacuum off, cold cache, median of 9 runs): 32.6 s right after pg_restore, 23 ms at the end. Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic — what pg_dump and pg_restore leave behind, why autovacuum may not catch up in time, and how Postgres itself could do better. [Check out the subtitles – we edit them! YouTube auto-translates them from English to other languages; try it and share it with people interested in Postgres!] Chapters: 00:00 Intro 00:59 Hacking Postgres sessions; the question: is a dump/restore enough? 05:46 What a dump contains, and what is missing after pg_restore 08:33 Statistics and the visibility map 11:27 Hint bits: xmin, xmax, and writes on standbys 15:16 "Just run VACUUM ANALYZE": why pg_restore does not do it 17:33 Docs audit: 28 pages, three that mention VACUUM 18:36 Will autovacuum fix it? Partitioned parents 21:35 Proposal: a --vacuum-analyze option for pg_restore 24:39 vacuumdb, agents, and a reminder line in dumps 29:05 Kirk's story: daily restores from dumps after moving from Oracle 32:09 Postgres 18 --statistics and the relallvisible trap 38:21 pg_restore TRUNCATE + COPY, and COPY FREEZE 42:43 The robot says: wrap up 43:20 The first read writes; Postgres 19 read-time visibility map 45:16 Takeaways Key links (video time of the first mention): Hacking Postgres sessions with Andrey, Kirk and Nik, YouTube playlist (0:34)    • Hacking Postgres   Hacking Postgres website (0:34) https://hacking.postgres.tv Hacking Postgres Telegram group (0:34) https://t.me/postgres_hacking Our previous episode with Kirk: GSoC B-Tree Merge (0:34) https://postgres.fm/episodes/gsoc-btr... Is pg_dump a backup tool? (3:37) https://postgres.fm/episodes/is-pg_du... Commit: transfer statistics during pg_upgrade, adds statistics to pg_dump, PG18 (4:53) https://github.com/postgres/postgres/... pg_restore --include-analyze proposal, Jian He, 2025 (15:53) https://www.postgresql.org/message-id... ANALYZE after restore, Christopher Kings-Lynne, 2002 (30:27) https://www.postgresql.org/message-id... Commit: vacuumdb --analyze-only processes partitioned tables, Laurenz Albe, PG19 (30:27) https://github.com/postgres/postgres/... Statistics Import and Export, PG18 statistics transfer (32:09) https://www.postgresql.org/message-id... Commit: set pd_prune_xid on insert, Melanie Plageman, PG19 (42:52) https://github.com/postgres/postgres/... All links: https://postgres.fm/episodes/dump-res... ~~~ What did you like or not like? What should we discuss next time? Let us know in the comments, or by tweeting us on @postgresfm   / postgresfm  , @samokhvalov   / samokhvalov   and @michristofides   / michristofides   ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard https://pgmustard.com/ Nikolay Samokhvalov, founder of PostgresAI https://postgres.ai/ With credit to: Jessie Draws for the elephant artwork Intro and outro music: our own arrangement of Apple Loops from GarageBand ~~~ This is the video version. Check out https://postgres.fm to subscribe to the audio-only version, to see the transcript, guest profiles, and more.