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 1qSseX-007eWu-NK for pgsql-general@arkaria.postgresql.org; Mon, 07 Aug 2023 05:19:38 +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 1qSseV-006aCE-VA for pgsql-general@arkaria.postgresql.org; Mon, 07 Aug 2023 05:19:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qSseV-006aC6-Jk for pgsql-general@lists.postgresql.org; Mon, 07 Aug 2023 05:19:36 +0000 Received: from mail-yb1-xb2b.google.com ([2607:f8b0:4864:20::b2b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qSseS-0011sL-N0 for pgsql-general@lists.postgresql.org; Mon, 07 Aug 2023 05:19:35 +0000 Received: by mail-yb1-xb2b.google.com with SMTP id 3f1490d57ef6-cfd4ea89978so4484683276.2 for ; Sun, 06 Aug 2023 22:19:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691385569; x=1691990369; h=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=F5TzpST1BcggP390F3rUcoYMFznNYMA+OnsEpE0PrUI=; b=iuWUNUBeBePczp+oYyeSespZPrmxGOuVLHzT736MKNh8WZ9vbDQXxET5U/tpmC1fRX 7kA/R0o64YixP47q8SPnu6t6j3/KzghFACN10Ar9DUSD/8GNN6rvtBLEgJ/2/aUbt/Vt DhD/BDOOAUNCqo9F7duqKO5zCaZdgrG7Y5OnocWDKqh/BSz2P0eCoiVTG2hinRHtGmFK 4KAHKbHyvZrNug6FDezUvyYDMUje2ItCy436qTRON57AiR30HtyovmWlYB6FabF5IJIm kkRk02TkJSVz/VXajy8QsAPRp9idDoMdZBx/ya2CVXeG0UEnn41WL6x7QFDNa1SwBi8J P1dQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691385569; x=1691990369; h=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=F5TzpST1BcggP390F3rUcoYMFznNYMA+OnsEpE0PrUI=; b=ccUnZHcS/OfMHO5HsPYBKGPieZPNFnkmZkAU/D4n0vSZAyBLMf49jSd5BVwBQww5Ar DI3mQmmdpZOtoI0Zw6hDpHVmNV187CJIHsYMErnakpa6Eva4IPFN8XEZPva1s/vHd6VE x2C6UwMoOX3RSE388pNda89Ar0yiOTh7W5i2tZZxGtya0YMJJBID1AtO2KbJ+h198XWO hGB73bADxDtjU8ft9RfeoPtxnaWEHQPGVXDB3UsC0AD5+ltTNrhjQXhlns8a28+8/9q6 sAstEH0QQJjASDYIPCwcDeQcQNH2X8IiebhwLAwy1eRtVYXOQclJ0div2u7ejTYey2RY caqg== X-Gm-Message-State: AOJu0YxAIfnG98hNLGhlZDV16eGjFP1IWTt+DfXj296pJck1dzUJPH/f VgP+SVga3WXxFhXfuV80dsUjIY/ifwI= X-Google-Smtp-Source: AGHT+IF8uZrpCxJ25hBgYpmXktS9G+PallnIy7AoP9wJoO5ivpRjsLe15MBUbPP8V6AmzkMXYtlR2Q== X-Received: by 2002:a05:6902:247:b0:d49:9ba4:bea8 with SMTP id k7-20020a056902024700b00d499ba4bea8mr6656413ybs.40.1691385569515; Sun, 06 Aug 2023 22:19:29 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:acc9:a51a:5ce3:d907? ([2600:1700:bd2c:b810:acc9:a51a:5ce3:d907]) by smtp.googlemail.com with ESMTPSA id c2-20020a258802000000b00d0c698ed6b6sm2090667ybl.41.2023.08.06.22.19.28 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 06 Aug 2023 22:19:29 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------M2C1OEPxf02ODcaGZ1mqHVKU" Message-ID: <3b5d46b0-4bcf-6d8a-d668-d8e52b4d050d@gmail.com> Date: Mon, 7 Aug 2023 00:19:28 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Backup Copy of a Production server. Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------M2C1OEPxf02ODcaGZ1mqHVKU Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 8/7/23 00:02, KK CHN wrote: > List, > > I am in need to copy a production PostgreSQL server  data( 1 TB)  to  an > external storage( Say USB Hard Drive) and need to set up a backup server > with this data dir. > > What is the trivial method to achieve this ?? > > 1. Is Sqldump an option at a production server ?? (  Will this affect the > server performance  and possible slowdown of the production server ? This > server has a high IOPS). This much size 1.2 TB will the Sqldump support ? > Any bottlenecks ? Whether or not there will be bottlenecks depends on how busy (CPU and disk load) the current server is. > > 2. Is copying the data directory from the production server to an external > storage and replace the data dir  at a  backup server with same postgres > version and replace it's data directory with this data dir copy is a > viable option ? > > > # cp  -r   ./data  /media/mydb_backup  ( Does this affect the Production > database server performance ??)   due to the copy command overhead ? > > > OR  doing a WAL Replication Configuration to a standby is the right method > to achieve this ?? But you say you can't establish a network connection outside the DC. > >  This is to take out the database backup outside the Datacenter and our DC > policy won't allow us to establish a network connection outside the DC to > a remote location for WAL replication . If you're unsure of what Linux distro & version and Postgresql version that you'll be restoring the database to, then the solution is: DB=the_database_you_want_to_backup THREADS= cd $PGDATA cp -v pg_hba.conf postgresql.conf /media/mydb_backup cd /media/mydb_backup pg_dumpall --globals-only > globals.sql pg_dump --format=d --verbose --jobs=$THREADS $DB &> ${DB}.log If you're 100% positive that the system you might someday restore to is *exactly* the same distro & version, and Postgresql major version, then I'd use PgBackRest. -- Born in Arizona, moved to Babylonia. --------------M2C1OEPxf02ODcaGZ1mqHVKU Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 8/7/23 00:02, KK CHN wrote:
List,

I am in need to copy a production PostgreSQL server  data( 1 TB)  to  an external storage( Say USB Hard Drive) and need to set up a backup server with this data dir.

What is the trivial method to achieve this ?? 

1. Is Sqldump an option at a production server ?? (  Will this affect the server performance  and possible slowdown of the production server ? This server has a high IOPS). This much size 1.2 TB will the Sqldump support ? Any bottlenecks ?

Whether or not there will be bottlenecks depends on how busy (CPU and disk load) the current server is.


2. Is copying the data directory from the production server to an external storage and replace the data dir  at a  backup server with same postgres version and replace it's data directory with this data dir copy is a viable option ?


# cp  -r   ./data  /media/mydb_backup  ( Does this affect the Production database server performance ??)   due to the copy command overhead ?


OR  doing a WAL Replication Configuration to a standby is the right method to achieve this ??

But you say you can't establish a network connection outside the DC.


 This is to take out the database backup outside the Datacenter and our DC policy won't allow us to establish a network connection outside the DC to a remote location for WAL replication .

If you're unsure of what Linux distro & version and Postgresql version that you'll be restoring the database to, then the solution is:
DB=the_database_you_want_to_backup
THREADS=<some_number>
cd $PGDATA
cp -v pg_hba.conf postgresql.conf
/media/mydb_backup
cd /media/mydb_backup
pg_dumpall --globals-only > globals.sql
pg_dump --format=d --verbose --jobs=$THREADS $DB &> ${DB}.log

If you're 100% positive that the system you might someday restore to is exactly the same distro & version, and Postgresql major version, then I'd use PgBackRest.

--
Born in Arizona, moved to Babylonia.
--------------M2C1OEPxf02ODcaGZ1mqHVKU--