On 8/7/23 07:05, KK CHN wrote:
On Mon, Aug 7, 2023 at 10:49 AM Ron <ronljohnso...@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.
