pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Backup Copy of a Production server.
Date: Mon, 7 Aug 2023 08:17:34 -0500
Message-ID: <b6e8f78e-b5cc-a5ef-1964-fe3724d9f5ba@gmail.com> (raw)
In-Reply-To: <CAKgGyB9OuVCXcA11s6M6esS0KMUNFPbW_PEkNmD4zy44fLnx9w@mail.gmail.com>
References: <CAKgGyB8yf8Dh0bYCgdgRivhEWsF5iR2NX-Grn=0BHtkWFiQcJA@mail.gmail.com>
	<3b5d46b0-4bcf-6d8a-d668-d8e52b4d050d@gmail.com>
	<CAKgGyB9OuVCXcA11s6M6esS0KMUNFPbW_PEkNmD4zy44fLnx9w@mail.gmail.com>

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.

view thread (5+ messages)

Message-ID: <b6e8f78e-b5cc-a5ef-1964-fe3724d9f5ba@gmail.com>
Permalink:  ../b6e8f78e-b5cc-a5ef-1964-fe3724d9f5ba@gmail.com/
Also on:    postgresql.org/message-id/b6e8f78e-b5cc-a5ef-1964-fe3724d9f5ba@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: Backup Copy of a Production server.
  In-Reply-To: <b6e8f78e-b5cc-a5ef-1964-fe3724d9f5ba@gmail.com>
  References: <CAKgGyB8yf8Dh0bYCgdgRivhEWsF5iR2NX-Grn=0BHtkWFiQcJA@mail.gmail.com> <3b5d46b0-4bcf-6d8a-d668-d8e52b4d050d@gmail.com> <CAKgGyB9OuVCXcA11s6M6esS0KMUNFPbW_PEkNmD4zy44fLnx9w@mail.gmail.com> <b6e8f78e-b5cc-a5ef-1964-fe3724d9f5ba@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox