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 ?
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 ??
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 .
Any hints most welcome ..
Thank you
Krishane