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 1qT07F-0083fa-In for pgsql-general@arkaria.postgresql.org; Mon, 07 Aug 2023 13:17:45 +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 1qT07D-009XS4-UA for pgsql-general@arkaria.postgresql.org; Mon, 07 Aug 2023 13:17:44 +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 1qT07D-009XRv-H7 for pgsql-general@lists.postgresql.org; Mon, 07 Aug 2023 13:17:43 +0000 Received: from mail-yw1-x1133.google.com ([2607:f8b0:4864:20::1133]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qT077-00168h-FF for pgsql-general@lists.postgresql.org; Mon, 07 Aug 2023 13:17:43 +0000 Received: by mail-yw1-x1133.google.com with SMTP id 00721157ae682-58688a0adbbso48222847b3.0 for ; Mon, 07 Aug 2023 06:17:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691414255; x=1692019055; 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=AppRKADKItuV4L9Lw3wxazRstyYuXXSrFtZCyr0+FRo=; b=ZUaDLZRwZtgylohsjhTTLQZ8tNY/raQrBlG6pllcmOBt6x4tuBPzBG0D6GUMRqc0ef S9/PNKMrYK1Yl5nkkSCs5m7Jv+ns/gLW5n9yCxzI4GfaBl9nx/ivNmm0cEWtXUWKhkCM n2+BLVJlWvanJT7wBpYa7On9qTkEpv/awKnbgTf6x2NcJqCqdnjzeBAkifDsdFcXdewa hTM9T4BzYDYXdsyOMuV3s/oXF5SJWqF2JhZpmSUjEYJAihQ83iojfPut++NdMdscnsVu EZk5AqHJqL9Z/FpwybhbeInGuNeGy/WZ+TCOXcKgpwcuq/tUH2RcpyDLy0KH0ESKvHJ4 es9Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691414255; x=1692019055; 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=AppRKADKItuV4L9Lw3wxazRstyYuXXSrFtZCyr0+FRo=; b=NIvbiWy4u61jwRFSRg5L8cX4h1r115WUtQ17q4XQCFKVnG0btSjYLyrxwHd2Kf1R7O rqzk3pdNfhSxGNzls+n8hpZJNal3E3DUZ0ytS6iZZpO6y/8QSA6p1CSo25Gz6ztcDB3z 4dn3Lga92+ERn5IB01hFQ4N/otv8UsZFC4K47sJ2usfBCJgttjgUOcnUMYmUdUQYjE26 OnLPpWzRf0bPQO3oQdmE80TaFhM1RB9sWmJYqlwBhWeMGeF7XcUhnLEL6VFZLJZYs7TU F7EtcFaPu6DDX6lrK6derCA3geJRi0NW7IQNUI2nwUTpy/+wegd9t33MPxcfCWmV0c08 b/Iw== X-Gm-Message-State: AOJu0YyrsSqVSbrFK4nRR9nmIfunlmf28Ld0HHw3xa/wX6XFoj1e1XbT XHc8NqW2zSrCNnEyJBP7w4ckHx1CJ2g= X-Google-Smtp-Source: AGHT+IFk5HErCuMz8DGfso+RzdStdONjsP/+uaAHRiFiwqumUAVt2Ego2p2FzzTB8FYlZriyqHv1uA== X-Received: by 2002:a0d:cc06:0:b0:572:83de:a011 with SMTP id o6-20020a0dcc06000000b0057283dea011mr9860393ywd.35.1691414255336; Mon, 07 Aug 2023 06:17:35 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:df7:89d:77c9:8e6a? ([2600:1700:bd2c:b810:df7:89d:77c9:8e6a]) by smtp.googlemail.com with ESMTPSA id s4-20020a0dd004000000b005704c4d3579sm2682771ywd.40.2023.08.07.06.17.34 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Aug 2023 06:17:34 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------zvB4GYF0ZyZD0VkLfnN00S3l" Message-ID: Date: Mon, 7 Aug 2023 08:17:34 -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: <3b5d46b0-4bcf-6d8a-d668-d8e52b4d050d@gmail.com> 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. --------------zvB4GYF0ZyZD0VkLfnN00S3l Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 8/7/23 07:05, KK CHN wrote: > > > On Mon, Aug 7, 2023 at 10:49 AM Ron wrote: > > 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. > ( I can't do for a remote machine .. But I can do  a WAL replication to another host in the same network inside the DC. So that If I  do a sqldump  or Copy of Data dir of the standby server it won't affect the production server, is this sounds good  ?  ) "WAL replication" is streaming replication.  Yes, you can do that. But to what end?  See my prior comments about when you should use PgBackRest. > >> >>  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 > > > What is the relevance of  globals-only It's all spelled out in the pg_dumpall man page. > and  what this will do  ${DB}.log  // or is it ${DB}.sql  ? > > pg_dump --format=d --verbose --jobs=$THREADS $DB &> ${DB}.log      // > .log couldn't get an idea what it mean > I get the impression that you don't have any experience with the bash shell. "&> ${DB}.log" redirects stdout and stderr to the file named ${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. --------------zvB4GYF0ZyZD0VkLfnN00S3l Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 8/7/23 07:05, KK CHN wrote:


On Mon, Aug 7, 2023 at 10:49 AM Ron <ronljohnsonjr@gmail.com> wrote:
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. 

( I can't do for a remote machine .. But I can do  a WAL replication to another host in the same network inside the DC. So that If I  do a sqldump  or Copy of Data dir of the standby server it won't affect the production server, is this sounds good  ?  )

"WAL replication" is streaming replication.  Yes, you can do that.

But to what end?  See my prior comments about when you should use PgBackRest.



 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

What is the relevance of  globals-only

It's all spelled out in the pg_dumpall man page.

and  what this will do  ${DB}.log  // or is it  ${DB}.sql  ?   

pg_dump --format=d --verbose --jobs=$THREADS $DB &> ${DB}.log      // .log couldn't get an idea what it mean

I get the impression that you don't have any experience with the bash shell.

"&> ${DB}.log" redirects stdout and stderr to the file named ${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.
--------------zvB4GYF0ZyZD0VkLfnN00S3l--