Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPqRu-00Ed6D-FV for pgsql-general@arkaria.postgresql.org; Tue, 16 Jan 2024 20:54:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rPqRt-007hID-97 for pgsql-general@arkaria.postgresql.org; Tue, 16 Jan 2024 20:54:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPqRs-007hI5-Tp for pgsql-general@lists.postgresql.org; Tue, 16 Jan 2024 20:54:16 +0000 Received: from mail-oa1-x2e.google.com ([2001:4860:4864:20::2e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rPqRp-001sKz-TK for pgsql-general@postgresql.org; Tue, 16 Jan 2024 20:54:15 +0000 Received: by mail-oa1-x2e.google.com with SMTP id 586e51a60fabf-21086556f32so356208fac.3 for ; Tue, 16 Jan 2024 12:54:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705438453; x=1706043253; darn=postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=s+NcOslX/W2qNNH5vj4stZDvun6NjuAfyKaHtMn5ytY=; b=CZdgyoOMLkULEgQRfUFuJqFX1eRk+SeuoEPZqrCXZn65ZHDSrxc6nZAGlohuR3yZyZ 4bDr4lGa7V5IpjW5Fns0UCoJfeXsrCtmI6ZuX6/B8+zdm0h+Rb786UKRPub2KGbsELWZ uT7Z9a5iydfwEmTrDKvY5zNWcieYQ+43f078/JRO5lS9m5ljthlB5f0CAXE7mlCt4539 EAKqgxav3LOZLWPpJh1nrj3QC5g0VpO4aKUmoc6Kk/lSzKKVqzLDJuN3eCBCudMIypk5 jnBh5NnR5emAz6XA6x28QbEaCU2RuYQ6wMDjf+87JGBBoks8hG0oNCbNyq3M+FsAm8H+ s2Pw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705438453; x=1706043253; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=s+NcOslX/W2qNNH5vj4stZDvun6NjuAfyKaHtMn5ytY=; b=uk5ttT88HejMZVCEwQr/wLyhTDbzoOyONl8Ti9U8YrbCLv9vy+jVgxeJ4PtU7vQny4 RiNKYRP5OEukvf6emijErHCKIb4mMypXy9NHAwWCRTTo8HqdwK9rdvZcLU+4o2Cm3P4I LlWZklOgQnJm/LToPhGB9aJffVR/uWk0Z5QbrNPNpzGg3njkB0g/EcfwwCtTiUVb6j7A g/SZ1Jkrm8YScvNErLfg5Ds+26ms5Wm+sO4VcU49ra23+hWu7pmwlwgAfmjJA2Y0TJDX stM1Bc8dBZD+Fcg7DTsIQ9IEGF1+yBr8RN1IntQi7xh5Ry6np/n1MoDYRDswnvtF3fdm 76ww== X-Gm-Message-State: AOJu0Yy4R/SPpgMD+J1I7+PMegqE802HajbOIzvMPU2CJWtJJbxRsMjJ idpNAI5XIgwUuzKBIcql8TzE2+EZ8gEb X-Google-Smtp-Source: AGHT+IF2mJlOtt6vGuvGk/UNOPPdpCY5kNHDqx8lIxLoA9U72brs5Br0OtJkDiUsP3BtuJ8lOkwO2w== X-Received: by 2002:a05:6870:9b0d:b0:203:e684:3a2a with SMTP id hq13-20020a0568709b0d00b00203e6843a2amr10857614oab.64.1705438453223; Tue, 16 Jan 2024 12:54:13 -0800 (PST) Received: from [192.168.2.16] (070-113-014-216.res.spectrum.com. [70.113.14.216]) by smtp.gmail.com with ESMTPSA id op7-20020a0568702dc700b002065818cb7fsm3178047oab.20.2024.01.16.12.54.12 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 16 Jan 2024 12:54:12 -0800 (PST) Message-ID: Date: Tue, 16 Jan 2024 14:54:10 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: pg_dump Running Slow Content-Language: en-US To: Yongye Serkfem , pgsql-general References: From: Jim Nasby In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/16/24 12:55 PM, Yongye Serkfem wrote: > Hello Engineers, > I trust you are all doing well. I need help on how to improve the speed > of pg_dump. I took a pg_dump on a 1TB database, which took almost a > whole day. I used this command: "pg_dump -U postgres -d dynamic -f > /backups/." Also, how do I check on the progression of the process? The advantage to pg_dump is that it's very simple and you'd have to work really hard to end up with a backup that won't restore. As such, I *always* recommend that it be *part* of your disaster recovery plan. It also directly supports only restoring one table. The flip-side is that all you get from pg_dump is (in one form or another) just a bunch of SQL that you'll then have to execute to restore. That process can be quite slow for anything but a very small cluster. (And as you've seen, pg_dump itself can be pretty slow.) While --jobs allows parallelizing that work, it's still a very expensive process. For clusters of any significant size you'll also want to look at some form of binary backup (aka: PITR) [1]. Since that operates directly at the OS level it's much faster than pg_dump on large clusters. The downside is there are lots of subtle ways to set it up incorrectly, resulting in backups that won't restore. Instead of trying to manage it by hand, I strongly recommend using a tool that's meant for managing Postgres binary backups. pgBackRest[2] and barman[3] are two popular choices for that. Most importantly: ALWAYS TEST YOUR BACKUPS. As I mentioned, it's pretty hard to screw up pg_dump, but you don't want to be trying to figure out how to restore in the middle of a disaster. Given all the subtlties involved with PITR I would actually recommend you test restoring *every* base backup you take, especially if you're not using one of the tools to manage it. BTW, one of the biggest advantages to those tools is that a lot of people use them, so any bugs (in the tool or in Postgres itself) are more likely to be found and eliminated. It's also a big advantage for cloud providers (as well as tools like PGO[4]): with many thousands of instances running regular backups it's harder for bugs to remain hidden. 1: https://www.postgresql.org/docs/current/continuous-archiving.html 2: https://pgbackrest.org/ 3: https://pgbarman.org/ 4: https://github.com/CrunchyData/postgres-operator -- Jim Nasby, Data Architect, Austin TX