Geek Answers Handbook

PHP backs up the entire PostgreSQL database and then restores part of the table

I am currently backing up the entire database using pg_dump:

<?php include_once("../db.php"); $password = getPGPassword(); $user = getPGUser(); $db = getPGDb(); putenv("PGPASSWORD=" . $password); $dumpcmd = array("pg_dump", "-i", "-U", escapeshellarg($user), "-F", "c", "-b", "-v", "-f", escapeshellarg('/var/www/backups/backup-'.time().'.sql'), escapeshellarg($db)); exec( join(' ', $dumpcmd), $cmdout, $cmdresult ); putenv("PGPASSWORD"); ?>

I know that I can use psql to restore the entire database, but is there a way that I can selectively restore part of the table using a query? The simplest thing I can think of is creating a temporary database with psql, reading rows from the desired table, removing conflicting rows based on the primary serial key, and pasting into the table.

Is there a better way to do this? I need a full sql query.

+6
source share
1 answer

In my opinion, the simplest effective solution is:

  • install the backup server on another computer,
  • back up / restore on a regular basis or as needed,
  • Connect the primary and backup servers using the external postgres_fdw data wrapper.

In my practice, a backup server is required even for relatively small projects. Data replication can be done in many ways. Dumping / restoring (possibly using cron) is one of the easiest, but setting up streaming replication is also not particularly difficult.

If we need to consider costs, the backup server can be any PC or laptop with any operating system. I think this can be done easily even at home.

The benefits of having a backup server are manifold. This is sometimes an economical solution.

+1
source

More articles:


All Articles