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